SELECT when, sql, SUM(sx) executions, sum (sd) rows_processed
FROM (
SELECT to_char(begin_interval_time,’YYYY_MM_DD HH24′) when,
dbms_lob.substr(sql_text,4000,1) sql,
dhss.instance_number inst_id,
dhss.sql_id,
sum(executions_delta) exec_delta,
sum(rows_processed_delta) rows_proc_delta
FROM dba_hist_sqlstat dhss,
dba_hist_snapshot dhs,
dba_hist_sqltext dhst
WHERE upper(dhst.sql_text) LIKE ‘%Z_PLACENO%’
AND ltrim(upper(dhst.sql_text)) NOT LIKE ‘SELECT%’
AND dhss.snap_id=dhs.snap_id
AND dhss.instance_Number=dhs.instance_number
AND dhss.sql_id = dhst.sql_id
AND begin_interval_time BETWEEN to_date(’12-02-07 12:00′,’YY-MM-DD HH24:MI’)
AND to_date(’12-02-07 16:00′,’YY-MM-DD HH24:MI’)
GROUP BY to_char(begin_interval_time,’YYYY_MM_DD HH24′),
dbms_lob.substr(sql_text,4000,1),
dhss.instance_number,
dhss.sql_id
)
group by when, sql;
