RE: RE: Hit Ratio

I hope not, but I think so.

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-----
ryan_oracle_at_cox.net
Sent: Tuesday, December 23, 2003 12:29 PM To: Multiple recipients of list ORACLE-L

are there really that many people who use hit ratio?
>
> From: "Cary Millsap" <cary.millsap_at_hotsos.com>
> Date: 2003/12/23 Tue AM 11:49:33 EST
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Subject: RE: Hit Ratio
>
> 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: <ryan_oracle_at_cox.net
  INET: ryan_oracle_at_cox.net

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