106 lines
3.5 KiB
SQL
106 lines
3.5 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Generate AWR Report for RAC
|
|
* Parameters : 1 - From Time ( YYYY-MM-DD HH24:MI:SS format) (Default = sysdate-1)
|
|
* 2 - To Time ( YYYY-MM-DD HH24:MI:SS format) (Default = sysdate)
|
|
* 3 - Instance ID List separated by comma( default value - All instances)
|
|
* 4 - Report Type ( text/html, default value - html)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 17-Feb-14 Vishal Gupta Changed order of input parameters
|
|
* 18-Oct-12 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE FROM_TIME
|
|
UNDEFINE TO_TIME
|
|
UNDEFINE REPORT_TYPE
|
|
UNDEFINE INST_ID_LIST
|
|
UNDEFINE begin_snap_id
|
|
UNDEFINE end_snap_id
|
|
--UNDEFINE DBID
|
|
|
|
DEFINE FROM_TIME="&&1"
|
|
DEFINE TO_TIME="&&2"
|
|
DEFINE INST_ID_LIST="&&3"
|
|
DEFINE REPORT_TYPE="&&4"
|
|
DEFINE begin_snap_id=""
|
|
DEFINE end_snap_id=""
|
|
--DEFINE DBID=""
|
|
|
|
COLUMN _FROM_TIME NEW_VALUE FROM_TIME NOPRINT
|
|
COLUMN _TO_TIME NEW_VALUE TO_TIME NOPRINT
|
|
COLUMN _INST_ID_LIST NEW_VALUE INST_ID_LIST NOPRINT
|
|
COLUMN _REPORT_TYPE NEW_VALUE REPORT_TYPE NOPRINT
|
|
|
|
set term off
|
|
SELECT DECODE('&&FROM_TIME','',to_char(sysdate - 1 - (1/1440),'YYYY-MM-DD HH24:MI:SS'),'&&FROM_TIME') "_FROM_TIME"
|
|
, DECODE('&&TO_TIME','',to_char(sysdate + (1/1440),'YYYY-MM-DD HH24:MI:SS'),'&&TO_TIME') "_TO_TIME"
|
|
, DECODE('&&INST_ID_LIST','',NULL,'&&INST_ID_LIST') "_INST_ID_LIST"
|
|
, DECODE('&&REPORT_TYPE','','html','&&REPORT_TYPE') "_REPORT_TYPE"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
PROMPT
|
|
PROMPT ***********************************************************************
|
|
PROMPT * A W R R E P O R T ( R A C )
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - From Timestamp = '&&FROM_TIME' ( YYYY-MM-DD HH24:MI:SS format)
|
|
PROMPT * - To Timestamp = '&&TO_TIME' ( YYYY-MM-DD HH24:MI:SS format)
|
|
PROMPT * - INST_ID_LIST = '&&INST_ID_LIST'
|
|
PROMPT * - Report Type = '&&REPORT_TYPE'
|
|
PROMPT ***********************************************************************
|
|
|
|
|
|
COLUMN _begin_snap_id NEW_VALUE begin_snap_id NOPRINT
|
|
COLUMN _end_snap_id NEW_VALUE end_snap_id NOPRINT
|
|
COLUMN _dbid NEW_VALUE dbid NOPRINT
|
|
|
|
set term off
|
|
SELECT min(s.snap_id) "_begin_snap_id"
|
|
, max(s.snap_id) "_end_snap_id"
|
|
, d.dbid "_dbid"
|
|
FROM dba_hist_snapshot s, v$database d
|
|
WHERE d.dbid = s.dbid
|
|
AND s.end_interval_time BETWEEN TO_TIMESTAMP('&&FROM_TIME','YYYY-MM-DD HH24:MI:SS')
|
|
AND TO_TIMESTAMP('&&to_time','YYYY-MM-DD HH24:MI:SS')
|
|
GROUP BY d.dbid
|
|
;
|
|
set term on
|
|
|
|
|
|
set pages 0
|
|
set lines 1500
|
|
PROMPT spooling to file &&_CONNECT_IDENTIFIER._awr_report.html
|
|
set term off
|
|
spool &&_CONNECT_IDENTIFIER._awr_report.html
|
|
SELECT * from table(dbms_workload_repository.awr_global_report_html
|
|
( l_dbid => &&DBID
|
|
, l_inst_num => '&&INST_ID_LIST'
|
|
, l_bid => &&begin_snap_id
|
|
, l_eid => &&end_snap_id
|
|
, l_options => 1 -- Currently not used used (as of v11.2.0.3)
|
|
)
|
|
);
|
|
spool off
|
|
set term on
|
|
PROMPT report generated as &&_CONNECT_IDENTIFIER._awr_report.html file.
|
|
host &&_CONNECT_IDENTIFIER._awr_report.html
|
|
|
|
set pages 5000
|
|
|
|
@@footer
|