Oracle-L: Re: Buf Hit Ratio

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 08 Apr 2002 08:33:34 -0800
Message-ID: <F001.0043E75C.20020408083334@fatcity.com>

Given that a 1 GHz CPU can do about 100,000 logical I/Os per second, and the largest machine is currently (I think) only 128 CPUs, I can only guess three possibilities:

Your instance has been up for the last 500 years

The statistics numbers don't match the ones that the Oracle code is supposed to match.

You are running a 64-bit version of Oracle, and the code the exposes v$sysstat (or rather the x$ underlying it) is not quite in-line with the actual memory content.

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 08 April 2002 16:15

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.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).

Received on Mon Apr 08 2002 - 11:33:34 CDT