system_times.sql

This script reports the total waiting time for both routine and resource waits, together with the CPU time used. The relative contribution of each type of wait to overall response times, and the potential benefit of tuning actions, can be estimated from this information.

select
e.event,
e.time_waited
from
sys.v_$system_event e
where
e.event != 'Null event' and
e.event != 'rdbms ipc message' and
e.event != 'pipe get' and
e.event != 'virtual circuit status' and
e.event != 'lock manager wait for remote message' and
e.event not like '% timer' and
e.event not like 'SQL*Net message from %'
union all
select
s.name,
s.value
from
sys.v_$sysstat s
where
s.name = 'CPU used by this session'
order by
2 desc
/