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" misses > 0 and spins & sleep[1-4] are zero, yet sleeps > 0

Re: "cache buffer chains" misses > 0 and spins & sleep[1-4] are zero, yet sleeps > 0

From: Christo Kutrovsky <kutrovsky.oracle_at_gmail.com>
Date: Mon, 6 Jun 2005 13:23:19 -0400
Message-ID: <52a152eb05060610233e353a0@mail.gmail.com>


A little bit of both.

This snapshot was during "offload" period, during the problematic period i = have:

Latch Name                       Requests      Misses      Sleeps Sleeps 1-=

>4
-------------------------- -------------- ----------- ----------- ---------=
---
cache buffers chains          245,864,551     171,099      89,853 0/0/0/0/0
cache buffers lru chain         8,827,850     102,355       4,694 97708/460=
1/4
                                                                  5/1/0
library cache                   8,081,449      88,918       8,436 80625/815=
6/1
                                                                  31/6/0
shared pool                     2,383,770      22,134         456 21691/430=
/13
                                                                  /0/0


We know what the problem is, the academic question is why the zeros in spins and sleep[1-4].

I did not post the problematic snapshot as i wanted to concentrate on the fact that the spin & sleeps is all zeroes.

On 6/6/05, Jared Still <jkstill_at_gmail.com> wrote:
> Hi Chris,
>=20
> For the short answer, see
> http://www.ixora.com.au/q+a/latches.htm.
>=20
> Oracle spins then sleeps if the spin did not obtain a latch.
>=20
> With a request:miss ratio of 15249:1 and 133 spins that successfully
> obtained the latch, I am wondering if this is considered a performance
> problem, or an academic exercise?
>=20
>=20
> --=20
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
>=20
>=20
> On 6/4/05, Christo Kutrovsky <kutrovsky.oracle_at_gmail.com> wrote:
> > Hello all,
> >=20
> > Latch Sleep breakdown for DB: WEBP1 Instance: webp1 Snaps: 32412 -324=
13
> > -> ordered by misses desc
> >=20
> > Get =20
> Spin &
> > Latch Name Requests Misses =20
> Sleeps Sleeps 1-=3D
> > >4
> > -------------------------- -------------- ----------- -----------
> ---------=3D
> > ---
> > ...
> > cache buffers chains 30,194,375 1,980 133
> 0/0/0/0/0
> > ...
> >=20
> > I am looking at this statspack extract over 20 minutes. I have a very
> > specific question.
> >=20
> > Given that I have ~2000 misses and 133 sleeps, howcome I dont have
> > anything in spins and sleeps[1-4] ?=20
> >=20
> > Are the cache buffer chains special, i.e. there is no spinning to
> > acquire them ? And if there are sleeps, last column is supposed to be
> > 4 or more, yet it is still zero?
> >=20
> > Looking in v$latch:
> >=20
> > ADDR=3D095000D3E0=20
> > LATCH#=3D0998
> > LEVEL#=3D091
> > NAME=3D09cache buffers chains
> > GETS=3D09113996341015
> > MISSES=3D09286248205
> > SLEEPS=3D092787346
> > IMMEDIATE_GETS=3D093233325716
> > IMMEDIATE_MISSES=3D0912384650
> > WAITERS_WOKEN=3D090
> > WAITS_HOLDING_LATCH=3D092933754=20
> > SPIN_GETS=3D090
> > SLEEP1=3D090
> > SLEEP2=3D090
> > SLEEP3=3D090
> > SLEEP4=3D090
> > SLEEP5=3D090
> > SLEEP6=3D090
> > SLEEP7=3D090
> > SLEEP8=3D090
> > SLEEP9=3D090
> > SLEEP10=3D090
> > SLEEP11=3D090
> > WAIT_TIME=3D090
> >=20
> >=20
> > You can see there's a large number of gets/misses but NONE in=20
> > spin_gets and sleeps 1-4, why is that ?
> >=20
> >=20
> >=20
>=20

--=20
Christo Kutrovsky
Database/System Administrator
The Pythian Group

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jun 06 2005 - 13:28:53 CDT

Original text of this message

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