Re: v$buffer_pool_statistics

I am on 8.0.4.4. I last checked it before I applied the patch coming from 8.0.4.0. I don't rememeber having those weird numbers on 8.0.4.0. So, it might be version related.

Djordje

• Original Message ----- To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Thursday, April 05, 2001 6:35 AM

> I'm theorising here..
>
> It may well be version related... I know in 8.1.7.0
> they've zero'd out a few of the figures in this view
> (possibly due to bugs in the way they were calculated
> in versions previous)
>
> I'm on 8.1.7.1 and the figures seem fine to date...
>
> hth
> connor
>
> --- Djordje Jankovic <djankovic_at_corp.attcanada.ca>
> wrote: > Hi,
> >
> > The physical_reads, db_block_gets, consistent_gets
> > fields in
> > v$buffer_pool_statistics have strange values, so if
> > the Hit ratio is
> > calculated according to the usual formula the result
> > is
> > -120. I hope it is not that bad ;-).
> >
> > For the KEEP pool everything is OK.
> >
> > Any idea, what is wrong there ? The database has
> > not been so long up for
> > numbers to wrap around the maximum.
> >
> > SQL> select name BufferPool
> > 2 , physical_reads, db_block_gets,
> > consistent_gets
> > 3 from sys.v$buffer_pool_statistics
> > 4 /
> >
> > BUFFERPOOL PHYSICAL_READS DB_BLOCK_GETS
> > CONSISTENT_GETS
> > -------------------- -------------- -------------
> > ---------------
> > KEEP 361 9,953,228
> > 19,849,8415
> > DEFAULT 2,150,451,312 202,265,542
> > 769,364,625
> >
> > SQL> select name BufferPool
> > 2 ,
> >
> 100*(1-physical_reads/(db_block_gets+consistent_gets))
> > HitRatio
> > 3 from sys.v$buffer_pool_statistics
> > 4 /
> >
> > BUFFERPOOL HITRATIO
> > -------------------- ----------
> > KEEP 99.9998269
> > DEFAULT -120.58298
> >
> >
> > I have also run Steve Adams's
> >
> (http://www.ixora.com.au/scripts/sql/buffer_pool_miss_rates.sql)
> > script and
> > got the similar weird result:
> >
> > BUFFER_POOL MISS_RATE
> > -------------------- ---------
> > DEFAULT 219.09%
> > KEEP 0.00%
> >
> > So, I guess the problem is in the outlining data.
> >
> >
> > In the v$sysstat the numbers for physical reads, and
> > db block gets are
> > similar, but the number for the "consistent gets"
> > defers a lot (approx. 60
> > times). There the hit ratio is not that good but at
> > least makes sense:
> >
> > column BlockGets format 999,999,999,999 heading
> > "Block Gets"
> > column ConsGets format 999,999,999,999 heading
> > "Consistent Gets"
> > column PhysReads format 999,999,999,999 heading
> > "Physical Reads"
> > column BuffCache format 999.999 heading
> > "Buffer Cache Ratio"
> > select BlockGets.value BlockGets
> > , ConsGets.value ConsGets
> > , PhysReads.value PhysReads
> > , 100*(1 -
> > PhysReads.value/(BlockGets.value+ConsGets.value))
> > BuffCache
> > from v$sysstat BlockGets
> > , v$sysstat ConsGets
> > , v$sysstat PhysReads
> > where BlockGets.name = 'db block gets'
> > and ConsGets.name = 'consistent gets'
> > and PhysReads.name = 'physical reads'
> > /
> > Block Gets Consistent Gets Physical Reads
> > Buffer Cache Ratio
> > ---------------- ---------------- ----------------
> > ------------------
> > 235,191,957 12,077,935,459 2,177,467,949
> > 82.316
> >
> > Thanks.
> >
> > Djordje Jankovic
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: Djordje Jankovic
> > INET: djankovic_at_corp.attcanada.ca
> >
> > Fat City Network Services -- (858) 538-5051 FAX:
> > (858) 538-5051
> > San Diego, California -- Public Internet
> > access / Mailing Lists
> >
> --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be removed
> > from). You may
> > also send the HELP command for other information
> > (like subscribing).
>
>
> =====
> Connor McDonald
> http://www.oracledba.co.uk (mirrored at
> http://www.oradba.freeserve.co.uk)
>
> "Some days you're the pigeon, some days you're the statue"
>
> ____________________________________________________________
> Do You Yahoo!?
> Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk
> or your free @yahoo.ie address at http://mail.yahoo.ie
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: =?iso-8859-1?q?Connor=20McDonald?=
> INET: hamcdc_at_yahoo.co.uk
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: djordjej
  INET: djordjej_at_home.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).