but...but... "Increasing your buffer hit ratio from 95 to 99 will give a 400% improvement in performance!"
I know I read that somewhere :-) Connor --- Cary Millsap <[EMAIL PROTECTED]> wrote: > Sure, I'd love to comment... > > 1. If you can inexpensively cache your whole > database working set in > memory, there's nothing wrong with doing that > *unless* you could have > better spent the resources somewhere else to make a > bigger positive > impact to the business (business = net profit & > return on investment & > cash flow). Does it make a perceptible performance > difference for you to > have your whole database in memory? I can't know > without seeing a > profile of some of your key application sessions, > but my experience over > a few hundred trace files recently tells me, > "probably not." > > <sidebar>Because of the masses of real-life field > data we've seen over > the last two years of collecting people's 10046 > trace files, I disagree > vehemently with the prediction that, "With 64-bit > Oracle and terabytes > of cheap memory, tuning will be a thing of the > past." Maybe tuning with > the buffer cache hit ratio will be a thing of the > past (imho, it should > have become a thing of the past in 1992 when Oracle > created 10046 data). > But 99%+ of the application inefficiencies that I > see today will be *no* > faster--zero percent--when they're made > memory-resident.</sidebar> > > 2. Having your entire database is in memory is no > guarantee that your > users' performance will be adequate. We see lots of > applications that do > *zero* PIOs, but that consume *hours* of 1GHz CPU > time because they do > so many LIOs. ...Cache hit ratios at 100.0%, > full-table scans at zero, > but performance at absolutely intolerable. The goal > is not a bunch of > ratios in their "green zones." The goal is a system > that provides > maximum business value. > > 3. It is the performance analyst's job to > *know*your*business* well > enough to know where response time improvement will > help the most. THE > SYSTEM CANNOT TELL YOU THIS. What if nobody's > complaining about lousy > performance? Take a user to lunch. Buy someone a > sandwich and ask the > simple question, "If I could make one thing faster > today, what would > most improve your time on Earth with this > application?" Every time you > ask this, a user will point your nose at Response > Time. When you go back > to work after lunch, you had better *keep* your nose > pointed at Response > Time. If you don't know how to measure or optimize > Response Time, then > take Anjo or me to lunch (:\). Pursuing the > optimization of *anything* > other than Response Time is reliable only in > creating the illusion of > progress, if that. If you're not communicating with > users and > specifically targeting their important Response > Times, then you're not > optimizing performance. > > 4. Finally, there's no such thing as an app in which > you have "no > control over the SQL." Even if you're still on RBO, > you have some > control over the schema (ability create/drop/rebuild > indexes). If you're > on CBO, you have absolute control over database > statistics (I like > Jonathan Lewis' proposal: consider telling the > database its statistics > [dbms_stats.set_%_stats] instead of asking it for > them). With 8.1.6 and > above, you have stored outlines, which give you > enormous control over > which plans the optimizer chooses (even with RBO, > which we demonstrate > in our class). And with meaningful statistics to > prove the case, I've > found vendors responsive to constructive suggestions > that improve > performance of their products noticeably for their > entire revenue base. > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 > Dallas, Dec 9-11 > Honolulu > - 2003 Hotsos Symposium on OracleR System > Performance, Feb 9-12 Dallas > - Next event: NCOAUG Training Day, Aug 16 Chicago > > > > -----Original Message----- > Rich > Sent: Thursday, August 08, 2002 5:29 PM > To: Multiple recipients of list ORACLE-L > > Hi Cary, > > This comment made me think. I agree in most cases, > but what about a > very > small DB situation where the buffer cache is larger > than all the tables > and > indexes combined (~300MB)? This is for a 3rd party > tool of which we > have no > control over the SQL. I sized the buffer cache as a > guesstimate of load > on > concurrent usage in the near future. As it turns > out, the amount of > data in > the DB seems to be relatively low, so theoretically, > all accessed data > and > indexes could be buffered. > > My kneejerk is that seems somehow wrong, but I can't > think of a downside > offhand. Care to comment? > > Always willing to learn, > Rich Jesse System/Database > Administrator > [EMAIL PROTECTED] Quad/Tech > International, Sussex, WI > USA > > -----Original Message----- > Sent: Thursday, August 08, 2002 5:05 PM > To: Multiple recipients of list ORACLE-L > > * If you have a really high database buffer cache > hit ratio (>99%), then > you > almost certainly have inefficient SQL in your > application. > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Jesse, Rich > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California -- Public Internet > access / Mailing Lists > -------------------------------------------------------------------- > 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.com > -- > Author: Cary Millsap > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California -- Public Internet > access / Mailing Lists > -------------------------------------------------------------------- > 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). ===== Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net "Remember amateurs built the ark - Professionals built the Titanic" __________________________________________________ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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).
