DESCRIPTION
This custom report will list the Top N SQL statements in your instance for a specified database user. 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 database username
accept DBUser
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, conu_&DBID u, 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.xcuw = u.id
and sw.izho = n.hash 
and upper(u.name) = upper('&DBUser')
group by nvl(n.name, sw.izho)
order by sum(sw.qp/100) desc)
where rownum <= &HowMany
order by timesecs desc
/