Oracle-L: RE: Caught YAPPing

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Fri, 17 Jan 2003 10:50:10 -0800
Message-ID: <F001.005334E8.20030117105010@fatcity.com>

Raj,

You can of course compute the average any list of numbers. However, if your list contains data from fundamentally different types of data, then your average will be meaningless. Example:

A = {Response times for program 'a'} = {20:58.13, 42:19.28, 34:11.23} B = {Response times for program 'b'} = {00:00.79, 00:00.42, 00:00.65}

Then we have:

Mean(A)         = 1,949.547 seconds
Mean(B)         =     0.620 seconds
Mean(A union B) =   975.083 seconds

So the real question is "does Mean(A union B) really mean anything?" The answer is no, because A and B come from radically different distributions. If you want to see some real problems here, imagine what happens when B has 15,000 elements in it per day and A has 20 per day. What does the combined average tell you? Nothing much, really, except for contrivances like...

"If on consecutive days, Count(A) = Count(B), but Mean(A union B) changed dramatically, then something interesting has happened."

This type of phenomenon is an important part of the reason why any method that uses count-based ratios (i.e., "hit ratios") to tune a database is an unreliable method.

On the other hand, if you're recording response times for only a single particular type of transaction (e.g., one list only for A, and another list only for B), then the averages (Mean(A) and Mean(B)) should be extremely instructive. The important thing is that you have to segment your transactions into different lists.

For response times, the criterion for splitting a list like "A union B" into separate lists like "A" and "B" is that the data in each individual list should fit the so-called "exponential distribution". To figure out whether a given list fits that distribution, you can either dust off your old statistics books, or you can wait until about June and use the Perl code that'll be supplied in the "Optimizing Response Time" book (O'Reilly) I'm working on.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:

- 2003 Hotsos Symposium, Feb 9-12 Dallas
- RMOUG Training Days 2003, Mar 5-6 Denver
- Hotsos Clinic 101, Mar 26-28 London

-----Original Message-----
Rajesh.Rao_at_jpmchase.com
Sent: Thursday, January 16, 2003 12:24 PM To: Multiple recipients of list ORACLE-L

Hello Everyone,

Is there anything, called an "Average Response Time" for an Oracle database? I got a request from one of the application owners as under :

< Snip>
If I can get a log of AVERAGE response time for 1/2 hour periods starting
at 9am and running thru 5 pm I would be in heaven. If I cannot get this,
what would be of similar help. Here is an example of what I would like to
see, say, for today:

Time      Average Response time
9-9:30             2.3 sec
9:30-10          3.0 sec
10-10:30        3.3 sec
10:30-11        3.3 sec
11-11:30        4.5 sec

and so on (one report for each day)

When we get to 10 seconds we have trouble. If we have spikes we can try to
figure out why. ALso, the cost of the above would be a factor. We have
NO money for this.
<End Snip>

How can I get this from system or session wait tables or v$sysstat? Any ideas?

Regards
Raj

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author:
INET: Rajesh.Rao_at_jpmchase.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

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

Author: Cary Millsap
INET: cary.millsap_at_hotsos.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------