Oracle-L: Buf Hit Ratio

From: Glenn Travis <Glenn.Travis_at_sas.com>
Date: Mon, 08 Apr 2002 07:38:30 -0800
Message-ID: <F001.0043E5C8.20020408073830@fatcity.com>

I am running the following (Oracle suggested - 8i) query to get the biffer hit ratio. This is our production Apps database. The numbers for logical and physical reads seem VERY high (especially phys_reads, which obviously causes our hit ratio to drop to 0). What could have caused this?

SQL> list

  1      select A.value + B.value  "logical_reads",
  2      C.value                   "phys_reads",
  3      D.value                   "phy_writes",
  4  (A.value+B.value)-C.value "log_minus_phys",
  5      round(100 * ((A.value+B.value)-C.value) / (A.value+B.value))
  6         "Buffer Hit Ratio"
  7      from V$SYSSTAT A, V$SYSSTAT B, V$SYSSTAT C, V$SYSSTAT D
  8      where A.statistic# = 38
  9      AND B.statistic# = 39
 10      AND C.statistic# = 40
 11*     AND D.statistic# = 44

SQL> /

               logical_reads                   phys_reads       phy_writes   log_minus_phys Buffer Hit Ratio
---------------------------- ---------------------------- ---------------- ---------------- ----------------
  18,446,744,070,414,253,130   18,446,744,069,433,707,559        2,043,488      980,545,571                0

Here are some other stats;

DTSTAMP                            PHYSICAL_READS    DB_BLOCK_GETS              CONSISTENT_GETS PHYSICAL_WRITES
-------------------- ---------------------------- ---------------- ---------------------------- ---------------
       PHYSICAL_READS_DIRECT PHYSICAL_WRITES_DIRECT SESSION_LOGICAL_READS
---------------------------- ---------------------- ---------------------
08-APR-2002 10:35:16   18,446,744,069,434,437,169      171,781,916   18,446,744,070,280,471,598       2,103,859
  18,446,744,047,946,114,866                966,679         1,032,014,671

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Glenn Travis
INET: Glenn.Travis_at_sas.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 Mon Apr 08 2002 - 10:38:30 CDT