When tuning a database, it is useful to see at a glance the top five timed events for a range of time periods as in the following listing. It shows how much time was consumed by the top five timed events as well as average active sessions (AAS) and percentage of database time that the events represent.
SNAP_TIME EVENT SECONDS AAS PCT
----------- ---------------------------- -------- ---- -----
01/07 01:00 CPU used by this session 127.05 0.04 47.32
RMAN backup & recovery I/O 74.75 0.02 27.84
control file parallel write 17.59 0.00 6.55
db file sequential read 16.89 0.00 6.29
control file sequential read 10.56 0.00 3.93
01/07 02:00 log file sync 269.49 0.07 36.58
log file parallel write 266.31 0.07 36.15
CPU used by this session 151.23 0.04 20.53
db file sequential read 31.48 0.01 4.27
control file parallel write 15.52 0.00 2.11
01/07 03:00 log file parallel write 867.97 0.24 31.45
CPU used by this session 653.38 0.18 23.67
log file sync 608.85 0.17 22.06
db file scattered read 273.92 0.08 9.93
log buffer space 121.16 0.03 4.39
01/07 04:00 log file parallel write 2,169.10 0.60 30.36
CPU used by this session 1,362.13 0.38 19.06
db file scattered read 1,308.39 0.36 18.31
db file sequential read 871.71 0.24 12.20
log file sequential read 606.94 0.17 8.49
01/07 05:00 db file scattered read 3,154.11 0.88 82.63
db file sequential read 264.16 0.07 6.92
CPU used by this session 102.78 0.03 2.69
log file parallel write 101.61 0.03 2.66
control file parallel write 84.06 0.02 2.20
01/07 06:00 db file scattered read 3,327.81 0.92 71.27
CPU used by this session 827.02 0.23 17.71
db file sequential read 261.75 0.07 5.61
read by other session 122.51 0.03 2.62
control file parallel write 58.31 0.02 1.25
The following query can be used to produce such a listing. It makes extensive use of "
subquery factoring" and "
analytic functions." The query uses the data from the Statspack tables but can easily be modified to use AWR tables. Note that you may only use AWR if you have licenses for both Oracle Enterprise Edition as well as
Diagnostics Pack.
set linesize 132
set pagesize 10000
set tab off
set trimout on
set trimspool on
set sqlblanklines on
alter session set nls_date_format = 'mm/dd hh24:mi';
column event format a30
column time_spent_d format 9,999,999.90
column aas format 9999.90
column percentage format 999.90
break on snap_id on snap_time skip 1
WITH
timed_events AS
-- Get wait time from STATS$SYSTEM_EVENT.
-- Select snapshots from start_snap_id to end_snap_id.
-- Convert wait microseconds into seconds.
-- Ignore the "Idle" class (wait_class = 6).
-- Wait classifications are found in V$EVENT_NAME.
-- Get CPU usage from the STATS$SYSSTAT table.
-- Convert CPU centiseconds into seconds.
(SELECT snap_id,
event,
time_waited_micro / 1000000 AS time_spent
FROM stats$system_event NATURAL JOIN v$event_name
WHERE snap_id between
&&start_snap_id and &&end_snap_id
AND wait_class# != 6
UNION ALL
SELECT snap_id,
'CPU used by this session' AS event,
VALUE / 100 as time_spent
FROM stats$sysstat
WHERE snap_id between
&&start_snap_id and &&end_snap_id
AND name = 'CPU used by this session'),
------------------------------------------------------------
deltas AS
-- Use the LAG function to determine the increase.
-- Partition the rows by database startup time.
-- STATS$SNAPSHOT tells us when the database was started.
(SELECT snap_id,
snap_time,
event,
(snap_time - LAG (snap_time)
OVER (PARTITION BY startup_time, event
ORDER BY snap_id)) * 86400
AS snap_time_d,
time_spent - LAG (time_spent)
OVER (PARTITION BY startup_time, event
ORDER BY snap_id)
AS time_spent_d
FROM timed_events NATURAL JOIN stats$snapshot),
------------------------------------------------------------
ranks AS
-- Use the RANK function to rank the events.
-- Also compute the percentage contribution of each event.
(SELECT snap_id,
snap_time,
event,
snap_time_d,
time_spent_d,
RANK()
OVER (PARTITION BY snap_id
ORDER BY time_spent_d DESC)
AS rank,
DECODE(time_spent_d,
0, 0,
time_spent_d / SUM(time_spent_d)
OVER (PARTITION BY snap_id) * 100)
AS percentage
FROM deltas
WHERE time_spent_d IS NOT NULL)
------------------------------------------------------------
-- Compute Average Active Sessions for each category.
-- List the top 5 events.
SELECT snap_id,
snap_time,
rank,
substr(event, 1, 30) as event,
time_spent_d,
time_spent_d / snap_time_d as aas,
percentage
FROM ranks
WHERE rank <= 5
ORDER BY snap_id,
rank;
