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
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
|
STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
PROD 48050787 prod 1 9.2.0.8.0 NO ORA_002
Snap Id Snap Time Sessions Curs/Sess Comment
--------- ------------------ -------- --------- -------------------
Begin Snap: 31 05-Feb-09 13:15:45 195 8.1
End Snap: 33 05-Feb-09 14:35:43 210 8.5
Elapsed: 79.97 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 1,800M Std Block Size: 8K
Shared Pool Size: 464M Log Buffer: 8K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 6,252.18 10,972.18
Logical reads: 28,730.26 50,419.83
Block changes: 40.03 70.26
Physical reads: 1,017.76 1,786.10
Physical writes: 64.53 113.25
User calls: 32.07 56.28
Parses: 18.59 32.62
Hard parses: 3.98 6.98
Sorts: 5.24 9.19
Logons: 0.05 0.09
Executes: 196.19 344.31
Transactions: 0.57
% Blocks changed per Read: 0.14 Recursive Call %: 88.66
Rollback per transaction %: 39.10 Rows per Sort: 2028.73
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 99.98
Buffer Hit %: 96.67 In-memory Sort %: 98.72
Library Hit %: 97.40 Soft Parse %: 78.61
Execute to Parse %: 90.53 Latch Hit %: 99.97
Parse CPU to Parse Elapsd %: 99.21 % Non-Parse CPU: 91.40
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 83.80 84.33
% SQL with executions>1: 18.19 16.40
% Memory for SQL w/exec>1: 30.03 23.62
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file scattered read 919,336 3,120 44.98
enqueue 532 1,590 22.92
CPU time 1,557 22.45
db file sequential read 51,195 226 3.25
direct path read 34,173 187 2.69
-------------------------------------------------------------
Wait Events for DB: PROD Instance: prod Snaps: 31 -33
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file scattered read 919,336 0 3,120 3 336.3
enqueue 532 528 1,590 2988 0.2
db file sequential read 51,195 0 226 4 18.7
direct path read 34,173 0 187 5 12.5
log file sync 2,891 0 92 32 1.1
db file parallel write 593 0 64 108 0.2
control file parallel write 1,678 0 63 38 0.6
direct path write 680 0 8 12 0.2
log buffer space 140 0 8 55 0.1
direct path read (lob) 1,011 0 7 7 0.4
control file sequential read 2,106 0 5 2 0.8
buffer busy waits 556 0 3 5 0.2
log file switch completion 13 0 3 229 0.0
direct path write (lob) 27 0 2 57 0.0
SQL*Net more data to client 16,662 0 1 0 6.1
log file sequential read 84 0 1 8 0.0
log file single write 36 0 1 16 0.0
log file parallel write 4,140 0 0 0 1.5
latch free 129 85 0 2 0.0
library cache pin 4 0 0 12 0.0
SQL*Net break/reset to clien 38 0 0 0 0.0
LGWR wait for redo copy 29 0 0 0 0.0
row cache lock 3 0 0 0 0.0
async disk IO 18 0 0 0 0.0
virtual circuit status 126,377 146 13,587 108 46.2
SQL*Net message from client 127,566 0 73 1 46.7
SQL*Net message to client 127,675 0 0 0 46.7
SQL*Net more data from clien 1,363 0 0 0 0.5
-------------------------------------------------------------
Background Wait Events for DB: PROD Instance: prod Snaps: 31 -33
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file parallel write 593 0 64 108 0.2
control file parallel write 1,678 0 63 38 0.6
control file sequential read 2,002 0 5 3 0.7
log file switch completion 3 0 1 335 0.0
log buffer space 35 0 1 24 0.0
log file sequential read 84 0 1 8 0.0
log file single write 36 0 1 16 0.0
direct path write 36 0 0 11 0.0
log file parallel write 4,140 0 0 0 1.5
direct path read 36 0 0 5 0.0
rdbms ipc reply 47 0 0 0 0.0
LGWR wait for redo copy 29 0 0 0 0.0
async disk IO 18 0 0 0 0.0
buffer busy waits 1 0 0 0 0.0
rdbms ipc message 9,963 4,801 28,520 2863 3.6
smon timer 299 1 4,841 16191 0.1
pmon timer 1,677 1,594 4,747 2831 0.6
-------------------------------------------------------------
-------------------------------------------------------------
Instance Activity Stats for DB: PROD Instance: prod Snaps: 31 -33
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 155,700 32.5 57.0
CPU used when call started 155,602 32.4 56.9
CR blocks created 12,115 2.5 4.4
DBWR checkpoint buffers written 6,313 1.3 2.3
DBWR checkpoints 6 0.0 0.0
DBWR transaction table writes 64 0.0 0.0
DBWR undo block writes 1,459 0.3 0.5
SQL*Net roundtrips to/from client 253,690 52.9 92.8
active txn count during cleanout 118,970 24.8 43.5
background checkpoints completed 6 0.0 0.0
background checkpoints started 6 0.0 0.0
background timeouts 5,830 1.2 2.1
branch node splits 0 0.0 0.0
buffer is not pinned count 90,786,459 18,921.7 33,206.5
buffer is pinned count 73,412,604 15,300.7 26,851.7
bytes received via SQL*Net from c 25,637,028 5,343.3 9,377.1
calls to get snapshot scn: kcmgss 1,297,984 270.5 474.8
calls to kcmgas 24,190 5.0 8.9
calls to kcmgcs 728 0.2 0.3
change write time 890 0.2 0.3
cleanout - number of ktugct calls 16,097 3.4 5.9
cleanouts and rollbacks - consist 11,670 2.4 4.3
cleanouts only - consistent read 28 0.0 0.0
cluster key scan block gets 30,856 6.4 11.3
cluster key scans 10,790 2.3 4.0
commit cleanout failures: buffer 6 0.0 0.0
commit cleanout failures: callbac 4 0.0 0.0
commit cleanout failures: cannot 1 0.0 0.0
commit cleanouts 21,709 4.5 7.9
commit cleanouts successfully com 21,698 4.5 7.9
commit txn count during cleanout 1,272 0.3 0.5
consistent changes 460,340 95.9 168.4
consistent gets 137,639,843 28,686.9 50,343.8
consistent gets - examination 11,293,517 2,353.8 4,130.8
cursor authentications 2,392 0.5 0.9
data blocks consistent reads - un 459,516 95.8 168.1
db block changes 192,086 40.0 70.3
db block gets 210,438 43.9 77.0
deferred (CURRENT) block cleanout 11,432 2.4 4.2
dirty buffers inspected 0 0.0 0.0
enqueue conversions 887 0.2 0.3
enqueue deadlocks 1 0.0 0.0
enqueue releases 60,287 12.6 22.1
enqueue requests 60,293 12.6 22.1
enqueue timeouts 4 0.0 0.0
enqueue waits 5 0.0 0.0
execute count 941,330 196.2 344.3
failed probes on index block recl 6 0.0 0.0
free buffer inspected 312 0.1 0.1
free buffer requested 4,606,330 960.1 1,684.8
hot buffers moved to head of LRU 25,235 5.3 9.2
immediate (CR) block cleanout app 11,698 2.4 4.3
immediate (CURRENT) block cleanou 1,990 0.4 0.7
index crx upgrade (positioned) 22,082 4.6 8.1
index fast full scans (full) 634 0.1 0.2
index fetch by key 4,916,543 1,024.7 1,798.3
Instance Activity Stats for DB: PROD Instance: prod Snaps: 31 -33
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
index scans kdiixs1 48,713,855 10,153.0 17,817.8
leaf node 90-10 splits 44 0.0 0.0
leaf node splits 153 0.0 0.1
logons cumulative 257 0.1 0.1
messages received 5,604 1.2 2.1
messages sent 5,604 1.2 2.1
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 125,973,953 26,255.5 46,076.8
opened cursors cumulative 86,427 18.0 31.6
parse count (failures) 0 0.0 0.0
parse count (hard) 19,074 4.0 7.0
parse count (total) 89,191 18.6 32.6
parse time cpu 13,388 2.8 4.9
parse time elapsed 13,495 2.8 4.9
physical reads 4,883,189 1,017.8 1,786.1
physical reads direct 289,521 60.3 105.9
physical reads direct (lob) 2,554 0.5 0.9
physical writes 309,619 64.5 113.3
physical writes direct 303,120 63.2 110.9
physical writes direct (lob) 186 0.0 0.1
physical writes non checkpoint 305,066 63.6 111.6
pinned buffers inspected 284 0.1 0.1
prefetched blocks 3,620,564 754.6 1,324.3
process last non-idle time 4,815 1.0 1.8
recursive calls 1,202,721 250.7 439.9
recursive cpu usage 51,775 10.8 18.9
redo blocks written 62,012 12.9 22.7
redo buffer allocation retries 155 0.0 0.1
redo entries 103,719 21.6 37.9
redo log space requests 17 0.0 0.0
redo log space wait time 298 0.1 0.1
redo size 29,997,936 6,252.2 10,972.2
redo synch time 9,167 1.9 3.4
redo synch writes 2,891 0.6 1.1
redo wastage 1,000,616 208.6 366.0
redo write time 13,211 2.8 4.8
redo writer latching time 0 0.0 0.0
redo writes 4,143 0.9 1.5
rollback changes - undo records a 7,478 1.6 2.7
rollbacks only - consistent read 517 0.1 0.2
rows fetched via callback 4,196,931 874.7 1,535.1
session connect time 0 0.0 0.0
session logical reads 137,847,808 28,730.3 50,419.8
session pga memory 4,254,380 886.7 1,556.1
session pga memory max 25,607,936 5,337.2 9,366.5
session uga memory 4,654,652 970.1 1,702.5
session uga memory max 38,878,016 8,103.0 14,220.2
shared hash latch upgrades - no w 32,039 6.7 11.7
sorts (disk) 322 0.1 0.1
sorts (memory) 24,814 5.2 9.1
sorts (rows) 50,994,119 10,628.2 18,651.8
summed dirty queue length 0 0.0 0.0
switch current to new buffer 1,353 0.3 0.5
table fetch by rowid 46,354,793 9,661.3 16,954.9
table fetch continued row 4,444 0.9 1.6
table scan blocks gotten 19,475,278 4,059.0 7,123.4
Instance Activity Stats for DB: PROD Instance: prod Snaps: 31 -33
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
table scan rows gotten 1,169,990,349 243,849.6 427,940.9
table scans (long tables) 2,743 0.6 1.0
table scans (short tables) 51,683 10.8 18.9
transaction rollbacks 108 0.0 0.0
user calls 153,874 32.1 56.3
user commits 1,665 0.4 0.6
user rollbacks 1,069 0.2 0.4
workarea executions - multipass 12 0.0 0.0
workarea executions - onepass 197 0.0 0.1
workarea executions - optimal 35,507 7.4 13.0
write clones created in backgroun 0 0.0 0.0
write clones created in foregroun 28 0.0 0.0
-------------------------------------------------------------
PGA Aggr Target Stats for DB: PROD Instance: prod Snaps: 31 -33
-> B: Begin snap E: End snap (rows dentified with B or E contain data
which is absolute i.e. not diffed over the interval)
-> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory
-> Auto PGA Target - actual workarea memory target
-> W/A PGA Used - amount of memory used for all Workareas (manual + auto)
-> %PGA W/A Mem - percentage of PGA memory allocated to workareas
-> %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt
-> %Man W/A Mem - percentage of workarea memory under manual control
PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
--------------- ---------------- -------------------------
89.6 15,018 1,738
%PGA %Auto %Man
PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global Mem
Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K)
- --------- --------- ---------- ---------- ------ ------ ------ ----------
B 150 119 20.9 0.8 3.7 .0 100.0 7,680
E 150 120 22.9 0.9 3.8 .0 100.0 7,680
-------------------------------------------------------------
PGA Aggr Target Histogram for DB: PROD Instance: prod Snaps: 31 -33
-> Optimal Executions are purely in-memory operations
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
2K 4K 18,371 18,371 0 0
4K 8K 611 611 0 0
8K 16K 155 155 0 0
16K 32K 615 615 0 0
32K 64K 816 816 0 0
64K 128K 163 163 0 0
128K 256K 272 272 0 0
256K 512K 68 68 0 0
512K 1024K 14,088 14,086 2 0
1M 2M 111 110 1 0
2M 4M 307 217 78 12
4M 8M 2 2 0 0
8M 16M 116 0 116 0
-------------------------------------------------------------
PGA Memory Advisory for DB: PROD Instance: prod End Snap: 33
-> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value
where Estd PGA Overalloc Count is 0
Estd Extra Estd PGA Estd PGA
PGA Target Size W/A MB W/A MB Read/ Cache Overalloc
Est (MB) Factr Processed Written to Disk Hit % Count
---------- ------- ---------------- ---------------- -------- ----------
19 0.1 766.6 1,294.3 37.0 76
38 0.3 766.6 561.5 58.0 5
75 0.5 766.6 287.1 73.0 0
113 0.8 766.6 247.2 76.0 0
150 1.0 766.6 125.6 86.0 0
180 1.2 766.6 81.0 90.0 0
210 1.4 766.6 51.0 94.0 0
240 1.6 766.6 39.8 95.0 0
270 1.8 766.6 0.0 100.0 0
300 2.0 766.6 0.0 100.0 0
450 3.0 766.6 0.0 100.0 0
600 4.0 766.6 0.0 100.0 0
900 6.0 766.6 0.0 100.0 0
1,200 8.0 766.6 0.0 100.0 0
-------------------------------------------------------------
Enqueue activity for DB: PROD Instance: prod Snaps: 31 -33
-> Enqueue stats gathered prior to 9i should not be compared with 9i data
-> ordered by Wait Time desc, Waits desc
Avg Wt Wait
Eq Requests Succ Gets Failed Gets Waits Time (ms) Time (s)
-- ------------ ------------ ----------- ----------- ------------- ------------
TM 20,239 20,235 2 2 607,320.50 1,215
TX 10,741 10,740 0 3 11,510.33 35
------------------------------------------------------------- |
Aide pour analyser ce rapport STATSPACK
Répondre avec citation
)
.
Partager