数据库等待事件分类

可以用等待事件查询结果对接监控平台，实时展示数据库等待事件，反应数据库是否忙碌

with class as
 (select '01 CPU+CPU Wait' def_class, 'CPU+CPU Wait' wait_class
    from dual
  union
  select '02 Scheduler' def_class, 'Scheduler' wait_class
    from dual
  union
  select '03 User I/O' def_class, 'User I/O' wait_class
    from dual
  union
  select '04 System I/O' def_class, 'System I/O' wait_class
    from dual
  union
  select '05 Concurrency' def_class, 'Concurrency' wait_class
    from dual
  union
  select '06 Application' def_class, 'Application' wait_class
    from dual
  union
  select '07 Commit' def_class, 'Commit' wait_class
    from dual
  union
  select '08 Configuration' def_class, 'Configuration' wait_class
    from dual
  union
  select '09 Administrative' def_class, 'Administrative' wait_class
    from dual
  union
  select '10 Network' def_class, 'Network' wait_class
    from dual
  union
  select '11 Queueing' def_class, 'Queueing' wait_class
    from dual
  union
  select '12 Cluster' def_class, 'Cluster' wait_class
    from dual
  union
  select '13 Other' def_class, 'Other' wait_class
    from dual)
select to_char(sample_time -
               mod(to_char(sample_time, 'ss'), 15) / 24 / 60 / 60,
               'yyyy-mm-dd hh24:mi:ss') sample_time,
       class.def_class wait_class,
       count(*) / 15 act_num
  from gv$active_session_history ash, class
 where ash.SAMPLE_TIME >= trunc(sysdate, 'MI') - 2 / 24 / 60
   and ash.SAMPLE_TIME < trunc(sysdate, 'MI')
   and nvl(ash.WAIT_CLASS, 'CPU+CPU Wait') = class.wait_class
 group by to_char(sample_time -
                  mod(to_char(sample_time, 'ss'), 15) / 24 / 60 / 60,
                  'yyyy-mm-dd hh24:mi:ss'),
          class.def_class
 order by 1, 2;