--
--   Usage: @script_name <start time> <end time>
--
--          where times are provided as YYYYMMDDHH24MI
--          or padded with 0s if need be (197000101 
--          is understood as Jan 1st 1970 at 0:00) 
--
--   Written by Stephane Faroult, RoughSea Ltd
--
--   Released under Creative Commons "By"
--
with workground as
  (select snap_id,
          dbid,
          instance_number,
          startup_time, 
          snap_time -- begin_interval_time in dba_hist_snapshot
   from perfstat.stats$snapshot
        -- or dba_hist_snapshot if you are licensed to use it
   where snap_time <= to_date(rpad('&2', 12, '0'), 'YYYYMMDDHH24MI')
         -- end_interval_time in dba_hist_snapshot
     and snap_time >= to_date(rpad('&1', 12, '0'), 'YYYYMMDDHH24MI')
         -- begin_interval_time in dba_hist_snapshot
     --
     --  The two following conditions assume that we are running the
     --  analysis on the database and instance where they were collected.
     --  Adjust if needed.
     --
     and instance_number = sys_context('USERENV', 'INSTANCE')
     and dbid = (select dbid from v$database)),
     sqlstat as -- If you are licensed for using it, you can
                -- remove this, and replace sqlstat by dba_hist_sqlstat
                -- in the query - the names given here are the
                -- names of the columns in dba_hist_sqlstat, not by
                -- chance ...
   (select s.sql_id,
           s.command_type,
           round((s.elapsed_time
               - nvl(lag(s.elapsed_time, 1)
                      over (partition by w.startup_time,
                                         s.sql_id
                            order by w.snap_time),
                     0))/10000) elapsed_time_delta,  -- converted to 1/100th of second
           round((s.cpu_time
              - nvl(lag(s.cpu_time, 1)
                    over (partition by w.startup_time,
                                       s.sql_id
                          order by w.snap_time),
                    0))/10000) cpu_time_delta,  -- converted to 1/100th of second
           s.buffer_gets
              - nvl(lag(s.buffer_gets, 1)
                    over (partition by w.startup_time,
                                       s.sql_id
                          order by w.snap_time),
                    0) buffer_gets_delta,
           s.executions
              - nvl(lag(s.executions, 1)
                    over (partition by w.startup_time,
                                       s.sql_id
                          order by w.snap_time),
                    0) executions_delta,
           s.rows_processed
              - nvl(lag(s.rows_processed, 1)
                    over (partition by w.startup_time,
                                       s.sql_id
                          order by w.snap_time),
                    0) rows_processed_delta,
           s.disk_reads
              - nvl(lag(s.disk_reads, 1)
                    over (partition by w.startup_time,
                                       s.sql_id
                          order by w.snap_time),
                    0) disk_reads_delta
    from workground w
         inner join perfstat.stats$sql_summary s
           on s.snap_id = w.snap_id
          and s.dbid = w.dbid
          and s.instance_number = w.instance_number
    where s.command_type in (2, 3, 6, 7))
select case command_type
         when 2 then 'Insert'
         when 3 then 'Select'
         when 6 then 'Update'
         when 7 then 'Delete'
       end stmt,
       count(distinct sql_id) statements,
       sum(elapsed_time_delta) elapsed_time,
       sum(cpu_time_delta) cpu_time,
       sum(executions_delta) executions,
       sum(rows_processed_delta) rows_processed,
       sum(buffer_gets_delta) buffer_gets
from sqlstat
group by command_type
;