137 lines
5.6 KiB
SQL
137 lines
5.6 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Generate ASH report for instances passed
|
|
* Parameters : 1 - From Time (Optional) ( YYYY-MM-DD HH24:MI:SS format) - Default is (sysdate - 1/24)
|
|
* 2 - To Time (Optional) ( YYYY-MM-DD HH24:MI:SS format) - Default is sysdate
|
|
* 2 - INST_ID (Optional) ( % for all or comma separated list of instance numbers).
|
|
* 4 - ServiceName (Optional) Default is NULL
|
|
* 5 - SID (Optional) Default is NULL
|
|
* 6 - SQL_ID (Optional) Default is NULL
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 17-Feb-14 Vishal Gupta Changed order of input parameters
|
|
* 28-Jan-15 Vishal Gupta Added SID, SQL_ID, ServiceName input parameters
|
|
* 04-Oct-12 Vishal Gupta Added default input parameter values
|
|
* 18-Feb-12 Vishal Gupta Created
|
|
*/
|
|
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE FROM_TIMESTAMP
|
|
UNDEFINE TO_TIMESTAMP
|
|
UNDEFINE INSTANCE_LIST
|
|
UNDEFINE SERVICE_NAME
|
|
UNDEFINE SID
|
|
UNDEFINE SQL_ID
|
|
|
|
DEFINE FROM_TIMESTAMP="&&1"
|
|
DEFINE TO_TIMESTAMP="&&2"
|
|
DEFINE INSTANCE_LIST="&&3"
|
|
DEFINE SERVICE_NAME="&&4"
|
|
DEFINE SID="&&5"
|
|
DEFINE SQL_ID="&&6"
|
|
|
|
|
|
|
|
COLUMN _FROM_TIMESTAMP NEW_VALUE FROM_TIMESTAMP NOPRINT
|
|
COLUMN _TO_TIMESTAMP NEW_VALUE TO_TIMESTAMP NOPRINT
|
|
COLUMN _INSTANCE_LIST NEW_VALUE INSTANCE_LIST NOPRINT
|
|
COLUMN _SID NEW_VALUE SID NOPRINT
|
|
COLUMN _SQL_ID NEW_VALUE SQL_ID NOPRINT
|
|
COLUMN _SERVICE_NAME NEW_VALUE SERVICE_NAME NOPRINT
|
|
COLUMN _SERVICE_HASH NEW_VALUE SERVICE_HASH NOPRINT
|
|
|
|
set term off
|
|
SELECT DECODE('&&FROM_TIMESTAMP','',to_char(sysdate - 1/24,'YYYY-MM-DD HH24:MI:SS'),'&&FROM_TIMESTAMP') "_FROM_TIMESTAMP"
|
|
, DECODE('&&TO_TIMESTAMP','',to_char(sysdate ,'YYYY-MM-DD HH24:MI:SS'),'&&TO_TIMESTAMP') "_TO_TIMESTAMP"
|
|
, DECODE('&&INSTANCE_LIST','','%','&&INSTANCE_LIST') "_INSTANCE_LIST"
|
|
, DECODE('&&SERVICE_NAME','','%','&&SERVICE_NAME') "_SERVICE_NAME"
|
|
, DECODE('&&SERVICE_HASH','','%','&&SERVICE_HASH') "_SERVICE_HASH"
|
|
, DECODE('&&SID','','%','&&SID') "_SID"
|
|
, DECODE('&&SQL_ID','','%','&&SQL_ID') "_SQL_ID"
|
|
FROM DUAL;
|
|
|
|
set term on
|
|
|
|
set term off
|
|
SELECT s.name_hash "_SERVICE_HASH"
|
|
FROM dba_services s
|
|
WHERE s.name = '&&SERVICE_NAME';
|
|
|
|
set term on
|
|
|
|
|
|
PROMPT
|
|
PROMPT ***********************************************************************
|
|
PROMPT * A S H R E P O R T (HTML)
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - From Timestamp = '&&FROM_TIMESTAMP' ( YYYY-MM-DD HH24:MI:SS format) - Default is (sysdate - 1/24)
|
|
PROMPT * - To Timestamp = '&&TO_TIMESTAMP' ( YYYY-MM-DD HH24:MI:SS format) - Default is (sysdate)
|
|
PROMPT * - Instance List = '&&INSTANCE_LIST' ( % for ALL or comma separated list of instance numbers) - Default is %
|
|
PROMPT * - SERVICE_NAME = '&&SERVICE_NAME' - Default is %
|
|
PROMPT * - SID = '&&SID' - Default is %
|
|
PROMPT * - SQL_ID = '&&SQL_ID' - Default is %
|
|
PROMPT ***********************************************************************
|
|
|
|
set pages 0
|
|
|
|
/*
|
|
|
|
DBMS_WORKLOAD_REPOSITORY.ASH_GLOBAL_REPORT_TEXT(
|
|
l_dbid IN VARCHAR2(1023),
|
|
l_inst_num IN VARCHAR2,
|
|
l_btime IN DATE,
|
|
l_etime IN DATE,
|
|
l_options IN NUMBER DEFAULT 0, -- Not currently used by Oracle
|
|
l_slot_width IN NUMBER DEFAULT 0, -- Not currently used by Oracle
|
|
l_sid IN NUMBER DEFAULT NULL, -- v$session.sid
|
|
l_sql_id IN VARCHAR2 DEFAULT NULL, -- V$SQL.SQL_ID (Wildcard allowed)
|
|
l_wait_class IN VARCHAR2 DEFAULT NULL, -- v$event_name.wait_class (Wildcard allowed)
|
|
l_service_hash IN NUMBER DEFAULT NULL, -- v$active_services.name_hash
|
|
l_module IN VARCHAR2 DEFAULT NULL, -- v$session.module (Wildcard allowed)
|
|
l_action IN VARCHAR2 DEFAULT NULL, -- v$session.action (Wildcard allowed)
|
|
l_client_id IN VARCHAR2 DEFAULT NULL, -- v$session.client_identifier (Wildcard allowed)
|
|
l_plsql_entry IN VARCHAR2 DEFAULT NULL,
|
|
l_data_src IN NUMBER DEFAULT 0)
|
|
RETURN awrrpt_text_type_table PIPELINED;
|
|
|
|
*/
|
|
|
|
set term off
|
|
spool ash_report.html
|
|
SELECT * from table(dbms_workload_repository.ash_global_report_html
|
|
( l_dbid => (select dbid from v$database)
|
|
, l_inst_num => DECODE(upper('&INSTANCE_LIST'),'%',NULL,'&INSTANCE_LIST')
|
|
, l_btime => TO_DATE('&&FROM_TIMESTAMP','YYYY-MM-DD HH24:MI:SS')
|
|
, l_etime => TO_DATE('&&TO_TIMESTAMP','YYYY-MM-DD HH24:MI:SS')
|
|
, l_sid => DECODE('&&SID','%',NULL,'&&SID')
|
|
, l_sql_id => DECODE('&&SQL_ID','%',NULL,'&&SQL_ID')
|
|
, l_wait_class => NULL
|
|
, l_service_hash => DECODE('&&SERVICE_HASH','%',NULL,'&&SERVICE_HASH')
|
|
, l_module => NULL
|
|
, l_action => NULL
|
|
, l_client_id => NULL
|
|
)
|
|
);
|
|
|
|
spool off
|
|
set term on
|
|
PROMPT
|
|
PROMPT Report generated as ash_report.html file. It will automatically open in your default browser.
|
|
|
|
set pages 5000
|
|
host ash_report.html
|
|
|
|
@@footer
|