Re: latch free and hot blocks

From: hrishy <hrishys_at_yahoo.co.uk>
Date: 3 Jun 2004 02:45:49 -0700
Message-ID: <4ef2a838.0406030145.6ee607b@posting.google.com>

Hi Jonathan

Thank you very much for clear explanation..so are you comming out with a 10g book :-) just like the one you had for 8i :-)..8i one is cool..i liked the book a lot specifically

1)Inline views (i knew they existed but didnt know when to use them)
2)Analytical functions
3)explanation about mult-versioning (hope u do that for undo

tablespaces too)
4)and the alter table intricacies

Srivenu thanks for your sql..i am on 8.1.7.4 i need to look upto the sql statements..i liked the clear cut explanation form you.

regards
Hrishy

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<c9k6su$42d$1_at_hercules.btinternet.com>...
> 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
> >
Received on Thu Jun 03 2004 - 04:45:49 CDT