================================================================================
RT6 - SQL IN THE CURSOR CACHE
================================================================================
-- RT6.1 Per-execution profile of SQL currently being executed
-- Note : GV$SQL values are cumulative since the cursor was loaded.
SELECT q.inst_id, q.sql_id, q.child_number, q.plan_hash_value, q.executions, q.users_executing,
ROUND(q.elapsed_time/1e6/NULLIF(q.executions,0),2) AS ela_pe_s,
ROUND(q.cpu_time/1e6/NULLIF(q.executions,0),2) AS cpu_pe_s,
ROUND(q.user_io_wait_time/1e6/NULLIF(q.executions,0),2) AS io_pe_s,
ROUND(q.buffer_gets/NULLIF(q.executions,0)) AS gets_pe,
ROUND(q.disk_reads/NULLIF(q.executions,0)) AS reads_pe,
ROUND(q.rows_processed/NULLIF(q.executions,0),1) AS rows_pe,
q.module, q.last_active_time
FROM gv$sql q
WHERE (q.inst_id, q.sql_id, q.child_number) IN
(SELECT inst_id, sql_id, sql_child_number
FROM gv$session
WHERE type = 'USER' AND status = 'ACTIVE' AND sql_id IS NOT NULL)
ORDER BY ela_pe_s DESC NULLS LAST;
-- RT6.2 Top SQL active in the last hour by total elapsed (cumulative stats)
SELECT inst_id, sql_id, plan_hash_value, executions,
ROUND(elapsed_time/1e6) AS ela_s,
ROUND(cpu_time/1e6) AS cpu_s,
ROUND(elapsed_time/1e6/NULLIF(executions,0),2) AS ela_pe_s,
ROUND(buffer_gets/NULLIF(executions,0)) AS gets_pe,
ROUND(disk_reads/NULLIF(executions,0)) AS reads_pe,
last_active_time
FROM gv$sqlstats
WHERE last_active_time > SYSDATE - 1/24
ORDER BY elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;
-- RT6.3 One SQL_ID: all children and their per-execution cost
SELECT inst_id, sql_id, child_number, plan_hash_value, executions, users_executing,
ROUND(elapsed_time/1e6/NULLIF(executions,0),3) AS ela_pe_s,
ROUND(buffer_gets/NULLIF(executions,0)) AS gets_pe,
ROUND(disk_reads/NULLIF(executions,0)) AS reads_pe,
ROUND(rows_processed/NULLIF(executions,0),1) AS rows_pe,
optimizer_cost, is_bind_sensitive, is_bind_aware, is_shareable,
sql_profile, sql_plan_baseline, last_active_time
FROM gv$sql
WHERE sql_id = :sql_id
ORDER BY inst_id, child_number;
-- RT6.4 Plan with predicates and peeked binds (run on the instance holding the cursor)
-- Note : ALLSTATS / A-Rows only exist if STATISTICS_LEVEL=ALL or the
-- GATHER_PLAN_STATISTICS hint was used. For actual rows use RT7.2.
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(:sql_id, :child_no,
'TYPICAL +PEEKED_BINDS +PREDICATE +OUTLINE'));
-- RT6.5 Captured binds
SELECT inst_id, sql_id, child_number, position, name, datatype_string, last_captured, value_string
FROM gv$sql_bind_capture
WHERE sql_id = :sql_id
ORDER BY inst_id, child_number, position;
-- RT6.6 SQL with many child cursors (cursor-sharing problems)
SELECT inst_id, sql_id, COUNT(*) AS children, SUM(executions) AS execs,
SUBSTR(MAX(sql_text),1,120) AS sql_text
FROM gv$sql
GROUP BY inst_id, sql_id
HAVING COUNT(*) > 20
ORDER BY children DESC
FETCH FIRST 20 ROWS ONLY;
-- RT6.7 Why children were not shared (look for 'Y' columns)
SELECT *
FROM gv$sql_shared_cursor
WHERE sql_id = :sql_id
AND ROWNUM <= 20;
-- RT6.8 Optimizer settings that differ between children of one SQL_ID
SELECT inst_id, name,
COUNT(DISTINCT value) AS distinct_values,
LISTAGG(child_number || '=' || value, ', ') WITHIN GROUP (ORDER BY child_number) AS child_values
FROM gv$sql_optimizer_env
WHERE sql_id = :sql_id
GROUP BY inst_id, name
HAVING COUNT(DISTINCT value) > 1
ORDER BY inst_id, name;
================================================================================
RT7 - SQL MONITOR (TUNING PACK) AND LONGOPS
================================================================================
-- RT7.1 Monitored SQL executing now
SELECT inst_id, sid, session_serial#, sql_id, sql_exec_id, sql_exec_start, status,
username, module, px_servers_allocated,
ROUND(elapsed_time/1e6) AS ela_s,
ROUND(cpu_time/1e6) AS cpu_s,
ROUND(user_io_wait_time/1e6) AS io_s,
ROUND(application_wait_time/1e6) AS app_s,
ROUND(concurrency_wait_time/1e6) AS conc_s,
ROUND(cluster_wait_time/1e6) AS cluster_s,
buffer_gets, disk_reads, physical_read_requests
FROM gv$sql_monitor
WHERE status = 'EXECUTING'
ORDER BY elapsed_time DESC;
-- RT7.2 Actual rows / starts / reads per plan line for one execution
-- Read : E-rows vs A-rows gaps = cardinality problem; huge starts on an
-- index line with few rows returned higher up = excessive probing.
SELECT plan_line_id,
LPAD(' ', plan_depth) || plan_operation || ' ' || plan_options AS operation,
plan_object_owner, plan_object_name,
plan_cardinality AS e_rows,
starts,
output_rows AS a_rows,
physical_read_requests,
ROUND(physical_read_bytes/1048576) AS read_mb,
ROUND(workarea_mem/1048576) AS wa_mem_mb,
ROUND(workarea_tempseg/1048576) AS wa_temp_mb
FROM gv$sql_plan_monitor
WHERE sql_id = :sql_id
AND sql_exec_id = :sql_exec_id
AND inst_id = :inst_id
ORDER BY plan_line_id;
-- RT7.3 Where the time goes inside that execution (ASH by plan line)
SELECT sql_plan_line_id, sql_plan_operation, sql_plan_options,
NVL(event,'ON CPU') AS event, COUNT(*) AS samples
FROM gv$active_session_history
WHERE sql_id = :sql_id
AND sql_exec_id = :sql_exec_id
GROUP BY sql_plan_line_id, sql_plan_operation, sql_plan_options, event
ORDER BY samples DESC;
-- RT7.4 Text SQL Monitor report for one execution
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => :sql_id,
sql_exec_id => :sql_exec_id,
type => 'TEXT',
report_level => 'ALL')
FROM dual;
-- RT7.5 Long operations in progress (instrumented operations only)
-- Note : an empty result does NOT mean the session is idle.
SELECT l.inst_id, l.sid, l.serial#, s.module, l.opname, l.target, l.sofar, l.totalwork, l.units,
ROUND(l.sofar/NULLIF(l.totalwork,0)*100,1) AS pct_done,
l.elapsed_seconds, l.time_remaining, l.sql_id
FROM gv$session_longops l
JOIN gv$session s ON s.inst_id = l.inst_id AND s.sid = l.sid AND s.serial# = l.serial#
WHERE l.totalwork > 0
AND l.sofar < l.totalwork
ORDER BY l.elapsed_seconds DESC;
================================================================================
RT8 - I/O LATENCY NOW
================================================================================
-- RT8.1 Per-datafile read latency in the last metric interval
-- Read : average_read_time is in centiseconds -> *10 = ms. One file or
-- diskgroup much slower than the rest = storage hotspot.
SELECT f.inst_id, d.tablespace_name, d.file_name,
f.physical_reads,
ROUND(f.average_read_time * 10, 2) AS avg_read_ms,
f.physical_writes,
ROUND(f.average_write_time * 10, 2) AS avg_write_ms,
TO_CHAR(f.begin_time,'HH24:MI:SS') AS from_t,
TO_CHAR(f.end_time,'HH24:MI:SS') AS to_t
FROM gv$filemetric f
JOIN dba_data_files d ON d.file_id = f.file_id
WHERE f.physical_reads > 0
ORDER BY f.physical_reads DESC
FETCH FIRST 25 ROWS ONLY;
-- RT8.2 I/O wait profile by event, last 15 minutes (live ASH)
SELECT event, COUNT(*) AS samples,
COUNT(DISTINCT inst_id || ':' || session_id || ',' || session_serial#) AS sessions,
ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100,1) AS pct
FROM gv$active_session_history
WHERE sample_time > SYSDATE - 15/1440
AND wait_class = 'User I/O'
GROUP BY event
ORDER BY samples DESC;
================================================================================
RT9 - TEMP, PGA, WORKAREAS
================================================================================
-- RT9.1 TEMP consumers (real block size, not assumed 8 KB)
SELECT s.inst_id, s.sid, s.serial#, s.username, s.module, s.sql_id AS current_sql,
u.sql_id AS temp_sql_id, u.tablespace, u.segtype,
ROUND(u.blocks * t.block_size / 1048576) AS temp_mb
FROM gv$tempseg_usage u
JOIN gv$session s
ON s.inst_id = u.inst_id AND s.saddr = u.session_addr
JOIN dba_tablespaces t ON t.tablespace_name = u.tablespace
ORDER BY u.blocks DESC
FETCH FIRST 20 ROWS ONLY;
-- RT9.2 TEMP tablespace headroom
SELECT tablespace_name,
ROUND(tablespace_size/1048576) AS size_mb,
ROUND(allocated_space/1048576) AS allocated_mb,
ROUND(free_space/1048576) AS free_mb
FROM dba_temp_free_space;
-- RT9.3 Active sort/hash workareas (spills in flight)
-- Read : number_passes > 0 or large tempseg_mb = workarea spilling to TEMP.
SELECT inst_id, sid, sql_id, operation_type, policy,
ROUND(actual_mem_used/1048576) AS mem_mb,
ROUND(max_mem_used/1048576) AS max_mem_mb,
number_passes,
ROUND(tempseg_size/1048576) AS tempseg_mb
FROM gv$sql_workarea_active
ORDER BY tempseg_size DESC NULLS LAST, actual_mem_used DESC;
-- RT9.4 PGA overview per instance
SELECT inst_id, name, ROUND(value/1048576) AS mb_or_count
FROM gv$pgastat
WHERE name IN ('aggregate PGA target parameter','total PGA allocated',
'total PGA inuse','maximum PGA allocated','over allocation count')
ORDER BY inst_id, name;
================================================================================
RT10 - UNDO AND OPEN TRANSACTIONS
================================================================================
-- RT10.1 Open transactions by undo used, with owning session
-- Read : large used_ublk on an INACTIVE session = long uncommitted transaction.
SELECT s.inst_id, s.sid, s.serial#, s.username, s.module, s.action, s.status,
s.last_call_et, s.sql_id, s.prev_sql_id,
t.start_time, t.used_ublk, t.used_urec,
ROUND(t.used_ublk * TO_NUMBER(p.value) / 1048576) AS undo_mb,
t.status AS txn_status
FROM gv$transaction t
JOIN gv$session s ON s.inst_id = t.inst_id AND s.taddr = t.addr
JOIN gv$parameter p ON p.inst_id = t.inst_id AND p.name = 'db_block_size'
ORDER BY t.used_ublk DESC;
-- RT10.2 Undo extent status
SELECT tablespace_name, status, ROUND(SUM(bytes)/1048576) AS mb
FROM dba_undo_extents
GROUP BY tablespace_name, status
ORDER BY tablespace_name, status;
================================================================================
RT11 - DRILL INTO ONE SESSION
================================================================================
-- RT11.1 Session identity and OS process
SELECT s.inst_id, s.sid, s.serial#, s.audsid, s.username, s.osuser, s.machine,
s.program, s.module, s.action, s.client_identifier,
p.spid AS os_pid, s.logon_time, s.status, s.last_call_et
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.inst_id = :inst_id
AND s.sid = :sid;
-- RT11.2 Session wait totals since logon
SELECT event, wait_class, total_waits,
ROUND(time_waited_micro/1e6,1) AS waited_s,
ROUND(time_waited_micro/1e3/NULLIF(total_waits,0),2) AS avg_ms
FROM gv$session_event
WHERE inst_id = :inst_id
AND sid = :sid
AND wait_class <> 'Idle'
ORDER BY time_waited_micro DESC;
-- RT11.3 Key session statistics since logon
SELECT n.name, st.value
FROM gv$sesstat st
JOIN gv$statname n ON n.inst_id = st.inst_id AND n.statistic# = st.statistic#
WHERE st.inst_id = :inst_id
AND st.sid = :sid
AND n.name IN ('CPU used by this session','session logical reads',
'physical reads','physical read IO requests',
'parse count (hard)','execute count','user commits',
'redo size','session pga memory','session pga memory max')
ORDER BY n.name;
-- RT11.4 What this session did in the last 30 minutes (live ASH)
SELECT sql_id, NVL(event,'ON CPU') AS event, COUNT(*) AS samples,
MIN(sample_time) AS first_seen, MAX(sample_time) AS last_seen
FROM gv$active_session_history
WHERE inst_id = :inst_id
AND session_id = :sid
AND session_serial# = :serial
AND sample_time > SYSDATE - 30/1440
GROUP BY sql_id, event
ORDER BY samples DESC;
================================================================================
RT12 - RAC AND RESOURCE LIMITS
================================================================================
-- RT12.1 Cluster waits in the last 15 minutes by event and object
SELECT h.inst_id, h.event, o.owner, o.object_name, COUNT(*) AS samples
FROM gv$active_session_history h
LEFT JOIN dba_objects o ON o.object_id = h.current_obj#
WHERE h.sample_time > SYSDATE - 15/1440
AND h.wait_class = 'Cluster'
GROUP BY h.inst_id, h.event, o.owner, o.object_name
ORDER BY samples DESC
FETCH FIRST 20 ROWS ONLY;
-- RT12.2 Sessions / processes vs limits
SELECT inst_id, resource_name, current_utilization, max_utilization,
initial_allocation, limit_value
FROM gv$resource_limit
WHERE resource_name IN ('processes','sessions','transactions','parallel_max_servers')
ORDER BY inst_id, resource_name;
================================================================================
QUICK TRIAGE ORDER
================================================================================
1. RT1.1 / RT1.3 -> CPU, I/O, locks or RAC? Where is the load?
2. RT4.1 -> If anyone is blocked, the blocker is the RCA first.
3. RT3.1 / RT3.3 -> Which EBS requests are running / stuck pending?
4. RT2.1 / RT2.3 -> Which SQL and modules dominate the last 15 minutes?
5. RT6.1 / RT7.2 -> Per-exec cost and actual rows for the top SQL.
6. RT8.1 -> Is storage latency abnormal right now?
7. RT9 / RT10 -> TEMP spills, long transactions, undo.
================================================================================
END OF PACK
================================================================================
