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:[EMAIL PROTECTED] Sent: Tue 12/23/2003 11:49 AM To: Multiple recipients of list ORACLE-L Cc: 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 >[EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
