AWR & ASH

Review SQL statements with the highest elapsed-time consumption during a selected AWR interval.

SELECT
    ss.sql_id,
    ss.plan_hash_value,
    sn.begin_interval_time,
    sn.end_interval_time,
    ss.executions_delta,
    ROUND(ss.elapsed_time_delta / 1000000, 2)
        AS elapsed_seconds,
    ROUND(
        CASE
            WHEN ss.executions_delta > 0
            THEN ss.elapsed_time_delta / ss.executions_delta / 1000
        END,
        2
    ) AS avg_elapsed_ms
FROM dba_hist_sqlstat ss
JOIN dba_hist_snapshot sn
    ON sn.snap_id = ss.snap_id
   AND sn.dbid = ss.dbid
   AND sn.instance_number = ss.instance_number
WHERE sn.begin_interval_time >= SYSDATE - 7
ORDER BY ss.elapsed_time_delta DESC
FETCH FIRST 20 ROWS ONLY;

Inspect interval-level executions, CPU, logical reads and physical reads for a specific SQL statement.

SELECT
    ss.snap_id,
    sn.begin_interval_time,
    sn.end_interval_time,
    ss.sql_id,
    ss.plan_hash_value,
    ss.executions_delta,
    ROUND(ss.elapsed_time_delta / 1000000, 2)
        AS elapsed_seconds,
    ROUND(ss.cpu_time_delta / 1000000, 2)
        AS cpu_seconds,
    ss.buffer_gets_delta,
    ss.disk_reads_delta
FROM dba_hist_sqlstat ss
JOIN dba_hist_snapshot sn
    ON sn.snap_id = ss.snap_id
   AND sn.dbid = ss.dbid
   AND sn.instance_number = ss.instance_number
WHERE ss.sql_id = '&sql_id'
ORDER BY ss.snap_id DESC;

Review interval changes in non-idle wait activity and identify events that accumulated significant wait time.

SELECT
    se.event_name,
    se.wait_class,
    SUM(se.total_waits) AS total_waits,
    ROUND(SUM(se.time_waited_micro) / 1000000, 2)
        AS wait_seconds
FROM dba_hist_system_event se
JOIN dba_hist_snapshot sn
    ON sn.snap_id = se.snap_id
   AND sn.dbid = se.dbid
   AND sn.instance_number = se.instance_number
WHERE sn.begin_interval_time >= SYSDATE - 7
  AND se.wait_class <> 'Idle'
GROUP BY
    se.event_name,
    se.wait_class
ORDER BY wait_seconds DESC
FETCH FIRST 20 ROWS ONLY;

Identify sessions repeatedly sampled as active and review their SQL, session state and wait information.

SELECT
    session_id,
    session_serial#,
    sql_id,
    session_state,
    event,
    wait_class,
    COUNT(*) AS samples
FROM v$active_session_history
WHERE sample_time >= SYSTIMESTAMP - INTERVAL '1' HOUR
GROUP BY
    session_id,
    session_serial#,
    sql_id,
    session_state,
    event,
    wait_class
ORDER BY samples DESC
FETCH FIRST 20 ROWS ONLY;

Group ASH samples by SQL identifier to see which statements were repeatedly active during a selected period.

SELECT
    sql_id,
    session_state,
    wait_class,
    COUNT(*) AS samples,
    MIN(sample_time) AS first_sample,
    MAX(sample_time) AS last_sample
FROM v$active_session_history
WHERE sample_time >= SYSTIMESTAMP - INTERVAL '1' HOUR
  AND sql_id IS NOT NULL
GROUP BY
    sql_id,
    session_state,
    wait_class
ORDER BY samples DESC
FETCH FIRST 20 ROWS ONLY;

Summarize sampled wait events and correlate them with SQL to understand where active database time was spent.

SELECT
    event,
    wait_class,
    sql_id,
    COUNT(*) AS samples,
    MIN(sample_time) AS first_sample,
    MAX(sample_time) AS last_sample
FROM v$active_session_history
WHERE sample_time >= SYSTIMESTAMP - INTERVAL '1' HOUR
  AND session_state = 'WAITING'
  AND wait_class <> 'Idle'
GROUP BY
    event,
    wait_class,
    sql_id
ORDER BY samples DESC
FETCH FIRST 20 ROWS ONLY;