forked from abdulirfan3/Oracle_SQL_Scripts
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathash_report_html.sql
More file actions
117 lines (96 loc) · 4.47 KB
/
ash_report_html.sql
File metadata and controls
117 lines (96 loc) · 4.47 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
@@header
/*
*
* Author : Vishal Gupta
* Purpose : Generate ASH report for instances passed
* Parameters : 1 - Instance Number ( % for all or comma separated list of instance numbers).
* 2 - From Time ( YYYY-MM-DD HH24:MI:SS format)
* 3 - To Time ( YYYY-MM-DD HH24:MI:SS format)
*
*
* Revision History:
* ===================
* Date Author Description
* --------- ------------ -----------------------------------------
* 18-Feb-12 Vishal Gupta Created
* 04-Oct-12 Vishal Gupta Added default input parameter values
*/
/************************************
* INPUT PARAMETERS
************************************/
UNDEFINE INSTANCE_LIST
UNDEFINE FROM_TIMESTAMP
UNDEFINE TO_TIMESTAMP
PROMPT
PROMPT ***********************************************************************
PROMPT * A S H R E P O R T (HTML)
PROMPT *
PROMPT * I F L E F T B L A N K T H E N G E T L A S T 15 M I N
PROMPT *
PROMPT * Input Parameters
PROMPT * - Instance ID = ( % for ALL or comma separated list of instance numbers)
PROMPT * - From Timestamp = ( YYYY-MM-DD HH24:MI:SS format)
PROMPT * - To Timestamp = ( YYYY-MM-DD HH24:MI:SS format)
PROMPT ***********************************************************************
DEFINE INSTANCE_LIST="&&1"
DEFINE FROM_TIMESTAMP="&&2"
DEFINE TO_TIMESTAMP="&&3"
COLUMN _INSTANCE_LIST NEW_VALUE INSTANCE_LIST NOPRINT
COLUMN _FROM_TIME NEW_VALUE FROM_TIMESTAMP NOPRINT
COLUMN _TO_TIME NEW_VALUE TO_TIMESTAMP NOPRINT
set term off
SELECT DECODE('&&INSTANCE_LIST','','%','&&INSTANCE_LIST') "_INSTANCE_LIST"
, DECODE('&&FROM_TIMESTAMP','',to_char(sysdate - ('15'/(24*60)),'YYYY-MM-DD HH24:MI:SS'),'&&FROM_TIMESTAMP') "_FROM_TIME"
, DECODE('&&TO_TIMESTAMP','',to_char(sysdate ,'YYYY-MM-DD HH24:MI:SS'),'&&TO_TIMESTAMP') "_TO_TIME"
FROM DUAL;
set term on
PROMPT
PROMPT ***********************************************************************
PROMPT * A S H R E P O R T (HTML)
PROMPT *
PROMPT * Input Parameters
PROMPT * - Instance List = '&&INSTANCE_LIST' ( % for ALL or comma separated list of instance numbers)
PROMPT * - From Timestamp = '&&FROM_TIMESTAMP' ( YYYY-MM-DD HH24:MI:SS format)
PROMPT * - To Timestamp = '&&TO_TIMESTAMP' ( YYYY-MM-DD HH24:MI:SS format)
PROMPT ***********************************************************************
set pages 0
/*
DBMS_WORKLOAD_REPOSITORY.ASH_GLOBAL_REPORT_TEXT(
l_dbid IN VARCHAR2(1023),
l_inst_num IN NUMBER,
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;
*/
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 => NULL
, l_sql_id => NULL
, l_wait_class => NULL
, l_service_hash => NULL
, l_module => NULL
, l_action => NULL
, l_client_id => NULL
)
);
spool off
prompt
PROMPT report generated as ash_report.html file.
set pages 5000
host ash_report.html
@@footer