RE: RE: Hit Ratio

Jared,

        I'm going to take some exception to what Cary has said on the subject, but I believe in the end she'll agree with me.

        LIO's are inherently cheaper than PIO's simply because you have to complete the LIO before asking for a PIO. And no you can't work with data that has not been allocated a space in the buffer pool, but that does not mean that a high hit ratio is a good thing either. The problem with a high hit ratio & consequently high LIO's is that the process is simply looking at the same bits of data over and over again in a senseless waste of CPU. The goal of any SQL tuning should be to get the process to complete in the shortest elapsed time as possible irrespective of the CPU, LIO, or PIO necessary to get the job done. That being the case a SQL statement should, ideally, be written to look at any single piece of data once and only once which would result in a lower LIO's & higher PIO's.

        That being said, it's also the case that the ideal SQL statement has not yet been written, including those I author. But at least I try.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-----Original Message-----
Sent: Tuesday, December 23, 2003 2:39 PM To: Multiple recipients of list ORACLE-L

Cary's being diplomatic, as well as engaging in some wishful thinking.  :)

They appear to be quite prevalent. There are other microcosms of Oracle users that you will find from time to time that realize how Oracle works, and how to go about fixing performance problems.

If though you consider the widespread use of texts that propagate ancient tuning advice, you must come to the conclusion that it is still in the mainstream.

Jared

On Tue, 2003-12-23 at 10:54, Cary Millsap wrote:
> 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.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: Goulet, Dick
  INET: DGoulet_at_vicr.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).