Hi Gurus

I am performing a health check on my database and the latch free event
is significant..i investigated for various types of latch misses and
the one that is signifcant seems to be cache buffers chain.The oracle
docs says i need to find hot blocks..Now how do i find these hot
blocks ?

regards
Hrishy

Do check that the event 'latch free' is actually
losing a worthwhile amount of time before you
try fixing the issue. If it is then:

select * from (
select addr, sleeps from v$latch_children
where name = 'cache buffers chains'
order by sleeps desc
)
where rownum <= 10
;

select ts#, file#, dbarfil, dbablk, obj, class, state, tch
from x$bh
where hladdr = {one of the addr values}
order by tch desc
/

(Have to be connected as SYS to run the above).

Blocks with a TCH (touch count) value that
goes into double figures or better are possible
targets for hot blocks.

The OBJ is the data_object_id from dba_objects,
but if it's over ca. 4 million it's a temporary object
or a rollback segment.

--
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@yahoo.co.uk> wrote in message
news:4ef2a838.0406010729.3fef58ae@posting.google.c om...
> Hi Gurus
>
> I am performing a health check on my database and the latch free event
> is significant..i investigated for various types of latch misses and
> the one that is signifcant seems to be cache buffers chain.The oracle
> docs says i need to find hot blocks..Now how do i find these hot
> blocks ?
>
> regards
> Hrishy

You can use this SQL to find the hot blocks on hot cache buffer chains
child latches.

col file# head "File|No" form 99999
col dbablk head "Block|No" form 99999
col tch head "Touch|Count" form 999999
col class head "Class" form a10
col state head "State" form a10

select file#,dbablk,tch,
decode(greatest(class,10),10,decode(class,1,'Data' ,2
,'Sort',4,'Header',to_char(class)),'Rollback') Class,
decode(state,0,'free',1,'xcur',2,'scur',3,'cr',
4,'read',5,'mrec',6,'irec',7,'write',8,'pi') state
from(
select file#,dbablk,tch,class,state
from x$bh
where hladdr in(
select addr
from v$latch_children
where child# = (
select child#
from (
select child#
from v$latch_children
where name='cache buffers chains'
order by misses desc)
where rownum=1))
order by tch desc)
/

You can also use this to find the hot buffers.

col file# head "File|No" form 99999
col dbablk head "Block|No" form 99999
col tch head "Touch|Count" form 999999
col class head "Class" form a10
col state head "State" form a10

select file#,dbablk,tch,
decode(greatest(class,10),10,decode(class,1,'Data' ,2
,'Sort',4,'Header',to_char(class)),'Rollback') "Class",
decode(state,0,'free',1,'xcur',2,'scur',3,'cr',
4,'read',5,'mrec',6,'irec',7,'write',8,'pi') state
from(
select file#,dbablk,tch,class,state
from x$bh
where tch >1000
order by tch desc)
where rownum <15
/

Which version are you on ?
There is a bug in 8i (think it is bug 1967363 which is fixed in
8.1.7.4) which can cause this (due to more frequent pinning of index
root block).
It would help if you give your version and also paste the output of
the above SQL's (which will show the type of blocks which are hot).
regards
Srivenu

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

"Jonathan Lewis" <jonathan@jlcomp.demon.co.uk> wrote in message news:<c9idv3$e4a$1@sparta.btinternet.com>...
> Do check that the event 'latch free' is actually
> losing a worthwhile amount of time before you
> try fixing the issue. If it is then:
>
> select * from (
> select addr, sleeps from v$latch_children
> where name = 'cache buffers chains'
> order by sleeps desc
> )
> where rownum <= 10
> ;
>
> select ts#, file#, dbarfil, dbablk, obj, class, state, tch
> from x$bh
> where hladdr = {one of the addr values}
> order by tch desc
> /
>
> (Have to be connected as SYS to run the above).
>
> Blocks with a TCH (touch count) value that
> goes into double figures or better are possible
> targets for hot blocks.
>
> The OBJ is the data_object_id from dba_objects,
> but if it's over ca. 4 million it's a temporary object
> or a rollback segment.
>
> --
> 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@yahoo.co.uk> wrote in message
> news:4ef2a838.0406010729.3fef58ae@posting.google.c om...
> > Hi Gurus
> >
> > I am performing a health check on my database and the latch free event
> > is significant..i investigated for various types of latch misses and
> > the one that is signifcant seems to be cache buffers chain.The oracle
> > docs says i need to find hot blocks..Now how do i find these hot
> > blocks ?
> >
> > regards
> > Hrishy

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@yahoo.co.uk> wrote in message
news:4ef2a838.0406020113.23c4f4e8@posting.google.c om...
> 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
>

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@jlcomp.demon.co.uk> wrote in message news:<c9k6su$42d$1@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@yahoo.co.uk> wrote in message
> news:4ef2a838.0406020113.23c4f4e8@posting.google.c om...
> > 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
> >