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
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
|
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
PROD 14612696 prod 1 10.1.0.3.0 NO
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 29861 21-Jun-11 07:00:36 13,106 .2
End Snap: 29864 21-Jun-11 10:01:01 13,113 .2
Elapsed: 180.41 (mins)
DB Time: 248.37 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 1,248M Std Block Size: 8K
Shared Pool Size: 1,264M Log Buffer: 1,024K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 112,303.65 25,632.10
Logical reads: 8,316.25 1,898.09
Block changes: 283.46 64.70
Physical reads: 88.91 20.29
Physical writes: 16.93 3.86
User calls: 916.65 209.22
Parses: 393.85 89.89
Hard parses: 10.53 2.40
Sorts: 32.07 7.32
Logons: 3.28 0.75
Executes: 641.92 146.51
Transactions: 4.38
% Blocks changed per Read: 3.41 Recursive Call %: 57.23
Rollback per transaction %: 12.51 Rows per Sort: 28.07
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 98.93 In-memory Sort %: 100.00
Library Hit %: 98.63 Soft Parse %: 97.33
Execute to Parse %: 38.65 Latch Hit %: 99.90
Parse CPU to Parse Elapsd %: 84.64 % Non-Parse CPU: 89.65
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 94.04 92.81
% SQL with executions>1: 67.85 81.60
% Memory for SQL w/exec>1: 64.64 60.31
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) DB Time Wait Class
------------------------------ ------------ ----------- --------- --------------
class slave wait 125 10,650 71.47 Other
db file sequential read 825,581 7,483 50.21 User I/O
CPU time 6,135 41.17
log file sync 46,429 926 6.21 Commit
log file parallel write 103,130 638 4.28 System I/O
-------------------------------------------------------------
Wait Events DB/Inst: PROD/prod Snaps: 29861-29864
-> 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
--------------------------- ------------ ---------- ---------- ------- ---------
class slave wait 125 91 10,650 85202 0.0
db file sequential read 825,581 0 7,483 9 17.4
log file sync 46,429 359 926 20 1.0
log file parallel write 103,130 0 638 6 2.2
control file parallel write 8,316 0 221 27 0.2
db file scattered read 20,583 0 183 9 0.4
db file parallel write 28,210 0 172 6 0.6
control file sequential rea 17,786 0 134 8 0.4
latch: library cache 36,210 0 122 3 0.8
SQL*Net more data to client 4,693,287 0 56 0 99.0
log file sequential read 1,032 0 51 49 0.0
log buffer space 206 2 50 244 0.0
latch: shared pool 47,426 0 32 1 1.0
control file single write 1,629 0 24 15 0.0
db file parallel read 424 0 19 45 0.0
master wait 42 2 10 230 0.0
SQL*Net message to client 7,941,071 0 9 0 167.4
read by other session 12 0 8 662 0.0
enq: CF - contention 7 1 7 1019 0.0
buffer busy waits 519 1 3 7 0.0
enq: HW - contention 464 0 3 7 0.0
SQL*Net more data from clie 141,950 0 2 0 3.0
SQL*Net break/reset to clie 11,352 0 1 0 0.2
library cache pin 2 0 1 441 0.0
log file switch completion 8 0 0 53 0.0
rdbms ipc reply 482 0 0 1 0.0
latch free 19 0 0 18 0.0
recovery area: computing ob 4 0 0 76 0.0
process startup 10 0 0 20 0.0
latch: session allocation 873 0 0 0 0.0
direct path read 152 0 0 1 0.0
LGWR wait for redo copy 644 1 0 0 0.0
enq: TX - index contention 2 0 0 23 0.0
wait list latch free 3 0 0 15 0.0
direct path write 52,249 0 0 0 1.1
latch: In memory undo latch 4 0 0 7 0.0
ksfd: async disk IO 10,621 0 0 0 0.2
latch: row cache objects 34 0 0 1 0.0
latch: library cache pin 19 0 0 0 0.0
latch: cache buffers chains 37 0 0 0 0.0
log file single write 8 0 0 1 0.0
enq: TX - row lock contenti 1 0 0 4 0.0
undo segment extension 4,264 4,245 0 0 0.1
latch: library cache lock 10 0 0 0 0.0
recovery area: computing ba 2 0 0 0 0.0
recovery area: computing dr 2 0 0 0 0.0
recovery area: computing ap 2 0 0 0 0.0
latch: cache buffers lru ch 7 0 0 0 0.0
latch: redo writing 2 0 0 0 0.0
latch: redo allocation 2 0 0 0 0.0
buffer deadlock 1 1 0 0 0.0
SQL*Net message from client 7,941,061 0 532,984 67 167.4
jobq slave wait 10,517 10,341 31,498 2995 0.2
ASM background timer 2,194 0 10,526 4798 0.0
queue messages 2,121 2,097 10,518 4959 0.0
Queue Monitor Wait 351 351 10,506 29931 0.0
virtual circuit status 359 359 10,502 29254 0.0
wait for unread message on 10,496 10,420 10,452 996 0.2
wakeup time manager 67 42 10,276 153368 0.0
-------------------------------------------------------------
Background Wait Events DB/Inst: PROD/prod Snaps: 29861-29864
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
--------------------------- ------------ ---------- ---------- ------- ---------
log file parallel write 103,110 0 638 6 2.2
control file parallel write 8,090 0 221 27 0.2
db file parallel write 28,210 0 172 6 0.6
control file sequential rea 9,274 0 72 8 0.2
log file sequential read 1,032 0 51 49 0.0
control file single write 1,556 0 24 15 0.0
enq: CF - contention 7 1 7 1019 0.0
master wait 19 2 7 358 0.0
db file scattered read 214 0 2 11 0.0
buffer busy waits 120 0 0 2 0.0
process startup 10 0 0 20 0.0
direct path read 96 0 0 1 0.0
rdbms ipc reply 467 0 0 0 0.0
LGWR wait for redo copy 644 1 0 0 0.0
ksfd: async disk IO 1,994 0 0 0 0.0
log file single write 8 0 0 1 0.0
direct path write 96 0 0 0 0.0
latch: redo writing 2 0 0 0 0.0
latch: cache buffers lru ch 4 0 0 0 0.0
latch: redo allocation 2 0 0 0 0.0
latch: cache buffers chains 1 0 0 0 0.0
latch: session allocation 6 0 -0 -1 0.0
latch: shared pool 61 0 -0 -0 0.0
latch: library cache 18 0 -1 -32 0.0
log buffer space 1 0 -2 -1630 0.0
db file sequential read 1,871 0 -7 -4 0.0
rdbms ipc message 86,591 33,416 110,549 1277 1.8
ASM background timer 2,194 0 10,526 4798 0.0
Queue Monitor Wait 351 351 10,506 29931 0.0
smon timer 2,804 0 10,427 3719 0.1
wakeup time manager 67 42 10,276 153368 0.0
-------------------------------------------------------------
Time Model Statistics DB/Inst: PROD/prod Snaps: 29861-29864
-> ordered by Time (seconds) desc
Time % Total
Statistic Name (seconds) DB Time
--------------------------------------------- -------------- -----------
background elapsed time 38,490.24 258.28
DB time 14,902.42 100.00
sql execute elapsed time 7,626.68 51.18
DB CPU 6,134.99 41.17
Java execution elapsed time 2,775.55 18.62
parse time elapsed 825.42 5.54
hard parse elapsed time 575.92 3.86
PL/SQL execution elapsed time 526.67 3.53
connection management call elapsed time 425.88 2.86
inbound PL/SQL rpc elapsed time 240.21 1.61
PL/SQL compilation elapsed time 89.01 .60
background cpu time 57.12 .38
hard parse (sharing criteria) elapsed time 8.16 .05
sequence load elapsed time 4.85 .03
hard parse (bind mismatch) elapsed time .30 .00
failed parse elapsed time .00 .00
failed parse (out of shared memory) elapsed t .00 .00
-------------------------------------------------------------
Operating System Statistics DB/Inst: PROD/prod Snaps: 29861-29864
No data exists for this section of the report.
-------------------------------------------------------------
Service Statistics DB/Inst: PROD/prod Snaps: 29861-29864
-> ordered by DB Time
Physical Logical
Service Name DB Time (s) DB CPU (s) Reads Reads
-------------------------------- ------------ ------------ ---------- ----------
SYS$USERS 8,539.1 1,279.4 855,455 ##########
prod 6,376.7 4,867.6 99,064 ##########
SYS$BACKGROUND 0.0 0.0 7,522 454,550
prodXDB 0.0 0.0 0 0
-------------------------------------------------------------
Service Wait Class Stats DB/Inst: PROD/prod Snaps: 29861-29864
-> Wait Class info for services in the Service Statistics section.
-> Total Waits and Time Waited displayed for the following wait
classes: User I/O, Concurrency, Administrative, Network
-> Time Waited (Wt Time) in centisecond (100th of a second)
Service Name
----------------------------------------------------------------
User I/O User I/O Concurcy Concurcy Admin Admin Network Network
Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time
--------- --------- --------- --------- --------- --------- --------- ---------
SYS$USERS
833039 666714 8303 3409 0 0 6900416 5930
prod
60303 96863 28016 9131 0 0 5770035 802
SYS$BACKGROUND
5658 5625 469 106 0 0 0 0
-------------------------------------------------------------
Instance Activity Stats DB/Inst: PROD/prod Snaps: 29861-29864
Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
CPU used by this session 632,116 58.4 13.3
CPU used when call started 632,190 58.4 13.3
CR blocks created 6,896 0.6 0.2
Cached Commit SCN referenced 78,956 7.3 1.7
Commit SCN cached 26 0.0 0.0
DB time 4,795,658 443.0 101.1
DBWR checkpoint buffers written 82,902 7.7 1.8
DBWR checkpoints 2 0.0 0.0
DBWR object drop buffers written 0 0.0 0.0
DBWR parallel query checkpoint b 0 0.0 0.0
DBWR revisited being-written buf 0 0.0 0.0
DBWR tablespace checkpoint buffe 0 0.0 0.0
DBWR thread checkpoint buffers w 0 0.0 0.0
DBWR transaction table writes 588 0.1 0.0
DBWR undo block writes 86,500 8.0 1.8
DDL statements parallelized 0 0.0 0.0
DFO trees parallelized 0 0.0 0.0
DML statements parallelized 0 0.0 0.0
IMU CR rollbacks 4,678 0.4 0.1
IMU Flushes 7,334 0.7 0.2
IMU Redo allocation size 17,414,416 1,608.8 367.2
IMU bind flushes 26 0.0 0.0
IMU commits 36,428 3.4 0.8
IMU contention 64 0.0 0.0
IMU ktichg flush 147 0.0 0.0
IMU pool not allocated 2 0.0 0.0
IMU recursive-transaction flush 9 0.0 0.0
IMU undo allocation size 194,349,640 17,954.7 4,098.0
IMU- failed to get a private str 2 0.0 0.0
Misses for writing mapping 0 0.0 0.0
OS Involuntary context switches 0 0.0 0.0
OS Page faults 0 0.0 0.0
OS Page reclaims 0 0.0 0.0
OS System time used 0 0.0 0.0
OS User time used 0 0.0 0.0
OS Voluntary context switches 0 0.0 0.0
PX local messages recv'd 0 0.0 0.0
PX local messages sent 0 0.0 0.0
Parallel operations not downgrad 0 0.0 0.0
SQL*Net roundtrips to/from clien 7,832,101 723.6 165.1
active txn count during cleanout 11,890 1.1 0.3
application wait time 135 0.0 0.0
background checkpoints completed 2 0.0 0.0
background checkpoints started 2 0.0 0.0
background timeouts 33,733 3.1 0.7
branch node splits 3 0.0 0.0
buffer is not pinned count 45,444,639 4,198.3 958.2
buffer is pinned count 80,441,282 7,431.4 1,696.1
bytes received via SQL*Net from 2,384,908,935 220,325.6 50,287.0
bytes sent via SQL*Net to client 14,219,129,697 1,313,609.1 299,817.2
calls to get snapshot scn: kcmgs 9,804,086 905.7 206.7
calls to kcmgas 171,535 15.9 3.6
calls to kcmgcs 12,913 1.2 0.3
change write time 6,792 0.6 0.1
cleanout - number of ktugct call 13,173 1.2 0.3
cleanouts and rollbacks - consis 2,590 0.2 0.1
cleanouts only - consistent read 1,042 0.1 0.0
cluster key scan block gets 68,417 6.3 1.4
cluster key scans 43,087 4.0 0.9
commit cleanout failures: block 825 0.1 0.0
Instance Activity Stats DB/Inst: PROD/prod Snaps: 29861-29864
Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
commit cleanout failures: buffer 0 0.0 0.0
commit cleanout failures: callba 6 0.0 0.0
commit cleanout failures: cannot 22 0.0 0.0
commit cleanouts 197,106 18.2 4.2
commit cleanouts successfully co 196,253 18.1 4.1
commit txn count during cleanout 6,581 0.6 0.1
concurrency wait time 12,636 1.2 0.3
consistent changes 123,408 11.4 2.6
consistent gets 85,556,854 7,904.0 1,804.0
consistent gets - examination 38,464,122 3,553.4 811.0
consistent gets direct 100 0.0 0.0
consistent gets from cache 85,556,753 7,904.0 1,804.0
current blocks converted for CR 3 0.0 0.0
cursor authentications 38,059 3.5 0.8
data blocks consistent reads - u 80,897 7.5 1.7
db block changes 3,068,353 283.5 64.7
db block gets 4,462,187 412.2 94.1
db block gets direct 29,061 2.7 0.6
db block gets from cache 4,433,126 409.6 93.5
deferred (CURRENT) block cleanou 89,988 8.3 1.9
dirty buffers inspected 52,478 4.9 1.1
enqueue conversions 5,166 0.5 0.1
enqueue releases 424,473 39.2 9.0
enqueue requests 424,461 39.2 9.0
enqueue timeouts 1 0.0 0.0
enqueue waits 473 0.0 0.0
exchange deadlocks 1 0.0 0.0
execute count 6,948,445 641.9 146.5
frame signature mismatch 0 0.0 0.0
free buffer inspected 1,077,039 99.5 22.7
free buffer requested 1,082,598 100.0 22.8
heap block compress 16,039 1.5 0.3
hot buffers moved to head of LRU 511,680 47.3 10.8
immediate (CR) block cleanout ap 3,632 0.3 0.1
immediate (CURRENT) block cleano 48,338 4.5 1.0
index fast full scans (direct re 0 0.0 0.0
index fast full scans (full) 9,215 0.9 0.2
index fast full scans (rowid ran 0 0.0 0.0
index fetch by key 12,590,897 1,163.2 265.5
index scans kdiixs1 7,154,211 660.9 150.9
java call heap collected bytes 2,326,836,872 214,960.7 49,062.5
java call heap collected count 73,951,711 6,831.9 1,559.3
java call heap gc count 4,669 0.4 0.1
java call heap live object count 11,302 1.0 0.2
java call heap live object count 61,085 5.6 1.3
java call heap live size 8,031,176 742.0 169.3
java call heap live size max 9,020,272 833.3 190.2
java call heap object count 18,280 1.7 0.4
java call heap object count max 119,236 11.0 2.5
java call heap total size 11,796,480 1,089.8 248.7
java call heap total size max 11,796,480 1,089.8 248.7
java call heap used size 8,398,736 775.9 177.1
java call heap used size max 11,603,632 1,072.0 244.7
leaf node 90-10 splits 367 0.0 0.0
leaf node splits 1,271 0.1 0.0
logons cumulative 35,536 3.3 0.8
messages received 79,692 7.4 1.7
messages sent 79,692 7.4 1.7
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 38,294,305 3,537.8 807.5
Instance Activity Stats DB/Inst: PROD/prod Snaps: 29861-29864
Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
opened cursors cumulative 4,180,338 386.2 88.1
parse count (failures) 129 0.0 0.0
parse count (hard) 113,930 10.5 2.4
parse count (total) 4,263,193 393.9 89.9
parse time cpu 63,486 5.9 1.3
parse time elapsed 75,010 6.9 1.6
physical read IO requests 856,540 79.1 18.1
physical reads 962,433 88.9 20.3
physical reads cache 962,281 88.9 20.3
physical reads cache prefetch 115,710 10.7 2.4
physical reads direct 152 0.0 0.0
physical reads direct (lob) 56 0.0 0.0
physical reads direct temporary 0 0.0 0.0
physical reads prefetch warmup 0 0.0 0.0
physical write IO requests 103,975 9.6 2.2
physical writes 183,247 16.9 3.9
physical writes direct 29,157 2.7 0.6
physical writes direct (lob) 29,001 2.7 0.6
physical writes direct temporary 0 0.0 0.0
physical writes from cache 154,090 14.2 3.3
physical writes non checkpoint 153,688 14.2 3.2
pinned buffers inspected 105 0.0 0.0
prefetch warmup blocks aged out 0 0.0 0.0
prefetched blocks aged out befor 59 0.0 0.0
process last non-idle time ################## ############## #############
queries parallelized 0 0.0 0.0
recursive calls 13,277,702 1,226.6 280.0
recursive cpu usage 408,029 37.7 8.6
redo blocks written 2,477,742 228.9 52.2
redo buffer allocation retries 212 0.0 0.0
redo entries 1,425,989 131.7 30.1
redo log space requests 8 0.0 0.0
redo log space wait time 46 0.0 0.0
redo ordering marks 85,050 7.9 1.8
redo size 1,215,628,112 112,303.7 25,632.1
redo synch time 94,783 8.8 2.0
redo synch writes 42,590 3.9 0.9
redo wastage 13,740,880 1,269.4 289.7
redo write time 65,380 6.0 1.4
redo writer latching time 7 0.0 0.0
redo writes 51,475 4.8 1.1
rollback changes - undo records 20,096 1.9 0.4
rollbacks only - consistent read 4,285 0.4 0.1
rows fetched via callback 10,779,322 995.8 227.3
session connect time ################## ############## #############
session cursor cache hits 3,770,537 348.3 79.5
session logical reads 90,019,053 8,316.3 1,898.1
session uga memory 857,321,984 79,202.2 18,077.1
session uga memory max 7,078,309,872 653,917.1 149,249.6
shared hash latch upgrades - no 8,178,727 755.6 172.5
sorts (disk) 0 0.0 0.0
sorts (memory) 347,165 32.1 7.3
sorts (rows) 9,745,946 900.4 205.5
summed dirty queue length 399,925 37.0 8.4
switch current to new buffer 15,066 1.4 0.3
table fetch by rowid 54,156,582 5,003.2 1,141.9
table fetch continued row 42,937 4.0 0.9
table scan blocks gotten 17,321,177 1,600.2 365.2
table scan rows gotten 1,266,950,600 117,045.0 26,714.3
table scans (cache partitions) 0 0.0 0.0
Instance Activity Stats DB/Inst: PROD/prod Snaps: 29861-29864
Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
table scans (direct read) 0 0.0 0.0
table scans (long tables) 13 0.0 0.0
table scans (rowid ranges) 0 0.0 0.0
table scans (short tables) 220,334 20.4 4.7
transaction rollbacks 142 0.0 0.0
transaction tables consistent re 1 0.0 0.0
transaction tables consistent re 218 0.0 0.0
undo change vector size 438,430,192 40,503.6 9,244.5
user I/O wait time 769,559 71.1 16.2
user calls 9,922,245 916.7 209.2
user commits 41,492 3.8 0.9
user rollbacks 5,934 0.6 0.1
workarea executions - onepass 0 0.0 0.0
workarea executions - optimal 319,301 29.5 6.7
write clones created in backgrou 0 0.0 0.0
write clones created in foregrou 17 0.0 0.0
-------------------------------------------------------------
Instance Activity Stats - Absolute Values DB/Inst: PROD/prod Snaps: 29861-298
-> Statistics with absolute values (should not be diffed)
-> Statistics identified by '(derived)' come from sources other than SYSSTAT
Statistic Begin Value End Value
-------------------------------- --------------- ---------------
logons current 13,106 13,113
open threads (derived) 1 1
opened cursors current 2,010 2,082
session cursor cache count 1,593 1,884
workarea memory allocated 17,146 4,729
-------------------------------------------------------------
Instance Activity Stats - Thread Activity DB/Inst: PROD/prod Snaps: 29861-2986
Statistic Total per Hour
-------------------------------- ------------------ ---------
log switches (derived) 2 .67
------------------------------------------------------------- |
Oracle 10.1.0.3 - Time Model Statistics vs Instance Activity Statistics
Répondre avec citation
Partager