DESCRIPTION
This custom report will list statistics for one specific SQL statement by day. 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
select to_char(min(d), 'mm/dd/yy hh24:mi') min_date,
to_char(max(d), 'mm/dd/yy hh24:mi') max_date
from conss_&DBID
/
prompt Enter Start Date (mm/dd/yy)
accept BeginDate
prompt Enter End Date (mm/dd/yy)
accept EndDate
Prompt Query hash value:
accept HashValue
set pagesize 999
set linesize 132
select to_char(trunc(d,'DD'),'mm/dd/yy') "Day",
sum(dreads) "Disk_Reads",
sum(bgets) "Buffer_Gets",
sum(execs) "Executions",
sum(row_count) "Rows_Processed",
sum(sorts) "Sorts",
sum(parses) "Parses"
from conss_&DBID
where h=&HashValue
and d between to_date('&BeginDate'||' 00:00','mm/dd/yy hh24:mi') and to_date('&EndDate'||' 23:59','mm/dd/yy hh24:mi')
group by to_char(trunc(d,'DD'),'mm/dd/yy')
order by 1
/
