Re: Cache buffer chains (kslbegin)

  • From: "Stephen Anderson" <st.anderson@xxxxxxxxx>
  • To: "Anand Rao" <panandrao@xxxxxxxxx>
  • Date: Tue, 25 Apr 2006 09:06:54 +0100

Thanks Anand, and all that have replied.

You are right. The system is suffering from a few pieces of code that are
run every minute or so, but are performing ~5 million LIO's and ~1 million
PIO's per exec.  I am now focusing on these queries and will attempt to tune
these before scouring other areas of the report, as these queries will
probably yield the biggest performance gain outright.

BR,
Steve.


On 4/25/06, Anand Rao <panandrao@xxxxxxxxx> wrote:
>
> Stephen,
>
> what version of Oracle are you using? there are a couple of bugs related
> to CBC latches, especially 1 crucial bug in 9204 (Bug 3611471). This is
> fixed in 10.2 core code but there are fixes in version 9205 and above.
>
> just looking at your latch stats in the post, it doesn't look like you
> have CBC latch issue. as Mark mentioned, it should help you if you look at
> the 'buffer busy waits' issue and if you have any hot blocks (*Note
> 163424.1)*
>
> kcbgtcr is the routine that begins the search for buffers in CR mode to
> satisfy your request. the search in the hash chain is done using a child
> latch in exclusive mode.
>
> well, usually you don't need to dig this deep but latch sleeps at this
> level usually indicate heavy logical IO, 'hot blocks' type of problems.
>
> To check for hot blocks, try this query:
>
> select name, addr, sleeps from v$latch_children
> where name like 'cache buffers%'
> and sleeps > 100000; -- Use an appropriate number
>
> select obj,tch, file#, dbablk from x$bh where
> hladdr in hextoraw('<value of ADDR>') order by tch asc;
>
> cheers
>
> anand
>
>
>
> On 25/04/06, Joseph Amalraj <joseph@xxxxxxxxxxxxxx > wrote:
> >
> >  You can also query v$segment_statistics for the object(s) with highest
> > "buffer busy waits"
> >
> > Joseph Amalraj
> >
> > 2006/4/24, Stephen Anderson :
> >
> > >
> > > Hello,
> > > I have a 10 minute statspack report from a busy time period, and have
> > seen
> > > that buffer busy waits were the highest wait events. Would I be
> > correct to
> > > then look at the Latch Miss Source at the largest sleeps? I have the
> > > following information there:
> >
> > --
> > Best regards,
> > Alex Gorbachev
> > --
> > //www.freelists.org/webpage/oracle-l
> >
> >
> >
> >
> >
>
>

Other related posts: