Re: 8.1.7 cache buffer chains contention

The reason why you get the problem when
moving from 8.0.6 to 8.1.7 is that the newer version of Oracle allows for different execution paths - sometimes even doing things differently whilst reporting the same execution path.

I think someone has already pointed out how to identify the one hot block - and you said you were confident it was the root of an index - would this be one on TRIL_JOIN_WRK ?
or on an index in the first large query in the v$sql list. Why, we also wonder, are there so many versions of those two queries loaded ?

My first guess is that the issue is related to 8.1.7 taking an index fast full scan path when it used to take an index scan path - possibly as the inner table in a nested loop: the problem is that a tablescan or index FFS does 4 current gets on the segment header block (Are you sure it was the index ROOT and not the index segment header block ?)

Another area of suspicion is in the read-consistency cycle - the average transactions applies 895 rollback changes - that's quite a lot, whilst making 274 forward block changes to 151 blocks. Your transactions seem to be colliding rolling back each others changes to get a consistent view of the data.

Your big problem is 'cache buffers chains' but has Oracle said anything about 'cache buffers handles' which you also miss on - more so that 'chains' but tend to get by spinning ?

Most of the cache buffers chains misses are related to "kcbgtcr: kslbegin" which I guess is 'get consistent read,' rather than "kcbgcur: kslbegin" which is probably 'get current read' - so that kicks my guess about segment header blocks and fast full scans into touch.

Final thought - your idea of a hash table is possibly going to work. Since you know which index is the critical one, create an 8-partition hash table from the offending table, partitions by the first column of that index. If the queries causing the problem are able to do partition elimination, then on average each root block will be requested one-eigth of the time,. and the drop may be sufficient to make a MUCH MORE significant improvement on the queue sizes than the factor of 8 would suggest.

However, if partition elimination cannot take place, then every partition is queried, and you get 8 times the latch delay.

I chose 8 as a number small enough to avoid extreme delays due to the possibility of other work having to check 8 partitions all the time, traded off against the benefit of reducing the latching.

The only other thing I'd say is that the first two queries on the SQL list are doing a lot of work - forget the latch issue for a bit, and try to find out if those queries can be made to do a lot less work: fix those, and perhaps your latch problem will cease to exist.

--
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.