Oracle-L: RE: missed Anjo's webcast..

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Thu, 08 Aug 2002 21:03:20 -0800
Message-ID: <F001.004B02F5.20020808210320@fatcity.com>

Sure, I'd love to comment...

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
--------------------------------------------------------------------