oracel-通过ASH查看sql的耗时等待事件及对应的object

SET SERVEROUT ON SIZE UNL;
REM 通过ASH查看sql的耗时等待事件及对应的object
PRO 
PRO 
PRO 通过ASH查看sql的耗时等待事件及对应的object
SET TERM ON ECHO OFF;
SET VERIFY OFF;
col sql_id new_value sql_id noprint

set line 200
col event for a60
col object_name for a30
col current_obj# for 999999999
col wait_num for 999999999
col wait_per for a10
select event,object_name,current_obj#,wait_num,
       wait_per * 100 || '%' as wait_per
  from (select a.event,
               nvl(o.object_name,'Null Object') as object_name,
               a.current_obj#,
               a.wait_num,
               round(ratio_to_report(a.wait_num) over(), 2) as wait_per
          from (select /*+ rule */
                 nvl(event, 'on cpu(object_name may be not accurate)') as event,
                 current_obj#,
                 count(*) as wait_num
                  from v$active_session_history h
                 where h.sql_id = '&sql_id'
                   and h.sample_time >=
                       (select max(sql_exec_start)
                          from v$active_session_history i
                         where i.sql_id = h.sql_id)
                 group by event, current_obj#) a,
               dba_objects o
         where a.current_obj# = o.object_id(+)) s
 where s.wait_per >= 0.1
 order by s.wait_per desc;