Is there a built-in Oracle feature that extends Real-Time SQL Monitoring to historical activity? I am specifically interested in getting wait events aggregated at the SQL operation level, not just per-statement.
Operation level information is available in
DBA_HIST_ACTIVE_SESS_HISTORY. Many AWR reports use that table, but do any of them display information at the operation level? Currently I am getting this data by joining
DBMS_XPLAN.DISPLAY_AWR, but it seems like there should be a built-in feature to do this for me. Before I build my own tool I want to ensure someone else hasn't already built it.
This missing feature may sound like a minor inconvenience but using the actual execution statistics, or even a sample of them, makes tuning so much easier. For example, below is a real SQL Monitoring Report for a recent problem, modified to fit here. With operation-level information there was no need to guess which step was slow, or even examine the waits. The problem was quickly found - the indexes were not disabled before a large
SQL Monitoring Report SQL Text ------------------------------ <SQL statement...> Global Information ------------------------------ <metadata like run status, session, sql_id, duration, etc...> Parallel Execution Details (DOP=64 , Servers Allocated=128) ... SQL Plan Monitoring Details (Plan Hash Value=2062479242) ================================================================================= | Id | Operation | Activity | Activity Detail | | | | (%) | (# samples) | ================================================================================= ... | 1 | PX COORDINATOR | 0.19 | enq: KO - fast object checkpoint (21)| | 2 | PX SEND QC (RANDOM) | | | | 3 | INDEX MAINTENANCE | 84.70 | buffer busy waits (2801) | | | | | enq: HW - contention (29) | | | | | enq: TX - allocate ITL entry (25) | | | | | free buffer waits (61734) | | | | | write complete waits (10365) | | | | | Cpu (341) | | | | | enq: FB - contention (1632) | | | | | enq: TX - contention (53) | | | | | db file sequential read (3717) | | | | | read by other session (641) | | 4 | PX RECEIVE | 0.02 | Cpu (18) | | | | | resmgr:cpu quantum (1) | ... =================================================================================
There are some other methods that produce similar information, such as tracing,
/*+ gather_plan_statistics */, or
alter session set statistics_level=all;. But those methods are only useful in a development environment. I don't have the luxury of being able to re-run statements with different settings.