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.





Awr top events ...

Awr top events ... 

Awr top events .......

from https://github.com/karlarao/run_awr-quickextract/blob/master/run_awr_topevents.sql ==== --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 set arraysize 5000 set termout off set echo off verify off 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 Events Report' skip 2 set pagesize 50000 set linesize 550 col instname format a15 col hostname format a30 col snap_id format 99999 heading snap_id -- "snapid" col tm format a17 heading tm -- "tm" col inst format 90 heading inst -- "inst" col dur format 999990.00 heading dur -- "dur" col event format a55 heading event -- "Event" col event_rank format 90 heading event_rank -- "EventRank" col waits format 9999999990.00 heading waits -- "Waits" col time format 9999999990.00 heading time -- "Timesec" col avgwt format 99990.00 heading avgwt -- "Avgwtms" col pctdbt format 9990.0 heading pctdbt -- "DBTimepct" col aas format 990.0 heading aas -- "Aas" col wait_class format a15 heading wait_class -- "WaitClass" 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_topevents-tableau-&_instname-&_hostname..csv select trim('&_instname') instname, trim('&_dbid') db_id, trim('&_hostname') hostname, snap_id, tm, inst, dur, event, event_rank, waits, time, avgwt, pctdbt, aas, wait_class from (select snap_id, TO_CHAR(tm,'MM/DD/YY HH24:MI:SS') tm, inst, dur, event, waits, time, avgwt, pctdbt, aas, wait_class, DENSE_RANK() OVER ( PARTITION BY snap_id ORDER BY time DESC) event_rank from ( select * from (select * 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.event_name event, e.total_waits - nvl(b.total_waits,0) waits, round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2) time, -- THIS IS EVENT (sec) round (decode ((e.total_waits - nvl(b.total_waits, 0)), 0, to_number(NULL), ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000) / (e.total_waits - nvl(b.total_waits,0))), 2) avgwt, ((round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2)) / NULLIF(((s5t1.value - nvl(s5t0.value,0)) / 1000000),0))*100 as pctdbt, -- THIS IS EVENT (sec) / DB TIME (sec) (round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2))/60 / 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) aas, -- THIS IS EVENT (min) / SnapDur (min) TO GET THE % DB CPU ON AAS e.wait_class wait_class from dba_hist_snapshot s0, dba_hist_snapshot s1, dba_hist_system_event b, dba_hist_system_event e, dba_hist_sys_time_model s5t0, dba_hist_sys_time_model s5t1 where s0.dbid = &_dbid -- CHANGE THE DBID HERE! AND s1.dbid = s0.dbid and b.dbid(+) = s0.dbid and e.dbid = s0.dbid AND s5t0.dbid = s0.dbid AND s5t1.dbid = s0.dbid --AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE! AND s1.instance_number = s0.instance_number and b.instance_number(+) = s0.instance_number and e.instance_number = s0.instance_number AND s5t0.instance_number = s0.instance_number AND s5t1.instance_number = s0.instance_number AND s1.snap_id = s0.snap_id + 1 AND b.snap_id(+) = s0.snap_id and e.snap_id = s0.snap_id + 1 AND s5t0.snap_id = s0.snap_id AND s5t1.snap_id = s0.snap_id + 1 AND s5t0.stat_name = 'DB time' AND s5t1.stat_name = s5t0.stat_name and b.event_id = e.event_id and e.wait_class != 'Idle' and e.total_waits > nvl(b.total_waits,0) and e.event_name not in ('smon timer', 'pmon timer', 'dispatcher timer', 'dispatcher listen timer', 'rdbms ipc message') order by snap_id, time desc, waits desc, event) union all 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, 'CPU time', 0, round ((s6t1.value - s6t0.value) / 1000000, 2) as time, -- THIS IS DB CPU (sec) 0, ((round ((s6t1.value - s6t0.value) / 1000000, 2)) / NULLIF(((s5t1.value - nvl(s5t0.value,0)) / 1000000),0))*100 as pctdbt, -- THIS IS DB CPU (sec) / DB TIME (sec)..TO GET % OF DB CPU ON DB TIME FOR TOP 5 TIMED EVENTS SECTION (round ((s6t1.value - s6t0.value) / 1000000, 2))/60 / 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) aas, -- THIS IS DB CPU (min) / SnapDur (min) TO GET THE % DB CPU ON AAS 'CPU' from dba_hist_snapshot s0, dba_hist_snapshot s1, dba_hist_sys_time_model s6t0, dba_hist_sys_time_model s6t1, dba_hist_sys_time_model s5t0, dba_hist_sys_time_model s5t1 WHERE s0.dbid = &_dbid -- CHANGE THE DBID HERE! AND s1.dbid = s0.dbid AND s6t0.dbid = s0.dbid AND s6t1.dbid = s0.dbid AND s5t0.dbid = s0.dbid AND s5t1.dbid = s0.dbid --AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE! AND s1.instance_number = s0.instance_number AND s6t0.instance_number = s0.instance_number AND s6t1.instance_number = s0.instance_number AND s5t0.instance_number = s0.instance_number AND s5t1.instance_number = s0.instance_number AND s1.snap_id = s0.snap_id + 1 AND s6t0.snap_id = s0.snap_id AND s6t1.snap_id = s0.snap_id + 1 AND s5t0.snap_id = s0.snap_id AND s5t1.snap_id = s0.snap_id + 1 AND s6t0.stat_name = 'DB CPU' AND s6t1.stat_name = s6t0.stat_name AND s5t0.stat_name = 'DB time' AND s5t1.stat_name = s5t0.stat_name union all (select dbtime.snap_id, dbtime.tm, dbtime.inst, dbtime.dur, 'CPU wait', 0, round(dbtime.time - accounted_dbtime.time, 2) time, -- THIS IS UNACCOUNTED FOR DB TIME (sec) 0, ((dbtime.aas - accounted_dbtime.aas)/ NULLIF(nvl(dbtime.aas,0),0))*100 as pctdbt, -- THIS IS UNACCOUNTED FOR DB TIME (sec) / DB TIME (sec) round(dbtime.aas - accounted_dbtime.aas, 2) aas, -- AAS OF UNACCOUNTED FOR DB TIME 'CPU wait' from (select s0.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, 'DB time', 0, round ((s5t1.value - s5t0.value) / 1000000, 2) as time, -- THIS IS DB time (sec) 0, 0, (round ((s5t1.value - s5t0.value) / 1000000, 2))/60 / 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) aas, 'DB time' from dba_hist_snapshot s0, dba_hist_snapshot s1, dba_hist_sys_time_model s5t0, dba_hist_sys_time_model s5t1 WHERE s0.dbid = &_dbid -- CHANGE THE DBID HERE! AND s1.dbid = s0.dbid AND s5t0.dbid = s0.dbid AND s5t1.dbid = s0.dbid --AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE! AND s1.instance_number = s0.instance_number AND s5t0.instance_number = s0.instance_number AND s5t1.instance_number = s0.instance_number AND s1.snap_id = s0.snap_id + 1 AND s5t0.snap_id = s0.snap_id AND s5t1.snap_id = s0.snap_id + 1 AND s5t0.stat_name = 'DB time' AND s5t1.stat_name = s5t0.stat_name) dbtime, (select snap_id, inst, sum(time) time, sum(AAS) aas from (select * 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.event_name event, e.total_waits - nvl(b.total_waits,0) waits, round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2) time, -- THIS IS EVENT (sec) round (decode ((e.total_waits - nvl(b.total_waits, 0)), 0, to_number(NULL), ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000) / (e.total_waits - nvl(b.total_waits,0))), 2) avgwt, ((round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2)) / NULLIF(((s5t1.value - nvl(s5t0.value,0)) / 1000000),0))*100 as pctdbt, -- THIS IS EVENT (sec) / DB TIME (sec) (round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2))/60 / 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) aas, -- THIS IS EVENT (min) / SnapDur (min) TO GET THE % DB CPU ON AAS e.wait_class wait_class from dba_hist_snapshot s0, dba_hist_snapshot s1, dba_hist_system_event b, dba_hist_system_event e, dba_hist_sys_time_model s5t0, dba_hist_sys_time_model s5t1 where s0.dbid = &_dbid -- CHANGE THE DBID HERE! AND s1.dbid = s0.dbid and b.dbid(+) = s0.dbid and e.dbid = s0.dbid AND s5t0.dbid = s0.dbid AND s5t1.dbid = s0.dbid --AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE! AND s1.instance_number = s0.instance_number and b.instance_number(+) = s0.instance_number and e.instance_number = s0.instance_number AND s5t0.instance_number = s0.instance_number AND s5t1.instance_number = s0.instance_number AND s1.snap_id = s0.snap_id + 1 AND b.snap_id(+) = s0.snap_id and e.snap_id = s0.snap_id + 1 AND s5t0.snap_id = s0.snap_id AND s5t1.snap_id = s0.snap_id + 1 AND s5t0.stat_name = 'DB time' AND s5t1.stat_name = s5t0.stat_name and b.event_id = e.event_id and e.wait_class != 'Idle' and e.total_waits > nvl(b.total_waits,0) and e.event_name not in ('smon timer', 'pmon timer', 'dispatcher timer', 'dispatcher listen timer', 'rdbms ipc message') order by snap_id, time desc, waits desc, event) union all 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, 'CPU time', 0, round ((s6t1.value - s6t0.value) / 1000000, 2) as time, -- THIS IS DB CPU (sec) 0, ((round ((s6t1.value - s6t0.value) / 1000000, 2)) / NULLIF(((s5t1.value - nvl(s5t0.value,0)) / 1000000),0))*100 as pctdbt, -- THIS IS DB CPU (sec) / DB TIME (sec)..TO GET % OF DB CPU ON DB TIME FOR TOP 5 TIMED EVENTS SECTION (round ((s6t1.value - s6t0.value) / 1000000, 2))/60 / 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) aas, -- THIS IS DB CPU (min) / SnapDur (min) TO GET THE % DB CPU ON AAS 'CPU' from dba_hist_snapshot s0, dba_hist_snapshot s1, dba_hist_sys_time_model s6t0, dba_hist_sys_time_model s6t1, dba_hist_sys_time_model s5t0, dba_hist_sys_time_model s5t1 WHERE s0.dbid = &_dbid -- CHANGE THE DBID HERE! AND s1.dbid = s0.dbid AND s6t0.dbid = s0.dbid AND s6t1.dbid = s0.dbid AND s5t0.dbid = s0.dbid AND s5t1.dbid = s0.dbid --AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE! AND s1.instance_number = s0.instance_number AND s6t0.instance_number = s0.instance_number AND s6t1.instance_number = s0.instance_number AND s5t0.instance_number = s0.instance_number AND s5t1.instance_number = s0.instance_number AND s1.snap_id = s0.snap_id + 1 AND s6t0.snap_id = s0.snap_id AND s6t1.snap_id = s0.snap_id + 1 AND s5t0.snap_id = s0.snap_id AND s5t1.snap_id = s0.snap_id + 1 AND s6t0.stat_name = 'DB CPU' AND s6t1.stat_name = s6t0.stat_name AND s5t0.stat_name = 'DB time' AND s5t1.stat_name = s5t0.stat_name ) group by snap_id, inst) accounted_dbtime where dbtime.snap_id = accounted_dbtime.snap_id and dbtime.inst = accounted_dbtime.inst ) ) ) ) WHERE event_rank <= 5 AND to_date(tm,'MM/DD/YY HH24:MI:SS') > sysdate - :g_retention -- 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 = 495 -- and snap_id >= 495 and snap_id <= 496 -- and event = 'db file sequential read' -- and event like 'CPU%' -- and avgwt > 5 -- and aas > .5 -- and wait_class = 'CPU' -- and wait_class like '%I/O%' -- and event_rank in (1,2,3) ORDER BY snap_id; --- =================================================== --with con_id --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 set arraysize 5000 set termout off set echo off verify off 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 Events Report' skip 2 set pagesize 50000 set linesize 550 col instname format a15 col hostname format a30 col snap_id format 99999 heading snap_id -- "snapid" col tm format a17 heading tm -- "tm" col inst format 90 heading inst -- "inst" col dur format 999990.00 heading dur -- "dur" col event format a55 heading event -- "Event" col event_rank format 90 heading event_rank -- "EventRank" col waits format 9999999990.00 heading waits -- "Waits" col time format 9999999990.00 heading time -- "Timesec" col avgwt format 99990.00 heading avgwt -- "Avgwtms" col pctdbt format 9990.0 heading pctdbt -- "DBTimepct" col aas format 990.0 heading aas -- "Aas" col wait_class format a15 heading wait_class -- "WaitClass" 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_topevents-tableau-&_instname-&_hostname..csv select trim('&_instname') instname, trim('&_dbid') db_id, trim('&_hostname') hostname, snap_id, tm, inst, dur, event, event_rank, waits, time, avgwt, pctdbt, aas, wait_class from (select snap_id, TO_CHAR(tm,'MM/DD/YY HH24:MI:SS') tm, inst, dur, event, waits, time, avgwt, pctdbt, aas, wait_class, DENSE_RANK() OVER ( PARTITION BY snap_id ORDER BY time DESC) event_rank from ( select * from (select * from (select s0.snap_id snap_id, s5t1.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.event_name event, e.total_waits - nvl(b.total_waits,0) waits, round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2) time, -- THIS IS EVENT (sec) round (decode ((e.total_waits - nvl(b.total_waits, 0)), 0, to_number(NULL), ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000) / (e.total_waits - nvl(b.total_waits,0))), 2) avgwt, ((round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2)) / NULLIF(((s5t1.value - nvl(s5t0.value,0)) / 1000000),0))*100 as pctdbt, -- THIS IS EVENT (sec) / DB TIME (sec) (round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2))/60 / 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) aas, -- THIS IS EVENT (min) / SnapDur (min) TO GET THE % DB CPU ON AAS e.wait_class wait_class from dba_hist_snapshot s0, dba_hist_snapshot s1, dba_hist_system_event b, dba_hist_system_event e, dba_hist_sys_time_model s5t0, dba_hist_sys_time_model s5t1 where s0.dbid = &_dbid -- CHANGE THE DBID HERE! AND s1.dbid = s0.dbid and b.dbid(+) = s0.dbid and e.dbid = s0.dbid AND s5t0.dbid = s0.dbid AND s5t1.dbid = s0.dbid --AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE! AND s1.instance_number = s0.instance_number and b.instance_number(+) = s0.instance_number and e.instance_number = s0.instance_number AND s5t0.instance_number = s0.instance_number AND s5t1.instance_number = s0.instance_number AND s1.snap_id = s0.snap_id + 1 AND b.snap_id(+) = s0.snap_id and e.snap_id = s0.snap_id + 1 AND s5t0.snap_id = s0.snap_id AND s5t1.snap_id = s0.snap_id + 1 AND s5t0.stat_name = 'DB time' AND s5t1.stat_name = s5t0.stat_name and b.event_id = e.event_id and e.wait_class != 'Idle' and e.total_waits > nvl(b.total_waits,0) and e.event_name not in ('smon timer', 'pmon timer', 'dispatcher timer', 'dispatcher listen timer', 'rdbms ipc message') order by snap_id, time desc, waits desc, event) union all select s0.snap_id snap_id, s5t1.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, 'CPU time', 0, round ((s6t1.value - s6t0.value) / 1000000, 2) as time, -- THIS IS DB CPU (sec) 0, ((round ((s6t1.value - s6t0.value) / 1000000, 2)) / NULLIF(((s5t1.value - nvl(s5t0.value,0)) / 1000000),0))*100 as pctdbt, -- THIS IS DB CPU (sec) / DB TIME (sec)..TO GET % OF DB CPU ON DB TIME FOR TOP 5 TIMED EVENTS SECTION (round ((s6t1.value - s6t0.value) / 1000000, 2))/60 / 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) aas, -- THIS IS DB CPU (min) / SnapDur (min) TO GET THE % DB CPU ON AAS 'CPU' from dba_hist_snapshot s0, dba_hist_snapshot s1, dba_hist_sys_time_model s6t0, dba_hist_sys_time_model s6t1, dba_hist_sys_time_model s5t0, dba_hist_sys_time_model s5t1 WHERE s0.dbid = &_dbid -- CHANGE THE DBID HERE! AND s1.dbid = s0.dbid AND s6t0.dbid = s0.dbid AND s6t1.dbid = s0.dbid AND s5t0.dbid = s0.dbid AND s5t1.dbid = s0.dbid --AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE! AND s1.instance_number = s0.instance_number AND s6t0.instance_number = s0.instance_number AND s6t1.instance_number = s0.instance_number AND s5t0.instance_number = s0.instance_number AND s5t1.instance_number = s0.instance_number AND s1.snap_id = s0.snap_id + 1 AND s6t0.snap_id = s0.snap_id AND s6t1.snap_id = s0.snap_id + 1 AND s5t0.snap_id = s0.snap_id AND s5t1.snap_id = s0.snap_id + 1 AND s6t0.stat_name = 'DB CPU' AND s6t1.stat_name = s6t0.stat_name AND s5t0.stat_name = 'DB time' AND s5t1.stat_name = s5t0.stat_name union all (select dbtime.snap_id, dbtime.CON_ID, dbtime.tm, dbtime.inst, dbtime.dur, 'CPU wait', 0, round(dbtime.time - accounted_dbtime.time, 2) time, -- THIS IS UNACCOUNTED FOR DB TIME (sec) 0, ((dbtime.aas - accounted_dbtime.aas)/ NULLIF(nvl(dbtime.aas,0),0))*100 as pctdbt, -- THIS IS UNACCOUNTED FOR DB TIME (sec) / DB TIME (sec) round(dbtime.aas - accounted_dbtime.aas, 2) aas, -- AAS OF UNACCOUNTED FOR DB TIME 'CPU wait' from (select s0.snap_id, s5t1.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, 'DB time', 0, round ((s5t1.value - s5t0.value) / 1000000, 2) as time, -- THIS IS DB time (sec) 0, 0, (round ((s5t1.value - s5t0.value) / 1000000, 2))/60 / 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) aas, 'DB time' from dba_hist_snapshot s0, dba_hist_snapshot s1, dba_hist_sys_time_model s5t0, dba_hist_sys_time_model s5t1 WHERE s0.dbid = &_dbid -- CHANGE THE DBID HERE! AND s1.dbid = s0.dbid AND s5t0.dbid = s0.dbid AND s5t1.dbid = s0.dbid --AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE! AND s1.instance_number = s0.instance_number AND s5t0.instance_number = s0.instance_number AND s5t1.instance_number = s0.instance_number AND s1.snap_id = s0.snap_id + 1 AND s5t0.snap_id = s0.snap_id AND s5t1.snap_id = s0.snap_id + 1 AND s5t0.stat_name = 'DB time' AND s5t1.stat_name = s5t0.stat_name) dbtime, (select snap_id, inst, sum(time) time, sum(AAS) aas from (select * from (select s0.snap_id snap_id, s5t1.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.event_name event, e.total_waits - nvl(b.total_waits,0) waits, round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2) time, -- THIS IS EVENT (sec) round (decode ((e.total_waits - nvl(b.total_waits, 0)), 0, to_number(NULL), ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000) / (e.total_waits - nvl(b.total_waits,0))), 2) avgwt, ((round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2)) / NULLIF(((s5t1.value - nvl(s5t0.value,0)) / 1000000),0))*100 as pctdbt, -- THIS IS EVENT (sec) / DB TIME (sec) (round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2))/60 / 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) aas, -- THIS IS EVENT (min) / SnapDur (min) TO GET THE % DB CPU ON AAS e.wait_class wait_class from dba_hist_snapshot s0, dba_hist_snapshot s1, dba_hist_system_event b, dba_hist_system_event e, dba_hist_sys_time_model s5t0, dba_hist_sys_time_model s5t1 where s0.dbid = &_dbid -- CHANGE THE DBID HERE! AND s1.dbid = s0.dbid and b.dbid(+) = s0.dbid and e.dbid = s0.dbid AND s5t0.dbid = s0.dbid AND s5t1.dbid = s0.dbid --AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE! AND s1.instance_number = s0.instance_number and b.instance_number(+) = s0.instance_number and e.instance_number = s0.instance_number AND s5t0.instance_number = s0.instance_number AND s5t1.instance_number = s0.instance_number AND s1.snap_id = s0.snap_id + 1 AND b.snap_id(+) = s0.snap_id and e.snap_id = s0.snap_id + 1 AND s5t0.snap_id = s0.snap_id AND s5t1.snap_id = s0.snap_id + 1 AND s5t0.stat_name = 'DB time' AND s5t1.stat_name = s5t0.stat_name and b.event_id = e.event_id and e.wait_class != 'Idle' and e.total_waits > nvl(b.total_waits,0) and e.event_name not in ('smon timer', 'pmon timer', 'dispatcher timer', 'dispatcher listen timer', 'rdbms ipc message') order by snap_id, time desc, waits desc, event) union all select s0.snap_id snap_id, s5t1.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, 'CPU time', 0, round ((s6t1.value - s6t0.value) / 1000000, 2) as time, -- THIS IS DB CPU (sec) 0, ((round ((s6t1.value - s6t0.value) / 1000000, 2)) / NULLIF(((s5t1.value - nvl(s5t0.value,0)) / 1000000),0))*100 as pctdbt, -- THIS IS DB CPU (sec) / DB TIME (sec)..TO GET % OF DB CPU ON DB TIME FOR TOP 5 TIMED EVENTS SECTION (round ((s6t1.value - s6t0.value) / 1000000, 2))/60 / 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) aas, -- THIS IS DB CPU (min) / SnapDur (min) TO GET THE % DB CPU ON AAS 'CPU' from dba_hist_snapshot s0, dba_hist_snapshot s1, dba_hist_sys_time_model s6t0, dba_hist_sys_time_model s6t1, dba_hist_sys_time_model s5t0, dba_hist_sys_time_model s5t1 WHERE s0.dbid = &_dbid -- CHANGE THE DBID HERE! AND s1.dbid = s0.dbid AND s6t0.dbid = s0.dbid AND s6t1.dbid = s0.dbid AND s5t0.dbid = s0.dbid AND s5t1.dbid = s0.dbid --AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE! AND s1.instance_number = s0.instance_number AND s6t0.instance_number = s0.instance_number AND s6t1.instance_number = s0.instance_number AND s5t0.instance_number = s0.instance_number AND s5t1.instance_number = s0.instance_number AND s1.snap_id = s0.snap_id + 1 AND s6t0.snap_id = s0.snap_id AND s6t1.snap_id = s0.snap_id + 1 AND s5t0.snap_id = s0.snap_id AND s5t1.snap_id = s0.snap_id + 1 AND s6t0.stat_name = 'DB CPU' AND s6t1.stat_name = s6t0.stat_name AND s5t0.stat_name = 'DB time' AND s5t1.stat_name = s5t0.stat_name ) group by snap_id, inst) accounted_dbtime where dbtime.snap_id = accounted_dbtime.snap_id and dbtime.inst = accounted_dbtime.inst ) ) ) ) WHERE event_rank <= 5 AND to_date(tm,'MM/DD/YY HH24:MI:SS') > sysdate - :g_retention -- 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 = 495 -- and snap_id >= 495 and snap_id <= 496 -- and event = 'db file sequential read' -- and event like 'CPU%' -- and avgwt > 5 -- and aas > .5 -- and wait_class = 'CPU' -- and wait_class like '%I/O%' -- and event_rank in (1,2,3) ORDER BY snap_id ;

====
https://blog.dbi-services.com/show-the-top-10-events-from-latest-awr-snapshot/

Oracle Top 10 events from latest AWR snapshot


define 1='Top Timed Events'
define 2=''


with snap as (
  select * from (
    select dbid,lead(snap_id)over(partition by instance_number order by end_interval_time desc) bid,snap_id eid,row_number() over(order by end_interval_time desc) n
    from dba_hist_snapshot where dbid=(select dbid from v$database)
  ) where n=1
),
awr as (
        select rownum line,output
        from table(
                dbms_workload_repository.awr_report_text(l_dbid=>(select dbid from snap),l_inst_num=>(select instance_number from v$instance),l_bid=>(select bid from snap),l_eid=>(select eid from snap),l_options=>1+4+8)
        )
),
awr_sections as (
        select
         last_value(case when regexp_replace(output,' *DB/Inst.*$') in (''
        ,'Database Summary'
        ,'Database Instances Included In Report'
        ,'Top Event P1/P2/P3 Values'
        ,'Top SQL with Top Events'
        ,'Top SQL with Top Row Sources'
        ,'Top Sessions'
        ,'Top Blocking Sessions'
        ,'Top PL/SQL Procedures'
        ,'Top Events'
        ,'Top DB Objects'
        ,'Activity Over Time'
        ,'Wait Event Histogram Detail (64 msec to 2 sec)'
        ,'Wait Event Histogram Detail (4 sec to 2 min)'
        ,'Wait Event Histogram Detail (4 min to 1 hr)'
        ,'SQL ordered by Elapsed Time'
        ,'SQL ordered by CPU Time'
        ,'SQL ordered by User I/O Wait Time'
        ,'SQL ordered by Gets'
        ,'SQL ordered by Reads'
        ,'SQL ordered by Physical Reads (UnOptimized)'
        ,'SQL ordered by Optimized Reads'
        ,'SQL ordered by Executions'
        ,'SQL ordered by Parse Calls'
        ,'SQL ordered by Sharable Memory'
        ,'SQL ordered by Version Count'
        ,'SQL ordered by Cluster Wait Time'
        ,'Key Instance Activity Stats'
        ,'Instance Activity Stats'
        ,'IOStat by Function summary'
        ,'IOStat by Filetype summary'
        ,'IOStat by Function/Filetype summary'
        ,'Tablespace IO Stats'
        ,'File IO Stats'
        ,'Checkpoint Activity'
        ,'MTTR Advisory'
        ,'Segments by Logical Reads'
        ,'Segments by Physical Reads'
        ,'Segments by Direct Physical Reads'
        ,'Segments by Physical Read Requests'
        ,'Segments by UnOptimized Reads'
        ,'Segments by Optimized Reads'
        ,'Segments by Physical Write Requests'
        ,'Segments by Physical Writes'
        ,'Segments by Direct Physical Writes'
        ,'Segments by DB Blocks Changes'
       ,'Segments by Table Scans'
        ,'Segments by Row Lock Waits'
        ,'Segments by ITL Waits'
        ,'Segments by Buffer Busy Waits'
        ,'Segments by Global Cache Buffer Busy'
        ,'Segments by CR Blocks Received'
        ,'Segments by Current Blocks Received'
        ,'In-Memory Segments by Scans'
        ,'In-Memory Segments by DB Block Changes'
        ,'In-Memory Segments by Populate CUs'
        ,'In-Memory Segments by Repopulate CUs'
        ,'Interconnect Device Statistics'
        ,'Dynamic Remastering Stats'
        ,'Resource Manager Plan Statistics'
        ,'Resource Manager Consumer Group Statistics'
        ,'Replication System Resource Usage'
        ,'Replication SGA Usage'
        ,'GoldenGate Capture'
        ,'GoldenGate Capture Rate'
        ,'GoldenGate Apply Reader'
        ,'GoldenGate Apply Coordinator'
        ,'GoldenGate Apply Server'
        ,'GoldenGate Apply Coordinator Rate'
        ,'GoldenGate Apply Reader and Server Rate'
        ,'XStream Capture'
        ,'XStream Capture Rate'
        ,'XStream Apply Reader'
        ,'XStream Apply Coordinator'
        ,'XStream Apply Server'
        ,'XStream Apply Coordinator Rate'
        ,'XStream Apply Reader and Server Rate'
        ,'Table Statistics by DML Operations'
        ,'Table Statistics by Conflict Resolutions'
        ,'Replication Large Transaction Statistics'
        ,'Replication Long Running Transaction Statistics'
        ,'Streams Capture'
        ,'Streams Capture Rate'
        ,'Streams Apply'
        ,'Streams Apply Rate'
        ,'Buffered Queues'
        ,'Buffered Queue Subscribers'
        ,'Persistent Queues'
        ,'Persistent Queues Rate'
        ,'Persistent Queue Subscribers'
        ,'Rule Set'
        ,'Shared Servers Activity'
        ,'Shared Servers Rates'
        ,'Shared Servers Utilization'
        ,'Shared Servers Common Queue'
        ,'Shared Servers Dispatchers'
        ,'init.ora Parameters'
        ,'init.ora Multi-Valued Parameters'
        ,'Cluster Interconnect'
        ,'Wait Classes by Total Wait Time'
        ,'Top 10 Foreground Events by Total Wait Time'
        ,'Top ADDM Findings by Average Active Sessions'
        ,'Cache Sizes'
        ,'Host Configuration Comparison'
        ,'Top Timed Events'
        ,'Top SQL Comparison by Elapsed Time'
        ,'Top SQL Comparison by I/O Time'
        ,'Top SQL Comparison by CPU Time'
        ,'Top SQL Comparison by Buffer Gets'
        ,'Top SQL Comparison by Physical Reads'
        ,'Top SQL Comparison by UnOptimized Read Requests'
        ,'Top SQL Comparison by Optimized Reads'
        ,'Top SQL Comparison by Executions'
        ,'Top SQL Comparison by Parse Calls'
        ,'Top SQL Comparison by Cluster Wait Time'
        ,'Top SQL Comparison by Sharable Memory'
        ,'Top SQL Comparison by Version Count'
        ,'Top Segments Comparison by Logical Reads'
        ,'Top Segments Comparison by Physical Reads'
        ,'Top Segments Comparison by Direct Physical Reads'
        ,'Top Segments Comparison by Physical Read Requests'
        ,'Top Segments Comparison by Optimized Read Requests'
        ,'Top Segments Comparison by Physical Write Requests'
        ,'Top Segments Comparison by Physical Writes'
        ,'Top Segments Comparison by Table Scans'
        ,'Top Segments Comparison by DB Block Changes'
        ,'Top Segments by Buffer Busy Waits'
        ,'Top Segments by Row Lock Waits'
        ,'Top Segments by ITL Waits'
        ,'Top Segments by CR Blocks Received'
        ,'Top Segments by Current Blocks Received'
        ,'Top Segments by GC Buffer Busy Waits'
        ,'Top In-Memory Segments Comparison by Scans'
        ,'Top In-Memory Segments Comparison by DB Block Changes'
        ,'Top In-Memory Segments Comparison by Populate CUs'
        ,'Top In-Memory Segments Comparison by Repopulate CUs'
        ,'Service Statistics'
        ,'Service Statistics (RAC)'
        ,'Global Messaging Statistics'
        ,'Global CR Served Stats'
        ,'Global CURRENT Served Stats'
        ,'Replication System Resource Usage'
        ,'Replication SGA Usage'
        ,'Streams by CPU Time'
        ,'GoldenGate Capture'
        ,'GoldenGate Capture Rate'
        ,'GoldenGate Apply Coordinator'
        ,'GoldenGate Apply Reader'
        ,'GoldenGate Apply Server'
        ,'GoldenGate Apply Coordinator Rate'
        ,'GoldenGate Apply Reader and Server Rate'
        ,'XStream Capture'
        ,'XStream Capture Rate'
        ,'XStream Apply Coordinator'
        ,'XStream Apply Reader'
        ,'XStream Apply Server'
        ,'XStream Apply Coordinator Rate'
        ,'XStream Apply Reader and Server Rate'
        ,'Table Statistics by DML Operations'
        ,'Table Statistics by Conflict Resolutions'
        ,'Replication Large Transaction Statistics'
        ,'Replication Long Running Transaction Statistics'
        ,'Streams by IO Time'
        ,'Streams Capture'
        ,'Streams Capture Rate'
        ,'Streams Apply'
        ,'Streams Apply Rate'
        ,'Buffered Queues'
        ,'Rule Set by Evaluations'
        ,'Rule Set by Elapsed Time'
        ,'Persistent Queues'
        ,'Persistent Queues Rate'
        ,'IOStat by Function - Data Rate per Second'
        ,'IOStat by Function - Requests per Second'
        ,'IOStat by File Type - Data Rate per Second'
        ,'IOStat by File Type - Requests per Second'
        ,'Tablespace IO Stats'
        ,'Top File Comparison by IO'
        ,'Top File Comparison by Read Time'
        ,'Top File Comparison by Buffer Waits'
        ,'Key Instance Activity Stats'
        ,'Other Instance Activity Stats'
        ,'Enqueue Activity'
        ,'Buffer Wait Statistics'
        ,'Dynamic Remastering Stats'
        ,'Library Cache Activity'
        ,'Library Cache Activity (RAC)'
        ,'init.ora Parameters'
        ,'init.ora Multi-Valued Parameters'
        ,'Buffered Subscribers'
        ,'Persistent Queue Subscribers'
        ,'Shared Servers Activity'
        ,'Shared Servers Rates'
        ,'Shared Servers Utilization'
        ,'Shared Servers Common Queue'
        ,'Shared Servers Dispatchers'
        ,'Database Summary'
        ,'Database Instances Included In Report'
        ,'Top ADDM Findings by Average Active Sessions'
        ,'Cache Sizes'
        ,'OS Statistics By Instance'
        ,'Foreground Wait Classes -  % of Total DB time'
        ,'Foreground Wait Classes'
        ,'Foreground Wait Classes -  % of DB time '
        ,'Time Model'
        ,'Time Model - % of DB time'
        ,'System Statistics'
        ,'System Statistics - Per Second'
        ,'System Statistics - Per Transaction'
        ,'Global Cache Efficiency Percentages'
        ,'Global Cache and Enqueue Workload Characteristics'
        ,'Global Cache and Enqueue Messaging Statistics'
        ,'SysStat and Global Messaging  - RAC'
        ,'SysStat and  Global Messaging (per Sec)- RAC'
        ,'SysStat and Global Messaging (per Tx)- RAC'
        ,'CR Blocks Served Statistics'
        ,'Current Blocks Served Statistics'
        ,'Global Cache Transfer Stats'
        ,'Global Cache Transfer (Immediate)'
        ,'Cluster Interconnect'
        ,'Interconnect Client Statistics'
        ,'Interconnect Client Statistics (per Second)'
        ,'Interconnect Device Statistics'
        ,'Interconnect Device Statistics (per Second)'
        ,'Ping Statistics'
        ,'Top Timed Events'
        ,'Top Timed Foreground Events'
        ,'Top Timed Background Events'
        ,'Resource Manager Plan Statistics'
        ,'Resource Manager Consumer Group Statistics'
        ,'SQL ordered by Elapsed Time (Global)'
        ,'SQL ordered by CPU Time (Global)'
        ,'SQL ordered by User I/O Time (Global)'
        ,'SQL ordered by Gets (Global)'
        ,'SQL ordered by Reads (Global)'
        ,'SQL ordered by UnOptimized Read Requests (Global)'
        ,'SQL ordered by Optimized Reads (Global)'
        ,'SQL ordered by Cluster Wait Time (Global)'
        ,'SQL ordered by Executions (Global)'
        ,'IOStat by Function (per Second)'
        ,'IOStat by File Type (per Second)'
        ,'Segment Statistics (Global)'
        ,'Library Cache Activity'
        ,'System Statistics (Global)'
        ,'Global Messaging Statistics (Global)'
        ,'System Statistics (Absolute Values)'
        ,'PGA Aggregate Target Statistics'
        ,'Process Memory Summary'
        ,'init.ora Parameters'
        ,'init.ora Multi-valued Parameters'
        ,'Database Summary'
        ,'Database Instances Included In Report'
        ,'Time Model Statistics'
        ,'Operating System Statistics'
        ,'Host Utilization Percentages'
        ,'Global Cache Load Profile'
        ,'Wait Classes'
        ,'Wait Events'
        ,'Cache Sizes'
        ,'PGA Aggr Target Stats'
        ,'init.ora Parameters'
        ,'init.ora Multi-valued Parameters'
        ,'Global Cache Transfer Stats'
        ,' Exadata Storage Server Model'
        ,' Exadata Storage Server Version'
        ,' Exadata Storage Information'
        ,' Exadata Griddisks'
        ,' Exadata Celldisks'
        ,' ASM Diskgroups'
        ,' Exadata Non-Online Disks'
        ,' Exadata Alerts Summary'
        ,' Exadata Alerts Detail'
        ,'Exadata Statistics'
) then output end ) ignore nulls over(order by line) section    ,output         from awr
)
select output AWR_REPORT_TEXT from awr_sections 
 --where regexp_like(section,'&1') or regexp_like(output,'&2')
  where section like ('%Events%') 
--w where section like ('%Top 10 Foreground%')
-- w where section like ('%Top Event P1/P2/P3 Values%')
-- w where section like ('%Top SQL%')
/





'Top SQL Comparison by Buffer Gets'
'SQL ordered by User I/O Wait Time'

AWR_REPORT_TEXT
--------------------------------------------------------------------------------
Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                           Total Wait       Avg   % DB Wait
Event                                Waits Time (sec)      Wait   time Class
------------------------------ ----------- ---------- --------- ------ --------
DB CPU                                         2886.9             51.8
direct path read                   231,557      815.4    3.52ms   14.6 User I/O
db file sequential read            977,929        760  777.19us   13.6 User I/O
db file scattered read             634,019      585.2  923.01us   10.5 User I/O
log file sync                       82,557      138.1    1.67ms    2.5 Commit
gc current block busy              136,235      118.8  872.26us    2.1 Cluster

AWR_REPORT_TEXT
--------------------------------------------------------------------------------
gc buffer busy acquire              77,351       86.7    1.12ms    1.6 Cluster
read by other session               98,405       81.4  826.93us    1.5 User I/O
db file parallel read               32,026       78.7    2.46ms    1.4 User I/O
gc buffer busy release              10,751       69.5    6.46ms    1.2 Cluster

=====



VARIABLE bid NUMBER
VARIABLE eid NUMBER

VARIABLE INST_NUMBER number
exec select max(snap_id) -1 into :bid from dba_hist_snapshot ;
exec select max(snap_id) into :eid from dba_hist_snapshot ;

col WAIT_CLASS     for a20                                                    
col EVENT_NAME for a35


set linesize 300 pagesize 300
SELECT snap_id,
  wait_class,
  event_name,
  pctdbt,
  total_time_s
FROM
  (SELECT a.snap_id,
    wait_class,
    event_name,
    b.dbt,
    ROUND(SUM(a.ttm) /b.dbt*100,2) pctdbt,
    SUM(a.ttm) total_time_s,
    dense_rank() over (partition BY a.snap_id order by SUM(a.ttm)/b.dbt*100 DESC nulls last) rnk
  FROM
    (SELECT snap_id,
      wait_class,
      event_name,
      ttm
    FROM
      (SELECT
        /*+ qb_name(systemevents) */
        (CAST (s.end_interval_time AS DATE) - CAST (s.begin_interval_time AS DATE)) * 24 * 3600 ela,
        s.snap_id,
        wait_class,
        e.event_name,
        CASE
          WHEN s.begin_interval_time = s.startup_time
          THEN e.time_waited_micro
          ELSE e.time_waited_micro - lag (e.time_waited_micro ) over (partition BY e.instance_number,e.event_name order by e.snap_id)
        END ttm
      FROM dba_hist_snapshot s,
        dba_hist_system_event e
      WHERE s.dbid          = e.dbid
    --  AND s.dbid            = :dbid
      AND s.instance_number = e.instance_number
      AND s.snap_id         = e.snap_id
      AND s.snap_id BETWEEN :bid AND :eid
      AND e.wait_class != 'Idle'
      UNION ALL
      SELECT
        /*+ qb_name(dbcpu) */
        (CAST (s.end_interval_time AS DATE) - CAST (s.begin_interval_time AS DATE)) * 24 * 3600 ela,
        s.snap_id,
        t.stat_name wait_class,
        t.stat_name event_name,
        CASE
          WHEN s.begin_interval_time = s.startup_time
          THEN t.value
          ELSE t.value - lag (t.value ) over (partition BY s.instance_number order by s.snap_id)
        END ttm
      FROM dba_hist_snapshot s,
        dba_hist_sys_time_model t
      WHERE s.dbid          = t.dbid
     -- AND s.dbid            = :dbid
      AND s.instance_number = t.instance_number
      AND s.snap_id         = t.snap_id
      AND s.snap_id BETWEEN :bid AND :eid
      AND t.stat_name = 'DB CPU'
      )
    ) a,
    (SELECT snap_id,
      SUM(dbt) dbt
    FROM
      (SELECT
        /*+ qb_name(dbtime) */
        s.snap_id,
        t.instance_number,
        t.stat_name nm,
        CASE
          WHEN s.begin_interval_time = s.startup_time
          THEN t.value
          ELSE t.value - lag (t.value ) over (partition BY s.instance_number order by s.snap_id)
        END dbt
      FROM dba_hist_snapshot s,
        dba_hist_sys_time_model t
      WHERE s.dbid          = t.dbid
    --  AND s.dbid            = :dbid
      AND s.instance_number = t.instance_number
      AND s.snap_id         = t.snap_id
      AND s.snap_id BETWEEN :bid AND :eid
      AND t.stat_name = 'DB time'
      ORDER BY s.snap_id,
        s.instance_number
      )
    GROUP BY snap_id
    HAVING SUM(dbt) > 0
    ) b
  WHERE a.snap_id = b.snap_id
  GROUP BY a.snap_id,
    a.wait_class,
    a.event_name,
    b.dbt
  )
WHERE pctdbt > 0
AND rnk     <= 10
ORDER BY snap_id,
  pctdbt DESC; 
  
   SNAP_ID WAIT_CLASS           EVENT_NAME                              PCTDBT TOTAL_TIME_S
---------- -------------------- ----------------------------------- ---------- ------------
      3101 DB CPU               DB CPU                                   71.16   7750949501
      3101 Concurrency          cursor: pin S wait on X                   4.63    503970620
      3101 User I/O             direct path read                          4.15    451648359
      3101 Network              SQL*Net message from dblink               2.76    300135356
      3101 System I/O           log file parallel write                   2.62    285647003
      3101 Commit               log file sync                              2.1    228775607
      3101 Other                LGWR any worker group                      .55     60287636
      3101 Concurrency          library cache lock                         .43     47314514
      3101 Other                oracle thread bootstrap                    .32     35209603
      3101 System I/O           db file async I/O submit                   .32     34775730

10 rows selected.



VARIABLE bid NUMBER
VARIABLE eid NUMBER

VARIABLE INST_NUMBER number
exec select max(snap_id) -1 into :bid from dba_hist_snapshot ;
exec select max(snap_id) into :eid from dba_hist_snapshot ;



set linesize 1000 pagesize 300
define SNAP_ID_MIN=34000
define SNAP_ID_MAX=34046
REPHEADER PAGE LEFT '~~BEGIN-MAIN-METRICS~~'
REPFOOTER PAGE LEFT '~~END-MAIN-METRICS~~'

 select snap_id "snap",num_interval "dur_m", end_time "end",inst "inst",
  max(decode(metric_name,'Host CPU Utilization (%)',					average,null)) "os_cpu",
  max(decode(metric_name,'Host CPU Utilization (%)',					maxval,null)) "os_cpu_max",
  max(decode(metric_name,'Host CPU Utilization (%)',					STANDARD_DEVIATION,null)) "os_cpu_sd",
  max(decode(metric_name,'Database Wait Time Ratio',                   round(average,1),null)) "db_wait_ratio",
max(decode(metric_name,'Database CPU Time Ratio',                   round(average,1),null)) "db_cpu_ratio",
max(decode(metric_name,'CPU Usage Per Sec',                   round(average/100,3),null)) "cpu_per_s",
max(decode(metric_name,'CPU Usage Per Sec',                   round(STANDARD_DEVIATION/100,3),null)) "cpu_per_s_sd",
max(decode(metric_name,'Host CPU Usage Per Sec',                   round(average/100,3),null)) "h_cpu_per_s",
max(decode(metric_name,'Host CPU Usage Per Sec',                   round(STANDARD_DEVIATION/100,3),null)) "h_cpu_per_s_sd",
max(decode(metric_name,'Average Active Sessions',                   average,null)) "aas",
max(decode(metric_name,'Average Active Sessions',                   STANDARD_DEVIATION,null)) "aas_sd",
max(decode(metric_name,'Average Active Sessions',                   maxval,null)) "aas_max",
max(decode(metric_name,'Database Time Per Sec',					average,null)) "db_time",
max(decode(metric_name,'Database Time Per Sec',					STANDARD_DEVIATION,null)) "db_time_sd",
max(decode(metric_name,'SQL Service Response Time',                   average,null)) "sql_res_t_cs",
max(decode(metric_name,'Background Time Per Sec',                   average,null)) "bkgd_t_per_s",
max(decode(metric_name,'Logons Per Sec',                            average,null)) "logons_s",
max(decode(metric_name,'Current Logons Count',                      average,null)) "logons_total",
max(decode(metric_name,'Executions Per Sec',                        average,null)) "exec_s",
max(decode(metric_name,'Hard Parse Count Per Sec',                  average,null)) "hard_p_s",
max(decode(metric_name,'Logical Reads Per Sec',                     average,null)) "l_reads_s",
max(decode(metric_name,'User Commits Per Sec',                      average,null)) "commits_s",
max(decode(metric_name,'Physical Read Total Bytes Per Sec',         round((average)/1024/1024,1),null)) "read_mb_s",
max(decode(metric_name,'Physical Read Total Bytes Per Sec',         round((maxval)/1024/1024,1),null)) "read_mb_s_max",
max(decode(metric_name,'Physical Read Total IO Requests Per Sec',   average,null)) "read_iops",
max(decode(metric_name,'Physical Read Total IO Requests Per Sec',   maxval,null)) "read_iops_max",
max(decode(metric_name,'Physical Reads Per Sec',  			average,null)) "read_bks",
max(decode(metric_name,'Physical Reads Direct Per Sec',  			average,null)) "read_bks_direct",
max(decode(metric_name,'Physical Write Total Bytes Per Sec',        round((average)/1024/1024,1),null)) "write_mb_s",
max(decode(metric_name,'Physical Write Total Bytes Per Sec',        round((maxval)/1024/1024,1),null)) "write_mb_s_max",
max(decode(metric_name,'Physical Write Total IO Requests Per Sec',  average,null)) "write_iops",
max(decode(metric_name,'Physical Write Total IO Requests Per Sec',  maxval,null)) "write_iops_max",
max(decode(metric_name,'Physical Writes Per Sec',  			average,null)) "write_bks",
max(decode(metric_name,'Physical Writes Direct Per Sec',  			average,null)) "write_bks_direct",
max(decode(metric_name,'Redo Generated Per Sec',                    round((average)/1024/1024,1),null)) "redo_mb_s",
max(decode(metric_name,'DB Block Gets Per Sec',                     average,null)) "db_block_gets_s",
max(decode(metric_name,'DB Block Changes Per Sec',                   average,null)) "db_block_changes_s",
max(decode(metric_name,'GC CR Block Received Per Second',            average,null)) "gc_cr_rec_s",
max(decode(metric_name,'GC Current Block Received Per Second',       average,null)) "gc_cu_rec_s",
max(decode(metric_name,'Global Cache Average CR Get Time',           average,null)) "gc_cr_get_cs",
max(decode(metric_name,'Global Cache Average Current Get Time',      average,null)) "gc_cu_get_cs",
max(decode(metric_name,'Global Cache Blocks Corrupted',              average,null)) "gc_bk_corrupted",
max(decode(metric_name,'Global Cache Blocks Lost',                   average,null)) "gc_bk_lost",
max(decode(metric_name,'Active Parallel Sessions',                   average,null)) "px_sess",
max(decode(metric_name,'Active Serial Sessions',                     average,null)) "se_sess",
max(decode(metric_name,'Average Synchronous Single-Block Read Latency', average,null)) "s_blk_r_lat",
max(decode(metric_name,'Cell Physical IO Interconnect Bytes',         round((average)/1024/1024,1),null)) "cell_io_int_mb",
max(decode(metric_name,'Cell Physical IO Interconnect Bytes',         round((maxval)/1024/1024,1),null)) "cell_io_int_mb_max"
  from(
  select  snap_id,num_interval,to_char(end_time,'YY/MM/DD HH24:MI') end_time,instance_number inst,metric_name,round(average,1) average,
  round(maxval,1) maxval,round(standard_deviation,1) standard_deviation
 from dba_hist_sysmetric_summary
where 1=1
-- and dbid = &DBID
 and snap_id between :bid  and :eid 
 --and snap_id = 920
 --and instance_number = 4
 and metric_name in ('Host CPU Utilization (%)','CPU Usage Per Sec','Host CPU Usage Per Sec','Average Active Sessions','Database Time Per Sec',
 'Executions Per Sec','Hard Parse Count Per Sec','Logical Reads Per Sec','Logons Per Sec',
 'Physical Read Total Bytes Per Sec','Physical Read Total IO Requests Per Sec','Physical Reads Per Sec','Physical Write Total Bytes Per Sec',
 'Redo Generated Per Sec','User Commits Per Sec','Current Logons Count','DB Block Gets Per Sec','DB Block Changes Per Sec',
 'Database Wait Time Ratio','Database CPU Time Ratio','SQL Service Response Time','Background Time Per Sec',
 'Physical Write Total IO Requests Per Sec','Physical Writes Per Sec','Physical Writes Direct Per Sec','Physical Writes Direct Lobs Per Sec',
 'Physical Reads Direct Per Sec','Physical Reads Direct Lobs Per Sec',
 'GC CR Block Received Per Second','GC Current Block Received Per Second','Global Cache Average CR Get Time','Global Cache Average Current Get Time',
 'Global Cache Blocks Corrupted','Global Cache Blocks Lost',
 'Active Parallel Sessions','Active Serial Sessions','Average Synchronous Single-Block Read Latency','Cell Physical IO Interconnect Bytes'
    )
 )
 group by snap_id,num_interval, end_time,inst
 order by snap_id, end_time,inst;
 
 =====

top 10 foreground events by total wait time


set linesize 300  pagesize 200
set trimspool on
break on startup skip 1 on snap_id skip 1 duplicate
select
    to_char(sss.startup_time,'dd-mon-yyyy hh24:mi:ss') startup,
    stats.snap_id, event_name,
    total_waits, total_timeouts, round(time_waited_micro/1e6,0) wait_seconds
from
    (
    select
        snap_id, dbid, instance_number, event_name,
        total_waits, total_timeouts, time_waited_micro,
        row_number() over (partition by snap_id order by time_waited_micro desc)  rn
    from
       (
        select
            snap_id, dbid, instance_number, event_name,
            total_waits_fg - lag(total_waits_fg,1) over (partition by event_name order by snap_id) total_waits,
            total_timeouts_fg - lag(total_timeouts_fg,1) over (partition by event_name order by snap_id) total_timeouts,
            time_waited_micro_fg - lag(time_waited_micro_fg,1) over (partition by event_name order by snap_id) time_waited_micro
        from   dba_hist_system_event sse
        where      dbid            = (select dbid from v$database)
     --   and instance_number = (select instance_number from v$instance)
        and wait_class     != 'Idle'
        )
    )   stats,
    dba_hist_snapshot sss
where
    rn <= 10
and sss.begin_interval_time >= sysdate -4/24  ---<<<<< last 4 hr
and sss.snap_id              = stats.snap_id
and sss.dbid                 = stats.dbid
and sss.instance_number      = stats.instance_number
order by
snap_id, wait_seconds desc
/

====
Between snap!!!
 
 https://github.com/karlarao/run_awr-quickextract/blob/master/run_awr_topevents.sql
 
 
 
 VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -24 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 ;
 
 
 set feedback off pages 0 term off head on und off trimspool on echo off lines 4000 

set arraysize 5000
set termout off
set echo off verify off

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 Events Report' skip 2
set pagesize 50000
set linesize 550

col instname    format a15
col hostname    format a30
col snap_id     format 99999            heading snap_id       -- "snapid"
col tm          format a17              heading tm            -- "tm"
col inst        format 90               heading inst          -- "inst"
col dur         format 999990.00        heading dur           -- "dur"
col event       format a55              heading event         -- "Event"
col event_rank  format 90               heading event_rank    -- "EventRank"
col waits       format 9999999990.00    heading waits         -- "Waits"
col time        format 9999999990.00    heading time          -- "Timesec"
col avgwt       format 99990.00         heading avgwt         -- "Avgwtms"
col pctdbt      format 9990.0           heading pctdbt        -- "DBTimepct"
col aas         format 990.0            heading aas           -- "Aas"
col wait_class  format a15              heading wait_class    -- "WaitClass"

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_topevents-tableau-&_instname-&_hostname..csv
select trim('&_instname') instname, trim('&_dbid') db_id, trim('&_hostname') hostname, snap_id, tm, inst, dur, event, event_rank, waits, time, avgwt, pctdbt, aas, wait_class
from
      (select snap_id, TO_CHAR(tm,'MM/DD/YY HH24:MI:SS') tm, inst, dur, event, waits, time, avgwt, pctdbt, aas, wait_class,
            DENSE_RANK() OVER (
          PARTITION BY snap_id ORDER BY time DESC) event_rank
      from
              (
              select * from
                    (select * 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.event_name event,
                            e.total_waits - nvl(b.total_waits,0)       waits,
                            round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2)  time,     -- THIS IS EVENT (sec)
                            round (decode ((e.total_waits - nvl(b.total_waits, 0)), 0, to_number(NULL), ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000) / (e.total_waits - nvl(b.total_waits,0))), 2) avgwt,
                            ((round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2)) / NULLIF(((s5t1.value - nvl(s5t0.value,0)) / 1000000),0))*100 as pctdbt,     -- THIS IS EVENT (sec) / DB TIME (sec)
                            (round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2))/60 /  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) aas,     -- THIS IS EVENT (min) / SnapDur (min) TO GET THE % DB CPU ON AAS
                            e.wait_class wait_class
                            from
                                 dba_hist_snapshot s0,
                                 dba_hist_snapshot s1,
                                 dba_hist_system_event b,
                                 dba_hist_system_event e,
                                 dba_hist_sys_time_model s5t0,
                                 dba_hist_sys_time_model s5t1
                            where
                              s0.dbid                   = &_dbid            -- CHANGE THE DBID HERE!
                              AND s1.dbid               = s0.dbid
                              and b.dbid(+)             = s0.dbid
                              and e.dbid                = s0.dbid
                              AND s5t0.dbid             = s0.dbid
                              AND s5t1.dbid             = s0.dbid
                              --AND s0.instance_number    = &_instancenumber  -- CHANGE THE INSTANCE_NUMBER HERE!
                              AND s1.instance_number    = s0.instance_number
                              and b.instance_number(+)  = s0.instance_number
                              and e.instance_number     = s0.instance_number
                              AND s5t0.instance_number = s0.instance_number
                              AND s5t1.instance_number = s0.instance_number
                              AND s1.snap_id            = s0.snap_id + 1
                              AND b.snap_id(+)          = s0.snap_id
                              and e.snap_id             = s0.snap_id + 1
                              AND s5t0.snap_id         = s0.snap_id
                              AND s5t1.snap_id         = s0.snap_id + 1
                              AND s5t0.stat_name       = 'DB time'
                              AND s5t1.stat_name       = s5t0.stat_name
                                    and b.event_id            = e.event_id
                                    and e.wait_class          != 'Idle'
                                    and e.total_waits         > nvl(b.total_waits,0)
                                    and e.event_name not in ('smon timer',
                                                             'pmon timer',
                                                             'dispatcher timer',
                                                             'dispatcher listen timer',
                                                             'rdbms ipc message')
                                  order by snap_id, time desc, waits desc, event)
                    union all
                              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,
                                        'CPU time',
                                        0,
                                        round ((s6t1.value - s6t0.value) / 1000000, 2) as time,     -- THIS IS DB CPU (sec)
                                        0,
                                        ((round ((s6t1.value - s6t0.value) / 1000000, 2)) / NULLIF(((s5t1.value - nvl(s5t0.value,0)) / 1000000),0))*100 as pctdbt,     -- THIS IS DB CPU (sec) / DB TIME (sec)..TO GET % OF DB CPU ON DB TIME FOR TOP 5 TIMED EVENTS SECTION
                                        (round ((s6t1.value - s6t0.value) / 1000000, 2))/60 /  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) aas,  -- THIS IS DB CPU (min) / SnapDur (min) TO GET THE % DB CPU ON AAS
                                        'CPU'
                                      from
                                        dba_hist_snapshot s0,
                                        dba_hist_snapshot s1,
                                        dba_hist_sys_time_model s6t0,
                                        dba_hist_sys_time_model s6t1,
                                        dba_hist_sys_time_model s5t0,
                                        dba_hist_sys_time_model s5t1
                                      WHERE
                                      s0.dbid                   = &_dbid              -- CHANGE THE DBID HERE!
                                      AND s1.dbid               = s0.dbid
                                      AND s6t0.dbid            = s0.dbid
                                      AND s6t1.dbid            = s0.dbid
                                      AND s5t0.dbid            = s0.dbid
                                      AND s5t1.dbid            = s0.dbid
                                      --AND s0.instance_number    = &_instancenumber    -- CHANGE THE INSTANCE_NUMBER HERE!
                                      AND s1.instance_number    = s0.instance_number
                                      AND s6t0.instance_number = s0.instance_number
                                      AND s6t1.instance_number = s0.instance_number
                                      AND s5t0.instance_number = s0.instance_number
                                      AND s5t1.instance_number = s0.instance_number
                                      AND s1.snap_id            = s0.snap_id + 1
                                      AND s6t0.snap_id         = s0.snap_id
                                      AND s6t1.snap_id         = s0.snap_id + 1
                                      AND s5t0.snap_id         = s0.snap_id
                                      AND s5t1.snap_id         = s0.snap_id + 1
                                      AND s6t0.stat_name       = 'DB CPU'
                                      AND s6t1.stat_name       = s6t0.stat_name
                                      AND s5t0.stat_name       = 'DB time'
                                      AND s5t1.stat_name       = s5t0.stat_name
                    union all
                                      (select
                                               dbtime.snap_id,
                                               dbtime.tm,
                                               dbtime.inst,
                                               dbtime.dur,
                                               'CPU wait',
                                                0,
                                                round(dbtime.time - accounted_dbtime.time, 2) time,     -- THIS IS UNACCOUNTED FOR DB TIME (sec)
                                                0,
                                                ((dbtime.aas - accounted_dbtime.aas)/ NULLIF(nvl(dbtime.aas,0),0))*100 as pctdbt,     -- THIS IS UNACCOUNTED FOR DB TIME (sec) / DB TIME (sec)
                                                round(dbtime.aas - accounted_dbtime.aas, 2) aas,     -- AAS OF UNACCOUNTED FOR DB TIME
                                                'CPU wait'
                                      from
                                                  (select
                                                     s0.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,
                                                    'DB time',
                                                    0,
                                                    round ((s5t1.value - s5t0.value) / 1000000, 2) as time,     -- THIS IS DB time (sec)
                                                    0,
                                                    0,
                                                     (round ((s5t1.value - s5t0.value) / 1000000, 2))/60 /  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) aas,
                                                    'DB time'
                                                  from
                                                                    dba_hist_snapshot s0,
                                                                    dba_hist_snapshot s1,
                                                                    dba_hist_sys_time_model s5t0,
                                                                    dba_hist_sys_time_model s5t1
                                                                  WHERE
                                                                  s0.dbid                   = &_dbid              -- CHANGE THE DBID HERE!
                                                                  AND s1.dbid               = s0.dbid
                                                                  AND s5t0.dbid            = s0.dbid
                                                                  AND s5t1.dbid            = s0.dbid
                                                                  --AND s0.instance_number    = &_instancenumber    -- CHANGE THE INSTANCE_NUMBER HERE!
                                                                  AND s1.instance_number    = s0.instance_number
                                                                  AND s5t0.instance_number = s0.instance_number
                                                                  AND s5t1.instance_number = s0.instance_number
                                                                  AND s1.snap_id            = s0.snap_id + 1
                                                                  AND s5t0.snap_id         = s0.snap_id
                                                                  AND s5t1.snap_id         = s0.snap_id + 1
                                                                  AND s5t0.stat_name       = 'DB time'
                                                                  AND s5t1.stat_name       = s5t0.stat_name) dbtime,
                                                  (select snap_id, inst, sum(time) time, sum(AAS) aas from
                                                          (select * 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.event_name event,
                                                                e.total_waits - nvl(b.total_waits,0)       waits,
                                                                round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2)  time,     -- THIS IS EVENT (sec)
                                                                round (decode ((e.total_waits - nvl(b.total_waits, 0)), 0, to_number(NULL), ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000) / (e.total_waits - nvl(b.total_waits,0))), 2) avgwt,
                                                                ((round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2)) / NULLIF(((s5t1.value - nvl(s5t0.value,0)) / 1000000),0))*100 as pctdbt,     -- THIS IS EVENT (sec) / DB TIME (sec)
                                                                (round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2))/60 /  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) aas,     -- THIS IS EVENT (min) / SnapDur (min) TO GET THE % DB CPU ON AAS
                                                                e.wait_class wait_class
                                                          from
                                                               dba_hist_snapshot s0,
                                                               dba_hist_snapshot s1,
                                                               dba_hist_system_event b,
                                                               dba_hist_system_event e,
                                                               dba_hist_sys_time_model s5t0,
                                                               dba_hist_sys_time_model s5t1
                                                          where
                                                            s0.dbid                   = &_dbid            -- CHANGE THE DBID HERE!
                                                            AND s1.dbid               = s0.dbid
                                                            and b.dbid(+)             = s0.dbid
                                                            and e.dbid                = s0.dbid
                                                            AND s5t0.dbid             = s0.dbid
                                                            AND s5t1.dbid             = s0.dbid
                                                            --AND s0.instance_number    = &_instancenumber  -- CHANGE THE INSTANCE_NUMBER HERE!
                                                            AND s1.instance_number    = s0.instance_number
                                                            and b.instance_number(+)  = s0.instance_number
                                                            and e.instance_number     = s0.instance_number
                                                            AND s5t0.instance_number = s0.instance_number
                                                            AND s5t1.instance_number = s0.instance_number
                                                            AND s1.snap_id            = s0.snap_id + 1
                                                            AND b.snap_id(+)          = s0.snap_id
                                                            and e.snap_id             = s0.snap_id + 1
                                                            AND s5t0.snap_id         = s0.snap_id
                                                            AND s5t1.snap_id         = s0.snap_id + 1
                                                      AND s5t0.stat_name       = 'DB time'
                                                      AND s5t1.stat_name       = s5t0.stat_name
                                                            and b.event_id            = e.event_id
                                                            and e.wait_class          != 'Idle'
                                                            and e.total_waits         > nvl(b.total_waits,0)
                                                            and e.event_name not in ('smon timer',
                                                                                     'pmon timer',
                                                                                     'dispatcher timer',
                                                                                     'dispatcher listen timer',
                                                                                     'rdbms ipc message')
                                                          order by snap_id, time desc, waits desc, event)
                                                    union all
                                                          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,
                                                                    'CPU time',
                                                                    0,
                                                                    round ((s6t1.value - s6t0.value) / 1000000, 2) as time,     -- THIS IS DB CPU (sec)
                                                                    0,
                                                                    ((round ((s6t1.value - s6t0.value) / 1000000, 2)) / NULLIF(((s5t1.value - nvl(s5t0.value,0)) / 1000000),0))*100 as pctdbt,     -- THIS IS DB CPU (sec) / DB TIME (sec)..TO GET % OF DB CPU ON DB TIME FOR TOP 5 TIMED EVENTS SECTION
                                                                    (round ((s6t1.value - s6t0.value) / 1000000, 2))/60 /  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) aas,  -- THIS IS DB CPU (min) / SnapDur (min) TO GET THE % DB CPU ON AAS
                                                                    'CPU'
                                                                  from
                                                                    dba_hist_snapshot s0,
                                                                    dba_hist_snapshot s1,
                                                                    dba_hist_sys_time_model s6t0,
                                                                    dba_hist_sys_time_model s6t1,
                                                                    dba_hist_sys_time_model s5t0,
                                                                    dba_hist_sys_time_model s5t1
                                                                  WHERE
                                                                  s0.dbid                   = &_dbid              -- CHANGE THE DBID HERE!
                                                                  AND s1.dbid               = s0.dbid
                                                                  AND s6t0.dbid            = s0.dbid
                                                                  AND s6t1.dbid            = s0.dbid
                                                                  AND s5t0.dbid            = s0.dbid
                                                                  AND s5t1.dbid            = s0.dbid
                                                                  --AND s0.instance_number    = &_instancenumber    -- CHANGE THE INSTANCE_NUMBER HERE!
                                                                  AND s1.instance_number    = s0.instance_number
                                                                  AND s6t0.instance_number = s0.instance_number
                                                                  AND s6t1.instance_number = s0.instance_number
                                                                  AND s5t0.instance_number = s0.instance_number
                                                                  AND s5t1.instance_number = s0.instance_number
                                                                  AND s1.snap_id            = s0.snap_id + 1
                                                                  AND s6t0.snap_id         = s0.snap_id
                                                                  AND s6t1.snap_id         = s0.snap_id + 1
                                                                  AND s5t0.snap_id         = s0.snap_id
                                                                  AND s5t1.snap_id         = s0.snap_id + 1
                                                                  AND s6t0.stat_name       = 'DB CPU'
                                                                  AND s6t1.stat_name       = s6t0.stat_name
                                                                  AND s5t0.stat_name       = 'DB time'
                                                                  AND s5t1.stat_name       = s5t0.stat_name
                                                          ) group by snap_id, inst) accounted_dbtime
                                                            where dbtime.snap_id = accounted_dbtime.snap_id
                                                            and dbtime.inst = accounted_dbtime.inst
                                        )
                    )
              )
      )
WHERE event_rank <= 5
--AND to_date(tm,'MM/DD/YY HH24:MI:SS') > sysdate - :g_retention
 and snap_id >= :BgnSnap and snap_id <=  :EndSnap
-- 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 = 495
-- and event = 'db file sequential read'
-- and event like 'CPU%'
-- and avgwt > 5
-- and aas > .5
-- and wait_class = 'CPU'
-- and wait_class like '%I/O%'
-- and event_rank in (1,2,3)
ORDER BY snap_id;


===

AWR Top 5 Timed Foreground Events



col Event for a25
VARIABLE BEGIN_SNAP_ID NUMBER
VARIABLE END_SNAP_ID NUMBER
VARIABLE DBID NUMBER
VARIABLE INSTANCE_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -1 into :BEGIN_SNAP_ID from dba_hist_snapshot ;
exec select max(snap_id) into :END_SNAP_ID from dba_hist_snapshot ;
exec select DBID into :DBID from v$database;
exec select INSTANCE_NUMBER into :INSTANCE_NUMBER from v$instance ;


set pagesize 10000
set linesize 10000
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=&instance_number
  and stats.snap_id between :BEGIN_SNAP_ID and :END_SNAP_ID
) 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;

====




 
Top 5 Event

set linesize 500
 

VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
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 ;


 
col event format a35 heading "Event"
col waits format 999,999,990 heading "Waits"
col time format 999,999,990 heading "Time (s)"
col avgwt format 990 heading "Avg|wait|(ms)"
col pctwtt format 9,999.9 heading "%Total|  Call|  Time"
col wait_class format a30 heading "Wait Class" justify right
 
SELECT EVENT,
WAITS,
TIME,
DECODE(WAITS,
NULL,
TO_NUMBER(NULL),
0,
TO_NUMBER(NULL),
TIME / WAITS * 1000) AVGWT,
PCTWTT,
WAIT_CLASS
FROM (SELECT EVENT, WAITS, TIME, PCTWTT, WAIT_CLASS
FROM (SELECT E.EVENT_NAME EVENT,
E.TOTAL_WAITS - NVL(B.TOTAL_WAITS, 0) WAITS,
(E.TIME_WAITED_MICRO - NVL(B.TIME_WAITED_MICRO, 0)) /1000000 TIME,100 * (E.TIME_WAITED_MICRO - NVL(B.TIME_WAITED_MICRO, 0)) /((SELECT sum(value) FROM DBA_HIST_SYS_TIME_MODEL e
WHERE e.SNAP_ID = :EndSnap
AND e.DBID = :DID
AND e.INSTANCE_NUMBER = :INST_NUMBER
AND e.STAT_NAME = 'DB time') -
(SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL b
WHERE b.SNAP_ID = :BgnSnap
AND b.DBID = :DID
AND b.INSTANCE_NUMBER = :INST_NUMBER
AND b.STAT_NAME = 'DB time')) PCTWTT,
E.WAIT_CLASS WAIT_CLASS
FROM DBA_HIST_SYSTEM_EVENT B, DBA_HIST_SYSTEM_EVENT E
WHERE B.SNAP_ID(+) = :BgnSnap
AND E.SNAP_ID = :EndSnap
AND B.DBID(+) = :DID
AND E.DBID = :DID
AND B.INSTANCE_NUMBER(+) = :INST_NUMBER
AND E.INSTANCE_NUMBER = :INST_NUMBER
AND B.EVENT_ID(+) = E.EVENT_ID
AND E.TOTAL_WAITS > NVL(B.TOTAL_WAITS, 0)
AND E.WAIT_CLASS != 'Idle'
UNION ALL
SELECT 'CPU time' EVENT,
TO_NUMBER(NULL) WAITS,
((SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL e
WHERE e.SNAP_ID = :EndSnap
AND e.DBID = :DID 
AND e.INSTANCE_NUMBER = :INST_NUMBER
AND e.STAT_NAME = 'DB CPU') - (SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL b
WHERE b.SNAP_ID = :BgnSnap
AND b.DBID =:DID 
AND b.INSTANCE_NUMBER = :INST_NUMBER
AND b.STAT_NAME = 'DB CPU')) / 1000000 TIME,
100 * ((SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL e
WHERE e.SNAP_ID = :EndSnap
AND e.DBID = :DID
AND e.INSTANCE_NUMBER = :INST_NUMBER
AND e.STAT_NAME = 'DB CPU') - 
(SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL b
WHERE b.SNAP_ID = :BgnSnap
AND b.DBID = :DID
AND b.INSTANCE_NUMBER = :INST_NUMBER
AND b.STAT_NAME = 'DB CPU')) /
((SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL e
WHERE e.SNAP_ID = :EndSnap
AND e.DBID = :DID
AND e.INSTANCE_NUMBER = :INST_NUMBER
AND e.STAT_NAME = 'DB time') -
(SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL b
WHERE b.SNAP_ID = :BgnSnap
AND b.DBID = :DID 
AND b.INSTANCE_NUMBER = :INST_NUMBER
AND b.STAT_NAME = 'DB time')) PCTWTT,
NULL WAIT_CLASS
from dual
WHERE ((SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL e
WHERE e.SNAP_ID = :EndSnap
AND e.DBID = :DID 
AND e.INSTANCE_NUMBER = :INST_NUMBER
AND e.STAT_NAME = 'DB CPU') -
(SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL b
WHERE b.SNAP_ID = :BgnSnap
AND b.DBID =:DID 
AND b.INSTANCE_NUMBER = :INST_NUMBER
AND b.STAT_NAME = 'DB CPU')) > 0)
ORDER BY TIME DESC, WAITS DESC)
WHERE ROWNUM <= 5;




                                                               Avg   %Total
                                                              wait     Call
Event                                      Waits     Time (s) (ms)     Time           Wait Class
----------------------------------- ------------ ------------ ---- -------- --------------------
CPU time                                               33,930          86.3
SQL*Net more data from client             34,494        4,147  120     10.6 Network
db file async I/O submit                 537,638          684    1      1.7 System I/O
cursor: pin S wait on X                    1,836          460  251      1.2 Concurrency
db file sequential read               37,938,424          331    0       .8 User I/O

SQL> SQL> SQL>


 

************************

top event 

	 

VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
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 ;


COLUMN DBTIME NEW_VALUE _dbtime NOPRINT

COLUMN TCPU NEW_VALUE _TCPU NOPRINT
 col TCPU  for 99999999999999
col  DBTIME for 9999999999999




set linesize 300

select   DBID, DB_NAME ,  INSTANCE_NUMBER, INST_NAME ,       BEGIN_SNAP_ID, END_SNAP_ID ,           TCPU ,        DBTIME from (select dbid,
       db_name,
       instance_number,
       inst_name,
       begin_snap_id,
       end_snap_id,
       (SELECT  sum(e.VALUE-b.value) as diff_value
           FROM dba_hist_sys_time_model    B,
                dba_hist_sys_time_model  E
          WHERE e.dbid = b.dbid
            and e.instance_number = b.instance_number
            and e.STAT_ID = b.STAT_ID
            and B.DBID            = base_info.dbid
            AND B.INSTANCE_NUMBER = base_info.instance_number
            AND B.SNAP_ID         = base_info.begin_snap_id
            AND E.SNAP_ID         = base_info.end_snap_id
            and e.stat_name in ('DB CPU') ) as tcpu,
       (SELECT  sum(e.VALUE-b.value) as diff_value
           FROM dba_hist_sys_time_model    B,
                dba_hist_sys_time_model  E
          WHERE e.dbid = b.dbid
            and e.instance_number = b.instance_number
            and e.STAT_ID = b.STAT_ID
            and B.DBID            = base_info.dbid
            AND B.INSTANCE_NUMBER = base_info.instance_number
            AND B.SNAP_ID         = base_info.begin_snap_id
            AND E.SNAP_ID         = base_info.end_snap_id
            and e.stat_name in ('DB time') ) as dbtime     
  from (with db_info as (select d.dbid            dbid,
                                d.name            db_name,
                                i.instance_number instance_number,
                                i.instance_name   inst_name
                           from v$database d, v$instance i), 
         snap_info as (select c.*,
                              EXTRACT(DAY FROM c.max_end_interval_time - c.min_end_interval_time) * 86400
                               + EXTRACT(HOUR FROM c.max_end_interval_time - c.min_end_interval_time) * 3600
                               + EXTRACT(MINUTE FROM c.max_end_interval_time - c.min_end_interval_time) * 60
                               + EXTRACT(SECOND FROM c.max_end_interval_time - c.min_end_interval_time) ELAPSED
                         from (select min(snap_id) begin_snap_id,
                                      max(snap_id) end_snap_id,
                                      min(END_INTERVAL_TIME) as  min_end_interval_time,
                                      max(END_INTERVAL_TIME) as max_end_interval_time
                                 from dba_hist_snapshot sn
                                where  1=1
				and SNAP_ID between :BgnSnap and :EndSnap
				-- sn.begin_interval_time >= trunc(sysdate) - 1  and sn.begin_interval_time < sysdate
								  ) c
                        )        
 select * from db_info, snap_info)
 base_info)

;
	   

	 
  
col Event   for a35   

col "Wait Class" for a20
       
select event "Event",
       waits "Waits",
       round(time,2)  "Time(s)",
       round(avwait,2) "Avg wait (ms)",
       round(pctwtt,2) "% DB time",
       wcls "Wait Class" 
from (  select event
					     , wtfg             waits
					     , tmfg/1000000     time
					     , decode(wtfg, 0, to_number(null), tmfg/wtfg)/1000  avwait
					     , decode(&_dbtime, 0, to_number(null), tmfg/&_dbtime)*100   pctwtt
					     , wcls
					  from (   select event, wtfg, ttofg, tmfg, wcls
					           from (
					              select e.event_name                                      event
					                   , case when e.total_waits_fg is not null
					                          then e.total_waits_fg - nvl(b.total_waits_fg,0)
					                          else (e.total_waits - nvl(b.total_waits,0))
					                                - greatest(0,(nvl(ebg.total_waits,0)
					                                          - nvl(bbg.total_waits,0)))
					                     end                                               wtfg
					                   , case when e.total_timeouts_fg is not null
					                          then e.total_timeouts_fg - nvl(b.total_timeouts_fg,0)
					                          else (e.total_timeouts - nvl(b.total_timeouts,0))
					                                - greatest(0,(nvl(ebg.total_timeouts,0)
					                                          - nvl(bbg.total_timeouts,0)))
					                     end                                              ttofg
					                   , case when e.time_waited_micro_fg is not null
					                          then e.time_waited_micro_fg - nvl(b.time_waited_micro_fg,0)
					                          else (e.time_waited_micro - nvl(b.time_waited_micro,0))
					                                - greatest(0,(nvl(ebg.time_waited_micro,0)
					                                          - nvl(bbg.time_waited_micro,0)))
					                     end                                              tmfg
					                   , e.wait_class                                     wcls
					                from dba_hist_system_event b
					                   , dba_hist_system_event e
					                   , dba_hist_bg_event_summary bbg
					                   , dba_hist_bg_event_summary ebg
					               where b.snap_id  (+) = :BgnSnap
					                 and e.snap_id      = :EndSnap
					                 and bbg.snap_id (+) = :BgnSnap
					                 and ebg.snap_id (+) = :EndSnap
					                 /*and e.dbid          = :dbid*/
					                 and e.instance_number = :INST_NUMBER
					                 and e.dbid            = b.dbid (+)
					                and e.instance_number = b.instance_number (+)
					                 and e.event_id        = b.event_id (+)
					                 and e.dbid            = ebg.dbid (+)
					                 and e.instance_number = ebg.instance_number (+)
					                 and e.event_id        = ebg.event_id (+)
					                 and e.dbid            = bbg.dbid (+)
					                 and e.instance_number = bbg.instance_number (+)
					                 and e.event_id        = bbg.event_id (+)
					                 and e.total_waits     > nvl(b.total_waits,0)
					                 and e.wait_class     <> 'Idle'
					              union all
					              select 'DB CPU'                   event
					                   , to_number(null)              wtfg
					                   , to_number(null)              ttofg
					                   , &_tcpu                        tmfg
					                   , ' '                        wcls
					                from dual
					               where &_tcpu > 0)
					            order by tmfg desc, wtfg desc)
					         where rownum <= 5 
 )       



Event                                    Waits    Time(s) Avg wait (ms)  % DB time Wait Class
----------------------------------- ---------- ---------- ------------- ---------- --------------------
DB CPU                                         1128290.65                    32.09
SQL*Net more data from client            34697    4315.17        124.37        .12 Network
cursor: pin S wait on X                   1800     457.21        254.01        .01 Concurrency
db file sequential read               34056796     211.47           .01        .01 User I/O
SQL*Net message from dblink             388380     191.16           .49        .01 Network

SQL>







set linesize 500 pagesize 300


col WAIT_CLASS for a20 
col EVENT  for a35
with snap as
 (select *
    from (select dbid,
                 lead(snap_id) over(partition by instance_number order by end_interval_time desc) bid,
                 snap_id eid,
                 row_number() over(order by end_interval_time desc) n
            from dba_hist_snapshot
           where dbid = (select dbid from v$database))
   where n = 1)
SELECT EVENT,
       DECODE(WAITS,
              NULL,
              TO_NUMBER('0'),
              0,
              TO_NUMBER(NULL),
              TIME / WAITS * 1000) AVGWT,
       PCTWTT, TIME, WAIT_CLASS
  FROM (SELECT EVENT, WAITS, TIME, PCTWTT, WAIT_CLASS
          FROM (SELECT E.EVENT_NAME EVENT,
                       E.TOTAL_WAITS - NVL(B.TOTAL_WAITS, 0) WAITS,
                       (E.TIME_WAITED_MICRO - NVL(B.TIME_WAITED_MICRO, 0)) /
                       1000000 TIME,
                       100 *
                       (E.TIME_WAITED_MICRO - NVL(B.TIME_WAITED_MICRO, 0)) /
                       ((SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL e
                          WHERE e.SNAP_ID = (select eid from snap)
                            AND e.DBID = (select dbid from snap)
                            AND e.INSTANCE_NUMBER = (select instance_number from v$instance)
                            AND e.STAT_NAME = 'DB time') -
                       (SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL b
                          WHERE b.SNAP_ID = (select bid from snap)
                            AND b.DBID = (select dbid from snap)
                            AND b.INSTANCE_NUMBER = (select instance_number from v$instance)
                            AND b.STAT_NAME = 'DB time')) PCTWTT,
                       E.WAIT_CLASS WAIT_CLASS
                  FROM DBA_HIST_SYSTEM_EVENT B, DBA_HIST_SYSTEM_EVENT E
                 WHERE B.SNAP_ID = (select bid from snap)
                   AND E.SNAP_ID = (select eid from snap)
                   AND B.DBID = (select dbid from snap)
                   AND E.DBID = (select dbid from snap)
                   AND B.INSTANCE_NUMBER = (select instance_number from v$instance)
                   AND E.INSTANCE_NUMBER = (select instance_number from v$instance)
                   AND B.EVENT_ID(+) = E.EVENT_ID
                   AND E.TOTAL_WAITS > NVL(B.TOTAL_WAITS, 0)
                   AND E.WAIT_CLASS != 'Idle'
                UNION ALL
                SELECT 'CPU time' EVENT,
                       TO_NUMBER(NULL) WAITS,
                       ((SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL e
                          WHERE e.SNAP_ID = (select eid from snap)
                            AND e.DBID = (select dbid from snap)
                            AND e.INSTANCE_NUMBER = (select instance_number from v$instance)
                            AND e.STAT_NAME = 'DB CPU') -
                       (SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL b
                          WHERE b.SNAP_ID = (select bid from snap)
                            AND b.DBID = (select dbid from snap)
                            AND b.INSTANCE_NUMBER = (select instance_number from v$instance)
                            AND b.STAT_NAME = 'DB CPU')) / 1000000 TIME,
                       100 * ((SELECT sum(value)
                                 FROM DBA_HIST_SYS_TIME_MODEL e
                                WHERE e.SNAP_ID = (select eid from snap)
                                  AND e.DBID = (select dbid from snap)
                                  AND e.INSTANCE_NUMBER = (select instance_number from v$instance)
                                  AND e.STAT_NAME = 'DB CPU') -
                       (SELECT sum(value)
                                 FROM DBA_HIST_SYS_TIME_MODEL b
                                WHERE b.SNAP_ID = (select bid from snap)
                                  AND b.DBID = (select dbid from snap)
                                  AND b.INSTANCE_NUMBER = (select instance_number from v$instance)
                                  AND b.STAT_NAME = 'DB CPU')) /
                       ((SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL e
                          WHERE e.SNAP_ID = (select eid from snap)
                            AND e.DBID = (select dbid from snap)
                            AND e.INSTANCE_NUMBER = (select instance_number from v$instance)
                            AND e.STAT_NAME = 'DB time') -
                       (SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL b
                          WHERE b.SNAP_ID = (select bid from snap)
                            AND b.DBID = (select dbid from snap)
                            AND b.INSTANCE_NUMBER = (select instance_number from v$instance)
                            AND b.STAT_NAME = 'DB time')) PCTWTT,
                       NULL WAIT_CLASS
                  from dual
                 WHERE ((SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL e
                          WHERE e.SNAP_ID = (select eid from snap)
                            AND e.DBID = (select dbid from snap)
                            AND e.INSTANCE_NUMBER = (select instance_number from v$instance)
                            AND e.STAT_NAME = 'DB CPU') -
                       (SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL b
                          WHERE b.SNAP_ID = (select bid from snap)
                            AND b.DBID = (select dbid from snap)
                            AND b.INSTANCE_NUMBER = (select instance_number from v$instance)
                            AND b.STAT_NAME = 'DB CPU')) > 0)
         ORDER BY TIME DESC, WAITS DESC)
 WHERE ROWNUM <= 10;


EVENT                                    AVGWT     PCTWTT       TIME WAIT_CLASS
----------------------------------- ---------- ---------- ---------- --------------------
CPU time                                     0  85.996498 32701.2587
SQL*Net more data from client       85.2547252 11.1312839 4232.81185 Network
cursor: pin S wait on X             254.015085  1.4562378 553.752886 Concurrency
log file sync                       2.79578428 .550594904 209.370693 Commit
db file sequential read             .005765488 .514617521 195.689837 User I/O
SQL*Net message from dblink         .452614936 .460765091 175.211768 Network
log file parallel write              .37174284 .352692763 134.115895 System I/O
db file async I/O submit            2.41185522  .08757232  33.300485 System I/O
direct path read                    .025731834 .080723604  30.696174 User I/O
LGWR any worker group               .885262368 .065128926    24.7661 Other

10 rows selected.






*******************************


for snap info !!
http://anuj-singh.blogspot.com/2011/10/oracle-awr-snap-id.html


VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -3 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 ;





set linesize 500 pagesize 300


col WAIT_CLASS for a20 
col EVENT  for a35
with snap as
 (select *
    from (select dbid,
                 lead(snap_id) over(partition by instance_number order by end_interval_time desc) bid,
                 snap_id eid,
                 row_number() over(order by end_interval_time desc) n
            from dba_hist_snapshot
           where dbid = (select dbid from v$database))
   where n = 1)
SELECT EVENT,
       DECODE(WAITS,
              NULL,
              TO_NUMBER('0'),
              0,
              TO_NUMBER(NULL),
              TIME / WAITS * 1000) AVGWT,
       PCTWTT, TIME, WAIT_CLASS
  FROM (SELECT EVENT, WAITS, TIME, PCTWTT, WAIT_CLASS
          FROM (SELECT E.EVENT_NAME EVENT,
                       E.TOTAL_WAITS - NVL(B.TOTAL_WAITS, 0) WAITS,
                       (E.TIME_WAITED_MICRO - NVL(B.TIME_WAITED_MICRO, 0)) /
                       1000000 TIME,
                       100 *
                       (E.TIME_WAITED_MICRO - NVL(B.TIME_WAITED_MICRO, 0)) /
                       ((SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL e
                          WHERE e.SNAP_ID = :EndSnap
                            AND e.DBID = (select dbid from snap)
                            AND e.INSTANCE_NUMBER = (select instance_number from v$instance)
                            AND e.STAT_NAME = 'DB time') -
                       (SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL b
                          WHERE b.SNAP_ID = :BgnSnap
                            AND b.DBID = (select dbid from snap)
                            AND b.INSTANCE_NUMBER = (select instance_number from v$instance)
                            AND b.STAT_NAME = 'DB time')) PCTWTT,
                       E.WAIT_CLASS WAIT_CLASS
                  FROM DBA_HIST_SYSTEM_EVENT B, DBA_HIST_SYSTEM_EVENT E
                 WHERE B.SNAP_ID = :BgnSnap
                   AND E.SNAP_ID = :EndSnap
                   AND B.DBID = (select dbid from snap)
                   AND E.DBID = (select dbid from snap)
                   AND B.INSTANCE_NUMBER = (select instance_number from v$instance)
                   AND E.INSTANCE_NUMBER = (select instance_number from v$instance)
                   AND B.EVENT_ID(+) = E.EVENT_ID
                   AND E.TOTAL_WAITS > NVL(B.TOTAL_WAITS, 0)
                   AND E.WAIT_CLASS != 'Idle'
                UNION ALL
                SELECT 'CPU time' EVENT,
                       TO_NUMBER(NULL) WAITS,
                       ((SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL e
                          WHERE e.SNAP_ID = :EndSnap
                            AND e.DBID = (select dbid from snap)
                            AND e.INSTANCE_NUMBER = (select instance_number from v$instance)
                            AND e.STAT_NAME = 'DB CPU') -
                       (SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL b
                          WHERE b.SNAP_ID = :BgnSnap
                            AND b.DBID = (select dbid from snap)
                            AND b.INSTANCE_NUMBER = (select instance_number from v$instance)
                            AND b.STAT_NAME = 'DB CPU')) / 1000000 TIME,
                       100 * ((SELECT sum(value)
                                 FROM DBA_HIST_SYS_TIME_MODEL e
                                WHERE e.SNAP_ID = :EndSnap
                                  AND e.DBID = (select dbid from snap)
                                  AND e.INSTANCE_NUMBER = (select instance_number from v$instance)
                                  AND e.STAT_NAME = 'DB CPU') -
                       (SELECT sum(value)
                                 FROM DBA_HIST_SYS_TIME_MODEL b
                                WHERE b.SNAP_ID = :BgnSnap
                                  AND b.DBID = (select dbid from snap)
                                  AND b.INSTANCE_NUMBER = (select instance_number from v$instance)
                                  AND b.STAT_NAME = 'DB CPU')) /
                       ((SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL e
                          WHERE e.SNAP_ID = :EndSnap
                            AND e.DBID = (select dbid from snap)
                            AND e.INSTANCE_NUMBER = (select instance_number from v$instance)
                            AND e.STAT_NAME = 'DB time') -
                       (SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL b
                          WHERE b.SNAP_ID = :BgnSnap
                            AND b.DBID = (select dbid from snap)
                            AND b.INSTANCE_NUMBER = (select instance_number from v$instance)
                            AND b.STAT_NAME = 'DB time')) PCTWTT,
                       NULL WAIT_CLASS
                  from dual
                 WHERE ((SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL e
                          WHERE e.SNAP_ID = :EndSnap
                            AND e.DBID = (select dbid from snap)
                            AND e.INSTANCE_NUMBER = (select instance_number from v$instance)
                            AND e.STAT_NAME = 'DB CPU') -
                       (SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL b
                          WHERE b.SNAP_ID = :BgnSnap
                            AND b.DBID = (select dbid from snap)
                            AND b.INSTANCE_NUMBER = (select instance_number from v$instance)
                            AND b.STAT_NAME = 'DB CPU')) > 0)
         ORDER BY TIME DESC, WAITS DESC)
 WHERE ROWNUM <= 10;





=======


 set numf 9999999999999999 linesize 300
   col dbid1 new_value dbid2 noprint
   col db_name1 new_value db_name2 noprint
   col inst_name1 new_value inst_name2 noprint
   col instance_number1 new_value instance_number2 noprint
   col begin_snap_id1 new_value begin_snap_id2 noprint
   col end_snap_id1 new_value end_snap_id2 noprint
   col tcpu1 new_value tcpu2 noprint
   col dbtime1 new_value dbtime2 noprint

select dbid 			 dbid1 ,
       db_name 			 db_name1 ,
       instance_number 	 instance_number1  ,
       inst_name 		 inst_name1 ,
       begin_snap_id 	 begin_snap_id1 ,
       end_snap_id 		 end_snap_id1 ,
       (SELECT  sum(e.VALUE-b.value) as diff_value
           FROM dba_hist_sys_time_model    B,
                dba_hist_sys_time_model  E
          WHERE e.dbid = b.dbid
            and e.instance_number = b.instance_number
            and e.STAT_ID = b.STAT_ID
            and B.DBID            = base_info.dbid
            AND B.INSTANCE_NUMBER = base_info.instance_number
            AND B.SNAP_ID         = base_info.begin_snap_id
            AND E.SNAP_ID         = base_info.end_snap_id
            and e.stat_name in ('DB CPU') )  tcpu1 ,
       (SELECT  sum(e.VALUE-b.value) as diff_value
           FROM dba_hist_sys_time_model    B,
                dba_hist_sys_time_model  E
          WHERE e.dbid = b.dbid
            and e.instance_number = b.instance_number
            and e.STAT_ID = b.STAT_ID
            and B.DBID            = base_info.dbid
            AND B.INSTANCE_NUMBER = base_info.instance_number
            AND B.SNAP_ID         = base_info.begin_snap_id
            AND E.SNAP_ID         = base_info.end_snap_id
            and e.stat_name in ('DB time') )  dbtime1 
--			into    :DBID, :DB_NAME  ,   :INSTANCE_NUMBER ,:INST_NAME ,  :BEGIN_SNAP_ID , :END_SNAP_ID, :TCPU, :DBTIME
  from (with db_info as (select d.dbid            dbid,
                                d.name            db_name,
                                i.instance_number instance_number,
                                i.instance_name   inst_name
                           from v$database d, v$instance i), 
         snap_info as (select c.*,
                              EXTRACT(DAY FROM c.max_end_interval_time - c.min_end_interval_time) * 86400
                               + EXTRACT(HOUR FROM c.max_end_interval_time - c.min_end_interval_time) * 3600
                               + EXTRACT(MINUTE FROM c.max_end_interval_time - c.min_end_interval_time) * 60
                               + EXTRACT(SECOND FROM c.max_end_interval_time - c.min_end_interval_time) ELAPSED
                         from (select min(snap_id) begin_snap_id,
                                      max(snap_id) end_snap_id,
                                      min(END_INTERVAL_TIME) as  min_end_interval_time,
                                      max(END_INTERVAL_TIME) as max_end_interval_time
                                 from dba_hist_snapshot sn
                                where 1=1
 								and sn.begin_interval_time >= trunc(sysdate) - 1/24         and sn.begin_interval_time < sysdate
														  ) c
                        )        
       select * from db_info, snap_info) base_info
		   ;
  
 
 col Event for a35
 
 col "Wait Class" for a15
select event "Event",
       waits "Waits",
       round(time,2)  "Time(s)",
       round(avwait,2) "Avg wait (ms)",
       round(pctwtt,2) "% DB time",
       wcls "Wait Class" 
from (  select event
					     , wtfg             waits
					     , tmfg/1000000     time
					     , decode(wtfg, 0, to_number(null), tmfg/wtfg)/1000  avwait
					     , decode(&dbtime2, 0, to_number(null), tmfg/&dbtime2)*100   pctwtt
					     , wcls
					  from (   select event, wtfg, ttofg, tmfg, wcls
					           from (
					              select e.event_name                                      event
					                   , case when e.total_waits_fg is not null
					                          then e.total_waits_fg - nvl(b.total_waits_fg,0)
					                          else (e.total_waits - nvl(b.total_waits,0))
					                                - greatest(0,(nvl(ebg.total_waits,0)
					                                          - nvl(bbg.total_waits,0)))
					                     end                                               wtfg
					                   , case when e.total_timeouts_fg is not null
					                          then e.total_timeouts_fg - nvl(b.total_timeouts_fg,0)
					                          else (e.total_timeouts - nvl(b.total_timeouts,0))
					                                - greatest(0,(nvl(ebg.total_timeouts,0)
					                                          - nvl(bbg.total_timeouts,0)))
					                     end                                              ttofg
					                   , case when e.time_waited_micro_fg is not null
					                          then e.time_waited_micro_fg - nvl(b.time_waited_micro_fg,0)
					                          else (e.time_waited_micro - nvl(b.time_waited_micro,0))
					                                - greatest(0,(nvl(ebg.time_waited_micro,0)
					                                          - nvl(bbg.time_waited_micro,0)))
					                     end                                              tmfg
					                   , e.wait_class                                     wcls
					                from dba_hist_system_event b
					                   , dba_hist_system_event e
					                   , dba_hist_bg_event_summary bbg
					                   , dba_hist_bg_event_summary ebg
					               where b.snap_id  (+) = &begin_snap_id2
					                 and e.snap_id      = &end_snap_id2
					                 and bbg.snap_id (+) = &begin_snap_id2
					                 and ebg.snap_id (+) = &end_snap_id2
					                 /*and e.dbid          = :dbid*/
					                 and e.instance_number = &instance_number2
					                 and e.dbid            = b.dbid (+)
					                and e.instance_number = b.instance_number (+)
					                 and e.event_id        = b.event_id (+)
					                 and e.dbid            = ebg.dbid (+)
					                 and e.instance_number = ebg.instance_number (+)
					                 and e.event_id        = ebg.event_id (+)
					                 and e.dbid            = bbg.dbid (+)
					                 and e.instance_number = bbg.instance_number (+)
					                 and e.event_id        = bbg.event_id (+)
					                 and e.total_waits     > nvl(b.total_waits,0)
					                 and e.wait_class     <> 'Idle'
					              union all
					              select 'DB CPU'                   event
					                   , to_number(null)              wtfg
					                   , to_number(null)              ttofg
					                   , &tcpu2                        tmfg
					                   , ' '                        wcls
					                from dual
					               where &tcpu2 > 0
								   )
					            order by tmfg desc, wtfg desc)
					         where rownum <= 5 
 )       


Event                                           Waits           Time(s)     Avg wait (ms)         % DB time Wait Class
----------------------------------- ----------------- ----------------- ----------------- ----------------- ---------------
DB CPU                                                            13011                                  95
SQL*Net vector data to client               437666295              3181                 0                23 Network
direct path read                            466825923              1964                 0                14 User I/O
direct path write                              989554               924                 1                 7 User I/O
SQL*Net more data to client                  37250820               285                 0                 2 Network

SQL>



VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
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 ;

set linesize 400 pagesize 300

COL EVENT FOR a50  HEAD "Top 10 Foreground Events by Total Wait Time"
 COL Waits HEAD "Waits|"
 COL TIME HEAD "Total Wait|Time(sec)"
 COL AVWAIT HEAD "Wait|Avg(ms)"
 COL PCTWTT for 99999.9 HEAD "% DB|time"
 COL WCLS FOR a70 HEAD "Wait Class"

WITH snp
     AS (  SELECT DBID,
                  INSTANCE_NUMBER,
                  MIN (SNAP_ID) bid,
                  MAX (SNAP_ID) eid,
                  ROUND (
                     EXTRACT (
                        DAY FROM MAX (END_INTERVAL_TIME)
                                  - MIN (END_INTERVAL_TIME))
                     * 86400				  
                     + EXTRACT (
                        HOUR FROM MAX (END_INTERVAL_TIME)
                                  - MIN (END_INTERVAL_TIME))
                     * 3600
                     + EXTRACT (
                          MINUTE FROM MAX (END_INTERVAL_TIME)
                                      - MIN (END_INTERVAL_TIME))
                       * 60
                     + EXTRACT (
                          SECOND FROM MAX (END_INTERVAL_TIME)
                                      - MIN (END_INTERVAL_TIME)))
                     ets
             FROM DBA_HIST_SNAPSHOT
            WHERE INSTANCE_NUMBER = USERENV ('Instance')
                  --AND snap_id BETWEEN :BgnSnap AND :EndSnap
         AND BEGIN_INTERVAL_TIME BETWEEN trunc(sysdate)-1 AND trunc(sysdate)
         GROUP BY DBID, INSTANCE_NUMBER),
     dbtime
     AS (SELECT NVL ( (e1.VALUE - b1.VALUE), -1) dbtimev
           FROM dba_hist_sys_time_model e1, dba_hist_sys_time_model b1, snp
          WHERE     b1.snap_id = snp.bid
                AND e1.snap_id = snp.eid
                AND b1.dbid = snp.DBID
                AND e1.dbid = snp.DBID
                AND b1.instance_number = snp.INSTANCE_NUMBER
                AND e1.instance_number = snp.INSTANCE_NUMBER
                AND b1.stat_name = 'DB time'
                AND b1.stat_id = e1.stat_id)
SELECT event,
       wtfg waits,
       ROUND (tmfg / 1000000, 1) time,
       ROUND (DECODE (wtfg, 0, TO_NUMBER (NULL), tmfg / wtfg) / 1000) avwait,
       ROUND (DECODE (dbtimev, 0, TO_NUMBER (NULL), tmfg / dbtimev) * 100, 1)
          pctwtt,
       wcls
  FROM (  SELECT event,
                 wtfg,
                 ttofg,
                 tmfg,
                 wcls
            FROM (SELECT e.event_name event,
                         CASE
                            WHEN e.total_waits_fg IS NOT NULL
                            THEN
                               e.total_waits_fg - NVL (b.total_waits_fg, 0)
                            ELSE
                               (e.total_waits - NVL (b.total_waits, 0))
                               - GREATEST (
                                    0,
                                    (NVL (ebg.total_waits, 0)
                                     - NVL (bbg.total_waits, 0)))
                         END
                            wtfg,
                         CASE
                            WHEN e.total_timeouts_fg IS NOT NULL
                            THEN
                               e.total_timeouts_fg
                               - NVL (b.total_timeouts_fg, 0)
                            ELSE
                               (e.total_timeouts - NVL (b.total_timeouts, 0))
                               - GREATEST (
                                    0,
                                    (NVL (ebg.total_timeouts, 0)
                                     - NVL (bbg.total_timeouts, 0)))
                         END
                            ttofg,
                         CASE
                            WHEN e.time_waited_micro_fg IS NOT NULL
                            THEN
                               e.time_waited_micro_fg
                               - NVL (b.time_waited_micro_fg, 0)
                            ELSE
                               (e.time_waited_micro
                                - NVL (b.time_waited_micro, 0))
                               - GREATEST (
                                    0,
                                    (NVL (ebg.time_waited_micro, 0)
                                     - NVL (bbg.time_waited_micro, 0)))
                         END
                            tmfg,
                         e.wait_class wcls
                    FROM dba_hist_system_event b,
                         dba_hist_system_event e,
                         dba_hist_bg_event_summary bbg,
                         dba_hist_bg_event_summary ebg,
                         snp
                   WHERE    
				   		/* 		   b.snap_id  (+) = :bid
         and e.snap_id      = :eid
         and bbg.snap_id (+) = :bid
         and ebg.snap_id (+) = :eid
         and e.dbid          = :dbid
         and e.instance_number = :inst_num
		 b.snap_id(+) = snp.bid
          */      			 bbg.snap_id (+)= 35279
                         AND b.snap_id(+) =35279
                         AND ebg.snap_id (+) = 35280
                         AND e.snap_id = snp.eid
                         AND e.dbid = snp.DBID
                         AND e.instance_number = snp.INSTANCE_NUMBER
                         AND e.dbid = b.dbid(+)
                         AND e.instance_number = b.instance_number(+)
                         AND e.event_id = b.event_id(+)
                         AND e.dbid = ebg.dbid(+)
                         AND e.instance_number = ebg.instance_number(+)
                         AND e.event_id = ebg.event_id(+)
                         AND e.dbid = bbg.dbid(+)
                         AND e.instance_number = bbg.instance_number(+)
                         AND e.event_id = bbg.event_id(+)
                         AND e.total_waits > NVL (b.total_waits, 0)
                         AND e.wait_class <> 'Idle'
                  UNION ALL
                  SELECT 'DB CPU' event,
                         TO_NUMBER (NULL) wtfg,
                         TO_NUMBER (NULL) ttofg,
                         NVL ( (e1.VALUE - b1.VALUE), -1) tmfg,
                            ' '                           wcls
                    FROM dba_hist_sys_time_model e1,
                         dba_hist_sys_time_model b1,
                         snp,
                         dbtime
                   WHERE     b1.snap_id = snp.bid
                         AND e1.snap_id = snp.eid
                         AND b1.dbid = snp.DBID
                         AND e1.dbid = snp.DBID
                         AND b1.instance_number = snp.INSTANCE_NUMBER
                         AND e1.instance_number = snp.INSTANCE_NUMBER
                         AND b1.stat_name = 'DB CPU'
                         AND b1.stat_id = e1.stat_id
				UNION ALL
				SELECT ' ' event,
                         TO_NUMBER (NULL) wtfg,
                         TO_NUMBER (NULL) ttofg,
                         TO_NUMBER (NULL)  tmfg,
                            '------------ For Inst-'
                         || snp.INSTANCE_NUMBER
                         || ' btwn snap_id '
                         || snp.bid
                         || ' and '
                         || snp.eid
                         || CHR (10)
                         || '------------ Elp Time '
                         || ROUND (ets / 60, 2)
                         || '(mins) and DB Time '
                         || ROUND (dbtimev / 1000000 / 60, 2)
                         || '(mins)'
                            wcls from
                         snp,
                         dbtime )
        ORDER BY tmfg DESC, wtfg DESC, event),
       dbtime
 WHERE (ROWNUM <= 11 AND tmfg > 0) or wcls like '%For Inst%'
 ;
 
 
 
                                                           Waits Total Wait       Wait     % DB
Top 10 Foreground Events by Total Wait Time                    Time(sec)    Avg(ms)     time Wait Class
-------------------------------------------------- ---------- ---------- ---------- -------- ----------------------------------------------------------------------
                                                                                             ------------ For Inst-1 btwn snap_id 364 and 387
                                                                                             ------------ Elp Time 1379.68(mins) and DB Time 11699.35(mins)

log file sync                                       263324271  1778916.4          7    253.4 Commit
direct path read                                    268631222   925651.9          3    131.9 User I/O
DB CPU                                                          274497.2                39.1
write complete waits                                  1218575   113776.7         93     16.2 Configuration
db file sequential read                              11650627    63752.4          5      9.1 User I/O
control file sequential read                          9256027    46057.1          5      6.6 System I/O
SQL*Net vector data to client                        74340146    11576.6          0      1.6 Network
SQL*Net more data to client                         118968690    11320.6          0      1.6 Network
db file scattered read                                1724480     9921.6          6      1.4 User I/O
SQL*Net message to client                          7649097212     6706.5          0      1.0 Network

11 rows selected.


****





set pages 999  lines 200
 
column c1 heading 'Event|Name'             format a50
column c2 heading 'Total|Waits'            format a10
column c3 heading 'Waiting'        			format a10
column c4 heading 'Total|Timeouts'         format a10
column c5 heading 'Average|Wait' 			format a10
 
ttitle 'System-wide Wait Analysis|for current wait events
 -- TOTAL_WAITS NUMBER Total number of waits for the event
 
select
   event                         c1,
   dbms_xplan.format_number(total_waits)                   c2,
   dbms_xplan.FORMAT_TIME_S(time_waited / 100 )            c3,
   dbms_xplan.format_number(total_timeouts)                c4,
   dbms_xplan.FORMAT_TIME_S(average_wait    /100 )         c5
from
   sys.v_$system_event
where
   event not in (
    'dispatcher timer',
    'lock element cleanup',
    'Null event',
    'parallel query dequeue wait',
    'parallel query idle wait - Slaves',
    'pipe get',
    'PL/SQL lock timer',
    'pmon timer',
    'rdbms ipc message',
    'slave wait',
    'smon timer',
    'SQL*Net break/reset to client',
    'SQL*Net message from client',
    'SQL*Net message to client',
    'SQL*Net more data to client',
    'virtual circuit status',
    'WMON goes to sleep'
   )
AND
 event not like 'DFS%'
and
   event not like '%done%'
and
   event not like '%Idle%'
AND
 event not like 'KXFX%'
order by
   c2 desc
;


-- from Web 




set serveroutput on linesize 300 pagesize 300
COLUMN p1 NEW_VALUE 1
COLUMN p2 NEW_VALUE 2
set termout off	
SELECT null p1, null p2 FROM dual WHERE 1=2;

SELECT nvl('&1','5') p1, nvl('&2','db file sequential read') p2 from dual;  
set termout on

DECLARE
  v_sleep_time                 number;
  v_event_name                 varchar2(50) := '&2';
  v_delta_waits                number;
  v_delta_waits_per_sec        number;
  v_delta_time_waited_estimate number;
  v_delta_time_waited_micro    number; 
  v_avg_wait_time_micro        number;
  v_latencybin                 varchar2(100);

  CURSOR c1 IS
    SELECT event, wait_time_micro, sum(wait_count) wait_count, max(last_update_time) last_update_time
    FROM v$event_histogram_micro
    WHERE event = v_event_name
	GROUP BY event, wait_time_micro
	ORDER BY event, wait_time_micro;
	
  CURSOR c2 IS
    SELECT event, sum(time_waited_micro) time_waited_micro, sum(total_waits) total_waits
    FROM v$system_event
    WHERE event = v_event_name
	GROUP BY event
	ORDER BY event;


  TYPE EventHisto IS TABLE OF c1%ROWTYPE;
  TYPE SysEvent   IS TABLE OF c2%ROWTYPE;

  t0_histval  EventHisto;  -- nested table of records for t0 snapshot
  t1_histval  EventHisto;  -- nested table of records for t1 snapshot
  t0_eventval SysEvent;    -- nested table of records for t0 snapshot
  t1_eventval SysEvent;    -- nested table of records for t1 snapshot

BEGIN
  -- input validation
  BEGIN
     v_sleep_time := TO_NUMBER('&1');
	 IF (v_sleep_time <= 0) THEN
        raise value_error;
	 END IF;
  EXCEPTION	 
       WHEN value_error THEN
	     RAISE_APPLICATION_ERROR(-20001,'Wait time must be numeric and >0. Example use wait time = 10');
  END;

  -- collect t0 data
  OPEN c1;
  OPEN c2;
  FETCH c1 BULK COLLECT INTO t0_histval;
  FETCH c2 BULK COLLECT INTO t0_eventval; 
  CLOSE c1;
  CLOSE c2;

  IF t0_eventval.COUNT <=0 THEN
      RAISE_APPLICATION_ERROR(-20001,'Not enough data. Probably wrong event name. Tip, try event = "db file sequential read".');
  END IF;

  IF t0_eventval.COUNT >= 100 THEN
    RAISE_APPLICATION_ERROR(-20002,'Too many values, soft limit set to 100');
  END IF;

  -- put wait time here note user needs exec privilege on dbms_lock  
  sys.DBMS_LOCK.SLEEP (v_sleep_time);

  -- collect t1 data
  OPEN c1;
  OPEN c2;
  FETCH c1 BULK COLLECT INTO t1_histval;
  FETCH c2 BULK COLLECT INTO t1_eventval; 
  CLOSE c1;
  CLOSE c2;

  -- check and report error if number of points is different between the two snapshots
  -- (rare, but can happen if a new histogram bin has been created)
  IF t0_histval.COUNT <> t1_histval.COUNT THEN
     RAISE_APPLICATION_ERROR(-20003,'Number of histogram bins changed during collection. Cannot handle it.');
  END IF;

  -- print out results
  -- compute delta values and print. 
  -- format with rpad to keep column width constant

  -- Latency histogram from v$event_histogram_micro
  DBMS_OUTPUT.PUT_LINE(chr(13));
  DBMS_OUTPUT.PUT_LINE('Latency histogram for event "&2" from V$EVENT_HISTOGRAM_MICRO:');
  DBMS_OUTPUT.PUT_LINE(chr(13));
  DBMS_OUTPUT.PUT_LINE ('Latency Bucket       Num Waits/DeltaT  Wait Time/DeltaT    Event Name                 Last Update Time');
  DBMS_OUTPUT.PUT_LINE ('(microsec)           (Hz)              (microsec/sec)                                                 ');
  DBMS_OUTPUT.PUT_LINE ('-------------------  ----------------  ------------------  -------------------------  -----------------------------------');

  FOR i IN t1_histval.FIRST .. t1_histval.LAST LOOP
    v_delta_waits := t1_histval(i).wait_count - t0_histval(i).wait_count;
    v_delta_waits_per_sec := round(v_delta_waits / v_sleep_time,1);
	v_delta_time_waited_estimate := round(0.75 * t1_histval(i).wait_time_micro * v_delta_waits_per_sec,1);  -- estimated value
	IF (t1_histval(i).wait_time_micro <> 1) THEN
		v_latencybin := to_char(t1_histval(i).wait_time_micro/2) ||' -> ' || to_char(t1_histval(i).wait_time_micro);
    ELSE
		v_latencybin := '0 -> 1';
	END IF;
    DBMS_OUTPUT.PUT_LINE (
        rpad(v_latencybin,19,' ')||'  '||
        lpad(to_char(v_delta_waits_per_sec),16,' ')||'  '||
        lpad(to_char(v_delta_time_waited_estimate),18,' ')||'   '||
        rpad(t1_histval(i).event,24,' ') ||' '||
        t1_histval(i).last_update_time 
      );
    END LOOP;

  -- This is the summary from v$system_event
  DBMS_OUTPUT.PUT_LINE(chr(13));
  DBMS_OUTPUT.PUT_LINE('Average values from V$SYSTEM_EVENT:');
  DBMS_OUTPUT.PUT_LINE(chr(13));
  DBMS_OUTPUT.PUT_LINE ('Mean Wait Time       Num Waits/DeltaT  Wait Time/DeltaT    Event Name               ');
  DBMS_OUTPUT.PUT_LINE ('(microsec)           (Hz)              (microsec/sec)                               ');
  DBMS_OUTPUT.PUT_LINE ('-------------------  ----------------  ------------------  -------------------------');

  FOR i IN t1_eventval.FIRST .. t1_eventval.LAST LOOP
    v_delta_time_waited_micro :=  t1_eventval(i).time_waited_micro - t0_eventval(i).time_waited_micro;
    v_delta_waits := t1_eventval(i).total_waits - t0_eventval(i).total_waits;
    v_delta_waits_per_sec := round(v_delta_waits / v_sleep_time, 1);
   IF v_delta_waits <> 0 then
       v_avg_wait_time_micro := round(v_delta_time_waited_micro/v_delta_waits,1);
    ELSE
       v_avg_wait_time_micro := 0;
    END IF;
    DBMS_OUTPUT.PUT_LINE(
        rpad(to_char(v_avg_wait_time_micro),19,' ')||'  '||
        lpad(to_char(v_delta_waits_per_sec),16,' ')||'  '||
        lpad(to_char(round(v_delta_time_waited_micro/v_sleep_time,1)),18,' ')||'  '||
        rpad(t1_histval(i).event,24,' ')
      );
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(chr(13));
  
END;
/


Latency Bucket       Num Waits/DeltaT  Wait Time/DeltaT    Event Name                 Last Update Time
(microsec)           (Hz)              (microsec/sec)
-------------------  ----------------  ------------------  -------------------------  -----------------------------------
0 -> 1                              0                   0   db file sequential read
1 -> 2                              0                   0   db file sequential read
2 -> 4                              0                   0   db file sequential read
4 -> 8                              0                   0   db file sequential read
8 -> 16                             0                   0   db file sequential read








Oracle DBA

anuj blog Archive