查看ASH指定时间内特定存储过程中的top-sql执行耗时

• &proc_name
• &begin_time
• &end_time

REM 查看ASH指定时间内特定存储过程中的top-sql执行耗时
pro
prompt 指定时间段内,特定存储过程中的top-sql耗时
set line 200
set pages 1000
set verify off
col event for a50

with t_sql as
 (select /*+ rule */
   sql_id, event, count(*) as elapsed_time
    from gv$active_session_history
   where to_char(sample_time, 'yyyy-mm-dd hh24:mi:ss') >=
         '&begin_time'
     and to_char(sample_time, 'yyyy-mm-dd hh24:mi:ss') <=
         '&end_time'
     and plsql_entry_object_id in
         (select object_id
            from dba_objects
           where object_name = upper('&proc_name'))
     and sql_id is not null
   group by sql_id, event)
select sqlid, most_event, total_time
  from (select a.sql_id     as sqlid,
               b.event      as most_event,
               a.total_time as total_time
          from (select sql_id, sum(elapsed_time) as total_time
                  from t_sql
                 group by sql_id) a,
               (select sql_id, event
                  from (select sql_id,
                               event,
                               row_number() over(partition by sql_id order by elapsed_time desc) as time_rank
                          from t_sql)
                 where time_rank = 1) b
         where a.sql_id = b.sql_id
         order by a.total_time desc) c
 where rownum <= 10;
undefine proc_name
undefine begin_time
undefine end_time