Anjo.
Connor McDonald wrote:
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
pe rformance? 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 D ay, 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
