Correlation Analysis Reports with AWR and ASH Views

For those tuning with the Oracle Wait Interface, there are interesting statistics that relate to system-wide wait events from dba_hist_waitstat with detailed wait event information from dba_hist_active_sess_history.

COLUMN	DESCRIPTION
SNAP_ID	Unique snapshot ID
DBID	Database ID for the snapshot
INSTANCE_NUMBER	Instance number for the snapshot
CLASS	Class of the block
WAIT_COUNT	Number of waits by the OPERATION for this CLASS of block
TIME	Sum of all wait times for all the waits by the OPERATION for this CLASS of block

Table 4.14 - dba_hist_waitstat statistics used to wait event analysis

For advanced correlation analysis, the DBA would seek to identify correlations between instance-wide wait events and block-level waits.  This is a critical way of using human insight and of using the AWR and ASH information to isolate the exact file and object where the wait contention is occurring.