Oracle-L: RE: Buf Hit Ratio

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Mon, 08 Apr 2002 08:51:47 -0800
Message-ID: <F001.0043E790.20020408085147@fatcity.com>

Glenn - V$SYSSTAT holds cumulative values since the instance was started. You need to measure it over a period of time. Note the values, then note them again in one hour, and measure the difference. Or use Oracle's utility STATSPACK or the older utlbstat/utlestat. Even better, get Oracle Performance Tuning 101 and learn why these ratio aren't the best way to tune your database.
http://www.amazon.com/exec/obidos/ASIN/0072131454/qid=1018280809/sr=8-1/ref= sr_8_7_1/002-7587220-4526465
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Monday, April 08, 2002 10:39 AM
To: Multiple recipients of list ORACLE-L

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).
--

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

Author: DENNIS WILLIAMS
INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------