Posts

Showing posts from September, 2025
-
  Findings – Step 1 (Cursor Cache: Recent Executions) Plan stability Both p011aor and p012aor used the same plan hash (517933086). Confirms this is not a plan regression issue. Execution volume p011aor: ~6.2K executions. p012aor: ~13.9K executions (2× higher). Suggests heavier workload routed here (possibly service routing) or repeated runs. Elapsed time profile p011aor: elapsed ~6,210s, dominated by CPU time (65%), very low IO waits. p012aor: elapsed ~13,860s, dominated by IO waits (94%), very little CPU work. Clear shift: p012aor performance is IO-bound, not CPU-bound. Rows processed p011aor: ~18.7M rows. p012aor: ~32.6M rows (~75% more). Indicates either different bind values or different data distribution leading to more row access. Logical & physical IO Buffer gets: 293M vs 421M (+43%). Disk reads: 5.6M vs 11.9M (2× higher). Reinforces that p012aor is performing heavier IO work. ✅ Step 1 Conclusion Not a plan issue (sam...
Read more
-
 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...
Read more
-
 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;
Read more
-
  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...
Read more