rem *********************************************************** 
rem
rem	File: top_level_waits.sql 
rem	Description: Break down of top level WAITCLASS waits
rem   
rem	From 'Oracle Performance Survival Guide' by Guy Harrison
rem		Chapter 23 Page 669
rem		ISBN: 978-0137011957
rem		See www.guyharrison.net for further information
rem  
rem		This work is in the public domain NSA 
rem   
rem
rem ********************************************************* 


col time_cat format a20 heading "Time category"
col time_secs format 999,999.99 "Time (s)"
col pct format 99.99 "Time|pct"
set pagesize 10000
set lines 80
set echo on 

SELECT wait_class time_cat, ROUND((time_secs), 2) time_secs,
       ROUND((time_secs) * 100 / SUM(time_secs) OVER (), 2) pct
FROM (SELECT wait_class wait_class,
             SUM(time_waited_micro) / 1000000 time_secs
      FROM gv$system_event
      WHERE wait_class <> 'Idle' AND time_waited > 0
      GROUP BY wait_class
      UNION
      SELECT 'CPU', ROUND((SUM(VALUE) / 1000000), 2) time_secs
      FROM gv$sys_time_model
      WHERE stat_name IN ('background cpu time', 'DB CPU'))
ORDER BY time_secs DESC;