Search

OakieTags

Who's online

There are currently 0 users and 44 guests online.

Recent comments

Affiliations

Latch - cache buffer chains on small index (primary key) caused by concurrent batch scripts (select statement) around 300

 Subject 
:  Latch - cache buffer chains on small index (primary key) caused by concurrent batch scripts (select statement) around 300. 
 
Symptom
: High %user of CPU ~100 (cpu_count=64), with high #runqueue (~200-300) during concurrency of batch scripts. The wait events were on small index (primary key, size 34 kbytes on ASSM tablespace). 
 
Solution provided
: Minimise its #rows/block of this problematic index from 200-400 to 40 more or less by increasing its pct_free from 10 to 80. 
: This change could fix this latch - CBC. However we need to rebuild this index very frequently every 2 minutes until all batch scripts complete to minimise the high #runqueue and %system (not %user). 
: Above 2 changes could significantly help improve the concurrent batch jobs.
 
Questions
: Do you think the above changes be the right way ?
: I don't quite understand why the frequent index rebuild could help reduce the #runqueue and %system a lot ?
 

The Oak Table challenge is not currently live - it goes active when a group of members of the OakTable arrange to meet at a public event - but since that wasn't clear when you raised the question, here are a couple of thoughts.
 
First - The Oracle version number really matters in this one; there have been bugs with latching on the root block of an index - the most recent that I know of being when an index had been rebuilt. See this blog item  
 
Second - is the latching problem on the root block or on the leaf blocks ? If it's only on the root block then trying to spread the index entries across multiple leaf blocks isn't going to help anyway
 
Third - Using pctfree to limit the number of rows per block only works when you create or rebuild the index; if you're deleting and inserting rows in the batch then the pattern of activity may simply leave you with well-packed blocks after a very short time. (Possibly this is why you are rebuilding the index every two minutes - but that's likely to cause other, more significant, overheads anyway).
 
Fourth - your question suggests that  you're only using select statements against this table: if that's true I can't see how rebuilding the index every two minutes could help - unless you're on an old version of Oracle that had bugs that generated excessive read-consistent copies of blocks.
 
Have you considered using a "single table hash cluster" for this table - if your access is by primary key and you can work out suitable sizing for the cluster (and happen to be a little lucky) then you can spread the load as much as you like and eliminate any single latch hot-spot. From what you've said, this may be the best solution to try.
 
Jonathan Lewis
 

CBC Latch

Comment moved to Answer.