Custom Report - Top SQL for a Machine

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 emoticons_plus.png   -- Note the emoticon join as it may not copy/paste. You may need to add the parentheses and + sign manually.

  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

/