How to create multiple SQL Monitor Reports from History

Sometimes I sat in front of the screen until late into the night and watched a batch job go by. At regular intervals, I kept storing SQL Monitor reports for later analysis.
With Oracle 12 the historical SQL Monitor became available.
I have written a small script that can generate sql monitor reports from history.
IMHO it’s the most useful script I’ve ever written.
It has no parameters, I prefer to edit my scripts.
The script has of course still room for improvement.
Maybe someone would like to publish a better version?

And, by the way, it’s written for Sql * Plus …


set newpage 0
set space 0
SET LONG 10000000 LONGCHUNKSIZE 10000000 pages 0 lines 500
set echo off
set feedback off
set verify off
set heading off
set trimspool on
set sqlp ''
spool temp.sql
WITH reps as (
	SELECT REPORT_ID,
	  KEY1 SQL_ID,
	  KEY2 SQL_EXEC_ID,
	  KEY3 SQL_EXEC_START,
	  T.PERIOD_START_TIME,
	  T.PERIOD_END_TIME,
	  TRUNC(86400*(T.PERIOD_END_TIME-T.PERIOD_START_TIME)) DURATION,
	  DECODE(TO_NUMBER(EXTRACTVALUE(XMLTYPE(REPORT_SUMMARY), '/report_repository_summary/sql/plsql_entry_object_id')), 
				NULL, EXTRACTVALUE(XMLTYPE(REPORT_SUMMARY), '/report_repository_summary/sql/module'),
			   (SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_ID = TO_NUMBER(EXTRACTVALUE(XMLTYPE(REPORT_SUMMARY), '/report_repository_summary/sql/plsql_entry_object_id')))) CALLED_FROM ,
	  EXTRACTVALUE(XMLTYPE(REPORT_SUMMARY), '/report_repository_summary/sql/module') module
	FROM DBA_HIST_REPORTS t,
	  dba_hist_sqltext st
	WHERE T.PERIOD_START_TIME BETWEEN TO_DATE('01.07.2017 01:00:00','DD.MM.YYYY HH24:MI:SS') AND TO_DATE('03.07.2017 23:59:00','DD.MM.YYYY HH24:MI:SS')
	  AND 86400*(T.PERIOD_END_TIME-T.PERIOD_START_TIME) >= 1000 -- abitrary treshold, longrunning statement
	  AND ST.SQL_ID                                      = T.KEY1
	  AND COMMAND_TYPE                                  <> 47 -- no PL/SQL 
	  and EXTRACTVALUE(XMLTYPE(REPORT_SUMMARY), '/report_repository_summary/sql/module')!='DBMS_SCHEDULER'), -- No gather stats
commands as (
	SELECT REPORT_ID, 1 line, 'spool sqlmon_'||SQL_ID||'_'||CALLED_FROM||'_'||REPORT_ID||'.html'  command FROM REPS
	UNION ALL
	SELECT REPORT_ID,2, 'SELECT DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL(RID => '|| REPORT_ID||', TYPE => ''EM'')       FROM dual;' FROM REPS
	UNION ALL
	SELECT REPORT_ID,3, 'SPOOL OFF' FROM REPS
)
SELECT COMMAND
  from commands
order by REPORT_ID,  line;
spool off
@@temp
set echo on
set feedback on
set verify on
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s