Repository navigation
Expand file tree
/
Copy pathdba_hist_ash_summaries_by_operations.sql
More file actions
184 lines (174 loc) · 6.27 KB
/
Copy pathdba_hist_ash_summaries_by_operations.sql
File metadata and controls
184 lines (174 loc) · 6.27 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
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
----------------------------------------------------------------------------------------
--
-- File name: dba_hist_ash_summaries.sql
--
-- Purpose: ASH summaries by timed events then by plan operation
--
-- Author: Carlos Sierra
--
-- Version: 2013/12/19
--
-- Usage: This script presents several summaries of historical ASH data in order
-- to provide a general view of how the DB time is consumed.
--
-- Example: @dba_hist_ash_summaries.sql
--
-- Notes: Developed and tested on 11.2.0.3
--
---------------------------------------------------------------------------------------
--
--SPO dba_hist_ash_summaries.txt;
prompt **************************************************************************
prompt **************************************************************************
prompt
prompt This RUNS was tested on 11.2+ , will Take LONG TIME TO RUN
prompt on system that has more AWR data
prompt ==========================================================
prompt
prompt To continue press Enter or To quit press Ctrl-C.
prompt
prompt **************************************************************************
prompt **************************************************************************
pause
SET ECHO OFF VER OFF NEWP NONE PAGES 50 LINES 32767 TRIMS ON;
COL total_samples NEW_V total_samples FOR 999,999,999,999,999;
SELECT COUNT(*) total_samples
FROM dba_hist_active_sess_history;
PRO
PRO DBA_HIST_ACTIVE_SESS_HISTORY (rollup by session_state, wait_class and event)
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SET PAGES 50000;
COL samples FOR 999,999,999,999,999;
COL percent FOR A10;
COL state FOR A7;
COL wait_class FOR A14;
COL event FOR A70;
COL total FOR A17;
SELECT --GROUPING(session_state), GROUPING(wait_class), GROUPING(event),
COUNT(*) samples,
TO_CHAR(ROUND(COUNT(*) * 100 / &&total_samples., 1), '9,990.0')||' %' percent,
CASE
WHEN GROUPING(session_state) = 1 THEN 'Total'
WHEN GROUPING(wait_class) = 1 THEN ' Sub Total'
WHEN GROUPING(event) = 1 THEN ' Sub Sub Total'
END total,
session_state state, wait_class, event
FROM dba_hist_active_sess_history
GROUP BY
ROLLUP(session_state, wait_class, event)
HAVING COUNT(*) * 100 / &&total_samples. > 0.05
AND (session_state = 'WAITING' OR GROUPING(wait_class) = 1);
PRO
PRO DBA_HIST_ACTIVE_SESS_HISTORY (group by operation)
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SET PAGES 50;
COL operation FOR A40;
SELECT COUNT(*) samples,
TO_CHAR(ROUND(COUNT(*) * 100 / &&total_samples., 1), '9,990.0')||' %' percent,
SUBSTR(sql_plan_operation||' '||sql_plan_options, 1, 40) operation
FROM dba_hist_active_sess_history
WHERE sql_plan_operation IS NOT NULL
GROUP BY
sql_plan_operation,
sql_plan_options
HAVING COUNT(*) * 100 / &&total_samples. > 0.05
ORDER BY
sql_plan_operation,
sql_plan_options;
PRO
PRO DBA_HIST_ACTIVE_SESS_HISTORY (group by operation and session_state)
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
COL session_state FOR A13;
SELECT COUNT(*) samples,
TO_CHAR(ROUND(COUNT(*) * 100 / &&total_samples., 1), '9,990.0')||' %' percent,
SUBSTR(sql_plan_operation||' '||sql_plan_options, 1, 40) operation,
session_state
FROM dba_hist_active_sess_history
WHERE sql_plan_operation IS NOT NULL
GROUP BY
sql_plan_operation,
sql_plan_options,
session_state
HAVING COUNT(*) * 100 / &&total_samples. > 0.05
ORDER BY
sql_plan_operation,
sql_plan_options,
session_state;
PRO
PRO DBA_HIST_ACTIVE_SESS_HISTORY (group by operation, session_state and wait_class)
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
COL wait_class FOR A24;
SELECT COUNT(*) samples,
TO_CHAR(ROUND(COUNT(*) * 100 / &&total_samples., 1), '9,990.0')||' %' percent,
SUBSTR(sql_plan_operation||' '||sql_plan_options, 1, 40) operation,
session_state||' '||wait_class wait_class
FROM dba_hist_active_sess_history
WHERE sql_plan_operation IS NOT NULL
GROUP BY
sql_plan_operation,
sql_plan_options,
session_state,
wait_class
HAVING COUNT(*) * 100 / &&total_samples. > 0.05
ORDER BY
sql_plan_operation,
sql_plan_options,
session_state,
wait_class;
PRO
PRO DBA_HIST_ACTIVE_SESS_HISTORY (group by operation, session_state, wait_class and event)
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
COL event FOR A70;
SELECT COUNT(*) samples,
TO_CHAR(ROUND(COUNT(*) * 100 / &&total_samples., 1), '9,990.0')||' %' percent,
SUBSTR(sql_plan_operation||' '||sql_plan_options, 1, 40) operation,
session_state||' '||wait_class||' '||
CASE WHEN event IS NOT NULL THEN '"'||event||'"' END event
FROM dba_hist_active_sess_history
WHERE sql_plan_operation IS NOT NULL
GROUP BY
sql_plan_operation,
sql_plan_options,
session_state,
wait_class,
event
HAVING COUNT(*) * 100 / &&total_samples. > 0.05
ORDER BY
sql_plan_operation,
sql_plan_options,
session_state,
wait_class,
event;
PRO
PRO DBA_HIST_ACTIVE_SESS_HISTORY I/O waits (group by operation, wait_class, event and obj#)
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
COL object_name FOR A30;
SELECT COUNT(*) samples,
TO_CHAR(ROUND(COUNT(*) * 100 / &&total_samples., 1), '9,990.0')||' %' percent,
SUBSTR(sql_plan_operation||' '||sql_plan_options, 1, 40) operation,
SUBSTR(wait_class||' '||
CASE WHEN event IS NOT NULL THEN '"'||event||'"' END, 1, 60) event,
current_obj#,
(SELECT object_name FROM dba_objects WHERE data_object_id = current_obj# AND ROWNUM = 1) object_name
FROM dba_hist_active_sess_history
WHERE sql_plan_operation IS NOT NULL
AND session_state = 'WAITING'
AND wait_class IN ('Application', 'Cluster', 'Concurrency', 'User I/O')
AND current_obj# IS NOT NULL
GROUP BY
sql_plan_operation,
sql_plan_options,
wait_class,
event,
current_obj#
HAVING COUNT(*) * 100 / &&total_samples. > 0.05
ORDER BY
sql_plan_operation,
sql_plan_options,
wait_class,
event,
current_obj#;
SET NEWP 1 PAGES 14 LINES 80 TRIMS OFF;
--SPO OFF;
set lines 2000
set pages 50