Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Hit Ratio

RE: Hit Ratio

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Tue, 23 Dec 2003 08:49:33 -0800
Message-ID: <F001.005DAE82.20031223084933@fatcity.com>


Yong,

Connor's script is not a joke, it's a proof by counterexample that the advice "You SQL is tuned if and only if it has a high hit ratio" is rubbish.

The buffer cache hit ratio is a tool. Used properly, nobody's objecting. It's proper use? To answer the question, "What percentage of LIO calls can be satisfied without an OS read call?" The correct point that many on this list make over and over again, is that this is often the wrong question to be asking. (And actually, the conventional "BCHR=(L-P)/L" formula doesn't answer that question very well anyway; see Steve Adams's site for more detail.)

It's not the ratio that needs condemning, it's the advice about how to use the ratio. The ratio just happens to be the emblem on the flag.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:

- Performance Diagnosis 101: 1/27 Atlanta
- SQL Optimization 101: 2/16 Dallas
- Hotsos Symposium 2004: March 7-10 Dallas
- Visit www.hotsos.com for schedule details...


-----Original Message-----
Yong Huang
Sent: Tuesday, December 23, 2003 9:29 AM To: Multiple recipients of list ORACLE-L

Hi, Carel-Jan and Rich,

Connor's script to bump up buffer cache hit ratios is meant to be a humor. Only
if you carefully comtemplate it will you see that there's no relevance of the
fact that you can get any hit ratio to the fact that hit ratios are insufficient in performance tuning.

It would be equally easy to write scripts to bump up some wait event times. If
you need very long db file reads, create a big table and keep scanning it. If
you need long enqueue waits, create a table and insert a row. Create 10 or 100
sessions (depending on your patience) and delete from that table and wait. The
fact that you can get arbitary wait times does not reduce the efficacy of wait
event interface as a performance tuning tool.

Buffer cache or library cache hit ratios are not sufficient, very insufficient
used alone, to tune the database. The reason is that they don't contain enough
information to tune the system with. This is the only reason we should not
solely rely on them; in fact, not using them at all doesn't hurt much. The
reason is not that we can get any value we want by playing pranks.

Hit ratios are still used in other performance tuning and not condemned. Although in UNIX performance tuning one looks at absolute numbers such as scan
rate, CPU usage and netstat output more often, hit ratios in some sar output
are still occasionally used. Most ratios could still be distored by a rogue
user repeatedly doing, say, "find /" for inodes or "find / -exec grep SomeThing
{} \;" for page cache.

In any tuning practice, Oracle or OS, artificially distorting usage patterns
invalidates your numbers even if you're using a well respected tuning method.
So only play pranks on a play box, not production.

Yong Huang

At 11:14 22-12-03 -0800, you wrote:
>My BCHR is currently 96.62%. In the past, it was normally over 99%.
What
>should I do?
>
>I'll be waiting for Mladen's reply... :)
>
>
>Rich
>
>Rich Jesse System/Database Administrator
>rjesse_at_qtiworld.com Quad/Tech Inc, Sussex, WI USA

Go to www.oracledba.co.uk (Connor) or go to O'Reilly (download page of Cary's book), and download one of the fabulous BCHR enhancement scripts.

Especially when your bonus depends on it, this is a good time to perform

some BCHR tuning.

Regards, Carel-Jan



Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Yong Huang
  INET: yong321_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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.net
-- 
Author: Cary Millsap
  INET: cary.millsap_at_hotsos.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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 Tue Dec 23 2003 - 10:49:33 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US