Oracle: DBA_HIST_SQLSTAT

Stats in a given period

select
   stat.sql_id                                                                            sql_id,
   replace (cast (dbms_lob.substr (text.sql_text, 100) as varchar (100)), chr (10), '')   sql_text,
   --
   executions                                                                             executions,
   --
   round(elapsed_time                                / 1000000  , 3)                      seconds_elapsed,
   round(cpu_time                                    / 1000000  , 3)                      seconds_cpu_elapsed,
   --
   round(elapsed_time / executions                   / 1000000  , 3)                      seconds_elapsed_per_exec,
   round(cpu_time     / executions                   / 1000000  , 3)                      seconds_cpu_elapsed_per_exec,
   round(iowait_time  / executions                   / 1000000  , 3)                      seconds_iowait_ela_per_exec,
   --
   disk_reads                                                                             disk_reads,
   buffer_gets                                                                            buffer_gets,
   writes                                                                                 writes,
   parses                                                                                 parses,
   sorts                                                                                  sorts
from
    ( select  --- {
               stat.sql_id                     sql_id,
          sum (stat.executions_delta    )      executions,
          sum (stat.elapsed_time_delta  )      elapsed_time,
          sum (stat.cpu_time_delta      )      cpu_time,
          sum (stat.iowait_delta        )      iowait_time,
          sum (stat.disk_reads_delta    )      disk_reads,
          sum (stat.buffer_gets_delta   )      buffer_gets,
          sum (stat.direct_writes_delta )      writes,
          sum (stat.parse_calls_delta   )      parses,
          sum (stat.sorts_delta         )      sorts
      from
          dba_hist_sqlstat   stat where snap_id in (
                                        ------------- See script find_snap_ids.sql
                                           select snap_id from dba_hist_ash_snapshot
                                           where
                                             end_interval_time   > sysdate - 1 and    -- first snap
                                             begin_interval_time < sysdate            -- last snap
                                        -------------
                                  )
      group by
          stat.sql_id
    )                       stat  --- }
    join dba_hist_sqltext   text on stat.sql_id = text.sql_id
where
    executions > 0
order by
    seconds_elapsed desc;

Find statements which high CPU time, physical read/written bytes, execution numbers etc.

with bas as (
   select
      to_char(snp.begin_interval_time, 'yyyy-mm-dd hh24:mi:ss') snap_begin,
      sta.sql_id,
      count(*) cnt,
      to_char(sum(sta.cpu_time_delta / 1e6), '999,990.0') cpu_tm_d_s,
      to_char(sum(sta.cpu_time_total / 1e6), '999,990.0') cpu_tm_t_s,
      sum(sta.executions_delta          ) exec_d,
      sum(sta.executions_total          ) exec_t,
      sum(sta.physical_read_bytes_delta ) phys_rb_d,
      sum(sta.physical_read_bytes_total ) phys_rb_t,
      sum(sta.physical_write_bytes_delta) phys_wb_d,
      sum(sta.physical_write_bytes_total) phys_wb_t,
      row_number() over (partition by snp.begin_interval_time order by
         sum(sta.cpu_time_total            )
      -- sum(sta.executions_total          )
      -- sum(sta.physical_write_bytes_delta)
      -- sum(sta.physical_read_bytes_delta )
      desc) rn
   from
      sys.dba_hist_sqlstat  sta                                                                 left join
      sys.dba_hist_snapshot snp on sta.snap_id         = snp.snap_id and sta.dbid = snp.dbid -- left join
   -- sys.dba_hist_sqltext  txt on sta.sql_id          = txt.sql_id  and sta.dbid = txt.dbid    left join
   -- sys.dba_users         usr on sta.parsing_user_id = usr.user_id
   where
      snp.begin_interval_time > sysdate - 1
   -- and sql_id not in ('‥', '‥')
   group by
      snp.begin_interval_time,
      sta.sql_id
)
select
   bas.snap_begin,
   bas.sql_id,
   bas.cnt,
   bas.cpu_tm_d_s,
   bas.cpu_tm_t_s,
   bas.exec_d,
   bas.exec_t,
   bas.phys_rb_d,
   bas.phys_rb_t,
   bas.phys_wb_d,
   bas.phys_wb_t,
   txt.sql_text
from
   bas                                                     left join
   sys.dba_hist_sqltext  txt on bas.sql_id  = txt.sql_id
where
   bas.rn = 1
order by
   snap_begin desc;

Stats of a given SQL ID

select
   snap.snap_id,
   to_char(snap.begin_interval_time, 'yyyy-mm-dd hh24:mi:ss')             snap_begin,
   stat.executions_delta,
   round(stat.elapsed_time_delta/1e6) ela_time_secs,
   round(stat.elapsed_time_delta/nullif(stat.executions_delta, 0)/1e6,2)  secs_per_stmt,
   round(stat.cpu_time_delta/1e6)                                         cpu_secs,
   round(stat.iowait_delta/1e6)                                           iowait_secs,
   stat.disk_reads_delta,
   stat.buffer_gets_delta,
   stat.parse_calls_delta,
   stat.sorts_delta
from
   dba_hist_sqlstat  stat join
   dba_hist_snapshot snap on stat.snap_id = snap.snap_id
where
   stat.dbid   = (select con_dbid from v$database) and
   stat.sql_id = '&sql_id'
order by
   snap.begin_interval_time desc;

See also