DESCRIPTION
This custom report will list the Top new SQL statements based on a reference date, e.g. if the reference date of 1/5/14 were used, the Top N SQLs that never executed prior to 1/5/14 would be shown. To use this report, run it from SQL*Plus while connected to the Ignite repository database.
SQL*PLUS SCRIPT
select id, name from cond order by name;
prompt Which Database ID
accept DBID
prompt Enter Reference Date (mm/dd/yy hh24:mi)
accept RefDate
prompt How many SQLs to show
accept HowMany
Rem
Rem
set pagesize 999
set linesize 132
select sqlhash, wait_time_secs from (
select sqlhash, sum(timesecs) wait_time_secs
from con_sql_sum_&DBID ss1
where datehour >= to_date('&RefDate','mm/dd/yy hh24:mi')
and sqlhash not in (
select sqlhash
from con_sql_sum_&DBID ss2
where datehour < to_date('&RefDate','mm/dd/yy hh24:mi'))
group by sqlhash
order by 2 desc)
where rownum < &HowMany
/
