1. Basic ASH Evidence by SQL_ID
SELECT ash.sql_id,
ash.sql_plan_hash_value,
ash.session_state,
ash.wait_class,
ash.event,
COUNT(*) ash_samples
FROM dba_hist_active_sess_history ash
WHERE ash.sql_id = '&SQL_ID'
AND ash.sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
GROUP BY ash.sql_id,
ash.sql_plan_hash_value,
ash.session_state,
ash.wait_class,
ash.event
ORDER BY ash_samples DESC;
Use: First-level proof of where SQL spent time.
2. ASH Wait Class Percentage
SELECT wait_class,
event,
COUNT(*) ash_samples,
ROUND(COUNT(*) * 100 / SUM(COUNT(*)) OVER (), 2) pct_of_total
FROM dba_hist_active_sess_history
WHERE sql_id = '&SQL_ID'
AND sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
GROUP BY wait_class, event
ORDER BY ash_samples DESC;
Use: Identify dominant bottleneck percentage.
3. SQL_ID Activity by RAC Instance
SELECT instance_number,
COUNT(*) ash_samples,
ROUND(COUNT(*) * 100 / SUM(COUNT(*)) OVER (), 2) pct_of_total
FROM dba_hist_active_sess_history
WHERE sql_id = '&SQL_ID'
AND sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
GROUP BY instance_number
ORDER BY ash_samples DESC;
Use: Check whether issue was on one RAC node or all nodes.
4. RAC Instance-wise Wait Class Drilldown
SELECT instance_number,
wait_class,
event,
COUNT(*) ash_samples
FROM dba_hist_active_sess_history
WHERE sql_id = '&SQL_ID'
AND sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
GROUP BY instance_number,
wait_class,
event
ORDER BY instance_number, ash_samples DESC;
Use: Identify node-specific waits like Cluster, User I/O, or CPU.
5. SQL_ID Timeline by Minute
SELECT TO_CHAR(sample_time, 'DD-MON-YYYY HH24:MI') sample_minute,
COUNT(*) ash_samples
FROM dba_hist_active_sess_history
WHERE sql_id = '&SQL_ID'
AND sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
GROUP BY TO_CHAR(sample_time, 'DD-MON-YYYY HH24:MI')
ORDER BY sample_minute;
Use: Show when the SQL was most active.
6. Wait Event Timeline by Minute
SELECT TO_CHAR(sample_time, 'DD-MON-YYYY HH24:MI') sample_minute,
wait_class,
event,
COUNT(*) ash_samples
FROM dba_hist_active_sess_history
WHERE sql_id = '&SQL_ID'
AND sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
GROUP BY TO_CHAR(sample_time, 'DD-MON-YYYY HH24:MI'),
wait_class,
event
ORDER BY sample_minute, ash_samples DESC;
Use: Shows whether SQL moved from CPU to I/O, TEMP, lock, or RAC wait.
7. Session-Level ASH Drilldown
SELECT instance_number,
session_id,
session_serial#,
sql_id,
sql_plan_hash_value,
wait_class,
event,
COUNT(*) ash_samples
FROM dba_hist_active_sess_history
WHERE sql_id = '&SQL_ID'
AND sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
GROUP BY instance_number,
session_id,
session_serial#,
sql_id,
sql_plan_hash_value,
wait_class,
event
ORDER BY ash_samples DESC;
Use: Find which session contributed most to the issue.
8. Module and Action Drilldown
SELECT module,
action,
wait_class,
event,
COUNT(*) ash_samples
FROM dba_hist_active_sess_history
WHERE sql_id = '&SQL_ID'
AND sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
GROUP BY module,
action,
wait_class,
event
ORDER BY ash_samples DESC;
Use: Helpful for EBS module/action visibility.
9. Program and Machine Drilldown
SELECT program,
machine,
wait_class,
event,
COUNT(*) ash_samples
FROM dba_hist_active_sess_history
WHERE sql_id = '&SQL_ID'
AND sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
GROUP BY program,
machine,
wait_class,
event
ORDER BY ash_samples DESC;
Use: Identify whether activity came from concurrent manager node, app tier, or user machine.
10. SQL Plan Hash Value Seen in ASH
SELECT sql_id,
sql_plan_hash_value,
COUNT(*) ash_samples,
MIN(sample_time) first_seen,
MAX(sample_time) last_seen
FROM dba_hist_active_sess_history
WHERE sql_id = '&SQL_ID'
AND sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
GROUP BY sql_id,
sql_plan_hash_value
ORDER BY ash_samples DESC;
Use: Check whether the SQL used one or multiple plans during issue.
11. Wait Class by Plan Hash Value
SELECT sql_plan_hash_value,
wait_class,
event,
COUNT(*) ash_samples
FROM dba_hist_active_sess_history
WHERE sql_id = '&SQL_ID'
AND sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
GROUP BY sql_plan_hash_value,
wait_class,
event
ORDER BY sql_plan_hash_value, ash_samples DESC;
Use: Compare bad plan vs good plan wait behavior.
12. Plan Operation Causing Wait
SELECT sql_plan_line_id,
sql_plan_operation,
sql_plan_options,
wait_class,
event,
COUNT(*) ash_samples
FROM dba_hist_active_sess_history
WHERE sql_id = '&SQL_ID'
AND sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
GROUP BY sql_plan_line_id,
sql_plan_operation,
sql_plan_options,
wait_class,
event
ORDER BY ash_samples DESC;
Use: Identify the execution plan step causing most wait.
13. Object-Level Hotspot by SQL_ID
SELECT ash.current_obj#,
obj.owner,
obj.object_name,
obj.object_type,
ash.wait_class,
ash.event,
COUNT(*) ash_samples
FROM dba_hist_active_sess_history ash
LEFT JOIN dba_objects obj
ON ash.current_obj# = obj.object_id
WHERE ash.sql_id = '&SQL_ID'
AND ash.sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
GROUP BY ash.current_obj#,
obj.owner,
obj.object_name,
obj.object_type,
ash.wait_class,
ash.event
ORDER BY ash_samples DESC;
Use: Find the table/index object involved in I/O, RAC, or buffer contention.
14. User I/O Specific Evidence
SELECT event,
current_obj#,
obj.owner,
obj.object_name,
obj.object_type,
COUNT(*) ash_samples
FROM dba_hist_active_sess_history ash
LEFT JOIN dba_objects obj
ON ash.current_obj# = obj.object_id
WHERE ash.sql_id = '&SQL_ID'
AND ash.wait_class = 'User I/O'
AND ash.sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
GROUP BY event,
current_obj#,
obj.owner,
obj.object_name,
obj.object_type
ORDER BY ash_samples DESC;
Use: Prove physical I/O issue: full scan, index read, direct read.
15. TEMP Wait Evidence
SELECT event,
sql_plan_line_id,
sql_plan_operation,
sql_plan_options,
COUNT(*) ash_samples
FROM dba_hist_active_sess_history
WHERE sql_id = '&SQL_ID'
AND sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
AND (event LIKE '%temp%' OR event IN ('direct path read temp',
'direct path write temp'))
GROUP BY event,
sql_plan_line_id,
sql_plan_operation,
sql_plan_options
ORDER BY ash_samples DESC;
Use: Identify sort/hash spill to TEMP.
16. Blocking Evidence by SQL_ID
SELECT instance_number,
session_id,
session_serial#,
blocking_inst_id,
blocking_session,
blocking_session_serial#,
event,
wait_class,
COUNT(*) ash_samples
FROM dba_hist_active_sess_history
WHERE sql_id = '&SQL_ID'
AND sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
AND blocking_session IS NOT NULL
GROUP BY instance_number,
session_id,
session_serial#,
blocking_inst_id,
blocking_session,
blocking_session_serial#,
event,
wait_class
ORDER BY ash_samples DESC;
Use: Prove if SQL was delayed due to row lock/blocking.
17. RAC Cluster Wait Evidence
SELECT instance_number,
event,
current_obj#,
obj.owner,
obj.object_name,
obj.object_type,
COUNT(*) ash_samples
FROM dba_hist_active_sess_history ash
LEFT JOIN dba_objects obj
ON ash.current_obj# = obj.object_id
WHERE ash.sql_id = '&SQL_ID'
AND ash.wait_class = 'Cluster'
AND ash.sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
GROUP BY instance_number,
event,
current_obj#,
obj.owner,
obj.object_name,
obj.object_type
ORDER BY ash_samples DESC;
Use: Prove RAC global cache contention.
18. CPU vs Waiting Split
SELECT CASE
WHEN session_state = 'ON CPU' THEN 'ON CPU'
ELSE 'WAITING'
END activity_type,
COUNT(*) ash_samples,
ROUND(COUNT(*) * 100 / SUM(COUNT(*)) OVER (), 2) pct_of_total
FROM dba_hist_active_sess_history
WHERE sql_id = '&SQL_ID'
AND sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
GROUP BY CASE
WHEN session_state = 'ON CPU' THEN 'ON CPU'
ELSE 'WAITING'
END
ORDER BY ash_samples DESC;
Use: Quickly classify CPU-bound vs wait-bound SQL.
19. Top Sessions by CPU Samples
SELECT instance_number,
session_id,
session_serial#,
sql_plan_hash_value,
COUNT(*) cpu_samples
FROM dba_hist_active_sess_history
WHERE sql_id = '&SQL_ID'
AND session_state = 'ON CPU'
AND sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
GROUP BY instance_number,
session_id,
session_serial#,
sql_plan_hash_value
ORDER BY cpu_samples DESC;
Use: Identify CPU-heavy sessions.
20. Top Sessions by Waiting Samples
SELECT instance_number,
session_id,
session_serial#,
wait_class,
event,
COUNT(*) wait_samples
FROM dba_hist_active_sess_history
WHERE sql_id = '&SQL_ID'
AND session_state = 'WAITING'
AND sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
GROUP BY instance_number,
session_id,
session_serial#,
wait_class,
event
ORDER BY wait_samples DESC;
Use: Identify session-specific wait bottleneck.
21. Parallel Query Evidence by QC Session
SELECT qc_instance_id,
qc_session_id,
qc_session_serial#,
instance_number,
session_id,
session_serial#,
sql_id,
wait_class,
event,
COUNT(*) ash_samples
FROM dba_hist_active_sess_history
WHERE sql_id = '&SQL_ID'
AND sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
AND qc_session_id IS NOT NULL
GROUP BY qc_instance_id,
qc_session_id,
qc_session_serial#,
instance_number,
session_id,
session_serial#,
sql_id,
wait_class,
event
ORDER BY qc_instance_id,
qc_session_id,
ash_samples DESC;
Use: Confirm if SQL ran in parallel and identify QC/slave wait behavior.
22. Parallel Wait Events for SQL_ID
SELECT event,
wait_class,
COUNT(*) ash_samples
FROM dba_hist_active_sess_history
WHERE sql_id = '&SQL_ID'
AND sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
AND (event LIKE 'PX%'
OR event LIKE 'parallel%'
OR qc_session_id IS NOT NULL)
GROUP BY event,
wait_class
ORDER BY ash_samples DESC;
Use: Identify PX skew, downgrade, producer/consumer bottleneck.
23. SQL_ID by Service / Consumer Group
SELECT service_hash,
consumer_group_id,
wait_class,
event,
COUNT(*) ash_samples
FROM dba_hist_active_sess_history
WHERE sql_id = '&SQL_ID'
AND sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
GROUP BY service_hash,
consumer_group_id,
wait_class,
event
ORDER BY ash_samples DESC;
Use: Helps detect Resource Manager / service-based behavior.
24. Previous SQL Evidence for Same Sessions
Sometimes the session moved from your SQL_ID to another SQL, or your SQL_ID appears as PREV_SQL_ID.
SELECT sql_id,
prev_sql_id,
wait_class,
event,
COUNT(*) ash_samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI')
AND (sql_id = '&SQL_ID' OR prev_sql_id = '&SQL_ID')
GROUP BY sql_id,
prev_sql_id,
wait_class,
event
ORDER BY ash_samples DESC;
Use: Useful when SQL_ID is not visible in AWR but appears as previous SQL.
25. Final RCA Summary Query by SQL_ID
SELECT '&SQL_ID' sql_id,
MIN(sample_time) first_sample_time,
MAX(sample_time) last_sample_time,
COUNT(*) total_ash_samples,
COUNT(DISTINCT instance_number) rac_instances,
COUNT(DISTINCT session_id || '-' || session_serial#) distinct_sessions,
COUNT(DISTINCT sql_plan_hash_value) distinct_plan_hash_values,
SUM(CASE WHEN session_state = 'ON CPU' THEN 1 ELSE 0 END) cpu_samples,
SUM(CASE WHEN session_state = 'WAITING' THEN 1 ELSE 0 END) waiting_samples,
ROUND(SUM(CASE WHEN session_state = 'ON CPU' THEN 1 ELSE 0 END)
* 100 / COUNT(*), 2) cpu_pct,
ROUND(SUM(CASE WHEN session_state = 'WAITING' THEN 1 ELSE 0 END)
* 100 / COUNT(*), 2) wait_pct
FROM dba_hist_active_sess_history
WHERE sql_id = '&SQL_ID'
AND sample_time BETWEEN TO_TIMESTAMP('&START_TIME','DD-MON-YYYY HH24:MI')
AND TO_TIMESTAMP('&END_TIME','DD-MON-YYYY HH24:MI');
Use: Good final evidence summary for RCA.
Performance DBA Interpretation Template
After running the above queries, write the conclusion like this:
ASH analysis for SQL_ID <SQL_ID> during <START_TIME> to <END_TIME> shows <TOTAL_SAMPLES> ASH samples.
The dominant wait class was <WAIT_CLASS>, mainly due to <WAIT_EVENT>, contributing approximately <PERCENTAGE>% of SQL activity.
The SQL was active on <INSTANCE_COUNT> RAC instance(s), with most activity on instance <INSTANCE_NUMBER>. The SQL used plan hash value <PLAN_HASH_VALUE>. ASH evidence shows the main bottleneck was <CPU / USER I/O / TEMP / BLOCKING / RAC CLUSTER / PARALLEL WAIT>.
Based on this evidence, the probable root cause is <ROOT_CAUSE>. No direct database session kill is recommended. The SQL/request should first be mapped to EBS request/user and any terminate/rerun decision should be business-approved.
Quick Decision Matrix
| ASH Result | Probable Root Cause |
|---|---|
High ON CPU | High logical reads, inefficient plan, high data volume |
High User I/O | Full scan, index access issue, physical I/O pressure |
High direct path read/write temp | Sort/hash spill to TEMP |
High Application | Blocking / row lock contention |
High Cluster | RAC global cache contention |
High PX waits | Parallel query skew or PX resource issue |
| Multiple plan hash values | Plan instability / regression |
| One session dominates | Single request issue |
| Many sessions dominate | Duplicate requests, PX, or concurrent workload |
No comments:
Post a Comment