query used in AIT episode 78 to retrieve the dbms_hprof results

V_EXECUTION_STATS.sql

create or replace force view v_execution_stats as
with execution_stats as (
  select fi.runid,
         fi.symbolid,
         pci.parentsymid,
         rtrim(fi.module || '.' || nullif(fi.function,fi.module), '.') as unit,
         nvl(pci.subtree_elapsed_time, fi.subtree_elapsed_time)/1000000 as subtree_elapsed_time_seconds,
         nvl(pci.function_elapsed_time, fi.function_elapsed_time)/1000000 as function_elapsed_time_seconds,
         fi.line#,
         nvl(pci.calls, fi.calls) as calls,
         namespace,
         sql_id, 
         sql_text
  from   dbmshp_function_info fi
  left join dbmshp_parent_child_info pci 
  on     fi.runid = pci.runid 
  and    fi.symbolid = pci.childsymid
  where  fi.module != 'DBMS_HPROF'
), execution_tree as (
  select rownum execution_order,
         runid, 
         rpad('.', (level-1)*2, '.') || unit as unit,
         line#,
         subtree_elapsed_time_seconds,
         function_elapsed_time_seconds,
         calls,
         namespace,
         sql_id, 
         sql_text,
         level cb_level
  from   execution_stats
  start with parentsymid is null
  connect by parentsymid = prior symbolid
  and runid = prior runid
)
  select runid, 
         execution_order,
         unit,
         line#,
         subtree_elapsed_time_seconds,
         function_elapsed_time_seconds,
         calls,
         namespace,
         sql_id, 
         sql_text,
         cb_level,
         rank() over (partition by runid order by subtree_elapsed_time_seconds desc) as time_rank
  from execution_tree
  order by execution_order

Credit to Chris Saxon for the basis of this view : https://devgym.oracle.com/pls/apex/dg/class/databases-for-developers-performance.html