ASH & AWR Performance Tuning Pack
A RAC-aware script for Oracle DBAs (11gR2–19c+)
This script collection provides a powerful toolkit for diagnosing performance issues in Oracle databases using the Active Session History (ASH) and Automatic Workload Repository (AWR). It's designed to be RAC-aware and works on versions from 11gR2 to 19c and beyond.
Requirements: SELECT_CATALOG_ROLE. AWR queries require the Oracle Diagnostics Pack license.
set pages 200 lines 200 trimspool on long 100000 longchunksize 100000
col event format a50 trunc
col wait_class format a20 trunc
col username format a20
col module format a35 trunc
col sql_text format a80 trunc
col object_name format a40 trunc
col object_type format a18 trunc
col segment_name format a35 trunc
set verify off
-- ---- Parameters (adjust as you like)
DEF mins_back = 60
DEF days_back = 1
DEF top_n = 5
DEF hour_from = 9
DEF hour_to = 11
DEF start_ts = '2012-11-14 22:00'
DEF end_ts = '2012-11-14 23:00'
DEF sqlid = '&&sqlid' -- set when needed
1. Top Recent Wait Events (ASH)
Shows the top wait events from the last &mins_back minutes.
WITH ash AS (
SELECT /*+ MATERIALIZE */
event, wait_class,
(wait_time + time_waited) AS wt_us
FROM gv$active_session_history
WHERE sample_time >= systimestamp - ( &mins_back / (24*60) )
AND event IS NOT NULL
)
SELECT * FROM (
SELECT event,
wait_class,
ROUND(SUM(wt_us)/1e6,2) AS total_wait_s,
ROUND(100*SUM(wt_us)/NULLIF(SUM(SUM(wt_us)) OVER (),0),1) AS pct
FROM ash
GROUP BY event, wait_class
ORDER BY total_wait_s DESC
)
FETCH FIRST &top_n ROWS ONLY;
2. Top Wait Class Since Startup
Aggregates wait times by class across the entire system since the last startup.
SELECT wait_class,
SUM(time_waited) AS time_waited_cs,
ROUND(SUM(time_waited)/100,1) AS time_waited_s
FROM v$system_event e JOIN v$event_name n ON n.event_id = e.event_id
WHERE n.wait_class <> 'Idle'
GROUP BY wait_class
ORDER BY time_waited_cs DESC;
3. Users Currently Waiting (non-Idle)
Lists active user sessions that are currently in a non-idle wait state.
SELECT s.inst_id, s.sid, s.serial#, s.username, s.module,
s.event, s.wait_class, s.state, s.seconds_in_wait
FROM gv$session s
WHERE s.type = 'USER'
AND s.username IS NOT NULL
AND s.state = 'WAITING'
AND s.wait_class <> 'Idle'
ORDER BY s.seconds_in_wait DESC;
4. Main DB Wait Events in a Time Interval (AWR)
First, find the snapshot range for your desired time window.
SELECT MIN(snap_id) begin_snap, MAX(snap_id) end_snap,
TO_CHAR(MIN(begin_interval_time),'YYYY-MM-DD HH24:MI') begin_time,
TO_CHAR(MAX(end_interval_time) ,'YYYY-MM-DD HH24:MI') end_time
FROM dba_hist_snapshot
WHERE end_interval_time BETWEEN TO_DATE('&&start_ts','YYYY-MM-DD HH24:MI')
AND TO_DATE('&&end_ts','YYYY-MM-DD HH24:MI');
Then, define begin_snap and end_snap and run the query below to see top events in that range.
-- DEF begin_snap = 12345
-- DEF end_snap = 12350
-- SELECT * FROM (
-- SELECT h.event, h.wait_class,
-- ROUND(SUM(h.time_waited)/1e6,2) total_wait_s
-- FROM dba_hist_active_sess_history h
-- WHERE h.snap_id BETWEEN &begin_snap AND &end_snap
-- AND h.event IS NOT NULL
-- GROUP BY h.event, h.wait_class
-- ORDER BY total_wait_s DESC
-- ) FETCH FIRST &top_n ROWS ONLY;
5. Top CPU-consuming SQL in a Time Window (AWR)
Identifies the most CPU-intensive SQL statements between specific hours over the last &days_back days.
SELECT * FROM (
SELECT a.sql_id,
ROUND(SUM(a.cpu_time_delta)/1e6,2) cpu_s,
ROUND(SUM(a.elapsed_time_delta)/1e6,2) ela_s,
SUM(a.executions_delta) execs
FROM dba_hist_sqlstat a
JOIN dba_hist_snapshot s ON s.snap_id = a.snap_id
WHERE s.begin_interval_time >= TRUNC(SYSDATE) - &days_back
AND EXTRACT(HOUR FROM s.end_interval_time) BETWEEN &hour_from AND &hour_to
GROUP BY a.sql_id
ORDER BY cpu_s DESC
) FETCH FIRST &top_n ROWS ONLY;
6. Objects with Most Waits in Past Hour (ASH)
Pinpoints which database objects (tables, indexes) have been sources of contention recently.
SELECT * FROM (
SELECT o.owner||'.'||o.object_name AS object_name,
o.object_type,
a.event,
ROUND(SUM(a.wait_time + a.time_waited)/1e6,2) AS total_wait_s
FROM gv$active_session_history a
JOIN dba_objects o ON o.object_id = a.current_obj#
WHERE a.sample_time BETWEEN SYSTIMESTAMP - (1/24) AND SYSTIMESTAMP
AND a.event IS NOT NULL
GROUP BY o.owner, o.object_name, o.object_type, a.event
ORDER BY total_wait_s DESC
) FETCH FIRST &top_n ROWS ONLY;
7. Top Segments by Physical Reads
Shows which segments are responsible for the most physical I/O.
SELECT * FROM (
SELECT owner||'.'||object_name AS segment_name,
object_type,
value AS total_physical_reads
FROM v$segment_statistics
WHERE statistic_name = 'physical reads'
ORDER BY total_physical_reads DESC
) FETCH FIRST &top_n ROWS ONLY;
8. Top SQL (ASH) in Past Hour
Finds the SQL statements with the highest total wait time in the last hour.
SELECT * FROM (
SELECT a.sql_id,
u.username,
ROUND(SUM(a.wait_time + a.time_waited)/1e6,2) AS total_wait_s,
MIN(sa.sql_text) KEEP (DENSE_RANK FIRST ORDER BY SUM(a.wait_time + a.time_waited) DESC) AS sql_text
FROM gv$active_session_history a
LEFT JOIN v$sqlarea sa ON sa.sql_id = a.sql_id
LEFT JOIN dba_users u ON u.user_id = a.user_id
WHERE a.sample_time >= SYSTIMESTAMP - (1/24)
AND a.sql_id IS NOT NULL
GROUP BY a.sql_id, u.username
ORDER BY total_wait_s DESC
) FETCH FIRST &top_n ROWS ONLY;
9. SQL with Highest I/O (reads) Past Day (AWR)
Identifies SQL that spent the most time on I/O wait events in the last 24 hours.
SELECT * FROM (
SELECT h.sql_id,
COUNT(*) AS ash_secs
FROM dba_hist_active_sess_history h
JOIN dba_hist_snapshot x
ON x.snap_id = h.snap_id
AND x.dbid = h.dbid
AND x.instance_number = h.instance_number
WHERE x.begin_interval_time > SYSDATE - 1
AND h.event IN ('db file sequential read','db file scattered read')
GROUP BY h.sql_id
ORDER BY ash_secs DESC
) FETCH FIRST &top_n ROWS ONLY;
10. Top CPU since Past Day (AWR)
Shows the top CPU consumers from the last day, along with their disk reads and execution counts.
SELECT * FROM (
SELECT a.sql_id,
ROUND(SUM(a.cpu_time_delta)/1e6,2) cpu_s,
ROUND(SUM(a.disk_reads_delta),0) disk_reads,
SUM(a.executions_delta) execs
FROM dba_hist_sqlstat a
JOIN dba_hist_snapshot s ON s.snap_id = a.snap_id
WHERE s.begin_interval_time > SYSDATE - 1
GROUP BY a.sql_id
ORDER BY cpu_s DESC
) FETCH FIRST &top_n ROWS ONLY;
11. Top SQL at a Reported Hour
First, find the specific snapshot ID for the hour you want to investigate.
SELECT snap_id, TO_CHAR(begin_interval_time,'YYYY-MM-DD HH24:MI') begin_time,
TO_CHAR(end_interval_time,'YYYY-MM-DD HH24:MI') end_time
FROM dba_hist_snapshot
WHERE end_interval_time BETWEEN TO_DATE('&&start_ts','YYYY-MM-DD HH24:MI')
AND TO_DATE('&&end_ts','YYYY-MM-DD HH24:MI')
ORDER BY snap_id;
Then, define the snapid and run the query below to see the top SQL by disk reads for that snapshot.
-- After you pick a specific &snapid:
-- SELECT * FROM (
-- SELECT sql.sql_id,
-- sql.buffer_gets_delta buf_gets,
-- sql.disk_reads_delta disk_reads,
-- ROUND(sql.iowait_delta/1e6,2) io_wait_s,
-- ROUND(sql.cpu_time_delta/1e6,2) cpu_s,
-- ROUND(sql.elapsed_time_delta/1e6,2) ela_s
-- FROM dba_hist_sqlstat sql
-- WHERE sql.snap_id = &snapid
-- ORDER BY disk_reads DESC
-- ) FETCH FIRST &top_n ROWS ONLY;
12. Trends for One SQL_ID (last day)
Tracks the performance metrics of a specific sql_id over the last day, snapshot by snapshot.
SELECT s.snap_id,
TO_CHAR(s.begin_interval_time,'YYYY-MM-DD HH24:MI') AS begin_time,
sql.executions_delta AS execs,
sql.buffer_gets_delta AS buffer_gets,
sql.disk_reads_delta AS disk_reads,
ROUND(sql.iowait_delta/1e6,2) AS io_wait_s,
ROUND(sql.cpu_time_delta/1e6,2) AS cpu_s,
ROUND(sql.elapsed_time_delta/1e6,2) AS ela_s
FROM dba_hist_sqlstat sql
JOIN dba_hist_snapshot s ON s.snap_id = sql.snap_id
WHERE s.begin_interval_time > SYSDATE - 1
AND sql.sql_id = '&&sqlid'
ORDER BY s.begin_interval_time;
13. Plans observed for SQL_ID
Checks if a specific SQL statement is "flipping" between different execution plans, which can cause performance instability.
SELECT sql_id,
plan_hash_value,
SUM(executions_delta) AS executions,
SUM(rows_processed_delta) AS rows_processed,
TRUNC(SUM(cpu_time_delta)/1e6/60) AS cpu_mins,
TRUNC(SUM(elapsed_time_delta)/1e6/60) AS ela_mins
FROM dba_hist_sqlstat
WHERE sql_id = '&&sqlid'
GROUP BY sql_id, plan_hash_value
ORDER BY cpu_mins DESC;
14. Top 5 SQL by ADDM Benefit (last 7 days)
Lists the SQL statements that the Automatic Database Diagnostic Monitor (ADDM) has identified as having the most potential for improvement.
SELECT * FROM (
SELECT b.attr1 AS sql_id, MAX(a.benefit) AS benefit
FROM dba_advisor_recommendations a
JOIN dba_advisor_objects b
ON a.task_id = b.task_id AND a.rec_id = b.object_id
WHERE a.task_id IN (
SELECT DISTINCT t.task_id
FROM dba_advisor_tasks t
JOIN dba_advisor_log l ON l.task_id = t.task_id AND l.status='COMPLETED'
WHERE t.advisor_name = 'ADDM'
AND t.created >= SYSDATE - 7
)
AND LENGTH(b.attr4) > 1
GROUP BY b.attr1
ORDER BY MAX(a.benefit) DESC
) FETCH FIRST 5 ROWS ONLY;
No comments:
Post a Comment