Posts

-
 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');
Read more
-
 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)        ...
Read more
-
 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...
Read more
-
 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...
Read more
-
 -- 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)                 ...
Read more
-
 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, ...
Read more
-
 -- =========================================== -- 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     ...
Read more