- Deducing individual runtime of specific SQL statements using ASH
- Identifying culprit SQL for transient slowdowns.
- Finding outlier executions for a specific SQL
Finding Outlier Executions for a Specific SQL
You may already know the problematic SQL ID but want to find which execution deviated from the norm. Use this query:
-- Identify slowest executions for a known SQL ID
-- Dated: May 2025 | Author: Yuan Yao
DEFINE sql_id = 'a8ntu3081hfgw'
SET tab OFF
SET linesize 200
SELECT session_id,
session_serial#,
sql_plan_hash_value,
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_id='&sql_id'
AND sample_time > TRUNC(sysdate)
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;
The output is:
SESSION_ID SESSION_SERIAL# SQL_PLAN_HASH_VALUE EXEC_START END_TIME RUN_TIME
_____________ __________________ ______________________ _____________ _______________ ______________________
410 3575 365293266 15:24:02 15:27:07.791 +00 00:03:05.791000
20 3370 592714042 15:20:58 15:21:13.728 +00 00:00:15.728000
267 58588 592714042 15:20:44 15:20:53.721 +00 00:00:09.721000
140 33463 592714042 15:20:44 15:20:53.721 +00 00:00:09.721000
271 45890 592714042 15:20:44 15:20:53.721 +00 00:00:09.721000
410 3575 592714042 15:22:15 15:22:23.749 +00 00:00:08.749000
140 33463 592714042 15:22:15 15:22:23.749 +00 00:00:08.749000
20 3370 592714042 15:22:15 15:22:23.749 +00 00:00:08.749000
271 45890 592714042 15:22:15 15:22:23.749 +00 00:00:08.749000
410 3575 592714042 15:20:45 15:20:53.721 +00 00:00:08.721000
10 rows selected.
This shows that the execution starting at 15:24:02 was an outlier, running over 3 minutes, while other executions were much faster. The distinct sql_plan_hash_value (365293266 vs. 592714042) suggests an inefficient execution plan as the likely cause.
Yuan Yao, An Oracle ACE's Blog 
Leave a reply to Identifying Transient Performance Issues via SQL Runtime Deduction from ASH Data (Part 2/3) – Yuan Yao, An Oracle ACE's Blog Cancel reply