Re: 8.1.7 cache buffer chains contention

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.

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.

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.

Unfortunately at this time Oracle support seems to have settled on the application at the root cause of this problem. It will be telling I suppose on Monday (we are going back to 8.0.6 over the weekend even with end of support coming, the business is suffering as a result of the latch contention and being unable to give pricing information in a timely manner) when the application remains as it was, and the latch contention goes away.       

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in <998577885.28275.0.nnrp-13.9e984b29_at_news.demon.co.uk>:

>Post the STATSPACK as a text attachment.
>We started have 'reviews' of such things a little
>while back on the newsgroup and haven't seen
>one recently.
>
>
>Has your 'block gets' also climbed and your
>'blocks pinned' dropped at the same time ?
>
>
>--
>Jonathan Lewis
>
>Host to The Co-Operative Oracle Users' FAQ
>http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>Author of:
>Practical Oracle 8i: Building Efficient Databases
>See http://www.jlcomp.demon.co.uk/book_rev.html
>
>For latest news of public appearances
>See http://www.jlcomp.demon.co.uk
>
>Screen saver or Life saver: http://www.ud.com
>Use spare CPU to assist in cancer research.
>
Received on Fri Aug 24 2001 - 08:32:06 CDT