Find historical SQL by date

Oracle's AWR and/or STATSPACK provides a historical repository for high-impact SQL statements in the dba_hist_sqlstat and stats$sqlstat tables, and you can query these tables to locate historical SQL associated with a Oracle performance problem.

 AWR uses a "Top N" method which defaults to collect the Top-30 SQL statements for each SQL category (statistics_level=typical).  If you set statistics_level = all, AWR will collect the top 100 SQL statements.

Also see my notes on

In STATSPACK, historical SQL by date is collected via threshold settings.

See these important notes on viewing SQL execution plans over time and analyzing historical SQL with AWR.

This article contains a great SQL statement to locate all SQL associated with a specific date.  This script can easily be modified to accept a date. It lists by sql_id the number of executions of each SQL statement before and after the specified date.

Once you have the sql_id, it is a simple matter to access dba_hist_sqltext by sql_id to get the full text. Getting this same information from STATSPACK is possible using the stats$sql_summary table:

Here is a handy SQL query that will display a summary of SQL execution history:

select * from
(
   select
      sql_id,
      sql_plan_hash_value,
      event,sql_exec_id,
      sql_exec_start,current_obj#,
      sql_plan_line_id,
      sql_plan_operation,
      sql_plan_options,
      SUM (delta_read_io_requests) lio_read ,
      SUM (delta_read_io_bytes) pio_read ,
      count(*) count_1
   from
      dba_hist_active_sess_history
   where
      sql_id='xxxx'
   and 
      snap_id >= :strt_snap
   and
      snap_id <= :end_snap
   group by
      sql_id,
      sql_plan_hash_value,
      event,sql_exec_id,
      sql_exec_start,
      current_obj#,
      sql_plan_line_id,
      sql_plan_operation,
      sql_plan_options
  )
  order by count_1 desc;

This script accepts three parameters, the SQL ID, the start and end snap numbers, and displays details about the historical SQL execution.

select
s.sql_id,
sum(case
when begin_interval_time = to_date('14-feb-2009 1100','dd-mon-yyyy hh24mi') then s.executions_total
else 0
end) sum_after,
(sum(case
when begin_interval_time >= to_date('14-feb-2009 1100','dd-mon-yyyy hh24mi') then s.executions_total
else 0
end) -
sum(case
when begin_interval_time < to_date('14-feb-2009 1100','dd-mon-yyyy hh24mi') then s.executions_total
else 0
end)) difference
from
   dba_hist_sqlstat s,
   dba_hist_snapshot sn
where
   sn.begin_interval_time between to_date('05-nov-2008 0001','dd-mon-yyyy hh24mi')
and
   to_date('05-nov-2008 2359','dd-mon-yyyy hh24mi')
and
   sn.snap_id=s.snap_id
group by
   s.sql_id
order by
   difference desc;

For full scripts to find historical SQL from inside Oracle, see the Oracle script collection, available for immediate download.

---