Raunak's DBA Blog

 PROMPT [STEP 11B] CDB I/O latency by file type for this PDB (AWR, CON_ID filter) -- Get this PDB's CON_ID (adjust name if needed) COLUMN con_id FORMAT 999 SELECT con_id FROM v$containers WHERE name = SYS_CONTEXT('USERENV','CON_NAME'); COLUMN filetype_name FORMAT A20 COLUMN ms_per_read   FORMAT 999.999 COLUMN ms_per_write  FORMAT 999.999 WITH r AS (   SELECT MIN(snap_id) b, MAX(snap_id) e   FROM   cdb_hist_sqlstat   WHERE  con_id = SYS_CONTEXT('USERENV','CON_ID')   AND    sql_id = '&sql_id' ) SELECT f.filetype_name,        ROUND( (SUM(f.small_read_servicetime + f.large_read_servicetime))               / NULLIF(SUM(f.small_read_reqs + f.large_read_reqs),0), 3) AS ms_per_read,        ROUND( (SUM(f.small_write_servicetime + f.large_write_servicetime))               / NULLIF(SUM(f.small_write_reqs + f.large_write_reqs),0), 3...

 PROMPT [STEP 11] I/O Latency during periods this SQL appeared COLUMN filetype_id   FORMAT 999 HEADING 'TYPE_ID' COLUMN ms_per_read   FORMAT 999.999 COLUMN ms_per_write  FORMAT 999.999 WITH r AS (   SELECT MIN(snap_id) b, MAX(snap_id) e   FROM   dba_hist_sqlstat   WHERE  sql_id='&sql_id' ) SELECT f.filetype_id,        ROUND(f.readtim  / DECODE(NULLIF(f.phyrds ,0),0,1,f.phyrds ),3) AS ms_per_read,        ROUND(f.writetim / DECODE(NULLIF(f.phywrts,0),0,1,f.phywrts),3) AS ms_per_write FROM   dba_hist_filestatxs f, r WHERE  f.snap_id BETWEEN r.b AND r.e GROUP  BY f.filetype_id, f.readtim, f.phyrds, f.writetim, f.phywrts ORDER  BY ms_per_read DESC NULLS LAST;

  Step 10a – Which TEMP column exists? PROMPT [STEP 10a] Check TEMP-related columns in DBA_HIST_SQLSTAT COLUMN column_name FORMAT A35 SELECT column_name FROM   dba_tab_columns WHERE  owner = 'SYS' AND    table_name = 'DBA_HIST_SQLSTAT' AND    column_name LIKE 'TEMP%'; Step 10b(i) – TEMP usage (if  TEMP_SPACE_ALLOCATED_DELTA  exists) PROMPT [STEP 10b(i)] TEMP usage (AWR, using TEMP_SPACE_ALLOCATED_DELTA) COLUMN temp_gb FORMAT 999,999,990.000 SELECT ROUND(SUM(temp_space_allocated_delta)/1024/1024/1024,3) AS temp_gb FROM   dba_hist_sqlstat WHERE  sql_id='&sql_id'; Step 10b(ii) – TEMP usage (if  TEMP_SPACE_ALLOCATED  exists) PROMPT [STEP 10b(ii)] TEMP usage (AWR, using TEMP_SPACE_ALLOCATED) COLUMN temp_gb FORMAT 999,999,990.000 SELECT ROUND(SUM(temp_space_allocated)/1024/1024/1024,3) AS temp_gb FROM   dba_hist_sqlstat WHERE  sql_id='&sql_id'; Step 10c – Workarea spill evidence (Optimal/One-pass/Multipass) P...

 PROMPT [STEP 10] TEMP/PGA Spill Indicators -- TEMP usage from AWR (adjust column based on availability) SELECT ROUND(SUM(NVL(temp_space_allocated_delta, temp_space_allocated))/1024/1024/1024,3) AS temp_gb FROM   dba_hist_sqlstat WHERE  sql_id='&sql_id'; -- Current PGA snapshot (runtime; may require privileges) COLUMN name FORMAT A35 COLUMN value FORMAT 999,999,999,999 SELECT name, value FROM   v$pgastat WHERE  name IN ('total PGA allocated','PGA cache hit percentage');

 PROMPT [STEP 5] AWR Time by Plan (aggregate per PLAN_HASH_VALUE) COLUMN plan_hash_val FORMAT 9999999999 HEADING 'PLAN_HASH' COLUMN elapsed_s     FORMAT 999,999,999.99 HEADING 'Elapsed(s)' COLUMN cpu_s         FORMAT 999,999,999.99 HEADING 'CPU(s)' COLUMN io_wait_s     FORMAT 999,999,999.99 HEADING 'IO_Wait(s)' COLUMN gc_wait_s     FORMAT 999,999,999.99 HEADING 'GC_Wait(s)' COLUMN buffer_gets   FORMAT 999,999,999,999 HEADING 'Buffer_Gets' COLUMN disk_reads    FORMAT 999,999,999,999 HEADING 'Disk_Reads' COLUMN rows_proc     FORMAT 999,999,999,999 HEADING 'Rows_Proc' SELECT ss.plan_hash_value           AS plan_hash_val,        ROUND(SUM(ss.elapsed_time_delta)/1e6,2)   AS elapsed_s,        ROUND(SUM(ss.cpu_time_delta)/1e6,2)       AS cpu_s,        ROUND(SUM(ss.iowait_delta)/1e6,2)        ...

 PROMPT [STEP 4] AWR Execution History (all snapshots for this SQL_ID) COLUMN snap_time     FORMAT A20 COLUMN plan_hash_val FORMAT 9999999999 HEADING 'PLAN_HASH' COLUMN execs         FORMAT 999,999,999 HEADING 'Execs' COLUMN elapsed_s     FORMAT 999,999,999.99 HEADING 'Elapsed(s)' COLUMN cpu_s         FORMAT 999,999,999.99 HEADING 'CPU(s)' COLUMN io_s          FORMAT 999,999,999.99 HEADING 'IO_Wait(s)' COLUMN gc_s          FORMAT 999,999,999.99 HEADING 'GC_Wait(s)' COLUMN buffer_gets   FORMAT 999,999,999,999 HEADING 'Buffer_Gets' COLUMN disk_reads    FORMAT 999,999,999,999 HEADING 'Disk_Reads' COLUMN rows_proc     FORMAT 999,999,999,999 HEADING 'Rows_Proc' WITH snaps AS (   SELECT s.snap_id, s.instance_number, s.dbid, s.begin_interval_time   FROM   dba_hist_snapshot s ) SELECT TO_CHAR(s.begin_interval_time,'YYYY-MM-DD HH24:MI') snap_t...

 PROMPT [STEP 4] AWR Execution History (all snapshots for this SQL_ID) COLUMN snap_time     FORMAT A20 COLUMN plan_hash_val FORMAT 9999999999 HEADING 'PLAN_HASH' COLUMN execs         FORMAT 999,999,999 HEADING 'Execs' COLUMN elapsed_s     FORMAT 999,999,999.99 HEADING 'Elapsed(s)' COLUMN cpu_s         FORMAT 999,999,999.99 HEADING 'CPU(s)' COLUMN io_s          FORMAT 999,999,999.99 HEADING 'IO_Wait(s)' COLUMN gc_s          FORMAT 999,999,999.99 HEADING 'GC_Wait(s)' COLUMN buffer_gets   FORMAT 999,999,999,999 HEADING 'Buffer_Gets' COLUMN disk_reads    FORMAT 999,999,999,999 HEADING 'Disk_Reads' COLUMN rows_proc     FORMAT 999,999,999,999 HEADING 'Rows_Proc' WITH snaps AS (   SELECT s.snap_id, s.instance_number, s.dbid, s.begin_interval_time   FROM   dba_hist_snapshot s ) SELECT TO_CHAR(s.begin_interval_time,'YYYY-MM-DD HH24:MI') snap_t...

 -- AWR Execution History WITH snaps AS (   SELECT s.snap_id, s.instance_number, s.dbid, s.begin_interval_time   FROM   dba_hist_snapshot s ) SELECT TO_CHAR(s.begin_interval_time,'YYYY-MM-DD HH24:MI') snap_time,        ss.plan_hash_value,        ss.executions_delta                                  AS execs,        ROUND(ss.elapsed_time_delta/1e6,2)                   AS elapsed_s,        ROUND(ss.cpu_time_delta/1e6,2)                       AS cpu_s,        ROUND(ss.iowait_delta/1e6,2)                         AS io_s,        ROUND(ss.clwait_delta/1e6,2)                 ...

 PROMPT [STEP 2] Bind Values (current capture) COLUMN inst_id         FORMAT 99 COLUMN name            FORMAT A30 COLUMN position        FORMAT 999 COLUMN datatype_string FORMAT A20 COLUMN value_string    FORMAT A40 COLUMN last_captured   FORMAT A20 SELECT inst_id,        name,        position,        datatype_string,        value_string,        TO_CHAR(last_captured,'YYYY-MM-DD HH24:MI:SS') last_captured FROM   gv$sql_bind_capture WHERE  sql_id='&sql_id' ORDER  BY last_captured DESC, position; PROMPT [STEP 3] Bind Values (AWR history) COLUMN name            FORMAT A30 COLUMN position        FORMAT 999 COLUMN datatype_string FORMAT A20 COLUMN value_string    FORMAT A40 COLUMN last_captured   FORMAT A20 SELECT name, ...

 -- =========================================== -- SQL_ID Deep-Dive: Step-by-Step (Copy-All) -- Safe (SELECT-only). No time window required. -- =========================================== -- Formatting SET LINESIZE 300 SET PAGESIZE 200 SET TRIMSPOOL ON SET TAB OFF SET VERIFY OFF SET FEEDBACK ON SET TIMING ON SET HEADING ON COLUMN inst_id              FORMAT 99 COLUMN sql_id               FORMAT A13 COLUMN child_number         FORMAT 999 COLUMN plan_hash_value      FORMAT 9999999999 COLUMN sql_plan_hash_value  FORMAT 9999999999 COLUMN last_active_time     FORMAT A19 COLUMN wait_or_class        FORMAT A40 COLUMN op                   FORMAT A45 COLUMN object_name          FORMAT A40 COLUMN snap_time            FORMAT A20 COLUMN name     ...