RE: negative value for buffer cache hit ratio

From: Andrey Bronfin <andreyb_at_elrontelesoft.com>
Date: Wed, 08 Aug 2001 02:45:12 -0700
Message-ID: <F001.00363C9F.20010808012104@fatcity.com>

Hi Christopher !

SQL> select to_char(100 * misses / (hits + misses), '9990.00') || '%' miss_rate
  2 from ( select total_waits misses

  3                from sys.v_$system_event
  4              where event = 'db file sequential read' ),
  5           ( select sum(dbbget + conget - pread)  hits
  6               from x$kcbwds
  7             where inst_id = userenv('Instance') )

  8 /

MISS_RATE

---

   -3.90%

Strange , isn't it ?

The actual results are :
SQL> select total_waits misses

  2                from sys.v_$system_event
  3              where event = 'db file sequential read' ;

    MISSES

---

 111086623

SQL> col HITS for 999,999,999,999,999,999 SQL> select sum(dbbget + conget - pread) hits from x$kcbwds   2 where inst_id = userenv('Instance');

                    HITS
------------------------
          -2,959,172,014

Why is it negative ?
Do U think it's a buffer overflow or something ?

Thanks a lot !!!

By the way , i forgot what should i run to create those sys.x_$* synonyms , i.e. sys.x_$kcbwds.
I had to connect as SYS and use x$kcbwds instead.

Thanks again !!!

-----Original Message-----
Sent: Tuesday, August 07, 2001 8:14 PM
To: Multiple recipients of list ORACLE-L

Try this more accurate query:

select to_char(100 * misses / (hits + misses), '9990.00') || '%' miss_rate   from ( select total_waits misses

              from sys.v_$system_event
            where event = 'db file sequential read' ),
         ( select sum(dbbget + conget - pread)  hits
             from sys.x_$kcbwds
           where inst_id = userenv('Instance') )

/

Also, send the actual values if this query still leads to similar results.

The difference here is it takes in account of direct writes.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Andrey Bronfin
  INET: andreyb_at_elrontelesoft.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).

Received on Wed Aug 08 2001 - 04:45:12 CDT