| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Hit Ratio
One place I can think of (the only place?) where BCHR may be useful,
is as a lower bound for the OPTIMIZER_INDEX_CACHING parameter. This
parameter pretty much asks the question that Cary posed in his email.
I say that the BCHR should be the lower bound for this value because the question that OPTIMIZER_INDEX_CACHING asks is slightly different in that it's asking specifically about index blocks. BCHR is across all block types, and, in some cases, on some databases, the answer to to those slightky different questions may vary greatly.
What it comes down to is that BCHR is an isolated datapoint. You can't use it in isolation to determine anything.
This has been done to death. I'll shut up now. If there's any doubt, read Cary's papers.
-Mark
-----Original Message-----
From: Cary Millsap [mailto:cary.millsap_at_hotsos.com] Sent: Tue 12/23/2003 11:49 AM To: Multiple recipients of list ORACLE-L Cc: Subject: RE: Hit Ratio
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
-- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bobak, Mark INET: Mark.Bobak_at_il.proquest.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 - 14:04:25 CST
 |
 |