DESCRIPTION
This custom report will list the Top N SQL statements that contain a specified string. You can use wildcards in the search string like % and _., e.g. a search string of 'DELETE%ORDERS' would list all SQL statement that start with the word DELETE and contains the ORDERS string after that. 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
select to_char(min(datehour),'mm/dd/yy') min_date, to_char(max(datehour),'mm/dd/yy') max_date
from con_stats_sum_&DBID
/
prompt Enter Start Date (mm/dd/yy)
accept BeginDate
prompt Enter End Date (mm/dd/yy)
accept EndDate
prompt How many SQLs should be displayed
accept HowMany
set pagesize 999
set linesize 132
column sql_stmt format a20
select sql_stmt, totalsecs, totalexecs, avgsecs
from (
select NVL(n.name,st.h) sql_stmt,
sum(timesecs) totalsecs,
sum(execs) totalexecs,
round(decode(sum(execs),0,0,sum(timesecs)/sum(execs)),4) avgsecs
from (
select distinct h
from const_&DBID
where upper(st) like upper('%&SQLText%')) st,
con_stats_sum_&DBID ss, con_sql_name n
where ss.datehour between to_date('&BeginDate','mm/dd/yy')
and to_date('&EndDate','mm/dd/yy')
and ss.sqlhash = st.h
and ss.sqlhash = n.hash 
group by NVL(n.name,st.h)
order by 2 desc) inner
where rownum < &HowMany
/
