Database Reference
In-Depth Information
Listing 10-6.
Script: ash_gcwait_to_sql_id_12c.sql
WITH ash_gc AS
(SELECT /*+ materialize */ inst_id, con_id,event, sql_id, COUNT(*) cnt
FROM gv$active_session_history
WHERE event=lower('&event')
GROUP BY inst_id, event, sql_id, con_id
HAVING COUNT (*) > &threshold )
SELECT inst_id,con_id, sql_id, cnt FROM ash_gc
ORDER BY cnt DESC
/
Note that PDB is an optional feature in version 12c, and if your database is a non-CDB, then the script in
Listing 10-4 is sufficient.
Understanding Wait Distribution
You should also identify the histogram of wait time, as averages can be misleading. Are a few occurrences of longer
waits for an event causing an increase in average response time? Or is the average wait time itself elevated? AWR
reports provide a section for wait event histograms too.
In Listing 10-7, histogram information about wait event is retrieved by querying gv$event_histogram view. The
following output shows that 99% of the waits for gc cr block 2-way wait event complete in under 4 ms. This output is
queried from a healthy database, and wait time is within the acceptable range.
Listing 10-7.
Script: event_histogram.sql
SELECT inst_id, event, wait_time_milli, wait_count,
TRUNC(100*(wait_count/tot),2) per
FROM
(SELECT inst_id, event, wait_time_milli, wait_count,
SUM (wait_count) over(partition BY inst_id, event
order by inst_id
rows BETWEEN unbounded preceding AND unbounded following
) tot
FROM
(SELECT * FROM gv$event_histogram
WHERE event LIKE '%&event_name%'
ORDER BY inst_id, event#, WAIT_TIME_MILLI
)
)
ORDER BY inst_id, event, WAIT_TIME_MILLI ;
Enter value for event_name: gc cr block 2-way
INST_ID EVENT WAIT_TIME_MILLI WAIT_COUNT PER
---------- ------------------------------ --------------- ---------- ----------
1 gc cr block 2-way 1 105310 2.03
1 gc cr block 2-way 2 3461802 66.87
1 gc cr block 2-way 4 1593929 30.79
1 gc cr block 2-way 8 15163 .29
1 gc cr block 2-way 16 340 0
1 gc cr block 2-way 32 26 0
...
