Re: Why did rebuilding index fix our cache buffers chains latch issues

Was the block the root block, a branch block, or a leaf block. If branch or leaf, was it a single block that was permanently hot, or did the hot block slowly move along the index ?

If you don't do deletes, then it should not have been possible for the index to be 90% empty unless it was created 90% (or more) empty in the last rebuild. There is a bug in 9i, however, which results in sequence-based indexes (or time-based indexes) with a key of less than about 32 bytes running at 50% empty because the "90/10" split doesn't appear when it should.

Which version of Oracle ?

Is it possible that you have some code that managed to do a number of large inserts followed by a rollback - as that might make it possible to engineer this effect at the branch block level ?

Are the indexes built in a tablespace using ASSM ?

Can we assume it is a simple B-tree index ?

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html


<bdurrettccci_at_yahoo.com> wrote in message 
news:1148486491.931289.112340_at_j73g2000cwa.googlegroups.com...


>I know there are some Oracle performance tuning experts that post on

> this group and so I thought I would post this problem to see if you

> have any ideas.

>

> Our datawarehouse suddenly started getting all kinds of cache buffers

> chains latch waits.  We went through the standard scripts from Metalink

> and books we have that say to look at v$latch_children and x$bh.  We

> found a hot block on an index.  We rebuilt the index.  All of the latch

> issues went away.

>

> But why did they?  I've read about cases with increasing key values

> (like from a sequence) where you will get a hot index block.  But these

> cases involve deletes on the lower values of the key.  This is that

> kind of index - a primary key which comes from a sequence.  But, as far

> as I can tell, records are never deleted from this table.  So, the

> index shouldn't be unbalanced in terms of having a bunch of leaf blocks

> that are mostly empty.  And, rebuilding the index really shouldn't

> change much about its internal structure.

>

> The only remaining clue that I have is that the index shrank to about

> 10% of its size before the rebuild.  Also, its last ddl time was two

> months ago, so maybe it was rebuilt it some wierd way in March and that

> caused it to have a hot block?  Then our rebuilt made it normal again?

> I'm really puzzled.

>

> I may not have enough information to go on here, but I thought I would

> put it out there.

>

> Thanks,

> Bobby

>