DESCRIPTION
This custom report will list the Top N SQL statements in your repository for a list of databases provided. The database names should match the display name on the DPA (formerly Ignite) home page. 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
select name from cond order by name
/
prompt List of databases to combine ('DB1', 'DB2', 'DB3', etc)
accept DBList
prompt How many SQLs should be shown
accept HowMany
DECLARE
nCount INTEGER := 0;
BEGIN
SELECT COUNT(1) INTO nCount FROM user_tables WHERE table_name='TOPSQLALLDB';
IF nCount > 0 THEN
EXECUTE IMMEDIATE 'DROP TABLE TopSQLAllDB';
END IF;
END;
/
CREATE GLOBAL TEMPORARY TABLE TopSQLAllDB(
dbname VARCHAR2(50),
sql_hash_or_name VARCHAR2(100),
timesecs NUMBER)
/
DECLARE
sSQL VARCHAR2(32767);
BEGIN
FOR r IN (SELECT id FROM cond WHERE name in (&DBList)) 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 = :3 ' ||
' 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
set lines 130
column dbname format a40
column sql_hash_or_name format a30
select * from (
select dbname, sql_hash_or_name, timesecs
from TopSQLAllDB
order by timesecs desc)
where rownum <= &HowMany
/