Bonjour,
qq'un connaiterait-il un site qui expliquerait comment interpéter un rapport statspack?
merci d'avance
Bonjour,
qq'un connaiterait-il un site qui expliquerait comment interpéter un rapport statspack?
merci d'avance
Peut-être le site référencé par Burleson Consulting et ex Oraperf (non testés).
Il y également des notes intéressantes sur le blog de Jonathan Lewis.
Idéalement il faudrait comparer les rapports avec un état du sytème "normal" et un état du système "lent".
Vous pouvez aussi poster votre rapport ici avec les bonnes balises de formatage.
en fait j'essai d'optimiser un traitement batch qui dure un peu moins de 30h.
j'ai lancé un cliché statspack avant le traitement et un autre à un peu plus de la moitié (le traitement est tjr en cours).
J'ai édité le rapport statspack mais j'ai du mal à interpréter les résultats.
qq'un peut-il m'aider? comment puis-je mettre à disposition du forum mon rapport?
Si le traitement est en cours, inutile de regarder le statspack. Toutes les infos sont dispo en live dans v$session_wait, v$system_event et autres tables du dictionnaire.
Statspack c'est plutôt pour trouver les maladies de la base, quand t'as un problème sur un traitement particulier une trace est plus utile
je sais mais j'ai pas encore préparé mes scripts pour requêter les vues dynamiques, j'aimerais juste interpréter dans un 1er temps mon rapport statspack pour voir pourquoi mon traitement batch est si long: est ce un pb de buffer cache?de tri?
Donnez-nous:
- la version d'Oracle
- la durée exacte du rapport Statspack
- le nombre de processeurs de la machine
- le rapport Statspack complet ou au moins les sections Cache Sizes, Load Profile, Instance Efficiency Percentages, Top 5 Timed Events, Events,
Instance Activity Stats, Tablespace IO Stats for DB, Buffer Pool Statistics for DB, Buffer Pool Advisory for DB, SGA Memory Summary for DB.
La bonne durée d'un rapport Statspack est 15 à 30 minutes.
le problème étant que si le rapport a été lancé durant une longue requête, on ne la verra pas
Enfin, au moins le top des events et waits pourraient être intéressant
la machine a 4 processeurs, c'est un solaris 10 64 bits avec 16Go de RAM
la base est une base 9.2.0.8
voici un extrait de mon rapport:
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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 STATSPACK report for DB Name DB Id Instance Inst Num Release Cluster Host ------------ ----------- ------------ -------- ----------- ------- ------------ PRISM 1511847528 prism 1 9.2.0.8.0 NO PRPDEF501 Snap Id Snap Time Sessions Curs/Sess Comment --------- ------------------ -------- --------- ------------------- Begin Snap: 9 18-Fev-08 15:59:19 68 4.3 End Snap: 10 19-Fev-08 09:51:27 68 6.1 Elapsed: 1,072.13 (mins) Cache Sizes (end) ~~~~~~~~~~~~~~~~~ Buffer Cache: 512M Std Block Size: 16K Shared Pool Size: 256M Log Buffer: 10,240K Load Profile ~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Redo size: 20,147.20 249,524.29 Logical reads: 26,849.96 332,538.31 Block changes: 3,553.65 44,012.21 Physical reads: 160.93 1,993.13 Physical writes: 29.08 360.16 User calls: 81.13 1,004.79 Parses: 460.60 5,704.57 Hard parses: 1.33 16.47 Sorts: 463.32 5,738.19 Logons: 0.70 8.70 Executes: 3,316.19 41,071.17 Transactions: 0.08 % Blocks changed per Read: 13.24 Recursive Call %: 98.58 Rollback per transaction %: 1.46 Rows per Sort: 28.18 Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 99.86 In-memory Sort %: 100.00 Library Hit %: 99.94 Soft Parse %: 99.71 Execute to Parse %: 86.11 Latch Hit %: 99.92 Parse CPU to Parse Elapsd %: 92.52 % Non-Parse CPU: 91.36 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 30.33 88.87 % SQL with executions>1: 59.21 96.96 % Memory for SQL w/exec>1: 61.07 95.25 Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Ela Time -------------------------------------------- ------------ ----------- -------- CPU time 14,604 60.42 db file sequential read 998,164 2,685 11.11 log file parallel write 167,783 1,459 6.04 log file sequential read 32,754 1,459 6.04 SQL*Net more data to client 2,135,088 1,191 4.93 ------------------------------------------------------------- Wait Events for DB: PRISM Instance: prism Snaps: 9 -10 -> 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 sequential read 998,164 0 2,685 3 192.2 log file parallel write 167,783 0 1,459 9 32.3 log file sequential read 32,754 0 1,459 45 6.3 SQL*Net more data to client 2,135,088 0 1,191 1 411.1 direct path read 529,464 0 1,028 2 101.9 db file parallel write 62,398 0 965 15 12.0 db file scattered read 100,843 0 407 4 19.4 log file sync 7,461 15 147 20 1.4 control file parallel write 26,319 0 54 2 5.1 latch free 27,685 121 37 1 5.3 direct path write 22,176 0 26 1 4.3 log file switch completion 656 2 22 34 0.1 enqueue 608 0 16 26 0.1 async disk IO 59,637 0 12 0 11.5 SQL*Net break/reset to clien 64,769 0 8 0 12.5 log buffer space 25 0 7 260 0.0 control file sequential read 37,491 0 3 0 7.2 log file single write 1,236 0 3 2 0.2 db file parallel read 24 0 1 61 0.0 local write wait 457 0 1 3 0.1 LGWR wait for redo copy 459 1 0 0 0.1 buffer busy waits 26 0 0 2 0.0 undo segment extension 52 50 0 0 0.0 SQL*Net message from client 6,884,729 0 507,784 74 1,325.5 SQL*Net message to client 6,884,728 0 8 0 1,325.5 SQL*Net more data from clien 3 0 0 0 0.0 ------------------------------------------------------------- Background Wait Events for DB: PRISM Instance: prism Snaps: 9 -10 -> 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 167,783 0 1,459 9 32.3 log file sequential read 32,754 0 1,459 45 6.3 db file parallel write 62,398 0 965 15 12.0 control file parallel write 26,319 0 54 2 5.1 db file scattered read 3,130 0 24 8 0.6 async disk IO 59,637 0 12 0 11.5 direct path write 12,320 0 6 0 2.4 db file sequential read 1,140 0 4 3 0.2 control file sequential read 36,551 0 3 0 7.0 log file single write 1,236 0 3 2 0.2 direct path read 12,320 0 0 0 2.4 log buffer space 3 0 0 120 0.0 latch free 37 21 0 9 0.0 rdbms ipc reply 576 0 0 0 0.1 LGWR wait for redo copy 459 1 0 0 0.1 buffer busy waits 8 0 0 0 0.0 rdbms ipc message 295,279 64,030 355,901 1205 56.9 pmon timer 21,372 21,372 62,755 2936 4.1 smon timer 527 197 60,681 ###### 0.1 -------------------------------------------------------------
et voici la suite:
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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 Instance Activity Stats for DB: PRISM Instance: prism Snaps: 9 -10 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ active txn count during cleanout 361,131 5.6 69.5 background checkpoints completed 308 0.0 0.1 background checkpoints started 309 0.0 0.1 background timeouts 78,166 1.2 15.1 branch node splits 110 0.0 0.0 buffer is not pinned count 707,284,575 10,995.0 136,173.4 buffer is pinned count 90,124,327 1,401.0 17,351.6 bytes received via SQL*Net from c 751,280,447 11,678.9 144,643.9 bytes sent via SQL*Net to client 2,069,224,446 32,166.8 398,387.5 Cached Commit SCN referenced 504,966 7.9 97.2 calls to get snapshot scn: kcmgss 336,504,933 5,231.1 64,787.2 calls to kcmgas 1,783,294 27.7 343.3 calls to kcmgcs 177,726 2.8 34.2 change write time 123,052 1.9 23.7 cleanout - number of ktugct calls 521,694 8.1 100.4 cleanouts and rollbacks - consist 44 0.0 0.0 cleanouts only - consistent read 159,708 2.5 30.8 cluster key scan block gets 356,240,770 5,537.9 68,587.0 cluster key scans 175,019,740 2,720.7 33,696.5 commit cleanout failures: block l 5,893 0.1 1.1 commit cleanout failures: buffer 3 0.0 0.0 commit cleanout failures: callbac 29 0.0 0.0 commit cleanout failures: cannot 3 0.0 0.0 commit cleanouts 431,222 6.7 83.0 commit cleanouts successfully com 425,294 6.6 81.9 Commit SCN cached 28 0.0 0.0 commit txn count during cleanout 263,190 4.1 50.7 consistent changes 144,455 2.3 27.8 consistent gets 1,398,162,122 21,734.9 269,187.9 consistent gets - examination 625,422,471 9,722.4 120,412.5 CPU used by this session 1,460,425 22.7 281.2 CPU used when call started 1,460,122 22.7 281.1 CR blocks created 35,787 0.6 6.9 current blocks converted for CR 18 0.0 0.0 cursor authentications 19,152 0.3 3.7 data blocks consistent reads - un 10,892,900 169.3 2,097.2 db block changes 228,599,441 3,553.7 44,012.2 db block gets 338,672,473 5,264.8 65,204.6 DBWR buffers scanned 652,445 10.1 125.6 DBWR checkpoint buffers written 1,380,804 21.5 265.9 DBWR checkpoints 917 0.0 0.2 DBWR free buffers found 542,051 8.4 104.4 DBWR lru scans 3,315 0.1 0.6 DBWR make free requests 4,101 0.1 0.8 DBWR revisited being-written buff 819 0.0 0.2 DBWR summed scan depth 652,445 10.1 125.6 DBWR transaction table writes 2,612 0.0 0.5 DBWR undo block writes 802,643 12.5 154.5 deferred (CURRENT) block cleanout 137,288 2.1 26.4 dirty buffers inspected 45,756 0.7 8.8 enqueue conversions 2,045 0.0 0.4 enqueue releases 761,123 11.8 146.5 enqueue requests 761,156 11.8 146.6 enqueue timeouts 29 0.0 0.0 enqueue waits 608 0.0 0.1 execute count 213,323,674 3,316.2 41,071.2 Instance Activity Stats for DB: PRISM Instance: prism Snaps: 9 -10 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ free buffer inspected 45,869 0.7 8.8 free buffer requested 4,717,811 73.3 908.3 hot buffers moved to head of LRU 996,539 15.5 191.9 immediate (CR) block cleanout app 159,752 2.5 30.8 immediate (CURRENT) block cleanou 290,270 4.5 55.9 index crx upgrade (found) 12 0.0 0.0 index crx upgrade (positioned) 266,612 4.1 51.3 index fast full scans (full) 49 0.0 0.0 index fetch by key 268,475,779 4,173.5 51,689.6 index scans kdiixs1 110,631,371 1,719.8 21,299.8 leaf node splits 102,772 1.6 19.8 leaf node 90-10 splits 41,001 0.6 7.9 logons cumulative 45,177 0.7 8.7 messages received 262,402 4.1 50.5 messages sent 262,402 4.1 50.5 no buffer to keep pinned count 0 0.0 0.0 no work - consistent read gets 649,021,728 10,089.3 124,956.1 opened cursors cumulative 29,577,601 459.8 5,694.6 parse count (failures) 12 0.0 0.0 parse count (hard) 85,568 1.3 16.5 parse count (total) 29,629,529 460.6 5,704.6 parse time cpu 126,224 2.0 24.3 parse time elapsed 136,436 2.1 26.3 physical reads 10,352,316 160.9 1,993.1 physical reads direct 7,906,988 122.9 1,522.3 physical writes 1,870,681 29.1 360.2 physical writes direct 303,926 4.7 58.5 physical writes non checkpoint 1,505,107 23.4 289.8 pinned buffers inspected 31 0.0 0.0 prefetched blocks 1,346,289 20.9 259.2 prefetched blocks aged out before 193 0.0 0.0 process last non-idle time 64,361 1.0 12.4 recursive calls 363,357,956 5,648.5 69,957.3 recursive cpu usage 5,295,892 82.3 1,019.6 redo blocks written 63,349,820 984.8 12,196.7 redo buffer allocation retries 701 0.0 0.1 redo entries 114,516,815 1,780.2 22,047.9 redo log space requests 732 0.0 0.1 redo log space wait time 2,290 0.0 0.4 redo ordering marks 54,572 0.9 10.5 redo size 1,296,029,152 20,147.2 249,524.3 redo synch time 15,147 0.2 2.9 redo synch writes 7,453 0.1 1.4 redo wastage 20,469,548 318.2 3,941.0 redo write time 149,664 2.3 28.8 redo writer latching time 7 0.0 0.0 redo writes 83,892 1.3 16.2 rollback changes - undo records a 284,391 4.4 54.8 rollbacks only - consistent read 63,758 1.0 12.3 rows fetched via callback 66,992,792 1,041.4 12,898.1 session connect time 0 0.0 0.0 session cursor cache count 175 0.0 0.0 session cursor cache hits 28,220,451 438.7 5,433.3 session logical reads 1,727,203,998 26,850.0 332,538.3 session pga memory 25,391,992 394.7 4,888.7 session pga memory max 253,238,480 3,936.7 48,756.0 Instance Activity Stats for DB: PRISM Instance: prism Snaps: 9 -10 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ session uga memory 615,414,136 9,566.8 118,485.6 session uga memory max 7,560,788,920 117,535.0 1,455,677.5 shared hash latch upgrades - no w 4,223,240 65.7 813.1 shared hash latch upgrades - wait 17 0.0 0.0 sorts (disk) 55 0.0 0.0 sorts (memory) 29,804,118 463.3 5,738.2 sorts (rows) 840,021,711 13,058.4 161,729.3 SQL*Net roundtrips to/from client 6,704,038 104.2 1,290.7 summed dirty queue length 413,906 6.4 79.7 switch current to new buffer 1,204,967 18.7 232.0 table fetch by rowid 181,851,365 2,826.9 35,011.8 table fetch continued row 59,320 0.9 11.4 table scan blocks gotten 78,252,478 1,216.5 15,065.9 table scan rows gotten 2,074,367,584 32,246.7 399,377.7 table scans (direct read) 8,615 0.1 1.7 table scans (long tables) 8,798 0.1 1.7 table scans (short tables) 61,469,815 955.6 11,834.8 transaction rollbacks 147 0.0 0.0 transaction tables consistent rea 19 0.0 0.0 transaction tables consistent rea 73,153 1.1 14.1 user calls 5,218,856 81.1 1,004.8 user commits 5,118 0.1 1.0 user rollbacks 76 0.0 0.0 workarea executions - multipass 1 0.0 0.0 workarea executions - onepass 61 0.0 0.0 workarea executions - optimal 58,133,245 903.7 11,192.4 write clones created in backgroun 10 0.0 0.0 write clones created in foregroun 15,149 0.2 2.9 ------------------------------------------------------------- Tablespace IO Stats for DB: PRISM Instance: prism Snaps: 9 -10 ->ordered by IOs (Reads + Writes) desc Tablespace ------------------------------ Av Av Av Av Buffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms) -------------- ------- ------ ------- ------------ -------- ---------- ------ UNDOTBS 253,132 4 2.4 1.0 806,605 13 23 0.0 PRISM_TB_DBO_TUNING_TAB 437,625 7 9.5 3.2 324,951 5 0 0.0 PRISM_TB_DBO_TUNING_IDX 277,352 4 2.2 1.0 429,646 7 0 0.0 PRISM_TB_DBO_DATA_TAB 101,064 2 28.8 9.2 914 0 0 0.0 PRISM_TB_TEMP 68,691 1 183.3 4.1 21,033 0 0 0.0 PRISM_TB_DBO_DATA_OCT07_TAB 48,081 1 34.8 15.3 973 0 0 0.0 PRISM_TB_DBO_DATA_SEP07_TAB 46,553 1 76.8 15.3 308 0 0 0.0 PRISM_TB_DBO_DATA_DEC06_TAB 40,863 1 113.1 15.3 308 0 0 0.0 PRISM_TB_DBO_JUIN07_ETU_TAB 40,048 1 73.4 15.2 308 0 0 0.0 PRISM_TB_DBO_JUIN06_ETU_TAB 39,139 1 96.0 15.3 308 0 0 0.0 PRISM_TB_DBO_NOV07_V402_TAB 38,864 1 69.5 15.3 310 0 0 0.0 PRISM_TB_DBO_DATA_JUIN07_TAB 36,516 1 72.8 15.2 308 0 0 0.0 PRISM_TB_DBO_DEC06_ETU_TAB 34,735 1 70.7 15.2 308 0 0 0.0 PRISM_TB_DBO_DEC07_ETU_TAB 31,298 0 67.1 15.0 627 0 0 0.0 SYSTEM 28,549 0 6.0 1.7 2,944 0 3 13.3 PRISM_TB_DBO_DATA_NOV07_TAB 30,357 0 57.6 14.6 308 0 0 0.0 PRISM_TB_DBO_OCT07_V402_TAB 28,628 0 63.1 15.0 308 0 0 0.0 PRISM_TB_DBO_SEP07_V402_TAB 28,263 0 64.2 15.1 308 0 0 0.0 PRISM_TB_DBO_DATA_JUIN06_TAB 24,779 0 80.5 15.3 308 0 0 0.0 PRISM_TB_DBO_DATA_IDX 20,372 0 1.4 1.4 3,037 0 0 0.0 PRISM_ADMIN 947 0 3.6 3.4 341 0 0 0.0 PRISM_TB_DBO_DEC07_ETU_IDX 831 0 2.8 1.0 308 0 0 0.0 TB_PERFSTAT 655 0 3.9 1.9 436 0 0 0.0 PRISM_TB_DBO_DATA_NOV07_IDX 563 0 3.3 2.5 308 0 0 0.0 PRISM_TB_DBO_DATA_SEP07_IDX 372 0 1.5 1.0 335 0 0 0.0 PRISM_TB_DBO_DATA_TMP 327 0 9.4 13.7 309 0 0 0.0 Tablespace IO Stats for DB: PRISM Instance: prism Snaps: 9 -10 ->ordered by IOs (Reads + Writes) desc Tablespace ------------------------------ Av Av Av Av Buffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms) -------------- ------- ------ ------- ------------ -------- ---------- ------ PRISM_TB_DBO_NOV07_V402_IDX 315 0 1.4 1.0 308 0 0 0.0 PRISM_TB_DBO_OCT07_V402_IDX 315 0 2.1 1.0 308 0 0 0.0 PRISM_TB_DBO_SEP07_V402_IDX 315 0 2.4 1.0 308 0 0 0.0 PRISM_TB_DBO_JUIN06_ETU_IDX 314 0 1.2 1.0 308 0 0 0.0 PRISM_TB_DBO_DATA_JUIN07_IDX 313 0 3.0 1.0 308 0 0 0.0 PRISM_TB_DBO_DATA_OCT07_IDX 313 0 2.0 1.0 308 0 0 0.0 PRISM_TB_DBO_DEC06_ETU_IDX 313 0 3.3 1.0 308 0 0 0.0 PRISM_TB_DBO_JUIN07_ETU_IDX 313 0 1.6 1.0 308 0 0 0.0 PATROL_DATA 309 0 0.9 1.0 308 0 0 0.0 PRISM_TB_DBO_DATA_DEC06_IDX 309 0 2.4 1.0 308 0 0 0.0 PRISM_TB_DBO_DATA_REST_IDX 309 0 2.1 1.0 308 0 0 0.0 PRISM_TB_DBO_DATA_REST_TAB 309 0 1.0 1.0 308 0 0 0.0 PRISM_TB_LARGE_RS 309 0 0.6 1.0 308 0 0 0.0 PRISM_TB_RBS 309 0 2.1 1.0 308 0 0 0.0 PRISM_TB_USER 309 0 2.1 1.0 308 0 0 0.0 PATROL_TMP 1 0 30.0 1.0 0 0 0 0.0 PRISM_ADMIN_TMP 1 0 40.0 1.0 0 0 0 0.0 PRISM_TB_DBO_DATA_DEC06_TMP 1 0 50.0 1.0 0 0 0 0.0 PRISM_TB_DBO_DATA_JUIN06_TMP 1 0 30.0 1.0 0 0 0 0.0 PRISM_TB_DBO_DATA_JUIN07_TMP 1 0 40.0 1.0 0 0 0 0.0 PRISM_TB_DBO_DEC06_ETU_TMP 1 0 80.0 1.0 0 0 0 0.0 PRISM_TB_DBO_JUIN06_ETU_TMP 1 0 10.0 1.0 0 0 0 0.0 PRISM_TB_DBO_JUIN07_ETU_TMP 1 0 50.0 1.0 0 0 0 0.0 PRISM_TB_USER_TMP 1 0 20.0 1.0 0 0 0 0.0 ------------------------------------------------------------- File IO Stats for DB: PRISM Instance: prism Snaps: 9 -10 ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- Av Av Av Av Buffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms) -------------- ------- ------ ------- ------------ -------- ---------- ------ PATROL_DATA /oradata/prism/dbs/patrol_data01.dbf 309 0 0.9 1.0 308 0 0 PATROL_TMP /oradata/prism/dbs/patrol_tmp01.dbf 1 0 30.0 1.0 0 0 0 PRISM_ADMIN /oradata/prism/dbs/PRISM_ADMIN.dbf 947 0 3.6 3.4 341 0 0 PRISM_ADMIN_TMP /oradata/prism/dbs/PRISM_ADMIN_TMP.dbf 1 0 40.0 1.0 0 0 0 PRISM_TB_DBO_DATA_DEC06_ /oradata/prism/dbs/PRISM_TB_DBO_DATA_DEC06_IDX.dbf 309 0 2.4 1.0 308 0 0 PRISM_TB_DBO_DATA_DEC06_ /oradata/prism/dbs/PRISM_TB_DBO_DATA_DEC06_TAB.dbf 40,863 1 113.1 15.3 308 0 0 PRISM_TB_DBO_DATA_DEC06_ /oradata/prism/dbs/PRISM_TB_DBO_DATA_DEC06_TMP.dbf 1 0 50.0 1.0 0 0 0 PRISM_TB_DBO_DATA_IDX /oradata/prism/dbs/PRISM_TB_DBO_DATA_IDX.dbf 20,372 0 1.4 1.4 3,037 0 0 PRISM_TB_DBO_DATA_JUIN06 /oradata/prism/dbs/PRISM_TB_DBO_DATA_JUIN06_TAB.dbf 24,779 0 80.5 15.3 308 0 0 PRISM_TB_DBO_DATA_JUIN06 /oradata/prism/dbs/PRISM_TB_DBO_DATA_juin06_TMP.dbf 1 0 30.0 1.0 0 0 0 PRISM_TB_DBO_DATA_JUIN07 /oradata/prism/dbs/PRISM_TB_DBO_DATA_JUIN07_IDX.dbf 313 0 3.0 1.0 308 0 0 PRISM_TB_DBO_DATA_JUIN07 /oradata/prism/dbs/PRISM_TB_DBO_DATA_JUIN07_TAB.dbf 36,516 1 72.8 15.2 308 0 0 PRISM_TB_DBO_DATA_JUIN07 /oradata/prism/dbs/PRISM_TB_DBO_DATA_JUIN07_TMP.dbf 1 0 40.0 1.0 0 0 0 PRISM_TB_DBO_DATA_NOV07_ /oradata/prism/dbs/PRISM_TB_DBO_DATA_NOV07_IDX.dbf 563 0 3.3 2.5 308 0 0 PRISM_TB_DBO_DATA_NOV07_ /oradata/prism/dbs/PRISM_TB_DBO_DATA_NOV07_TAB.dbf 30,357 0 57.6 14.6 308 0 0 PRISM_TB_DBO_DATA_OCT07_ /oradata/prism/dbs/PRISM_TB_DBO_DATA_OCT07_IDX.dbf 313 0 2.0 1.0 308 0 0 PRISM_TB_DBO_DATA_OCT07_ /oradata/prism/dbs/PRISM_TB_DBO_DATA_OCT07_TAB.dbf 48,081 1 34.8 15.3 973 0 0 PRISM_TB_DBO_DATA_REST_I /oradata/prism/dbs/prism_tb_DBO_DATA_REST_idx.dbf File IO Stats for DB: PRISM Instance: prism Snaps: 9 -10 ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- Av Av Av Av Buffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms) -------------- ------- ------ ------- ------------ -------- ---------- ------ 309 0 2.1 1.0 308 0 0 PRISM_TB_DBO_DATA_REST_T /oradata/prism/dbs/prism_tb_DBO_DATA_REST_tab.dbf 309 0 1.0 1.0 308 0 0 PRISM_TB_DBO_DATA_SEP07_ /oradata/prism/dbs/PRISM_TB_DBO_DATA_SEP07_IDX.dbf 372 0 1.5 1.0 335 0 0 PRISM_TB_DBO_DATA_SEP07_ /oradata/prism/dbs/PRISM_TB_DBO_DATA_SEP07_TAB.dbf 46,553 1 76.8 15.3 308 0 0 PRISM_TB_DBO_DATA_TAB /oradata/prism/dbs/PRISM_TB_DBO_DATA_TAB.dbf 101,064 2 28.8 9.2 914 0 0 PRISM_TB_DBO_DATA_TMP /oradata/prism/dbs/PRISM_TB_DBO_DATA_TMP.dbf 327 0 9.4 13.7 309 0 0 PRISM_TB_DBO_DEC06_ETU_I /oradata/prism/dbs/PRISM_TB_DBO_DEC06_ETU_IDX.dbf 313 0 3.3 1.0 308 0 0 PRISM_TB_DBO_DEC06_ETU_T /oradata/prism/dbs/PRISM_TB_DBO_DEC06_ETU_TAB.dbf 34,735 1 70.7 15.2 308 0 0 PRISM_TB_DBO_DEC06_ETU_T /oradata/prism/dbs/PRISM_TB_DBO_DEC06_ETU_TMP.dbf 1 0 80.0 1.0 0 0 0 PRISM_TB_DBO_DEC07_ETU_I /oradata/prism/dbs/PRISM_TB_DBO_DEC07_ETU_IDX.dbf 831 0 2.8 1.0 308 0 0 PRISM_TB_DBO_DEC07_ETU_T /oradata/prism/dbs/PRISM_TB_DBO_DEC07_ETU_TAB.dbf 31,298 0 67.1 15.0 627 0 0 PRISM_TB_DBO_JUIN06_ETU_ /oradata/prism/dbs/PRISM_TB_DBO_JUIN06_ETU_IDX.dbf 314 0 1.2 1.0 308 0 0 PRISM_TB_DBO_JUIN06_ETU_ /oradata/prism/dbs/PRISM_TB_DBO_JUIN06_ETU_TAB.dbf 39,139 1 96.0 15.3 308 0 0 PRISM_TB_DBO_JUIN06_ETU_ /oradata/prism/dbs/PRISM_TB_DBO_JUIN06_ETU_TMP.dbf 1 0 10.0 1.0 0 0 0 PRISM_TB_DBO_JUIN07_ETU_ /oradata/prism/dbs/PRISM_TB_DBO_JUIN07_ETU_IDX.dbf 313 0 1.6 1.0 308 0 0 PRISM_TB_DBO_JUIN07_ETU_ /oradata/prism/dbs/PRISM_TB_DBO_JUIN07_ETU_TAB.dbf 40,048 1 73.4 15.2 308 0 0 PRISM_TB_DBO_JUIN07_ETU_ /oradata/prism/dbs/PRISM_TB_DBO_JUIN07_ETU_TMP.dbf 1 0 50.0 1.0 0 0 0 PRISM_TB_DBO_NOV07_V402_ /oradata/prism/dbs/PRISM_TB_DBO_NOV07_V402_IDX.dbf 315 0 1.4 1.0 308 0 0 File IO Stats for DB: PRISM Instance: prism Snaps: 9 -10 ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- Av Av Av Av Buffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms) -------------- ------- ------ ------- ------------ -------- ---------- ------ PRISM_TB_DBO_NOV07_V402_ /oradata/prism/dbs/PRISM_TB_DBO_NOV07_V402_TAB.dbf 38,864 1 69.5 15.3 310 0 0 PRISM_TB_DBO_OCT07_V402_ /oradata/prism/dbs/PRISM_TB_DBO_OCT07_V402_IDX.dbf 315 0 2.1 1.0 308 0 0 PRISM_TB_DBO_OCT07_V402_ /oradata/prism/dbs/PRISM_TB_DBO_OCT07_V402_TAB.dbf 28,628 0 63.1 15.0 308 0 0 PRISM_TB_DBO_SEP07_V402_ /oradata/prism/dbs/PRISM_TB_DBO_SEP07_V402_IDX.dbf 315 0 2.4 1.0 308 0 0 PRISM_TB_DBO_SEP07_V402_ /oradata/prism/dbs/PRISM_TB_DBO_SEP07_V402_TAB.dbf 28,263 0 64.2 15.1 308 0 0 PRISM_TB_DBO_TUNING_IDX /oradata/prism/dbs/PRISM_TB_DBO_TUNING_IDX.dbf 277,352 4 2.2 1.0 429,646 7 0 PRISM_TB_DBO_TUNING_TAB /oradata/prism/dbs/PRISM_TB_DBO_TUNING_TAB.dbf 437,625 7 9.5 3.2 324,951 5 0 PRISM_TB_LARGE_RS /oradata/prism/dbs/PRISM_TB_LARGE_RS.dbf 309 0 0.6 1.0 308 0 0 PRISM_TB_RBS /oradata/prism/dbs/PRISM_TB_RBS_01.dbf 309 0 2.1 1.0 308 0 0 PRISM_TB_TEMP /oradata/prism/dbs/temp01.dbf 68,691 1 183.3 4.1 21,033 0 0 PRISM_TB_USER /oradata/prism/dbs/PRISM_TB_USER.dbf 309 0 2.1 1.0 308 0 0 PRISM_TB_USER_TMP /oradata/prism/dbs/PRISM_TB_USER_TMP.dbf 1 0 20.0 1.0 0 0 0 SYSTEM /oradata/prism/dbs/system01.dbf 28,549 0 6.0 1.7 2,944 0 3 13.3 TB_PERFSTAT /oradata/prism/dbs/TB_PERFSTAT.dbf 655 0 3.9 1.9 436 0 0 UNDOTBS /oradata/prism/dbs/undotbs01.dbf 126,961 2 2.4 1.0 405,343 6 23 0.0 /oradata/prism/dbs/undotbs02.dbf 126,171 2 2.4 1.0 401,262 6 0 -------------------------------------------------------------
voici la partie advice:
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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 Buffer Pool Statistics for DB: PRISM Instance: prism Snaps: 9 -10 -> Standard block size Pools D: default, K: keep, R: recycle -> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k Free Write Buffer Number of Cache Buffer Physical Physical Buffer Complete Busy P Buffers Hit % Gets Reads Writes Waits Waits Waits --- ---------- ----- ----------- ----------- ---------- ------- -------- ------ D 32,256 99.9############ 2,445,298 1,566,755 0 0 26 ------------------------------------------------------------- Instance Recovery Stats for DB: PRISM Instance: prism Snaps: 9 -10 -> B: Begin snapshot, E: End snapshot Targt Estd Log File Log Ckpt Log Ckpt MTTR MTTR Recovery Actual Target Size Timeout Interval (s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks - ----- ----- ---------- ---------- ---------- ---------- ---------- ---------- B 21 8 156 454 306 184320 306 E 21 18 3135 188086 184320 184320 550027 ------------------------------------------------------------- Buffer Pool Advisory for DB: PRISM Instance: prism End Snap: 10 -> Only rows with estimated physical reads >0 are displayed -> ordered by Block Size, Buffers For Estimate (default block size first) Size for Size Buffers for Est Physical Estimated P Estimate (M) Factr Estimate Read Factor Physical Reads --- ------------ ----- ---------------- ------------- ------------------ D 48 .1 3,024 3.38 14,529,386 D 96 .2 6,048 2.65 11,411,544 D 144 .3 9,072 2.20 9,472,745 D 192 .4 12,096 1.94 8,332,931 D 240 .5 15,120 1.75 7,545,935 D 288 .6 18,144 1.55 6,683,289 D 336 .7 21,168 1.34 5,751,512 D 384 .8 24,192 1.22 5,247,557 D 432 .8 27,216 1.15 4,927,998 D 480 .9 30,240 1.07 4,597,030 D 512 1.0 32,256 1.00 4,300,773 D 528 1.0 33,264 0.97 4,163,254 D 576 1.1 36,288 0.88 3,771,225 D 624 1.2 39,312 0.83 3,576,759 D 672 1.3 42,336 0.80 3,461,880 D 720 1.4 45,360 0.78 3,364,446 D 768 1.5 48,384 0.76 3,269,819 D 816 1.6 51,408 0.75 3,208,725 D 864 1.7 54,432 0.73 3,144,903 D 912 1.8 57,456 0.72 3,086,714 D 960 1.9 60,480 0.71 3,058,377 ------------------------------------------------------------- Buffer wait Statistics for DB: PRISM Instance: prism Snaps: 9 -10 -> ordered by wait time desc, waits desc Tot Wait Avg Class Waits Time (s) Time (ms) ------------------ ----------- ---------- --------- data block 3 0 13 undo header 23 0 0 ------------------------------------------------------------- PGA Aggr Target Stats for DB: PRISM Instance: prism Snaps: 9 -10 -> 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 --------------- ---------------- ------------------------- 96.4 178,505 6,638 %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 1,024 892 63.1 0.0 .0 .0 .0 52,428 E 1,024 888 88.8 18.3 20.6 100.0 .0 52,428 ------------------------------------------------------------- PGA Aggr Target Histogram for DB: PRISM Instance: prism Snaps: 9 -10 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- ------------- ------------ ------------ 2K 4K 57,920,457 57,920,457 0 0 4K 8K 60,489 60,489 0 0 8K 16K 76,836 76,836 0 0 16K 32K 73,752 73,752 0 0 32K 64K 1,107 1,107 0 0 64K 128K 831 831 0 0 128K 256K 406 406 0 0 256K 512K 98 98 0 0 512K 1024K 80 80 0 0 1M 2M 31 31 0 0 2M 4M 12 10 2 0 4M 8M 24 16 8 0 8M 16M 24 11 13 0 16M 32M 17 2 15 0 32M 64M 10 5 5 0 64M 128M 9 0 9 0 128M 256M 4 0 4 0 256M 512M 1 0 0 1 512M 1024M 5 0 5 0 ------------------------------------------------------------- PGA Memory Advisory for DB: PRISM Instance: prism End Snap: 10 -> 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 ---------- ------- ---------------- ---------------- -------- ---------- 128 0.1 194,494.7 40,051.7 83.0 0 256 0.3 194,494.7 29,045.2 87.0 0 512 0.5 194,494.7 27,744.1 88.0 0 768 0.8 194,494.7 27,323.5 88.0 0 1,024 1.0 194,494.7 25,785.7 88.0 0 1,229 1.2 194,494.7 9,711.5 95.0 0 1,434 1.4 194,494.7 9,711.5 95.0 0 1,638 1.6 194,494.7 9,264.9 95.0 0 1,843 1.8 194,494.7 7,775.9 96.0 0 2,048 2.0 194,494.7 7,309.5 96.0 0 3,072 3.0 194,494.7 7,309.5 96.0 0 4,096 4.0 194,494.7 7,309.5 96.0 0 6,144 6.0 194,494.7 7,309.5 96.0 0 8,192 8.0 194,494.7 7,309.5 96.0 0 ------------------------------------------------------------- Enqueue activity for DB: PRISM Instance: prism Snaps: 9 -10 -> 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) -- ------------ ------------ ----------- ----------- ------------- ------------ TC 3,040 3,040 0 608 26.45 16 -------------------------------------------------------------
voici la fin:
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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 Dictionary Cache Stats for DB: PRISM Instance: prism Snaps: 9 -10 ->"Pct Misses" should be very low (< 2% in most cases) ->"Cache Usage" is the number of cache entries being used ->"Pct SGA" is the ratio of usage to allocated size for that cache Get Pct Scan Pct Mod Final Cache Requests Miss Reqs Miss Reqs Usage ------------------------- ------------ ------ ------- ----- -------- ---------- dc_constraints 9 44.4 0 9 1 dc_files 39 100.0 0 0 0 dc_global_oids 29 37.9 0 0 12 dc_histogram_data 6,181 80.6 0 1,284 3,955 dc_histogram_data_values 575 76.7 0 109 354 dc_histogram_defs 257,429 31.5 0 5,446 42,063 dc_object_ids 976,824 1.0 0 1 9,897 dc_objects 155,457 5.7 0 410 9,493 dc_profiles 45,177 0.0 0 0 1 dc_rollback_segments 6,253 0.0 0 0 15 dc_segments 388,333 4.6 0 16,845 13,971 dc_sequences 93,967 0.0 0 93,967 4 dc_table_scns 1 100.0 0 0 0 dc_tablespace_quotas 16,574 0.0 0 16,574 7 dc_tablespaces 327,244 0.0 0 0 21 dc_user_grants 305,732 0.0 0 0 28 dc_usernames 121,353 0.0 0 0 24 dc_users 535,491 0.0 0 0 32 ------------------------------------------------------------- Library Cache Activity for DB: PRISM Instance: prism Snaps: 9 -10 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- BODY 155 38.1 155 41.3 3 0 CLUSTER 1,146 0.6 1,075 1.3 0 0 INDEX 376 47.9 374 48.1 0 0 SQL AREA 1,409,744 6.0 244,821,497 0.1 822 1,772 TABLE/PROCEDURE 6,014,453 0.2 99,117,166 0.0 2,036 0 TRIGGER 341 7.9 341 9.1 4 0 ------------------------------------------------------------- Shared Pool Advisory for DB: PRISM Instance: prism End Snap: 10 -> Note there is often a 1:Many correlation between a single logical object in the Library Cache, and the physical number of memory objects associated with it. Therefore comparing the number of Lib Cache objects (e.g. in v$librarycache), with the number of Lib Cache Memory Objects is invalid Estd Shared Pool SP Estd Estd Estd Lib LC Time Size for Size Lib Cache Lib Cache Cache Time Saved Estd Lib Cache Estim (M) Factr Size (M) Mem Obj Saved (s) Factr Mem Obj Hits ----------- ----- ---------- ------------ ------------ ------- --------------- 128 .5 131 31,031 38,393 1.0 24,532,781 160 .6 162 38,861 38,394 1.0 24,532,834 192 .8 193 46,685 38,394 1.0 24,532,927 224 .9 224 54,518 38,394 1.0 24,533,121 256 1.0 255 62,384 38,394 1.0 24,533,293 288 1.1 286 70,624 38,400 1.0 24,536,038 320 1.3 313 74,619 38,406 1.0 24,538,471 352 1.4 313 74,619 38,406 1.0 24,538,471 384 1.5 313 74,619 38,406 1.0 24,538,471 416 1.6 313 74,619 38,406 1.0 24,538,471 448 1.8 313 74,619 38,406 1.0 24,538,471 480 1.9 313 74,619 38,406 1.0 24,538,471 512 2.0 313 74,619 38,406 1.0 24,538,471 ------------------------------------------------------------- SGA Memory Summary for DB: PRISM Instance: prism Snaps: 9 -10 SGA regions Size in Bytes ------------------------------ ---------------- Database Buffers 536,870,912 Fixed Size 729,976 Redo Buffers 10,772,480 Variable Size 352,321,536 ---------------- sum 900,694,904 ------------------------------------------------------------- SGA breakdown difference for DB: PRISM Instance: prism Snaps: 9 -10 Pool Name Begin value End value % Diff ------ ------------------------------ ---------------- ---------------- ------- java free memory 33,554,432 33,554,432 0.00 large free memory 16,777,216 16,777,216 0.00 shared Checkpoint queue 1,026,560 1,026,560 0.00 shared dictionary cache 4,274,432 4,274,432 0.00 shared enqueue 443,976 443,976 0.00 shared errors 7,296 60,536 729.71 shared event statistics per sess 2,557,800 2,557,800 0.00 shared FileOpenBlock 1,563,248 1,563,248 0.00 shared fixed allocation callback 2,120 2,120 0.00 shared free memory 210,402,696 33,597,040 -84.03 shared joxs heap init 4,240 4,240 0.00 shared KGK heap 7,000 7,000 0.00 shared KGLS heap 9,082,568 44,113,472 385.69 shared KQR L PO 2,751,656 19,887,536 622.75 shared KQR L SO 69,632 372,912 435.55 shared KQR M PO 2,197,016 30,215,792 ####### shared KQR M SO 278,016 2,085,752 650.23 shared KQR S SO 6,648 7,416 11.55 shared ksm_file2sga region 370,496 370,496 0.00 shared KSXR pending messages que 853,952 853,952 0.00 shared KSXR receive buffers 1,034,000 1,034,000 0.00 shared library cache 14,840,552 70,958,008 378.14 shared message pool freequeue 771,984 771,984 0.00 shared miscellaneous 16,619,280 31,786,880 91.27 shared MTTR advisory 106,552 106,552 0.00 shared parameters 42,560 33,328 -21.69 shared PLS non-lib hp 2,088 2,088 0.00 shared PL/SQL DIANA 1,537,696 1,080,376 -29.74 shared PL/SQL MPCODE 1,231,256 2,296,936 86.55 shared sessions 608,400 608,400 0.00 shared sim memory hea 260,080 260,080 0.00 shared sql area 25,828,744 49,096,552 90.08 shared table definiti 9,888 2,576 -73.95 shared transaction 405,504 405,504 0.00 shared trigger defini 6,656 3,056 -54.09 shared trigger inform 1,240 1,112 -10.32 shared 1M buffer 2,098,176 2,098,176 0.00 buffer_cache 536,870,912 536,870,912 0.00 fixed_sga 729,976 729,976 0.00 log_buffer 10,748,928 10,748,928 0.00 ------------------------------------------------------------- init.ora Parameters for DB: PRISM Instance: prism Snaps: 9 -10 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- background_dump_dest /oradata/prism/bdump compatible 9.2.0 control_files /oradata/prism/ctl/control01.ctl, core_dump_dest /oradata/prism/cdump db_block_size 16384 db_cache_size 536870912 db_file_multiblock_read_count 16 db_name prism dml_locks 100 fast_start_mttr_target 300 job_queue_processes 2 large_pool_size 16777216 log_archive_dest /dbo_data/archive/archive_log log_archive_format arch_prism_%s.arc log_archive_start TRUE log_buffer 10485760 max_dump_file_size 10240 nls_date_format DD-MM-YYYY nls_language FRENCH nls_numeric_characters , nls_territory FRANCE open_cursors 1000 os_authent_prefix pga_aggregate_target 1073741824 processes 200 query_rewrite_enabled TRUE query_rewrite_integrity TRUSTED remote_login_passwordfile NONE session_cached_cursors 50 shared_pool_size 268435456 sort_area_size 0 timed_statistics TRUE transaction_auditing FALSE undo_management AUTO undo_retention 3600 undo_tablespace UNDOTBS user_dump_dest /oradata/prism/udump utl_file_dir /dbo_data/log/oracle workarea_size_policy AUTO ------------------------------------------------------------- End of Report
quelle est la taille des REDO et la fréquence de switch ? As-tu bien mis les redos et leur mirroir sur des disques différents ? Cette instance est-elle répliquée via Dataguard par exemple ?
le rapport porte sur des snapshots trop éloignés aussi... faudrait en refaire 2 avec seulement 30 minutes d'intervalle.
Tiens, regarde ce tuto : http://mbouayoun.developpez.com/reglredo
Je pense qu'il pourrait t'aider
enfin avec un CPU Time de 60% on aura du mal à voir quelque chose de probant Faudrait vraiment un rapport sur une période plus courte.
les redo font 100 Mo
la fréquence de switch varie entre 3 et 6 minutes et sont tous sur le même disk.
Il n'y a pas de Dataguard.
je sais que le switch devrait se faire toutes les 20/30 minutes.
Comment as-tu su à partir du rapport statspack qu'il y'avait un souci au niveau des redo? Pourquoi un intervalle de 30 minutes pour le rapport statspack?
20 minutes c'est trop long... 3/6 minutes c'est très bien
j'ai suspecté les redos en regardant les événements d'attente :
CPU Time n'est pas significatif, db file sequential READ c'est de l'accès par index, et en continuant je trouve 2 événements liés aux logs.Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 14,604 60.42
db file sequential READ 998,164 2,685 11.11
log file parallel WRITE 167,783 1,459 6.04
log file sequential READ 32,754 1,459 6.04
SQL*Net more DATA TO client 2,135,088 1,191 4.93
Mais au regard de ta réponse j'vois pas où il pourrait y avoir souci... encore une fois, la période est trop longue pour se faire une idée
c bizarre que tu me dises ça.3/6 minutes c'est très bien
j'étais en formation TUNING chez Oracle fin Janvier et le formateur m'a dit que c'était trop court et qu'il fallait compter environ 20 à 30 minutes entre chaque switch.
Autre question: le tutoriel sur le réglage des redo tu l'as trouvé où? parceque dans la page des tutoriels Oracle il n'y figure pas.
je suis en train de générer un rapport entre 2 snapshot de 30 minutes.
Voici un rapport statspack avec un interval de 30 minutes:
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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 STATSPACK report for DB Name DB Id Instance Inst Num Release Cluster Host ------------ ----------- ------------ -------- ----------- ------- ------------ PRISM 1511847528 prism 1 9.2.0.8.0 NO PRPDEF501 Snap Id Snap Time Sessions Curs/Sess Comment --------- ------------------ -------- --------- ------------------- Begin Snap: 11 19-Fev-08 11:17:20 71 5.3 End Snap: 12 19-Fev-08 11:46:36 70 5.4 Elapsed: 29.27 (mins) Cache Sizes (end) ~~~~~~~~~~~~~~~~~ Buffer Cache: 512M Std Block Size: 16K Shared Pool Size: 256M Log Buffer: 10,240K Load Profile ~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Redo size: -1,655,696.19 -41,536.10 Logical reads: 37,289.03 935.46 Block changes: 5,296.25 132.87 Physical reads: 284.46 7.14 Physical writes: 49.63 1.25 User calls: 82.74 2.08 Parses: 57.19 1.43 Hard parses: 21.11 0.53 Sorts: 94.76 2.38 Logons: 0.71 0.02 Executes: 5,147.05 129.12 Transactions: 39.86 % Blocks changed per Read: 14.20 Recursive Call %: 98.99 Rollback per transaction %: 0.00 Rows per Sort: 103.84 Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 99.24 In-memory Sort %: 100.00 Library Hit %: 99.29 Soft Parse %: 63.09 Execute to Parse %: 98.89 Latch Hit %: 99.92 Parse CPU to Parse Elapsd %: 96.15 % Non-Parse CPU: 99.88 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 90.98 89.88 % SQL with executions>1: 6.38 3.76 % Memory for SQL w/exec>1: 6.55 8.45 Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Ela Time -------------------------------------------- ------------ ----------- -------- CPU time 64,505 99.50 log file parallel write 142,911 134 .21 db file scattered read 30,563 66 .10 db file parallel write 3,869 43 .07 db file sequential read 57,730 38 .06 ------------------------------------------------------------- Wait Events for DB: PRISM Instance: prism Snaps: 11 -12 -> 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 ---------------------------- ------------ ---------- ---------- ------ -------- log file parallel write 142,911 0 134 1 2.0 db file scattered read 30,563 0 66 2 0.4 db file parallel write 3,869 0 43 11 0.1 db file sequential read 57,730 0 38 1 0.8 log file sequential read 1,484 0 31 21 0.0 latch free 5,008 12 2 0 0.1 log file sync 208 0 2 11 0.0 log buffer space 6 0 2 298 0.0 async disk IO 2,895 0 1 0 0.0 control file parallel write 810 0 1 1 0.0 direct path write 788 0 1 1 0.0 log file switch completion 28 0 1 18 0.0 SQL*Net more data to client 1,660 0 0 0 0.0 control file sequential read 1,348 0 0 0 0.0 log file single write 56 0 0 1 0.0 direct path read 753 0 0 0 0.0 SQL*Net break/reset to clien 2 0 0 6 0.0 local write wait 2 0 0 2 0.0 LGWR wait for redo copy 77 0 0 0 0.0 db file single write 1 0 0 1 0.0 SQL*Net message from client 144,036 0 21,524 149 2.1 SQL*Net message to client 144,034 0 0 0 2.1 ------------------------------------------------------------- Background Wait Events for DB: PRISM Instance: prism Snaps: 11 -12 -> 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 142,910 0 134 1 2.0 db file parallel write 3,869 0 43 11 0.1 log file sequential read 1,484 0 31 21 0.0 control file parallel write 808 0 1 1 0.0 async disk IO 2,702 0 1 0 0.0 log buffer space 2 0 1 250 0.0 db file scattered read 146 0 0 2 0.0 direct path write 560 0 0 0 0.0 db file sequential read 56 0 0 2 0.0 control file sequential read 1,240 0 0 0 0.0 log file single write 56 0 0 1 0.0 rdbms ipc reply 45 0 0 0 0.0 latch free 5 1 0 4 0.0 LGWR wait for redo copy 77 0 0 0 0.0 direct path read 560 0 0 0 0.0 rdbms ipc message 117,285 1,559 10,032 86 1.7 smon timer 9 4 1,718 ###### 0.0 pmon timer 583 583 1,713 2937 0.0
Non car:
Vous avez 3316 * 60 * 1072 = 213 285 120 requêtes exécutées pendant la durée analysée: c'est énorme !Buffer Hit %: 99.86 In-memory Sort %: 100.00
Vous avez environ 7 millions d'échanges entre l'instance et ses clients:
Comme le temps d'attente de 507 784 secondes est supérieure à la durée d'analyse, cela veut dire que le batch semble tourner en paralllèle avec des sessions d'utilisateurs qui utilisent l'application. Est-ce le cas ?
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2 SQL*Net message FROM client 6,884,729 0 507,784 74 1,325.5 SQL*Net message TO client 6,884,728 0 8 0 1,325.5
Il faudrait mettre le traitement batch du début à la fin en mode trace avec les temps d'attente pour avoir les détails au niveau du batch en lui-même avec:
Si le batch contribute pour une partie importante sur les 507 000 secondes d'attente, cela peut signifier que les programmes batch client travaillent plus que l'instance Oracle. Il est aussi possible ques les programmes batch exécutent trop de requêtes SQL qui à chaque fois retourne quelques lignes de données.
Code : Sélectionner tout - Visualiser dans une fenêtre à part ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'
C'est sans doute un problème mineur mais vous avez aussi des temps d'E/S qui sont parfois trop longs (70/80 voir 100 ms) sur certains tablespaces.
est-ce que ça veut dire qu'entre les 2 snapshots tous mes tris se font en mémoire et que 99.86% des fois que le process cherche une donnée il la trouve dans le buffer cache? Si oui pourquoi les vues dynamique V$DB_CACHE_ADVICE et V$PGA_TARGET_ADVICE me recommandent-elles de doubler le db_cache_size et le pga_aggregate_target????Buffer Hit %: 99.86 In-memory Sort %: 100.00
comment obtenez-vous ce chiffre?Vous avez 3316 * 60 * 1072 = 213 285 120 requêtes exécutées pendant la durée analysée: c'est énorme !
j'ai bcp de requêtes car mon traitement batch fais des boucles sur des tables et insère dans d'autres tables après avoir appliquer pas mal de règles de gestion.
oui c'est le cas. mon traitement batch s'execute sous le user DBO_TUNING, mais il y'a une IHM disponible aux utilisateurs qui effectuent certaines requetes sur d'autres shémas (mais rien de bien méchant). il y'a aussi un moteur de calcul JAVA qui se connecte et se deconnecte sans cesse de la base pour voir s'il y'a des traitements le concernant.Comme le temps d'attente de 507 784 secondes est supérieure à la durée d'analyse, cela veut dire que le batch semble tourner en paralllèle avec des sessions d'utilisateurs qui utilisent l'application. Est-ce le cas ?
où voyez-vous ça?vous avez aussi des temps d'E/S qui sont parfois trop longs (70/80 voir 100 ms) sur certains tablespaces
Dois je relancer mon traitement batch avec ce mode? et après? je récupère des snapshots et j'execute un rapport statspack ou bien ça n'a rien à voir?Il faudrait mettre le traitement batch du début à la fin en mode trace avec les temps d'attente pour avoir les détails au niveau du batch
Hello,
Tous ça me parle pas trop à part 2 ou 3 paramêtres à toucher, peut on voir dans ce même statpack les ordres sql ?
Merci
Vous avez un bloqueur de publicités installé.
Le Club Developpez.com n'affiche que des publicités IT, discrètes et non intrusives.
Afin que nous puissions continuer à vous fournir gratuitement du contenu de qualité, merci de nous soutenir en désactivant votre bloqueur de publicités sur Developpez.com.
Partager