Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Performance base line

Re: Performance base line

From: Brian Peasland <dba_at_remove_spam.peasland.com>
Date: Tue, 16 Sep 2003 14:36:00 GMT
Message-ID: <3F671FD0.1468984B@remove_spam.peasland.com>

> I don't need the
> statspack to see that the server is overloaded (I happen to be a Unix
> administrator and usually I keep a close eye on CPU, Network and I/O).
> I even examine the hit rate of the battery buffered Raid read/write
> cache.

True, you could look at server statistics through sar or whatever. But that will only tell you things from the server's viewpoint. Which can be very important! However, for tuning what is going on inside the database, it is most often advantageous to use the database's viewpoint. For that Statspack is the way to go. It provides so much more about what is going on inside the database than you can ever get from the server's viewpoint. There is also the client's viewpoint. One can learn many things by seeing it from the client software's side of things. Tom Kyte talks about this in Chapter 10 of his Expert One-on-one book in a section called Instrumentation.

> My concern is the effectiveness of my tuning measures, which can be
> assessed mainly by the time consumed to execute something (and by the
> resources consumed thereby, but that is secondary in case there is no
> contention by concurrent users).

I think that if you limit yourself to just the server's viewpoint, then your effectiveness of your tuning measures decreases.

> In this sense, I don't think that I have a performance issue when the
> machine is servicing heavily and the number of buffer busy waits is
> high - that is completely logical to me in the given situation. I have
> a performance issue if the jobs are running slower than they could
> because something hasn't been optimized. So if the counts delivered by
> statspack aren't normalized to time units, then they are useless to
> me, because there is no way to relate increase/decrease of workload to
> my tuning actions.

The trick is to see how things scale as your workload increases. And I believe that this is how your started off this thread. You want a baseline. Statspack can give that to you. Take a look at a Statspack report when the load was nominal and see how different areas were performing. Then increase your workload. Generate another Statspack report for the time period of the high workload. Compare with the report from the nominal workload. Did your average wait time to service a physical read increase, decrease, or stay the same? Did your average wait time to service a log sync increase, decrease, or stay the same? How do the physical reads for a certain query compare? Increase the load again and make additional comparisions. If you have increases, are the increases linear or exponential? This is how you go about using the baseline with other reports to see the effects on your system to see if it will scale well.

> As a matter of fact, statspack could be even misleading. Suppose that
> I switch the degree of parallelism for a big table (along with the
> corresponding init-parameters) in order to take advantage of several
> CPUs. Then the associated query would take a shorter time to execute
> but would cause unproportionally more I/O (assuming the existence of
> independent disk devices) and consume unproportionally more CPU time.
> Statspack would reflect that and some DBA would think that there is a
> performance issue.

If a DBA is only looking at Statspack with blinders on.......One has to take an overall view of things too. If I see my physical reads go up, that doesn't necessarily mean that a problem exists! From the server viewpoint, if I see I/O rates on a particular disk volume go up, does that mean a problem exists? Not necessarily.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"
Received on Tue Sep 16 2003 - 09:36:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US