-

 -- ===========================================

-- 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                 FORMAT A35

COLUMN value                FORMAT A40


-- >>>> EDIT THIS (required) <<<<

DEFINE sql_id = 'PUT_SQL_ID_HERE';


-- (Optional) Spool output to a file:

-- SPOOL sqlid_&&sql_id..lst


PROMPT [STEP 1] Cursor Cache: recent executions (if still in memory)

SELECT inst_id,

       sql_id,

       child_number,

       plan_hash_value,

       TO_CHAR(last_active_time,'YYYY-MM-DD HH24:MI:SS') last_active_time,

       executions                                                AS execs,

       ROUND(elapsed_time/1e6,2)                                 AS elapsed_s,

       ROUND(cpu_time/1e6,2)                                     AS cpu_s,

       ROUND(user_io_wait_time/1e6,2)                            AS io_s,

       buffer_gets,

       disk_reads,

       rows_processed                                            AS rows_proc

FROM   gv$sql

WHERE  sql_id='&sql_id'

ORDER  BY last_active_time DESC;


PROMPT [STEP 2] Bind Values (current capture)

SELECT inst_id, name, position, datatype_string, value_string, 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)

SELECT name, position, datatype_string, value_string, last_captured

FROM   dba_hist_sqlbind

WHERE  sql_id='&sql_id'

ORDER  BY last_captured DESC, position;


PROMPT [STEP 4] AWR Execution History (all snapshots for this SQL_ID)

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.cluster_wait_time_delta/1e6,2)              AS gc_s,

       ss.buffer_gets_delta                                 AS buffer_gets,

       ss.disk_reads_delta                                  AS disk_reads,

       ss.rows_processed_delta                              AS rows_proc

FROM   dba_hist_sqlstat ss

JOIN   snaps s

  ON   s.snap_id=ss.snap_id AND s.dbid=ss.dbid AND s.instance_number=ss.instance_number

WHERE  ss.sql_id='&sql_id'

ORDER  BY s.begin_interval_time DESC;


PROMPT [STEP 5] AWR Time by Plan: aggregate per PLAN_HASH_VALUE

SELECT ss.plan_hash_value,

       ROUND(SUM(ss.elapsed_time_delta)/1e6,1)            AS elapsed_s,

       ROUND(SUM(ss.cpu_time_delta)/1e6,1)                AS cpu_s,

       ROUND(SUM(ss.iowait_delta)/1e6,1)                  AS io_wait_s,

       ROUND(SUM(ss.cluster_wait_time_delta)/1e6,1)       AS gc_wait_s,

       SUM(ss.buffer_gets_delta)                           AS buffer_gets,

       SUM(ss.disk_reads_delta)                            AS disk_reads,

       SUM(ss.rows_processed_delta)                        AS rows_proc

FROM   dba_hist_sqlstat ss

WHERE  ss.sql_id='&sql_id'

GROUP  BY ss.plan_hash_value

ORDER  BY elapsed_s DESC;


PROMPT [STEP 6] ASH Wait Profile (entire ASH history for this SQL_ID)

SELECT NVL(event, wait_class) AS wait_or_class,

       COUNT(*)               AS samples,

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

FROM   dba_hist_active_sess_history

WHERE  sql_id='&sql_id'

GROUP  BY NVL(event, wait_class)

ORDER  BY samples DESC FETCH FIRST 20 ROWS ONLY;


PROMPT [STEP 7] ASH Hot Plan Lines (use SQL_PLAN_HASH_VALUE)

SELECT sql_plan_hash_value,

       sql_plan_line_id,

       sql_plan_operation||' '||sql_plan_options AS op,

       COUNT(*)                                  AS samples

FROM   dba_hist_active_sess_history

WHERE  sql_id='&sql_id'

GROUP  BY sql_plan_hash_value, sql_plan_line_id, sql_plan_operation, sql_plan_options

ORDER  BY samples DESC FETCH FIRST 25 ROWS ONLY;


PROMPT [STEP 8] RAC: Samples by Instance (distribution across nodes)

SELECT instance_number, COUNT(*) AS samples

FROM   dba_hist_active_sess_history

WHERE  sql_id='&sql_id'

GROUP  BY instance_number

ORDER  BY samples DESC;


PROMPT [STEP 9] RAC: Top Objects during Cluster (GC) waits

SELECT NVL(o.owner||'.'||o.object_name,'-') AS object_name,

       COUNT(*) AS samples

FROM   dba_hist_active_sess_history a

LEFT   JOIN dba_objects o ON a.current_obj#=o.object_id

WHERE  a.sql_id='&sql_id'

AND    a.wait_class='Cluster'

GROUP  BY NVL(o.owner||'.'||o.object_name,'-')

ORDER  BY samples DESC FETCH FIRST 15 ROWS ONLY;


PROMPT [STEP 10] TEMP/PGA Spill Indicators

-- Total TEMP allocated across AWR history for this SQL_ID

SELECT ROUND(SUM(temp_space_allocated_delta)/1024/1024/1024,3) AS temp_gb

FROM   dba_hist_sqlstat

WHERE  sql_id='&sql_id';


-- Current PGA snapshot (runtime; may require privileges)

SELECT name, value

FROM   v$pgastat

WHERE  name IN ('total PGA allocated','PGA cache hit percentage');


PROMPT [STEP 11] I/O Latency (during min..max snaps where this SQL appeared)

WITH r AS (

  SELECT MIN(snap_id) b, MAX(snap_id) e

  FROM   dba_hist_sqlstat

  WHERE  sql_id='&sql_id'

)

SELECT f.filetype_name,

       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_name, f.readtim, f.phyrds, f.writetim, f.phywrts

ORDER  BY ms_per_read DESC;


PROMPT [STEP 12] Plan Inventory (current cache + historical)

-- Current (if in cache)

SELECT DISTINCT inst_id, plan_hash_value

FROM   gv$sql

WHERE  sql_id='&sql_id'

ORDER  BY inst_id;


-- Historical plans seen in AWR

SELECT DISTINCT plan_hash_value, COUNT(*) AS occurrences

FROM   dba_hist_sql_plan

WHERE  sql_id='&sql_id'

GROUP  BY plan_hash_value

ORDER  BY occurrences DESC;


-- Current plan with actuals (if cached recently)

SELECT *

FROM   gv$sql_plan_statistics_all

WHERE  sql_id='&sql_id'

ORDER  BY inst_id, plan_hash_value, id;


PROMPT [STEP 13 - OPTIONAL] Object Stats Freshness (set DEFINE owner before running)

-- To use this step, first run: DEFINE owner = 'OWNER_SCHEMA';

-- Then execute the query below.

-- SELECT owner, table_name, last_analyzed, stale_stats

-- FROM   dba_tab_statistics

-- WHERE  owner = '&owner'

--   AND  table_name IN (

--     SELECT DISTINCT o.object_name

--     FROM   dba_objects o

--     WHERE  o.object_id IN (

--       SELECT DISTINCT current_obj#

--       FROM   dba_hist_active_sess_history

--       WHERE  sql_id='&sql_id'

--     )

--   )

-- ORDER  BY last_analyzed DESC;


PROMPT [STEP 14] Key Optimizer/Parallel/PGA Params (snapshot for env diff)

SELECT name, value

FROM   v$parameter

WHERE  name IN (

  'optimizer_features_enable','optimizer_mode','cursor_sharing',

  'parallel_degree_policy','parallel_min_time_threshold',

  'pga_aggregate_target','pga_aggregate_limit'

)

ORDER  BY name;


-- (Optional) Real-time ASH if statement is currently running:

-- SELECT NVL(event,wait_class) wait_or_class, COUNT(*) samples

-- FROM   gv$active_session_history

-- WHERE  sql_id='&sql_id'

-- GROUP  BY NVL(event,wait_class)

-- ORDER  BY samples DESC;


-- SPOOL OFF

-- ===== End of script =====

Popular posts from this blog

-
Image
How to shutdown a Single Instance Database in Oracle 19c Overview Shutdown is an operation performed when a command is issued by the Administrator to bring down the Database or when it receives a signal to bring down the Database incase of a failure/malfunction or due to SHUTDOWN ABORT comm and execution. The options used by t he Administrator to bring down the Database is called as Shutdown Modes and the Database Server follows a sequence to shutdown a Database . Let's check how does that happen. Concepts The following are the shutdown Modes. Shutdown Modes A database administrator with SYSDBA or SYSOPER privileges can shut down the database using the SQL*Plus SHUTDOWN command or Enterprise Manager. The SHUTDOWN command has the following options that determine the shutdown behavior. Now let's look into each SHUTDOWN option in detail. SHUTDOWN ABORT  This mode is used for emergency situations, such as when shutdown with "IMMEDIATE" option doesn't work. This mode o...
Read more
-
How to startup a Single Instance Database in Oracle 19c Overview An Oracle Database goes through various stages until it is made available to the users. They are categorized into the below stages. Concepts The following are the stages of the startup sequence. Nomount   The instance is started, but is not yet associated with a database. The stages are as follows:  1. Searches for a server parameter file in a platform-specific default location and, if not found, for a text initialization parameter file (specifying STARTUP with the SPFILE or PFILE parameters overrides the default behavior)  2. Reads the parameter file to determine the values of initialization parameters  3. Allocates the SGA based on the initialization parameter settings  4. Starts the Oracle background processes  5. Opens the alert log and trace files and writes all explicit parameter settings to the alert log in valid parameter syntax At this stage, no database is associated with the instanc...
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