oracle-查看ASH过去5分钟的top会话信息

REM 查看ASH过去5分钟的top会话信息
pro
prompt 查看ASH过去5分钟的top会话信息

set line 400
col sql_id for a20
col sql_num for 999999
col sql_weight for a10
col event for a40
col object_name for a33
col program for a30
col module_name for a30

select sql_id,
       sql_num,
       sql_weight,
       event,
       object_name,
       program,
       module_name
  from (select /*+ rule */
         sql_id,
         count(*) as sql_num,
         max(event) as event,
         max(program) as program,
         max(module) as module_name,
         max(obj.object_name) as object_name,
         round(ratio_to_report(count(*)) over(), 2) * 100 as sql_ratio,
         round(ratio_to_report(count(*)) over(), 2) * 100 || '%' as sql_weight
          from gv$active_session_history ash
          left join dba_objects obj
            on obj.object_id = ash.current_obj#
         where sample_time between sysdate - 5 / 24 / 60 and sysdate
           and sql_id is not null
         group by sql_id)
 where sql_ratio > 0
 order by sql_ratio desc;