-
Notifications
You must be signed in to change notification settings - Fork 10
/
Copy pathawr_load_history.sql
202 lines (188 loc) · 9.74 KB
/
awr_load_history.sql
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
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
/* when using statspack, this query shows the statspack summary (same values as 'Load Profile' in statspack report)
for each snapshot during the last week.
Thus you can quickly check the database load history and then run statspack report on desired snap_id if you want further details.
It shows following columns for each timestamp:
Redo size (in blocks) per second
Logical reads per second
Block changes per second
Physical reads per second (disk io/secs)
Physical writes per second
User calls per second
Parses per second
Hard parses per second
Sorts per second
Logons per second
Executes per second
Transactions per second
Blocks changed per Read %
Recursive Call %
Rollback per transaction %
Rows per Sort %
cpu per elapsed %
Buffer hit ratio %
This query shows history for the seven last days.
You can modify it on the first lines
*/
set linesize 500
set pagesize 9999
alter session set nls_numeric_characters=',.';
set trimspool on
set tab off
set verify off
set feedback off
column instance_number format 99
column bsnap_id format 9999999
column bsnap_time format a16
column esnap_id format 9999999
column esnap_time format a16
column redo_blocks format 999999999990D999
column logical_reads format 999999999990D999
column block_changes format 999999999990D999
column physical_reads format 999999999990D999
column physical_writes format 999999999990D999
column user_calls format 999999999990D999
column parses format 999999999990D999
column hard_parses format 999999999990D999
column sorts format 999999999990D999
column logons format 999999999990D999
column executes format 999999999990D999
column transactions format 999999999990D999
column changes_per_read format a8
column recursive format a8
column rollback format a8
column rows_per_sort format a18
column cpusecs_pct format 99990D00
column rec_cpusecs_pct format 99990D00
column parse_cpusecs_pct format 99990D00
column buffer_hit format a8
column undo_records format 99999999999999999
column rollbacks format 99999999999999999
column a format a500
prompt Enter the begindate in the format DD/MM/YYYY HH24:MI
accept start_time prompt 'begin date: '
prompt Enter the enddate in the format DD/MM/YYYY HH24:MI
accept end_time prompt 'end date: '
prompt Enter the spoolfile
accept spoolfile prompt 'spool file: '
spool &spoolfile
WITH p as
( select dbid, instance_number, snap_id,
lag(snap_id, 1, snap_id) over
( partition by dbid, instance_number
order by snap_id
) prev_snap_id,
begin_interval_time, end_interval_time
from dba_hist_snapshot
where begin_interval_time between
to_timestamp ('&start_time', 'DD/MM/YYYY HH24:MI')
and to_timestamp ('&end_time', 'DD/MM/YYYY HH24:MI')
),
s as
( select d.name database, p.dbid, p.instance_number, p.prev_snap_id bsnap_id, p.snap_id esnap_id,
p.begin_interval_time bsnap_time, p.end_interval_time esnap_time, bs.stat_name,
round((es.value-bs.value)/( extract(second from (p.end_interval_time - p.begin_interval_time))
+ extract(minute from (p.end_interval_time - p.begin_interval_time)) * 60
+ extract(hour from (p.end_interval_time - p.begin_interval_time)) * 60 * 60
+ extract(day from (p.end_interval_time - p.begin_interval_time)) * 24 * 60 * 60
)
,6
) valuepersecond
from v$database d, p,
dba_hist_sysstat bs, dba_hist_sysstat es
where d.dbid = p.dbid
and ( p.dbid = bs.dbid
and p.instance_number = bs.instance_number
and p.prev_snap_id = bs.snap_id
)
and ( p.dbid = es.dbid
and p.instance_number = es.instance_number
and p.snap_id = es.snap_id
)
and ( bs.stat_id = es.stat_id
and bs.instance_number = es.instance_number
and bs.stat_name=es.stat_name
)
and bs.stat_name in
( 'redo size','redo blocks written','session logical reads','db block changes','physical reads','physical writes','user calls',
'parse count (total)','parse count (hard)','sorts (memory)','sorts (disk)','logons cumulative','execute count','user rollbacks',
'user commits', 'recursive calls','sorts (rows)','CPU used by this session','recursive cpu usage','parse time cpu',
'rollback changes - undo records applied', 'DB time', 'Read IO (MB)', 'Write IO (MB)'
)
),
g as
( select /*+ FIRST_ROWS */
database, instance_number, bsnap_id, esnap_id, bsnap_time, esnap_time,
sum(decode( stat_name, 'redo size' , valuepersecond, 0 )) redo_size,
sum(decode( stat_name, 'redo blocks written' , valuepersecond, 0 )) redo_blocks,
sum(decode( stat_name, 'session logical reads' , valuepersecond, 0 )) logical_reads,
sum(decode( stat_name, 'db block changes' , valuepersecond, 0 )) block_changes,
sum(decode( stat_name, 'physical reads' , valuepersecond, 0 )) physical_reads ,
sum(decode( stat_name, 'physical writes' , valuepersecond, 0 )) physical_writes,
sum(decode( stat_name, 'user calls' , valuepersecond, 0 )) user_calls,
sum(decode( stat_name, 'recursive calls' , valuepersecond, 0 )) recursive_calls,
sum(decode( stat_name, 'parse count (total)' , valuepersecond, 0 )) parses ,
sum(decode( stat_name, 'parse count (hard)' , valuepersecond, 0 )) hard_parses ,
sum(decode( stat_name, 'sorts (rows)' , valuepersecond, 0 )) sort_rows ,
sum(decode( stat_name, 'sorts (memory)' , valuepersecond,
'sorts (disk)' , valuepersecond, 0 )) sorts ,
sum(decode( stat_name, 'logons cumulative' , valuepersecond, 0 )) logons ,
sum(decode( stat_name, 'execute count' , valuepersecond, 0 )) executes ,
sum(decode( stat_name, 'user rollbacks' , valuepersecond,
'user commits' , valuepersecond, 0 )) transactions,
sum(decode( stat_name, 'user rollbacks' , valuepersecond, 0 )) rollbacks,
sum(decode( stat_name, 'rollback changes - undo records applied' , valuepersecond, 0 )) undo_records,
sum(decode( stat_name, 'CPU used by this session' , valuepersecond/100, 0 )) cpusecs,
sum(decode( stat_name, 'recursive cpu usage' , valuepersecond/100, 0 )) rec_cpusecs,
sum(decode( stat_name, 'parse time cpu' , valuepersecond/100, 0 )) parse_cpusecs,
sum(decode( stat_name, 'DB time' , valuepersecond, 0 )) db_time,
sum(decode( stat_name, 'Read IO (MB)' , valuepersecond, 0 )) read_io_mb,
sum(decode( stat_name, 'Write IO (MB)' , valuepersecond, 0 )) write_io_mb
from s
group by database,instance_number, bsnap_id, esnap_id, bsnap_time, esnap_time
)
/*select instance_number, bsnap_id, to_char(bsnap_time,'DD-MON-YY HH24:MI') bsnap_time_str, esnap_id, to_char(esnap_time,'DD-MON-YY HH24:MI') esnap_time_str,read_io_mb, write_io_mb
db_time, redo_blocks, logical_reads, block_changes, physical_reads,
physical_writes, user_calls, parses, hard_parses, sorts, logons, executes, transactions,
to_char(100 * (block_changes / decode(logical_reads,0,1,logical_reads)),'990D00')||'%' changes_per_read,
to_char(100 * (recursive_calls / decode(user_calls + recursive_calls, 0, 1,user_calls + recursive_calls)),'990D00') ||'%' recursive,
to_char(100 * (rollbacks / decode(transactions,0,1,transactions)),'990D00') ||'%' rollback,
to_char(decode(sorts, 0, NULL, (sort_rows/sorts)),'99999999999999999') rows_per_sort,
100 * cpusecs cpusecs_pct,
100 * rec_cpusecs rec_cpusecs_pct,
100 * parse_cpusecs parse_cpusecs_pct,
to_char(100 * (1 - physical_reads / decode(logical_reads, 0, 1,logical_reads)),'990D00') ||'%' buffer_hit,
undo_records, rollbacks
*/select
(instance_number ||';'||
bsnap_id ||';'||
to_char(bsnap_time,'DD-MON-YY HH24:MI') ||';'||
esnap_id ||';'||
to_char(esnap_time,'DD-MON-YY HH24:MI') ||';'||
db_time ||';' ||
redo_blocks ||';'||
logical_reads ||';'||
block_changes ||';'||
physical_reads ||';'||
physical_writes ||';'||
user_calls ||';'||
parses ||';'||
hard_parses ||';'||
sorts ||';'||
logons ||';'||
executes ||';'||
transactions ||';'||
to_char(100 * (block_changes / decode(logical_reads,0,1,logical_reads)),'990D00')||'%' ||';'||
to_char(100 * (recursive_calls / decode(user_calls + recursive_calls, 0, 1,user_calls + recursive_calls)),'990D00') ||'%' ||';'||
to_char(100 * (rollbacks / decode(transactions,0,1,transactions)),'990D00') ||'%' ||';'||
to_char(decode(sorts, 0, NULL, (sort_rows/sorts)),'99999999999999999') ||';'||
100 * cpusecs ||';'||
100 * rec_cpusecs ||';'||
100 * parse_cpusecs ||';'||
-- to_char(100 * (1 - physical_reads / decode(logical_reads, 0, 1,logical_reads)),'990D00') ||'%' ||';'||
undo_records ||';'||
rollbacks
) a
from g
order by instance_number, bsnap_time;
spool off
set feedback on