Re: Help: large "cache buffers lru chain", large performace degradation

Hi,

More information are required to address the problem (report from utlbstat/estat will help).

I don't think it's a contention problem, but the problem could be the size of the cache buffer, values of parameters like DB_BLOCK_LRU_LATCHES, the number of db writers, ...

Regards

Dias

xml_naga_at_yahoo.com (Nagarajan) wrote in message news:<b2ea4548.0301111034.2c6c2ce3_at_posting.google.com>...
> 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 Sun Jan 12 2003 - 01:55:12 CST