Re: 8.1.7 cache buffer chains contention

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 ?

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.

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.

-- 
Andrew Mobbs - http://www.chiark.greenend.org.uk/~andrewm/