Re: "cache buffer chains" misses > 0 and spins & sleep[1-4] are zero, yet sleeps > 0
- From: Christo Kutrovsky <kutrovsky.oracle@xxxxxxxxx>
- To: Jared Still <jkstill@xxxxxxxxx>
- Date: Mon, 6 Jun 2005 13:23:19 -0400
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@xxxxxxxxx> 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@xxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
