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 instancenumber NEW_VALUE _instancenumber NOPRINT
select instance_number instancenumber from v$instance;

ttitle center 'AWR AAS Report' skip 2
set pagesize 50000
set linesize 250

col tm          format a15              heading "Snap|Start|Time"
col id          format 99999            heading "Snap|ID"
col inst        format 90               heading "i|n|s|t|#"
col dur         format 999990.00        heading "Snap|Dur|(m)"
col cpu         format 90               heading "C|P|U"
col cap         format 9999990.00       heading "***|Total|CPU|Time|(s)"
col dbt         format 999990.00        heading "DB|Time"
col dbc         format 99990.00         heading "DB|CPU"
col bgc         format 99990.00         heading "Bg|CPU"
col rman        format 9990.00          heading "RMAN|CPU"
col aas         format 990.0            heading "A|A|S"
col totora      format 9999990.00       heading "***|Total|Oracle|CPU|(s)"
col busy        format 9999990.00       heading "Busy|Time"
col load        format 990.00           heading "OS|Load"
col totos       format 9999990.00       heading "***|Total|OS|CPU|(s)"
col mem         format 999990.00        heading "Physical|Memory|(mb)"
col IORs        format 9990.000         heading "IOPs|r"
col IOWs        format 9990.000         heading "IOPs|w"
col IORedo      format 9990.000         heading "IOPs|redo"
col IORmbs      format 9990.000         heading "IO r|(mb)/s"
col IOWmbs      format 9990.000         heading "IO w|(mb)/s"
col redosizesec format 9990.000         heading "Redo|(mb)/s"
col logons      format 990              heading "Sess"
col logone      format 990              heading "Sess|End"
col exsraw      format 99990.000        heading "Exec|raw|delta"
col exs         format 9990.000         heading "Exec|/s"
col oracpupct   format 990              heading "Oracle|CPU|%"
col rmancpupct  format 990              heading "RMAN|CPU|%"
col oscpupct    format 990              heading "OS|CPU|%"
col oscpuusr    format 990              heading "U|S|R|%"
col oscpusys    format 990              heading "S|Y|S|%"
col oscpuio     format 990              heading "I|O|%"

SELECT * FROM
(
  SELECT s0.snap_id id,
  TO_CHAR(s0.SNAP_TIME,'MM/DD/YY HH24:MI') tm,
  s0.instance_number inst,
        round((to_char(s1.SNAP_TIME+1/24,'DD') - to_char(s0.SNAP_TIME+1/24,'DD')) * 1440
                + round(to_char(s1.SNAP_TIME+1/24,'HH24') - to_char(s0.SNAP_TIME+1/24,'HH24')) * 60
                + to_char(s1.SNAP_TIME+1/24,'MI') - to_char(s0.SNAP_TIME+1/24,'MI')
                + to_char(s1.SNAP_TIME+1/24,'SS') - to_char(s0.SNAP_TIME+1/24,'SS') / 60,2) dur,
  s3t1.value AS cpu,
  (round((to_char(s1.SNAP_TIME+1/24,'DD') - to_char(s0.SNAP_TIME+1/24,'DD')) * 1440
                + round(to_char(s1.SNAP_TIME+1/24,'HH24') - to_char(s0.SNAP_TIME+1/24,'HH24')) * 60
                + to_char(s1.SNAP_TIME+1/24,'MI') - to_char(s0.SNAP_TIME+1/24,'MI')
                + to_char(s1.SNAP_TIME+1/24,'SS') - to_char(s0.SNAP_TIME+1/24,'SS') / 60,2)*60)*s3t1.value cap,
  (s5t1.value - s5t0.value) / 1000000 as dbt,
  ((s5t1.value - s5t0.value) / 1000000)/60 / round((to_char(s1.SNAP_TIME+1/24,'DD') - to_char(s0.SNAP_TIME+1/24,'DD')) * 1440
                + round(to_char(s1.SNAP_TIME+1/24,'HH24') - to_char(s0.SNAP_TIME+1/24,'HH24')) * 60
                + to_char(s1.SNAP_TIME+1/24,'MI') - to_char(s0.SNAP_TIME+1/24,'MI')
                + to_char(s1.SNAP_TIME+1/24,'SS') - to_char(s0.SNAP_TIME+1/24,'SS') / 60,2) aas
FROM stats$snapshot s0,
  stats$snapshot s1,
  stats$osstat s3t1,         -- osstat just get the end value
  stats$sys_time_model s5t0,
  stats$sys_time_model s5t1
WHERE s0.dbid            = &_dbid    -- CHANGE THE DBID HERE!
AND s1.dbid              = s0.dbid
AND s3t1.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 s3t1.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 s3t1.snap_id         = s0.snap_id + 1
AND s5t0.snap_id         = s0.snap_id
AND s5t1.snap_id         = s0.snap_id + 1
AND s3t1.OSSTAT_ID         = 0
AND s5t0.stat_id         = 3649082374
AND s5t1.stat_id       = s5t0.stat_id
AND s0.SNAP_TIME > sysdate - &DaysBefore
)
--WHERE
--TO_CHAR(s0.SNAP_TIME,'D') >= 1
-- id  in (select snap_id from (select * from r2toolkit.r2_regression_data union all select * from r2toolkit.r2_outlier_data))
-- id in (538)
-- aas > 1
-- oracpupct > 50
-- oscpupct > 50
-- AND TO_CHAR(s0.SNAP_TIME,'D') >= 1     -- Day of week: 1=Sunday 7=Saturday
-- AND TO_CHAR(s0.SNAP_TIME,'D') <= 7
-- AND TO_CHAR(s0.SNAP_TIME,'HH24MI') >= 0900     -- Hour
-- AND TO_CHAR(s0.SNAP_TIME,'HH24MI') <= 1800
-- AND s0.SNAP_TIME >= TO_DATE('2010-jan-17 00:00:00','yyyy-mon-dd hh24:mi:ss')     -- Data range
-- AND s0.SNAP_TIME <= TO_DATE('2010-aug-22 23:59:59','yyyy-mon-dd hh24:mi:ss')
ORDER BY tm ASC;