-

 


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)


PROMPT [STEP 10c] Workarea executions (Optimal vs One-pass vs Multipass)

COLUMN optimal    FORMAT 999,999,999 HEADING 'OPTIMAL'

COLUMN onepass    FORMAT 999,999,999 HEADING 'ONEPASS'

COLUMN multipass  FORMAT 999,999,999 HEADING 'MULTIPASS'

SELECT SUM(optimal_executions)     AS optimal,

       SUM(onepass_executions)     AS onepass,

       SUM(multipasses_executions) AS multipass

FROM   dba_hist_sql_workarea

WHERE  sql_id='&sql_id';



Step 10d – ASH corroboration (TEMP wait events)


PROMPT [STEP 10d] ASH temp wait events (evidence of TEMP spills)

COLUMN event   FORMAT A30

COLUMN samples FORMAT 999,999,999

SELECT event, COUNT(*) AS samples

FROM   dba_hist_active_sess_history

WHERE  sql_id='&sql_id'

AND    event IN ('direct path read temp','direct path write temp')

GROUP  BY event

ORDER  BY samples DESC;



Step 10e – Current PGA headroom (runtime)


PROMPT [STEP 10e] PGA memory snapshot (current)

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');



Step 11 – I/O latency during periods this SQL appeared


PROMPT [STEP 11] I/O Latency (AWR filestats restricted to this SQL's min..max snaps)

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   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 NULLS LAST;



Step 12 – Plan inventory (cache + historical + actuals)


PROMPT [STEP 12a] Current plan hashes in cache (if present)

COLUMN inst_id FORMAT 99

COLUMN plan_hash_value FORMAT 9999999999

SELECT DISTINCT inst_id, plan_hash_value

FROM   gv$sql

WHERE  sql_id='&sql_id'

ORDER  BY inst_id;


PROMPT [STEP 12b] Historical plans seen in AWR

COLUMN occurrences FORMAT 999,999

SELECT 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;


PROMPT [STEP 12c] Current plan with actuals (if cached recently)

COLUMN id        FORMAT 999

COLUMN operation FORMAT A45

COLUMN op_elapsed_s FORMAT 999,999,999.000

SELECT inst_id,

       plan_hash_value,

       id,

       LPAD(' ',2*depth)||operation||' '||options AS operation,

       cardinality,

       starts,

       output_rows,

       ROUND(elapsed_time/1e6,3) AS op_elapsed_s

FROM   gv$sql_plan_statistics_all

WHERE  sql_id='&sql_id'

ORDER  BY inst_id, plan_hash_value, id;



Step 13 - Key optimizer/parallel/PGA params (for env diff)


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

COLUMN name  FORMAT A40

COLUMN value FORMAT A60

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;


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