| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Help: large "cache buffers lru chain", large performace degradation
Hello All,
I am running Oracle 7.3.2 on alpha. It runs Oracle Web server and there are around 10000 hits per day. All the data is stored in the database and the processing of the requests is done through pl/sql routines. Lately there is a very severe performance degradation during peak usage, checking the v$session event gives the following information
large number of total_waits and total_timeouts for the following
latch free (4 - 4259)
rdbms ipc message (29853 - 94551)
there are many timeouts for db file sequential read but are within 12 - 600
The v$session event table gives the following information (the noticable ones)
event total_wait total_timeout
----------------- ------------------------------- latch free 1192651 1166887 rdbms ipc mess 148032 69677 buffer busy waits 133389 354 free buffer waits 9932 1563
i noticed that the "latch free" keeps on increasing and the timeouts also along with it. The other values dont have so many timeouts (less than 1%).
After checking v$latch_children during a test, the
"cache buffers lru chain" has the highest number of
misses etc. There are also many "cache buffers chains".
i am trying to find out which table/index is causing the
contention. for cache buffer chains i can use the addr
field. how do i find out which one is causing the
"cache buffers lru chain" contention. I assume that
this is the key to the performance problem as the
spin_gets (484253) and sleeps are very huge..
please help me, i have to get the stuff ready by monday.
Nagarajan
here is the complete list, i have truncated the list of cache buffer chains so as not to flood ur inboxes
select name, gets, misses, sleeps, immediate_gets, immediate_misses,
spin_gets, sleep1, sleep2, sleep3
from v$latch_children where spin_gets > 0 order by spin_gets desc;
cache buffers lru chain
180240447 1049798 671406 192973662
1910294 484253 489808
56810 18927
library cache
12661385 11555 11847 2665
6 2362 7525
1191 477
library cache
15074864 11052 12581 2268
3 1785 6931
1679 657
library cache
13968181 10177 11589 2750
3 1697 6616
1282 582
cache buffers chains
5039816 3997 5745 108882
479 666 2643
197 491
enqueue hash chains
2306325 1271 1196 0
0 181 1033
35 22
cache buffers chains
2837592 335 454 57844
35 116 170 8
41
cache buffers chains
11146893 438 849 105638
202 114 212
20 92
cache buffers chains
1622348 402 361 68978
6 66 328 3
5
cache buffers chains
1592862 446 691 107171
160 60 279
35 72
session idle bit
4391326 56 2 0
0 54 2 0
0
cache buffers chains
2094997 571 782 109789
538 53 417
36 65
cache buffers chains
1818946 449 532 109442
367 50 348
17 34
cache buffers chains
1638259 347 335 67506
9 50 281 7
9
cache buffers chains
5775628 209 233 90179
374 42 135
17 15
cache buffers chains
3188060 182 228 91522
60 42 114 8
18
cache buffers chains
2653185 354 470 58812
165 38 260
22 34
enqueue hash chains 941922
237 344 0
0 28 180 8
21
cache buffers chains
1551617 419 1145 99846
278 25 192
46 156
cache buffers chains
1617436 184 308 113526
105 25 103
17 39
cache buffers chains
2800498 69 47 96765
11 24 44 0
1
cache buffers chains
3179040 213 757 67610
96 23 75
15 100
cache buffers chains
1555371 281 932 103544
202 22 111
24 124
cache buffers chains
2142326 152 268 130211
173 20 80
17 35
cache buffers chains
2018044 130 187 99380
63 19 86 8
17
cache buffers chains
2085800 158 200 116984
110 17 113
11 17
session idle bit
1197386 17 1 0
0 16 1 0
0
cache buffers chains 304860
49 82 95018
25 16 18 7
8
cache buffers chains
1973394 66 69 78858
13 16 42 3
5
cache buffers chains 652235
113 158 79930
63 15 74 10
14
cache buffers chains 811224
55 61 104705
13 15 32 4
4
cache buffers chains
2149861 97 213 127421
85 15 56 4
22
cache buffers chains 282177
79 187 75056
43 15 31 8
25
cache buffers chains 764360
75 128 102809
10 14 43 6
12
cache buffers chains 952109
153 364 128074
131 14 79 19
41
cache buffers chains 390959
65 89 128312
19 12 37 7
9
cache buffers chains 276696
32 25 93251
61 12 16 3
1
cache buffers chains
1436718 170 485 133018
234 12 64
19 75
cache buffers chains 418530
56 143 86907
22 12 21 1
22
cache buffers chains 557647
58 60 86454
9 11 39 5
3
cache buffers chains 277561
39 53 95364
24 10 22 2
5
cache buffers chains 567162
37 41 96044
28 10 20 4
3
cache buffers chains 224516
38 39 55662
22 10 21 4
3
cache buffers chains 401777
89 176 92838
83 10 40 17
22
Received on Sat Jan 11 2003 - 12:34:42 CST
 |
 |