1. Deducing individual runtime of specific SQL statements using ASH
2. Identifying culprit SQL for transient slowdowns.
3. Finding outlier executions for a specific SQL

Identifying Culprit SQL for Transient Slowdowns

This method is very useful to locate culprit SQL statements behind random and transient slowdown. Image the following dialog:

User: The application suddenly took a long time to process a request!

DBA: Can you show me what happened?

User: No, it was a random occurrence.

DBA: Has this happened before?

User: No, it seemed like a one-off issue.

DBA: How long does this request usually take?

User: It usually takes a few seconds.

DBA: When did this slow response occur?

User: About two hours ago.

DBA: Can you provide a more specific time?

User: No, I’m not sure exactly when it happened.

To address the user’s complaint, we run the following query to capture 10 longest SQL executions within a one-hour window before and after two hours ago (you can easily change to your desired time window):

SET tab OFF
SET linesize 200

SELECT session_id,
       session_serial#,
       sql_id,
       TO_CHAR(sql_exec_start, 'HH24:MI:SS') AS exec_start,
       TO_CHAR(MAX(sample_time), 'HH24:MI:SS.FF3') AS end_time,
       MAX(sample_time) - sql_exec_start AS run_time
FROM dba_hist_active_sess_history
WHERE sql_exec_start BETWEEN SYSDATE - INTERVAL '3' HOUR - INTERVAL '30' MINUTE
                         AND SYSDATE - INTERVAL '1' HOUR + INTERVAL '30' MINUTE
  AND sql_exec_id > 0
GROUP BY session_id,
         session_serial#,
         sql_id,
         sql_plan_hash_value,
         sql_exec_id,
         sql_exec_start
ORDER BY run_time DESC
FETCH FIRST 10 ROWS ONLY;

An output example:

In this case, 3cmkzt241wvu8, which ran for over 1 minute, stands out as a likely cause.