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 <cary.millsap_at_hotsos.com> 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
> Rich.Jesse_at_qtiworld.com 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: Rich.Jesse_at_qtiworld.com
>
> 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: 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.com
> --
> Author: Cary Millsap
> INET: cary.millsap_at_hotsos.com
>
> 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: 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).
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: hamcdc_at_yahoo.co.uk
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: 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 Fri Aug 09 2002 - 03:53:23 CDT