Time model unioned with wait data to show waits combined with CPU timings

Time model unioned with wait data to show waits combined with CPU timings

rem *********************************************************** 
rem
rem File: timeModelSimple.sql 
rem Description: Time model unioned with wait data to show waits combined with CPU timings 
rem   
rem From 'Oracle Performance Survival Guide' by Guy Harrison
rem  Chapter 3 Page 73
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 ********************************************************* 


set lines 100
set pages 10000
set echo on 
column total_waits format  999999999


SELECT   event, total_waits,
         ROUND (time_waited_micro / 1000000) AS time_waited_secs,
         ROUND (time_waited_micro * 100 / 
            SUM (time_waited_micro) OVER (),2) AS pct_time
    FROM (SELECT event, total_waits, time_waited_micro
            FROM v$system_event
           WHERE wait_class <> 'Idle'
          UNION
          SELECT stat_name, NULL, VALUE
            FROM v$sys_time_model
           WHERE stat_name IN ('DB CPU', 'background cpu time'))
ORDER BY 3 DESC;