Description
This custom report will list the Top N SQL statements in your instance for a specified machine name. 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 Start Date (mm/dd/yy hh24:mi) accept BeginDate prompt Enter End Date (mm/dd/yy hh24:mi) accept EndDate prompt How many SQLs should be displayed accept HowMany prompt Enter the Machine name accept Machine set pagesize 999 set linesize 132 column hash_or_name format a22 select hash_or_name, timesecs from ( select nvl(n.name, sw.izho) hash_or_name, sum(sw.qp/100) timesecs FROM consw_&DBID sw, conm_&DBID m, con_sql_name n where sw.d between to_date('&BeginDate','mm/dd/yy hh24:mi') and to_date('&EndDate','mm/dd/yy hh24:mi') and sw.pwmy = m.id and sw.izho = n.hash (+) and upper(m.name) = upper('&Machine') group by nvl(n.name, sw.izho) order by sum(sw.qp/100) desc) where rownum <= &HowMany order by timesecs desc