DESCRIPTION
This custom report will list the Top N SQL statements across your entire monitored environment. To use this report, run it from SQL*Plus while connected to the Ignite repository database.
SQL*PLUS SCRIPT
prompt Enter starting time (mm/dd/yy hh24:mi)
accept StartDate
prompt Enter ending time (mm/dd/yy hh24:mi)
accept EndDate
prompt How many SQLs should be shown
accept HowMany
CREATE GLOBAL TEMPORARY TABLE TopSQLAllDB(
dbname VARCHAR2(50),
sql_hash_or_name VARCHAR2(100),
timesecs NUMBER)
/
DELETE TopSQLAllDB
/
DECLARE
sSQL VARCHAR2(32767);
BEGIN
FOR r IN (SELECT id FROM cond) LOOP
sSQL := 'INSERT INTO TopSQLAllDB select * from ( ' ||
' select d.name, nvl(n.name, ss.sqlhash) sql_hash_or_name, sum(ss.timesecs) timesecs ' ||
' from con_sql_sum_'||r.id||' ss, con_sql_name n, cond d ' ||
' where ss.datehour between TO_DATE(:1,''mm/dd/yy hh24:mi'') and TO_DATE(:2,''mm/dd/yy hh24:mi'') ' ||
' and ss.sqlhash = n.hash 
' ||
' and d.id = 
' ||
' group by d.name, nvl(n.name, ss.sqlhash) ' ||
' order by 3 desc) ' ||
'where rownum <= :4 ';
EXECUTE IMMEDIATE sSQL USING '&StartDate', '&EndDate', r.id, &HowMany;
END LOOP;
END;
/
set pagesize 999
column dbname format a20
column sql_hash_or_name format a20
select * from (
select dbname, sql_hash_or_name, timesecs
from TopSQLAllDB
order by timesecs desc)
where rownum <= &HowMany
/
