--
--   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)) -- You may want to add 47 here 
                                          -- (PL/SQL procedure or block)
--
--   A bit differnt with dba_hist_sqltext where the statement
--   text (SQL_TEXT) is provided as a CLOB. No need to juggle
--   with PIECE, which also leaves the ORDER BY
--
select t.sql_text,
       case piece
         when 0 then elapsed_time
         else null
       end elapsed_time, 
       case piece
         when 0 then cpu_time
         else null
       end cpu_time, 
       case piece
         when 0 then executions
         else null
       end executions, 
       case piece
         when 0 then rows_processed
         else null
       end rows_processed,
       case piece
         when 0 then buffer_gets
         else null
       end buffer_gets -- Add disk_reads if you want ...
from (select sql_id, elapsed_time, cpu_time,
             executions, rows_processed,
             buffer_gets, disk_reads
      from (select sql_id,
                   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,
                   sum(disk_reads_delta) disk_reads
            from sqlstat
            group by sql_id
            order by 2 desc)
      where rownum <= 20) s     -- Put any value you want here.
                                -- 20 is usually good.
     inner join perfstat.stats$sqltext t -- or dba_hist_sqltext
        on t.sql_id = s.sql_id
order by s.elapsed_time desc, t.sql_id, t.piece;