Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Cache buffer chains (kslbegin)

Re: Cache buffer chains (kslbegin)

From: Stephen Anderson <st.anderson_at_gmail.com>
Date: Tue, 25 Apr 2006 09:06:54 +0100
Message-ID: <a9a8b6c0604250106j7bb6128tbdc6e3dccf0370ea@mail.gmail.com>


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_at_gmail.com> 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_at_amalrajinc.com > 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
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
> >
> >
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Apr 25 2006 - 03:06:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US