-


Step 1. Recent Executions from Cursor Cache


COL inst_id           FOR 99

COL sql_id            FOR a13

COL child_number      FOR 999

COL plan_hash_value   FOR 9999999999

COL last_active_time  FOR a20

COL execs             FOR 999,999

COL elapsed_s         FOR 999,999,999.99

COL cpu_s             FOR 999,999,999.99

COL io_s              FOR 999,999,999.99

COL buffer_gets       FOR 999,999,999

COL disk_reads        FOR 999,999,999

COL rows_proc         FOR 999,999,999


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                  execs,

       ROUND(elapsed_time/1e6,2)   elapsed_s,

       ROUND(cpu_time/1e6,2)       cpu_s,

       ROUND(user_io_wait_time/1e6,2) io_s,

       buffer_gets,

       disk_reads,

       rows_processed              rows_proc

FROM   gv$sql

WHERE  sql_id='&sql_id'

ORDER  BY last_active_time COL wait_or_class FOR a35

COL op            FOR a40


SELECT NVL(event, wait_class) AS wait_or_class,

       COUNT(*) samples,

       ROUND(100*RATIO_TO_REPORT(COUNT(*)) OVER (),1) 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;


SELECT plan_hash_value,

       sql_plan_line_id,

       sql_plan_operation||' '||sql_plan_options op,

       COUNT(*) samples

FROM   dba_hist_active_sess_history

WHERE  sql_id='&sql_id'

GROUP  BY plan_hash_value, sql_plan_line_id, sql_plan_operation, sql_plan_options

ORDER  BY samples DESC FETCH FIRST 20 ROWS ONLY;


Step 2. Execution History from AWR (no time filter)


COL snap_time        FOR a20

COL plan_hash_value  FOR 9999999999

COL execs            FOR 999,999

COL elapsed_s        FOR 999,999,999.99

COL cpu_s            FOR 999,999,999.99

COL io_s             FOR 999,999,999.99

COL gc_s             FOR 999,999,999.99

COL buffer_gets      FOR 999,999,999

COL disk_reads       FOR 999,999,999

COL rows_proc        FOR 999,999,999


SELECT TO_CHAR(s.begin_interval_time,'YYYY-MM-DD HH24:MI') snap_time,

       ss.plan_hash_value,

       ss.executions_delta        execs,

       ROUND(ss.elapsed_time_delta/1e6,2) elapsed_s,

       ROUND(ss.cpu_time_delta/1e6,2)     cpu_s,

       ROUND(ss.iowait_delta/1e6,2)       io_s,

       ROUND(ss.cluster_wait_time_delta/1e6,2) gc_s,

       ss.buffer_gets_delta       buffer_gets,

       ss.disk_reads_delta        disk_reads,

       ss.rows_processed_delta    rows_proc

FROM   dba_hist_sqlstat ss

JOIN   dba_hist_snapshot s

  ON ss.snap_id = s.snap_id

 AND ss.dbid    = s.dbid

 AND ss.instance_number = s.instance_number

WHERE  ss.sql_id='&sql_id'

ORDER  BY s.begin_interval_time DESC;



Step 3. ASH Analysis (Top Waits & Plan Lines)


COL wait_or_class FOR a35

COL op            FOR a40


SELECT NVL(event, wait_class) AS wait_or_class,

       COUNT(*) samples,

       ROUND(100*RATIO_TO_REPORT(COUNT(*)) OVER (),1) 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;


SELECT plan_hash_value,

       sql_plan_line_id,

       sql_plan_operation||' '||sql_plan_options op,

       COUNT(*) samples

FROM   dba_hist_active_sess_history

WHERE  sql_id='&sql_id'

GROUP  BY plan_hash_value, sql_plan_line_id, sql_plan_operation, sql_plan_options

ORDER  BY samples DESC FETCH FIRST 20 ROWS ONLY;



Step 4. RAC-Specific (GC waits by Instance & Object)


-- Instance distribution

SELECT instance_number, COUNT(*) samples

FROM   dba_hist_active_sess_history

WHERE  sql_id='&sql_id'

GROUP  BY instance_number

ORDER  BY samples DESC;


-- Objects contributing to GC waits

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

       COUNT(*) 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;












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