Re: latch free and hot blocks

From: srivenu <srivenu_at_hotmail.com>
Date: 1 Jun 2004 22:24:48 -0700
Message-ID: <1a68177.0406012124.7820860@posting.google.com>

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 Received on Wed Jun 02 2004 - 00:24:48 CDT