Search This Blog

Total Pageviews

Sunday, 12 December 2021

awr top sql


awr top sql....



awr top sql .sql from https://github.com/karlarao/run_awr-quickextract/blob/master/run_awr_topevents.sql
awr_topsqlx-exa.sql



-- awr_topsqlx-exa.sql
-- AWR Top SQL Report, a version of "Top SQL" but across SNAP_IDs with AAS metric and more details
-- Karl Arao, Oracle ACE (bit.ly/karlarao), OCP-DBA, RHCE
-- http://karlarao.wordpress.com
--
-- NOTES: SEE COMMENTS ON THE SCRIPT..ESPECIALLY ON SQL_TEXT, TIME_RANK, AND ORDER BY SECTIONS
--
-- Changes:
-- 20100512     added timestamp to filter specific workload periods, must uncomment to use
-- 20120825     added the join of dba_hist_sqltext to audit_actions to show the short name of command_type

--set feedback off pages 0 term off head on und off trimspool on echo off lines 4000 colsep ','


set feedback off pages 0 term off head on und off trimspool on echo off lines 4000 colsep 

set arraysize 5000
set termout off
set echo off verify off

COLUMN blocksize NEW_VALUE _blocksize NOPRINT
select distinct block_size blocksize from v$datafile;

COLUMN dbid NEW_VALUE _dbid NOPRINT
select dbid from v$database;

COLUMN name NEW_VALUE _instname NOPRINT
select lower(instance_name) name from v$instance;

COLUMN name NEW_VALUE _hostname NOPRINT
select lower(host_name) name from v$instance;

COLUMN instancenumber NEW_VALUE _instancenumber NOPRINT
select instance_number instancenumber from v$instance;

-- ttitle center 'AWR Top SQL Report' skip 2
set pagesize 50000
set linesize 1500

col snap_id             format 99999            heading -- "Snap|ID"
col tm                  format a15              heading -- "Snap|Start|Time"
col inst                format 90               heading -- "i|n|s|t|#"
col dur                 format 990.00           heading -- "Snap|Dur|(m)"
col sql_id              format a15              heading -- "SQL|ID"
col phv                 format 99999999999      heading -- "Plan|Hash|Value"
col module              format a50
col action              format a50
col elap                format 999990.00        heading -- "Ela|Time|(s)"
col elapexec            format 999990.00        heading -- "Ela|Time|per|exec|(s)"
col cput                format 999990.00        heading -- "CPU|Time|(s)"
col iowait              format 999990.00        heading -- "IO|Wait|(s)"
col appwait             format 999990.00        heading -- "App|Wait|(s)"
col concurwait          format 999990.00        heading -- "Ccr|Wait|(s)"
col clwait              format 999990.00        heading -- "Cluster|Wait|(s)"
col bget                format 99999999990      heading -- "LIO"
col dskr                format 99999999990      heading -- "PIO"
col dpath               format 99999999990      heading -- "Direct|Writes"
col rowp                format 99999999990      heading -- "Rows"
col exec                format 9999990          heading -- "Exec"
col prsc                format 999999990        heading -- "Parse|Count"
col pxexec              format 9999990          heading -- "PX|Server|Exec"
col icbytes             format 99999990         heading -- "IC|MB"
col offloadbytes        format 99999990         heading -- "Offload|MB"
col offloadreturnbytes  format 99999990         heading -- "Offload|return|MB"
col flashcachereads     format 99999990         heading -- "Flash|Cache|MB"
col uncompbytes         format 99999990         heading -- "Uncomp|MB"
col pctdbt              format 990              heading -- "DB Time|%"
col aas                 format 990.00           heading -- "A|A|S"
col time_rank           format 90               heading -- "Time|Rank"
col sql_text            format a6               heading -- "SQL|Text"
col fms                 format 99999999999999999999999999

VARIABLE  g_retention  NUMBER
DEFINE    p_default = 8
DEFINE    p_max = 300
SET VERIFY OFF
DECLARE
  v_default  NUMBER(3) := &p_default;
  v_max      NUMBER(3) := &p_max;
BEGIN
  select
    ((TRUNC(SYSDATE) + RETENTION - TRUNC(SYSDATE)) * 86400)/60/60/24 AS RETENTION_DAYS
    into :g_retention
  from dba_hist_wr_control
  where dbid in (select dbid from v$database);

  if :g_retention > v_default then
    :g_retention := v_max;
  else
    :g_retention := v_default;
  end if;
END;
/

spool awr_topsqlx_rlio-tableau-exa-&_instname-&_hostname..csv
     select *
       from (
             select
                  trim('&_instname') instname,
                  trim('&_dbid') db_id,
                  trim('&_hostname') hostname,
                  sqt.snap_id snap_id,
                  TO_CHAR(sqt.tm,'MM/DD/YY HH24:MI:SS') tm,
                  sqt.inst inst,
                  sqt.dur dur,
                  sqt.aas aas,
                  nvl((sqt.elap), to_number(null)) elap,
                  nvl((sqt.elapexec), 0) elapexec,
                  nvl((sqt.cput), to_number(null)) cput,
                  sqt.iowait iowait,
                  sqt.appwait appwait,
                  sqt.concurwait concurwait,
                  sqt.clwait clwait,
                  sqt.bget bget,
                  sqt.dskr dskr,
                  sqt.dpath dpath,
                  sqt.rowp rowp,
                  sqt.exec exec,
                  sqt.prsc prsc,
                  sqt.pxexec pxexec,
                  sqt.icbytes,
                  sqt.offloadbytes,
                  sqt.offloadreturnbytes,
                  sqt.flashcachereads,
                  sqt.uncompbytes,
                  sqt.time_rank time_rank,
                  sqt.sql_id sql_id,
                  sqt.phv phv,
                  sqt.fms fms,
                  sqt.parse_schema parse_schema,
                  substr(to_clob(decode(sqt.module, null, null, sqt.module)),1,50) module,
                  substr(to_clob(decode(sqt.action, null, null, sqt.action)),1,50) action,
                  st.sql_text sql_text     -- PUT/REMOVE COMMENT TO HIDE/SHOW THE SQL_TEXT
             from        (
                          select snap_id, tm, inst, dur, sql_id, phv, fms, parse_schema, module, action, elap, elapexec, cput, iowait, appwait, concurwait, clwait, bget, dskr, dpath, rowp, exec, prsc, pxexec, icbytes, offloadbytes, offloadreturnbytes, flashcachereads, uncompbytes, aas, time_rank
                          from
                                             (
                                               select
                                                      s0.snap_id snap_id,
                                                      s0.END_INTERVAL_TIME tm,
                                                      s0.instance_number inst,
                                                      round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
                                                              + EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
                                                              + EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
                                                              + EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) dur,
                                                      e.sql_id sql_id,
                                                      e.plan_hash_value phv,
                                                      e.force_matching_signature fms,
                                                      e.parsing_schema_name parse_schema,
                                                      max(e.module) module,
                                                      max(e.action) action,
                                                      sum(e.elapsed_time_delta)/1000000 elap,
                                                      decode((sum(e.executions_delta)), 0, to_number(null), ((sum(e.elapsed_time_delta)) / (sum(e.executions_delta)) / 1000000)) elapexec,
                                                      sum(e.cpu_time_delta)/1000000     cput,
                                                      sum(e.iowait_delta)/1000000 iowait,
                                                      sum(e.apwait_delta)/1000000 appwait,
                                                      sum(e.ccwait_delta)/1000000 concurwait,
                                                      sum(e.clwait_delta)/1000000 clwait,
                                                      sum(e.buffer_gets_delta) bget,
                                                      sum(e.disk_reads_delta) dskr,
                                                      sum(e.direct_writes_delta) dpath,
                                                      sum(e.rows_processed_delta) rowp,
                                                      sum(e.executions_delta)   exec,
                                                      sum(e.parse_calls_delta) prsc,
                                                      sum(e.px_servers_execs_delta) pxexec,
                                                      sum(e.io_interconnect_bytes_delta)/1024/1024 icbytes,
                                                      sum(e.io_offload_elig_bytes_delta)/1024/1024 offloadbytes,
                                                      sum(e.io_offload_return_bytes_delta)/1024/1024 offloadreturnbytes,
                                                      (sum(e.optimized_physical_reads_delta)* &_blocksize)/1024/1024 flashcachereads,
                                                      sum(e.cell_uncompressed_bytes_delta)/1024/1024 uncompbytes,
                                                      (sum(e.elapsed_time_delta)/1000000) / ((round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
                                                                                            + EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
                                                                                            + EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
                                                                                            + EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2))*60) aas,
                                                      DENSE_RANK() OVER (
                                                      PARTITION BY s0.snap_id ORDER BY e.buffer_gets_delta + e.disk_reads_delta DESC) time_rank
                                               from
                                                   dba_hist_snapshot s0,
                                                   dba_hist_snapshot s1,
                                                   dba_hist_sqlstat e
                                                   where
                                                    s0.dbid                   = &_dbid                -- CHANGE THE DBID HERE!
                                                    AND s1.dbid               = s0.dbid
                                                    and e.dbid                = s0.dbid
                                                    --AND s0.instance_number    = &_instancenumber      -- CHANGE THE INSTANCE_NUMBER HERE!
                                                    AND s1.instance_number    = s0.instance_number
                                                    and e.instance_number     = s0.instance_number
                                                    AND s1.snap_id            = s0.snap_id + 1
                                                    and e.snap_id             = s0.snap_id + 1
                                               group by
                                                    s0.snap_id, s0.END_INTERVAL_TIME, s0.instance_number, e.sql_id, e.plan_hash_value, e.force_matching_signature, e.parsing_schema_name, e.buffer_gets_delta, e.disk_reads_delta, s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME
                                             )
                          where
                          time_rank <= 15                                     -- GET TOP 5 SQL ACROSS SNAP_IDs... YOU CAN ALTER THIS TO HAVE MORE DATA POINTS
                         )
                        sqt,
                        (select sql_id, dbid, nvl(b.name, a.command_type) sql_text from dba_hist_sqltext a, audit_actions b where a.command_type =  b.action(+)) st
             where st.sql_id(+)             = sqt.sql_id
             and st.dbid(+)                 = &_dbid
-- AND TO_CHAR(tm,'D') >= 1                                                  -- Day of week: 1=Sunday 7=Saturday
-- AND TO_CHAR(tm,'D') <= 7
-- AND TO_CHAR(tm,'HH24MI') >= 0900                                          -- Hour
-- AND TO_CHAR(tm,'HH24MI') <= 1800
-- AND tm >= TO_DATE('2010-jan-17 00:00:00','yyyy-mon-dd hh24:mi:ss')     -- Data range
-- AND tm <= TO_DATE('2010-aug-22 23:59:59','yyyy-mon-dd hh24:mi:ss')
-- AND snap_id in (338,339)
-- AND snap_id = 338
-- AND snap_id >= 335 and snap_id <= 339
-- AND lower(st.sql_text) like 'select%'
-- AND lower(st.sql_text) like 'insert%'
-- AND lower(st.sql_text) like 'update%'
-- AND lower(st.sql_text) like 'merge%'
-- AND pxexec > 0
-- AND aas > .5
             order by
             snap_id                             -- TO GET SQL OUTPUT ACROSS SNAP_IDs SEQUENTIALLY AND ASC
             -- nvl(sqt.elap, -1) desc, sqt.sql_id     -- TO GET SQL OUTPUT BY ELAPSED TIME
             )
-- where rownum <= 20
WHERE
to_date(tm,'MM/DD/YY HH24:MI:SS') > sysdate - :g_retention
;
spool off
host sed -n -i '2,$ p' awr_topsqlx_rlio-tableau-exa-&_instname-&_hostname..csv
-- host gzip -v awr_topsqlx-tableau-exa-&_instname-&_hostname..csv
-- host tar -cvf awr_topsqlx-tableau-exa-&_instname-&_hostname..tar awr_topsqlx-tableau-exa-&_instname-&_hostname..csv.gz
-- host rm awr_topsqlx-tableau-exa-&_instname-&_hostname..csv.gz



-- *********************************************************

---with con_id



-- awr_topsqlx-exa.sql
-- AWR Top SQL Report, a version of "Top SQL" but across SNAP_IDs with AAS metric and more details
-- Karl Arao, Oracle ACE (bit.ly/karlarao), OCP-DBA, RHCE
-- http://karlarao.wordpress.com
--
-- NOTES: SEE COMMENTS ON THE SCRIPT..ESPECIALLY ON SQL_TEXT, TIME_RANK, AND ORDER BY SECTIONS
--
-- Changes:
-- 20100512     added timestamp to filter specific workload periods, must uncomment to use
-- 20120825     added the join of dba_hist_sqltext to audit_actions to show the short name of command_type

-- set feedback off pages 0 term off head on und off trimspool on echo off lines 4000 colsep ','
set feedback off pages 0 term off head on und off trimspool on echo off lines 4000 colsep
set arraysize 5000
set termout off
set echo off verify off

COLUMN blocksize NEW_VALUE _blocksize NOPRINT
select distinct block_size blocksize from v$datafile;

COLUMN dbid NEW_VALUE _dbid NOPRINT
select dbid from v$database;

COLUMN name NEW_VALUE _instname NOPRINT
select lower(instance_name) name from v$instance;

COLUMN name NEW_VALUE _hostname NOPRINT
select lower(host_name) name from v$instance;

COLUMN instancenumber NEW_VALUE _instancenumber NOPRINT
select instance_number instancenumber from v$instance;

-- ttitle center 'AWR Top SQL Report' skip 2
set pagesize 50000
set linesize 1500

col snap_id             format 99999            heading -- "Snap|ID"
col tm                  format a15              heading -- "Snap|Start|Time"
col inst                format 90               heading -- "i|n|s|t|#"
col dur                 format 990.00           heading -- "Snap|Dur|(m)"
col sql_id              format a15              heading -- "SQL|ID"
col phv                 format 99999999999      heading -- "Plan|Hash|Value"
col module              format a50
col action              format a50
col elap                format 999990.00        heading -- "Ela|Time|(s)"
col elapexec            format 999990.00        heading -- "Ela|Time|per|exec|(s)"
col cput                format 999990.00        heading -- "CPU|Time|(s)"
col iowait              format 999990.00        heading -- "IO|Wait|(s)"
col appwait             format 999990.00        heading -- "App|Wait|(s)"
col concurwait          format 999990.00        heading -- "Ccr|Wait|(s)"
col clwait              format 999990.00        heading -- "Cluster|Wait|(s)"
col bget                format 99999999990      heading -- "LIO"
col dskr                format 99999999990      heading -- "PIO"
col dpath               format 99999999990      heading -- "Direct|Writes"
col rowp                format 99999999990      heading -- "Rows"
col exec                format 9999990          heading -- "Exec"
col prsc                format 999999990        heading -- "Parse|Count"
col pxexec              format 9999990          heading -- "PX|Server|Exec"
col icbytes             format 99999990         heading -- "IC|MB"
col offloadbytes        format 99999990         heading -- "Offload|MB"
col offloadreturnbytes  format 99999990         heading -- "Offload|return|MB"
col flashcachereads     format 99999990         heading -- "Flash|Cache|MB"
col uncompbytes         format 99999990         heading -- "Uncomp|MB"
col pctdbt              format 990              heading -- "DB Time|%"
col aas                 format 990.00           heading -- "A|A|S"
col time_rank           format 90               heading -- "Time|Rank"
col sql_text            format a6               heading -- "SQL|Text"
col fms                 format 99999999999999999999999999

VARIABLE  g_retention  NUMBER
DEFINE    p_default = 8
DEFINE    p_max = 300
SET VERIFY OFF
DECLARE
  v_default  NUMBER(3) := &p_default;
  v_max      NUMBER(3) := &p_max;
BEGIN
  select
    ((TRUNC(SYSDATE) + RETENTION - TRUNC(SYSDATE)) * 86400)/60/60/24 AS RETENTION_DAYS
    into :g_retention
  from dba_hist_wr_control
  where dbid in (select dbid from v$database);

  if :g_retention > v_default then
    :g_retention := v_max;
  else
    :g_retention := v_default;
  end if;
END;
/

-- spool awr_topsqlx_rlio-tableau-exa-&_instname-&_hostname..csv
     select *
       from (
             select
                  trim('&_instname') instname,
                  trim('&_dbid') db_id,
                  trim('&_hostname') hostname,
                  sqt.snap_id snap_id,
				  con_id,
                  TO_CHAR(sqt.tm,'MM/DD/YY HH24:MI:SS') tm,
                  sqt.inst inst,
                  sqt.dur dur,
                  sqt.aas aas,
                  nvl((sqt.elap), to_number(null)) elap,
                  nvl((sqt.elapexec), 0) elapexec,
                  nvl((sqt.cput), to_number(null)) cput,
                  sqt.iowait iowait,
                  sqt.appwait appwait,
                  sqt.concurwait concurwait,
                  sqt.clwait clwait,
                  sqt.bget bget,
                  sqt.dskr dskr,
                  sqt.dpath dpath,
                  sqt.rowp rowp,
                  sqt.exec exec,
                  sqt.prsc prsc,
                  sqt.pxexec pxexec,
                  sqt.icbytes,
                  sqt.offloadbytes,
                  sqt.offloadreturnbytes,
                  sqt.flashcachereads,
                  sqt.uncompbytes,
                  sqt.time_rank time_rank,
                  sqt.sql_id sql_id,
                  sqt.phv phv,
                  sqt.fms fms,
                  sqt.parse_schema parse_schema,
                  substr(to_clob(decode(sqt.module, null, null, sqt.module)),1,50) module,
                  substr(to_clob(decode(sqt.action, null, null, sqt.action)),1,50) action,
                  st.sql_text sql_text     -- PUT/REMOVE COMMENT TO HIDE/SHOW THE SQL_TEXT
             from        (
                          select snap_id,con_id, tm, inst, dur, sql_id, phv, fms, parse_schema, module, action, elap, elapexec, cput, iowait, appwait, concurwait, clwait, bget, dskr, dpath, rowp, exec, prsc, pxexec, icbytes, offloadbytes, offloadreturnbytes, flashcachereads, uncompbytes, aas, time_rank
                          from          (    select
                                                      s0.snap_id snap_id,
													  e.con_id con_id,
                                                      s0.END_INTERVAL_TIME tm,
                                                      s0.instance_number inst,
                                                      round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
                                                              + EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
                                                              + EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
                                                              + EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) dur,
                                                      e.sql_id sql_id,
                                                      e.plan_hash_value phv,
                                                      e.force_matching_signature fms,
                                                      e.parsing_schema_name parse_schema,
                                                      max(e.module) module,
                                                      max(e.action) action,
                                                      sum(e.elapsed_time_delta)/1000000 elap,
                                                      decode((sum(e.executions_delta)), 0, to_number(null), ((sum(e.elapsed_time_delta)) / (sum(e.executions_delta)) / 1000000)) elapexec,
                                                      sum(e.cpu_time_delta)/1000000     cput,
                                                      sum(e.iowait_delta)/1000000 iowait,
                                                      sum(e.apwait_delta)/1000000 appwait,
                                                      sum(e.ccwait_delta)/1000000 concurwait,
                                                      sum(e.clwait_delta)/1000000 clwait,
                                                      sum(e.buffer_gets_delta) bget,
                                                      sum(e.disk_reads_delta) dskr,
                                                      sum(e.direct_writes_delta) dpath,
                                                      sum(e.rows_processed_delta) rowp,
                                                      sum(e.executions_delta)   exec,
                                                      sum(e.parse_calls_delta) prsc,
                                                      sum(e.px_servers_execs_delta) pxexec,
                                                      sum(e.io_interconnect_bytes_delta)/1024/1024 icbytes,
                                                      sum(e.io_offload_elig_bytes_delta)/1024/1024 offloadbytes,
                                                      sum(e.io_offload_return_bytes_delta)/1024/1024 offloadreturnbytes,
                                                      (sum(e.optimized_physical_reads_delta)* &_blocksize)/1024/1024 flashcachereads,
                                                      sum(e.cell_uncompressed_bytes_delta)/1024/1024 uncompbytes,
                                                      (sum(e.elapsed_time_delta)/1000000) / ((round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
                                                                                            + EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
                                                                                            + EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
                                                                                            + EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2))*60) aas,
                                                      DENSE_RANK() OVER (
                                                      PARTITION BY s0.snap_id ORDER BY e.buffer_gets_delta + e.disk_reads_delta DESC) time_rank
                                               from
                                                   dba_hist_snapshot s0,
                                                   dba_hist_snapshot s1,
                                                   dba_hist_sqlstat e
                                                   where
                                                    s0.dbid                   = &_dbid                -- CHANGE THE DBID HERE!
                                                    AND s1.dbid               = s0.dbid
                                                    and e.dbid                = s0.dbid
                                                    --AND s0.instance_number    = &_instancenumber      -- CHANGE THE INSTANCE_NUMBER HERE!
                                                    AND s1.instance_number    = s0.instance_number
                                                    and e.instance_number     = s0.instance_number
                                                    AND s1.snap_id            = s0.snap_id + 1
                                                    and e.snap_id             = s0.snap_id + 1
                                               group by
                                                    s0.snap_id,e.con_id, s0.END_INTERVAL_TIME, s0.instance_number, e.sql_id, e.plan_hash_value, e.force_matching_signature, e.parsing_schema_name, e.buffer_gets_delta, e.disk_reads_delta, s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME
                                             )
                          where
                          time_rank <= 15                                     -- GET TOP 5 SQL ACROSS SNAP_IDs... YOU CAN ALTER THIS TO HAVE MORE DATA POINTS
                         )
                        sqt,
                        (select sql_id, dbid, nvl(b.name, a.command_type) sql_text from dba_hist_sqltext a, audit_actions b where a.command_type =  b.action(+)) st
             where st.sql_id(+)             = sqt.sql_id
             and st.dbid(+)                 = &_dbid
-- AND TO_CHAR(tm,'D') >= 1                                                  -- Day of week: 1=Sunday 7=Saturday
-- AND TO_CHAR(tm,'D') <= 7
-- AND TO_CHAR(tm,'HH24MI') >= 0900                                          -- Hour
-- AND TO_CHAR(tm,'HH24MI') <= 1800
-- AND tm >= TO_DATE('2010-jan-17 00:00:00','yyyy-mon-dd hh24:mi:ss')     -- Data range
-- AND tm <= TO_DATE('2010-aug-22 23:59:59','yyyy-mon-dd hh24:mi:ss')
-- AND snap_id in (338,339)
-- AND snap_id = 338
-- AND snap_id >= 335 and snap_id <= 339
-- AND lower(st.sql_text) like 'select%'
-- AND lower(st.sql_text) like 'insert%'
-- AND lower(st.sql_text) like 'update%'
-- AND lower(st.sql_text) like 'merge%'
-- AND pxexec > 0
-- AND aas > .5
             order by
             snap_id                             -- TO GET SQL OUTPUT ACROSS SNAP_IDs SEQUENTIALLY AND ASC
             -- nvl(sqt.elap, -1) desc, sqt.sql_id     -- TO GET SQL OUTPUT BY ELAPSED TIME
             )
-- where rownum <= 20
WHERE
to_date(tm,'MM/DD/YY HH24:MI:SS') > sysdate - :g_retention
;



=====



set verify off
column event format a40
column BEGIN_INTERVAL_TIME format a30


set linesize 500  pages 900

VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;


select case wait_rank when 1 then inst_id end "Inst Num",
 case wait_rank when 1 then snap_id end "Snap Id",
 case wait_rank when 1 then begin_snap end "Begin Snap",
 case wait_rank when 1 then end_snap end "End Snap",
 event_name "Event",
 total_waits "Waits",
 time_waited "Time(s)",
 round((time_waited/total_waits)*1000) "Avg wait(ms)",
 round((time_waited/db_time)*100, 2) "% DB time",
 substr(wait_class, 1, 15) "Wait Class"
from (
select
  inst_id,
  snap_id, to_char(begin_snap, 'DD-MM-YY hh24:mi:ss') begin_snap,
  to_char(end_snap, 'hh24:mi:ss') end_snap,
  event_name,
  wait_class,
  total_waits,
  time_waited,
  dense_rank() over (partition by inst_id, snap_id order by time_waited desc)-1 wait_rank,
  max(time_waited) over (partition by inst_id, snap_id) db_time
from (
select
  s.instance_number inst_id,
  s.snap_id,
  s.begin_interval_time begin_snap,
  s.end_interval_time end_snap,
  event_name,
  wait_class,
  total_waits-lag(total_waits, 1, total_waits) over
   (partition by s.startup_time, s.instance_number, stats.event_name order by s.snap_id) total_waits,
  time_waited-lag(time_waited, 1, time_waited) over
   (partition by s.startup_time, s.instance_number, stats.event_name order by s.snap_id) time_waited,
  min(s.snap_id) over (partition by s.startup_time, s.instance_number, stats.event_name) min_snap_id
from (
 select dbid, instance_number, snap_id, event_name, wait_class, total_waits_fg total_waits, round(time_waited_micro_fg/1000000, 2) time_waited
  from dba_hist_system_event
  where wait_class not in ('Idle', 'System I/O')
 union all
 select dbid, instance_number, snap_id, stat_name event_name, null wait_class, null total_waits, round(value/1000000, 2) time_waited
  from dba_hist_sys_time_model
  where stat_name in ('DB CPU', 'DB time')
) stats, dba_hist_snapshot s
 where stats.instance_number=s.instance_number
  and stats.snap_id=s.snap_id
  and stats.dbid=s.dbid
 -- and s.instance_number = decode(:INST_NUMBER,0,s.instance_number,&inst)
  and stats.snap_id between :BgnSnap and :EndSnap
 and stats.dbid = :DID
) where snap_id > min_snap_id and nvl(total_waits,1) > 0
) where event_name!='DB time' and wait_rank <= 5
order by inst_id, snap_id;





  Inst Num    Snap Id Begin Snap        End Snap Event                                                                 Waits   Time(s) Avg wait(ms)  % DB time Wait Class
---------- ---------- ----------------- -------- ---------------------------------------------------------------- ---------- ---------- ------------ ---------- ------------------------------------------------------------
         1      16216 13-08-23 12:00:01 13:00:08 db file sequential read                                             7812655   2343.19     0      23.65 User I/O
                                                 DB CPU                                                                         2065.5            20.85
                                                 gc buffer busy acquire                                              1929481   1077.08     1      10.87 Cluster
                                                 read by other session                                               1954351     627.3     0       6.33 User I/O
                                                 gc cr disk read                                                     2981969    605.31     0       6.11 Cluster
         2      16216 13-08-23 12:00:01 13:00:08 DB CPU                                                                         276.61            56.44
                                                 acknowledge over PGA limit                                            10634    102.02    10      20.82 Scheduler
                                                 enq: IV -  contention                                                  6527      3.17     0        .65 Other
                                                 PGA memory operation                                                 156406      1.91     0        .39 Other
                                                 row cache lock                                                         2133       .79     0        .16 Concurrency

10 rows selected.





No comments:

Oracle DBA

anuj blog Archive