Performance Issue RCA — Deep Dive Query Workbook

 

ORACLE EBS 12.2  |  DATABASE 19c

Performance Issue RCA — Deep Dive Query Workbook

Production troubleshooting pack for slow concurrent requests, SQL regressions, blocking, waits, statistics, and space. Queries are generic (no SID, host, or client names). Prefer GV$ on RAC.

Classification: Internal DBA use  |  Diagnostics Pack required for DBA_HIST_* and historical ASH  |  26-Sep-2026

1. Purpose and rules of engagement

Use this workbook when a production incident needs evidence: which session, which SQL_ID, which wait class, whether the plan changed, and whether EBS concurrent processing is the entry point.

Always

•       Start live (GV$ / V$ / FND) before AWR. History cannot explain a session that is blocked right now.

•       Record request_id, audsid, inst_id, sid, serial#, sql_id, plan_hash_value, wait event, and the exact time window.

•       Treat elapsed time as a product of executions × time-per-exec × data volume. A higher total does not prove a regression.

•       On RAC query GV$ so you do not miss the instance that owns the session.

•       Do not change SQL, stats, or parameters until the wait class and plan_hash are documented.

Note: Most DBA_HIST_% views require the Oracle Diagnostics Pack. Confirm licensing before running historical ASH/AWR in production.

2. Incident decision tree

Symptom	First path
Concurrent running longer than normal	FND_CONCURRENT_REQUESTS → GV$SESSION (audsid) → GV$SQL → LONGOPS / SQL Monitor → ASH
SQL fast yesterday, slow today	DBA_HIST_SNAPSHOT → DBA_HIST_SQLSTAT → DBA_HIST_SQL_PLAN → DBA_HIST_ACTIVE_SESS_HISTORY
Sessions blocked or hung	GV$SESSION → V$WAIT_CHAINS → GV$LOCK → GV$LOCKED_OBJECT
High CPU or DB time	GV$ACTIVE_SESSION_HISTORY → DBA_HIST_SYS_TIME_MODEL → top SQL by elapsed
Suspected stale statistics	DBA_TAB_STATISTICS → DBA_TAB_MODIFICATIONS → DBA_OPTSTAT_OPERATIONS
Large GL archive / purge	FND request → SQL Monitor → GV$TRANSACTION → temp/undo → DBA_SEGMENTS
Temp or tablespace pressure	GV$TEMPSEG_USAGE → DBA_FREE_SPACE → DBA_HIST_TBSPC_SPACE_USAGE

3. Views used in production RCA

3.1 Historical AWR and ASH

View	Why it is used
DBA_HIST_SNAPSHOT	Snap IDs and interval times for the incident window
DBA_HIST_ACTIVE_SESS_HISTORY	SQL_ID, event, blocking, object# over time
DBA_HIST_SQLSTAT	Elapsed, CPU, gets, reads, rows, executions, plan_hash
DBA_HIST_SQLTEXT	Full SQL text from AWR
DBA_HIST_SQL_PLAN	Historical plans / access path change
DBA_HIST_SQLBIND	Captured binds for the window
DBA_HIST_SYSTEM_EVENT	Wait event deltas by snapshot
DBA_HIST_SYS_TIME_MODEL	DB time vs DB CPU vs parse time
DBA_HIST_SEG_STAT	Hot segment I/O and block contention
DBA_HIST_TBSPC_SPACE_USAGE	Tablespace growth during the incident
DBA_HIST_RESOURCE_LIMIT	Sessions / processes vs limit
DBA_HIST_DATABASE_INSTANCE	Instance startup and role context

3.2 Real-time SQL and sessions

View	Why it is used
GV$SESSION	Live sid, sql_id, event, blocking, module, audsid
GV$ACTIVE_SESSION_HISTORY	Recent samples (seconds to ~1 hour)
GV$SQL / GV$SQLAREA	Cursor-cache elapsed, gets, text, children
GV$SQL_PLAN / DISPLAY_CURSOR	Current plan + actuals if gathered
GV$SQL_MONITOR	Monitored long SQL (batch / PX)
GV$SQL_BIND_CAPTURE	Binds for the current child
GV$SESSION_LONGOPS	Instrumented long operations only
GV$PROCESS	OS PID for OS-level evidence
GV$TRANSACTION	Undo used by open transactions
GV$LOCK / GV$LOCKED_OBJECT	Enqueue and object holders
GV$TEMPSEG_USAGE	Who is consuming TEMP
GV$SESSTAT / GV$SYSSTAT	Parse, I/O, CPU counters

3.3 Locks, waiters, plans extras

View	Why it is used
V$SESSION_BLOCKERS	Direct blocker pairs
V$WAIT_CHAINS	Full wait chain (best on RAC)
V$SESSION_EVENT / V$SYSTEM_EVENT	Wait totals session / instance
DBA_BLOCKERS / DBA_WAITERS	Simple lock wait list
V$SQL_SHARED_CURSOR	Why a SQL_ID has many children
V$SQL_OPTIMIZER_ENV	Optimizer parameters of a child
V$SQL_WORKAREA_ACTIVE	PGA / TEMP spill in flight

3.4 Optimizer statistics and storage

View	Why it is used
DBA_TAB_STATISTICS	Stale / missing table stats
DBA_TAB_MODIFICATIONS	DML since last gather
DBA_IND_STATISTICS / DBA_INDEXES	Index stats and UNUSABLE
DBA_SEGMENTS / DBA_EXTENTS	Size and file#/block# map
DBA_TABLESPACES / DBA_DATA_FILES / DBA_FREE_SPACE	Space headroom
DBA_OPTSTAT_OPERATIONS	Last stats jobs

3.5 EBS concurrent processing (APPS)

Table / view	Why it is used
FND_CONCURRENT_REQUESTS	Request status, times, audsid, arguments
FND_CONCURRENT_PROGRAMS(_TL)	Program name and enabled flag
FND_EXECUTABLES	Executable / method
FND_CONCURRENT_QUEUES	Manager capacity vs running
FND_CONCURRENT_PROCESSES	Live manager OS/DB process
FND_CONCURRENT_PROGRAM_SERIAL	Incompatibility holding a request

4. Session setup (run once)

ALTER SESSION SET nls_date_format = 'YYYY-MM-DD HH24:MI:SS';

ALTER SESSION SET nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS.FF';

 

-- Substitution variables used below

-- DEFINE SQL_ID     = 'xxxxxxxxxxxxxxxx'

-- DEFINE REQUEST_ID = 0

-- DEFINE BID        = 0

-- DEFINE EID        = 0

-- DEFINE SID        = 0

-- DEFINE INST_ID    = 1

-- DEFINE OWNER      = 'GL'

-- DEFINE TABLE_NAME = 'GL_JE_LINES'

 

Instance and AWR snap window

SELECT inst_id, instance_name, host_name, status, database_status,

       startup_time, version

FROM   gv$instance

ORDER BY inst_id;

 

SELECT name, dbid, created, log_mode, open_mode, database_role

FROM   v$database;

 

SELECT snap_id, instance_number, begin_interval_time, end_interval_time

FROM   dba_hist_snapshot

WHERE  begin_interval_time > SYSDATE - 2

ORDER BY snap_id, instance_number;

 

5. Scenario A — Concurrent request running long

Path: FND → session → SQL → longops / monitor → ASH. Run the FND queries as APPS.

A1. Request header

SELECT request_id, parent_request_id,

       phase_code, status_code, priority,

       requested_by, request_date, requested_start_date,

       actual_start_date, actual_completion_date,

       ROUND((NVL(actual_completion_date, SYSDATE) - actual_start_date) * 1440, 2) run_min,

       oracle_session_id, oracle_process_id,

       logfile_name, outfile_name, argument_text

FROM   fnd_concurrent_requests

WHERE  request_id = &REQUEST_ID;

 

A2. All running / pending requests

SELECT request_id, parent_request_id, phase_code, status_code,

       concurrent_program_id, requested_by,

       request_date, requested_start_date, actual_start_date,

       ROUND((SYSDATE - actual_start_date) * 1440, 1) run_min,

       oracle_session_id, argument_text

FROM   fnd_concurrent_requests

WHERE  phase_code IN ('R', 'P')

ORDER BY DECODE(phase_code, 'R', 1, 2), actual_start_date NULLS LAST;

 

A3. Map request AUDSID to GV$SESSION

SELECT r.request_id, r.phase_code, r.status_code,

       r.oracle_session_id,

       s.inst_id, s.sid, s.serial#,

       s.event, s.wait_class, s.seconds_in_wait,

       s.sql_id, s.sql_child_number, s.sql_exec_start,

       s.module, s.action,

       s.blocking_instance, s.blocking_session

FROM   fnd_concurrent_requests r

JOIN   gv$session s ON s.audsid = r.oracle_session_id

WHERE  r.request_id = &REQUEST_ID;

 

Note: Elapsed minutes from SQL_EXEC_START is the current cursor age, not necessarily the full request duration. Parent requests can sit on a different session than child workers.

A4. Program definition

SELECT p.concurrent_program_id, p.concurrent_program_name,

       t.user_concurrent_program_name,

       p.enabled_flag, p.execution_method_code,

       e.execution_file_name

FROM   fnd_concurrent_programs p

JOIN   fnd_concurrent_programs_tl t

       ON t.application_id = p.application_id

      AND t.concurrent_program_id = p.concurrent_program_id

      AND t.language = USERENV('LANG')

LEFT JOIN fnd_executables e

       ON e.application_id = p.executable_application_id

      AND e.executable_id  = p.executable_id

WHERE  p.concurrent_program_id = &CONC_PROG_ID;

 

A5. Manager capacity and incompatibilities

SELECT concurrent_queue_name, running_processes, max_processes,

       cache_size, target_node, enabled_flag

FROM   fnd_concurrent_queues

WHERE  enabled_flag = 'Y'

ORDER BY running_processes DESC;

 

SELECT *

FROM   fnd_concurrent_program_serial

WHERE  running_concurrent_program_id = &CONC_PROG_ID

    OR to_run_concurrent_program_id  = &CONC_PROG_ID;

 

6. Scenario B — Live session and current SQL

B1. Active user sessions

SELECT s.inst_id, s.sid, s.serial#, s.audsid, s.username,

       s.osuser, s.machine, s.program, s.module, s.action, s.status,

       s.sql_id, s.sql_child_number, s.sql_exec_start,

       ROUND((SYSDATE - s.sql_exec_start) * 1440, 2) sql_elapsed_min,

       s.event, s.wait_class, s.seconds_in_wait, s.state,

       s.blocking_instance, s.blocking_session,

       s.last_call_et

FROM   gv$session s

WHERE  s.type = 'USER'

AND    s.status = 'ACTIVE'

ORDER BY s.sql_exec_start NULLS LAST;

 

B2. Wait-class mix now

SELECT inst_id,

       COUNT(*) sessions,

       SUM(DECODE(status, 'ACTIVE', 1, 0)) active,

       SUM(DECODE(wait_class, 'Idle', 0, 1)) nonidle,

       SUM(DECODE(wait_class, 'User I/O', 1, 0)) user_io,

       SUM(DECODE(wait_class, 'Concurrency', 1, 0)) concurrency,

       SUM(DECODE(wait_class, 'Application', 1, 0)) application,

       SUM(DECODE(wait_class, 'Commit', 1, 0)) commit_w

FROM   gv$session

WHERE  type = 'USER'

GROUP BY inst_id

ORDER BY inst_id;

 

B3. Cursor-cache stats and text

SELECT inst_id, sql_id, child_number, plan_hash_value, executions,

       ROUND(elapsed_time / 1e6, 2) ela_s,

       ROUND(cpu_time / 1e6, 2) cpu_s,

       buffer_gets, disk_reads, rows_processed,

       ROUND(elapsed_time / NULLIF(executions, 0) / 1e6, 4) ela_per_exec_s,

       ROUND(buffer_gets / NULLIF(executions, 0)) gets_per_exec,

       last_active_time

FROM   gv$sql

WHERE  sql_id = '&SQL_ID';

 

SELECT * FROM TABLE(

  DBMS_XPLAN.DISPLAY_CURSOR('&SQL_ID', NULL,

    'ALLSTATS LAST +PEEKED_BINDS +OUTLINE +ADAPTIVE'));

 

B4. Longops and SQL Monitor

SELECT inst_id, sid, serial#, opname, target, sofar, totalwork, units,

       ROUND(sofar / NULLIF(totalwork, 0) * 100, 2) pct,

       elapsed_seconds, time_remaining, sql_id

FROM   gv$session_longops

WHERE  totalwork > 0

AND    sofar < totalwork

ORDER BY elapsed_seconds DESC;

 

SELECT inst_id, sid, session_serial#, sql_id, sql_exec_id, sql_exec_start,

       status, username, module, px_servers_allocated,

       ROUND(elapsed_time / 1e6, 2) ela_s,

       ROUND(cpu_time / 1e6, 2) cpu_s,

       buffer_gets, disk_reads

FROM   gv$sql_monitor

WHERE  status IN ('EXECUTING', 'DONE (ERROR)')

    OR sql_exec_start > SYSDATE - 1/24

ORDER BY sql_exec_start DESC

FETCH FIRST 30 ROWS ONLY;

 

Note: A missing LONGOPS row does not mean the request is idle. Only instrumented operations appear there. Use SQL Monitor and ASH when LONGOPS is empty.

B5. Binds and OS PID

SELECT inst_id, sql_id, child_number, name, position, datatype_string,

       last_captured, value_string

FROM   gv$sql_bind_capture

WHERE  sql_id = '&SQL_ID'

ORDER BY child_number, position;

 

SELECT s.inst_id, s.sid, s.serial#, p.spid os_pid, p.pid,

       s.username, s.sql_id, s.program

FROM   gv$session s

JOIN   gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE  s.sid = &SID

AND    s.inst_id = &INST_ID;

 

7. Scenario C — Blocking, locks, wait chains

If blocking_session is populated, that is the RCA until the holder is released or explained. Do not tune the waiter first.

C1. Who blocks whom

SELECT s.inst_id, s.sid, s.serial#, s.username, s.program, s.module,

       s.sql_id, s.event, s.seconds_in_wait,

       s.blocking_instance, s.blocking_session,

       bs.username blk_user, bs.program blk_prog, bs.sql_id blk_sql,

       bs.event blk_event, bs.status blk_status

FROM   gv$session s

JOIN   gv$session bs

       ON bs.inst_id = s.blocking_instance

      AND bs.sid     = s.blocking_session

WHERE  s.blocking_session IS NOT NULL

ORDER BY s.seconds_in_wait DESC;

 

C2. Wait chains, locks, locked objects

SELECT * FROM v$wait_chains

ORDER BY num_waiters DESC, chain_signature;

 

SELECT inst_id, sid, type, id1, id2, lmode, request, ctime, block

FROM   gv$lock

WHERE  request > 0 OR block > 0

ORDER BY block DESC, ctime DESC;

 

SELECT lo.inst_id, lo.session_id sid, lo.oracle_username,

       o.owner, o.object_name, o.object_type, lo.locked_mode

FROM   gv$locked_object lo

JOIN   dba_objects o ON o.object_id = lo.object_id

ORDER BY lo.inst_id, lo.session_id;

 

8. Scenario D — Live ASH (last hour)

SELECT inst_id, wait_class, event, session_state, COUNT(*) samples

FROM   gv$active_session_history

WHERE  sample_time > SYSDATE - 1/24

AND    session_type = 'FOREGROUND'

GROUP BY inst_id, wait_class, event, session_state

ORDER BY samples DESC

FETCH FIRST 40 ROWS ONLY;

 

SELECT sql_id, COUNT(*) samples,

       COUNT(DISTINCT session_id || ',' || session_serial#) sess,

       ROUND(100 * RATIO_TO_REPORT(COUNT(*)) OVER (), 1) pct

FROM   gv$active_session_history

WHERE  sample_time > SYSDATE - 1/24

AND    sql_id IS NOT NULL

GROUP BY sql_id

ORDER BY samples DESC

FETCH FIRST 20 ROWS ONLY;

 

SELECT current_obj#, COUNT(*) samples

FROM   gv$active_session_history

WHERE  sample_time > SYSDATE - 1/24

AND    current_obj# > 0

GROUP BY current_obj#

ORDER BY samples DESC

FETCH FIRST 20 ROWS ONLY;

 

SELECT owner, object_name, object_type, subobject_name

FROM   dba_objects

WHERE  object_id = &OBJ_ID;

 

Hot block map

SELECT inst_id, event, p1 file#, p2 block#, COUNT(*) cnt

FROM   gv$session

WHERE  event IN ('latch: cache buffers chains', 'read by other session',

                 'buffer busy waits', 'gc cr block busy', 'gc current block busy')

GROUP BY inst_id, event, p1, p2

ORDER BY cnt DESC

FETCH FIRST 20 ROWS ONLY;

 

SELECT owner, segment_name, segment_type, partition_name, tablespace_name

FROM   dba_extents

WHERE  file_id = &FILE_ID

AND    &BLOCK_ID BETWEEN block_id AND block_id + blocks - 1;

 

9. Scenario E — Historical SQL (fast yesterday, slow today)

Set BID/EID from DBA_HIST_SNAPSHOT. Compare plan_hash_value, elapsed per execution, buffer gets per execution, and rows processed — not only total elapsed.

E1. Time model

SELECT sn.begin_interval_time, tm.instance_number, tm.stat_name,

       ROUND(tm.value / 1e6, 2) seconds

FROM   dba_hist_sys_time_model tm

JOIN   dba_hist_snapshot sn

       ON sn.snap_id = tm.snap_id

      AND sn.dbid = tm.dbid

      AND sn.instance_number = tm.instance_number

WHERE  tm.stat_name IN ('DB time', 'DB CPU', 'sql execute elapsed time',

                        'parse time elapsed', 'hard parse elapsed time')

AND    sn.begin_interval_time > SYSDATE - 2

ORDER BY sn.begin_interval_time, tm.instance_number, tm.stat_name;

 

E2. SQL_ID across snapshots

SELECT sn.begin_interval_time, st.instance_number, st.sql_id,

       st.plan_hash_value, st.executions_delta execs,

       ROUND(st.elapsed_time_delta / 1e6, 2) elapsed_s,

       ROUND(st.cpu_time_delta / 1e6, 2) cpu_s,

       st.buffer_gets_delta gets, st.disk_reads_delta reads,

       st.rows_processed_delta rows_proc,

       ROUND(st.elapsed_time_delta / NULLIF(st.executions_delta, 0) / 1e6, 4) ela_px_s,

       ROUND(st.buffer_gets_delta / NULLIF(st.executions_delta, 0)) gets_px

FROM   dba_hist_sqlstat st

JOIN   dba_hist_snapshot sn

       ON st.snap_id = sn.snap_id

      AND st.dbid = sn.dbid

      AND st.instance_number = sn.instance_number

WHERE  st.sql_id = '&SQL_ID'

ORDER BY sn.begin_interval_time DESC, st.instance_number;

 

E3. Plan-hash regression

SELECT sql_id, plan_hash_value,

       SUM(executions_delta) execs,

       ROUND(SUM(elapsed_time_delta) / 1e6, 2) ela_s,

       ROUND(SUM(elapsed_time_delta) / NULLIF(SUM(executions_delta), 0) / 1e6, 4) ela_px

FROM   dba_hist_sqlstat

WHERE  sql_id = '&SQL_ID'

GROUP BY sql_id, plan_hash_value

ORDER BY ela_s DESC;

 

SELECT sql_id, dbid, sql_text

FROM   dba_hist_sqltext

WHERE  sql_id = '&SQL_ID';

 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&SQL_ID'));

 

E4. Historical ASH for one SQL_ID

SELECT event, wait_class, session_state, COUNT(*) sample_count

FROM   dba_hist_active_sess_history

WHERE  sql_id = '&SQL_ID'

AND    sample_time >= SYSDATE - 1

GROUP BY event, wait_class, session_state

ORDER BY sample_count DESC;

 

Note: ASH sample counts are not exact elapsed seconds. They show the distribution of time. Statements that never entered AWR will not appear in DBA_HIST_SQLSTAT.

E5. Top SQL in a snap window

SELECT sql_id,

       ROUND(SUM(elapsed_time_delta) / 1e6, 2) elapsed_s,

       ROUND(SUM(cpu_time_delta) / 1e6, 2) cpu_s,

       SUM(executions_delta) execs,

       ROUND(SUM(elapsed_time_delta) / NULLIF(SUM(executions_delta), 0) / 1e6, 4) ela_px,

       SUM(buffer_gets_delta) gets,

       SUM(disk_reads_delta) reads,

       SUM(rows_processed_delta) rows_proc

FROM   dba_hist_sqlstat

WHERE  snap_id > &BID AND snap_id <= &EID

GROUP BY sql_id

ORDER BY elapsed_s DESC

FETCH FIRST 20 ROWS ONLY;

 

10. Scenario F — Temp, undo, PGA, space

SELECT inst_id, tablespace, segtype,

       ROUND(SUM(blocks) * 8 / 1024) mb_approx_8k,

       COUNT(DISTINCT session_addr) sess

FROM   gv$tempseg_usage

GROUP BY inst_id, tablespace, segtype;

 

SELECT s.inst_id, s.sid, s.username, s.sql_id, t.tablespace, t.segtype,

       ROUND(t.blocks * 8 / 1024) mb_approx_8k

FROM   gv$tempseg_usage t

JOIN   gv$session s ON s.saddr = t.session_addr AND s.inst_id = t.inst_id

ORDER BY t.blocks DESC

FETCH FIRST 20 ROWS ONLY;

 

SELECT tablespace_name, status, ROUND(SUM(bytes) / 1024 / 1024) mb

FROM   dba_undo_extents

GROUP BY tablespace_name, status;

 

SELECT inst_id, ses_addr, start_time, used_ublk, used_urec, status

FROM   gv$transaction

ORDER BY used_ublk DESC;

 

SELECT name, ROUND(value / 1024 / 1024) mb

FROM   v$pgastat

WHERE  name IN ('aggregate PGA target parameter', 'total PGA allocated',

                'total PGA used for auto workareas', 'over allocation count');

 

SELECT tablespace_name, ROUND(SUM(bytes) / 1024 / 1024) free_mb

FROM   dba_free_space

GROUP BY tablespace_name

ORDER BY free_mb;

 

Note: MB math above assumes 8 KB blocks. Confirm DB_BLOCK_SIZE before quoting sizes in the RCA.

11. Scenario G — Cursor sharing and statistics

SELECT sql_id, COUNT(*) children, SUM(executions) execs,

       SUBSTR(MAX(sql_text), 1, 160) txt

FROM   gv$sql

GROUP BY sql_id

HAVING COUNT(*) > 20

ORDER BY children DESC

FETCH FIRST 20 ROWS ONLY;

 

SELECT * FROM v$sql_shared_cursor

WHERE  sql_id = '&SQL_ID'

AND    ROWNUM <= 10;

 

SELECT owner, table_name, num_rows, blocks, last_analyzed, stale_stats

FROM   dba_tab_statistics

WHERE  owner = UPPER('&OWNER')

AND    table_name = UPPER('&TABLE_NAME');

 

SELECT table_owner, table_name, inserts, updates, deletes, timestamp, truncated

FROM   dba_tab_modifications

WHERE  table_owner = UPPER('&OWNER')

AND    table_name = UPPER('&TABLE_NAME');

 

SELECT owner, index_name, table_name, uniqueness, status,

       clustering_factor, num_rows, last_analyzed, stale_stats

FROM   dba_ind_statistics

WHERE  owner = UPPER('&OWNER')

AND    table_name = UPPER('&TABLE_NAME');

 

12. Scenario H — RAC interconnect

SELECT inst_id, event, total_waits, time_waited

FROM   gv$system_event

WHERE  event LIKE 'gc %'

ORDER BY time_waited DESC;

 

SELECT inst_id, name, value

FROM   gv$sysstat

WHERE  name LIKE 'gc %'

ORDER BY inst_id, name;

 

13. Wait class to next check

Wait / class	Next check
db file sequential / scattered read	Hot segment, missing index, stale stats, multiblock I/O
log file sync / parallel write	Commit rate, log dest latency, LGWR
enq: TX / enq: TM	Blocker SQL, unindexed FK, ITL, uncommitted form
library cache / cursor: pin S wait on X	Hard parse, invalidations, literal SQL
cache buffers chains / read by other session	Hot block → DBA_EXTENTS
direct path read/write temp	PGA, HASH/SORT spill, workarea
undo / TX row lock	Long transaction, undo sizing, purge job
gc cr / gc current (RAC)	Interconnect, hot block, sequence

14. Evidence to attach to the ticket

•       Request_id, program name, phase/status, actual_start, argument_text

•       inst_id, sid, serial#, audsid, OS PID

•       sql_id, child_number, plan_hash_value (good window vs bad window)

•       Dominant wait class and top event from ASH

•       Elapsed per exec, gets per exec, rows processed (not only total elapsed)

•       Blocker sid and locked object if Application / Concurrency waits

•       TEMP / undo MB and whether workareas spilled

•       Stale_stats and last gather time on objects in the plan

Root-cause sentence format: Symptom → wait class → sql_id / object → change (plan, volume, blocker, space) → action.

15. Licensing and safety

•       DBA_HIST_* and DBA_HIST_ACTIVE_SESS_HISTORY: Diagnostics Pack.

•       SQL Monitor / DBMS_SQLTUNE.REPORT_SQL_MONITOR: Tuning Pack in many estates — confirm before use.

•       These queries are read-only. Do not embed ALTER SYSTEM, gather stats, or kill session in the same worksheet as the RCA capture.

•       Kill / disconnect only after blocker identity, module, and business owner are documented.