Re: latch free and hot blocks

First take a look at srivenu's note.

There is a bug in some versions of 8.1.7 which puts excess pressure on the latches covering root blocks of indexes. Are any of your hot index blocks the root block. (Check dba_segments for the location of the segment header block, the index root block is always the block after the segment header block).

There is no reason why a hot block has to be a table block. In fact, for latching reasons, index blocks are more likely to get hot than table blocks if the tables are popular lookup tables. (There are more table blocks than index blocks usually, so the so visits are spread out more thinly across the table).

If the problem is the root block problem, you may need to upgrade. The other option is to find out why you are doing so many lookups, and reduce the amount of work. Index-related latch contention is sometimes an indication of excessive reliance on indexed access paths when the occasional tablescan might be more cost-effective.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

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

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st


"hrishy" <hrishys_at_yahoo.co.uk> wrote in message
news:4ef2a838.0406020113.23c4f4e8_at_posting.google.com...


> Hi Jonathan

>

> whe i run your query i see the hot blocks are for indexes..i was

> expecting tables there..now what i am kind of lost

>

> regards

> Hrishy

>