Oracle Top Wait Events

oracle_top_wait_events.sql

select
    name,
    round(time_secs, 2) time_secs,
    case when time_secs = 0 then
        0
    else
        round(time_secs*100 / sum(time_secs) Over(), 2)
    end pct
from
    (
        select
            e.event Name,
            e.time_waited / 100 time_secs
        from
            v$system_event e
            join v$event_name n on n.name = e.event
        where
            n.wait_class <> 'Idle'
        and
            time_waited > 0

        UNION

        select
            'server CPU',
            sum(value / 1000000) time_secs
        from
            v$sys_time_model
        where
            stat_name in ('background cpu time', 'DB CPU' )
    )
order by
    time_secs DESC;