Re: 8.1.7 cache buffer chains contention

comments inline.

Andrew Mobbs <andrewm_at_chiark.greenend.org.uk> wrote in <E7y*rRz4o_at_news.chiark.greenend.org.uk>:

>Scott Gamble <zifnab_at_NOSPAM.reddragon.org> wrote:
>>I tried to post the statspack as an attachment yesterday afternoon.
>>Seems it never made it (work has this thing about attachments).
>>
>>I put it up at http://www.reddragon.org/~zifnab/oracle/sp_6_11.lst
>>
>>if that is of any help. The number of gets has stayed the same,
>>I was not able to find where the 'pinned' information was at.
>
>Yow! And I thought *I* was seeing cache buffers chains latch contention.
>
>>Unfortunately Oracle seems to have redone the buffer cache architecture
>>(according to support) and I am sure its for the better most cases, but
>>it took an already bad application and made it much worse and seems to
>>have done the same with others.
>
>You mentioned this earlier, do you know whether the defaults for
>_db_block_hash_latches and _db_block_hash_buckets have changed from
>db_block_buffers/128 rounded to next power of two, and
>db_block_buffers*2 ?
>

what you mention above is the correct defaults for 8.1.7

>It was my understanding that these defaults remained consistent across
>8.1.6 and 8.1.7, but I'd be really keen to know if they didn't.
>
>>One thing that keeps popping up in our discussions with Oracle and
>>other sources when you have one 'hot' block. How do you address that
>>at the application level (not that we have that option its a 3rd party
>>package) to stop that block from being so hot. I think I am failing to
>>understand that, from my perspective and from what I have read you seem
>>to have a few options...
>>
>> 1) Partition the table and index to spread the load out (multiple
>> root
>> blocks, not really application fix, but should help)
>>
>> 2) changing the application to not hit that index so hard. But this
>> to me implies a couple of things, you either stop making the call
>> as often (dont think this is a choice, as it needs up to the
>> minute prices), or somehow change the index/query to not need to
>> read so many blocks from the index.
>
>Have you proved that it's the index root block? The two causes of cbc
>latch contention I've seen are index roots, and when more than one
>active block hashes to the same latch or worse, bucket.
>

Yes one of the 'tests' we did this week was to set _db_block_hash_latches and db_block_hash_buckets to 262411, this had the effect of getting us down to a point where the blocks were thinned out (Average of 2) on each latch, and we were able to easily identify the index block. (We had also done this under 8.0.6 as a learning thing after reading a few things)

>If not (and sorry if you know this), join x$bh to v$latch_children and
>order by sleeps, you should soon see if the hottest latches are down to
>a single hot block, or multiple warmish blocks on the same latch.
>Then dump interesting looking blocks, and grovel around dba_objects to
>find out what they are.
>
>If it is an index root, what to do about them is something I'd like
>to know too. There are some OS level tricks that I've find helped.
>If the OS is aging server processes off the CPU while they're holding
>latches, that can be *really* bad, you might be able to modifiy the
>sceduler settings. Not sure of this on Tru64, I haven't seen such bad
>latch contention there. However, I've had good results from binding
>individual server processes to CPUs on Alpha (or binding to QBBs on
>Wildfire architecture, but I'd guess you're not on that if you're
>running 4.0f ?). I understand that might not be practical in all
>operating environments though.
>
Received on Fri Aug 24 2001 - 11:26:33 CDT