Statspack Question

From: Carle, William T (Bill), NLCIO <wcarle_at_att.com>
Date: Mon, 22 Oct 2001 13:37:46 -0700
Message-ID: <F001.003B1B7E.20011022132518@fatcity.com>

Howdy,

I must not know what I'm doing here. I ran a Statspack report for 2 different periods of time, each an hour long. In the first case, my instance efficiency percentages look pretty bad. They look like this:

Instance Efficiency Percentages (Target 100%)

        Buffer Nowait Ratio:      100.00
        Buffer  Hit   Ratio:       20.00
        Library Hit   Ratio:       93.04
        Redo   NoWait Ratio:      100.00
       In-memory Sort Ratio:       99.83
           Soft Parse Ratio:       85.75
            Latch Hit Ratio:      100.00

In the second case, the percentages look much better:

Instance Efficiency Percentages (Target 100%)

        Buffer Nowait Ratio:      100.00
        Buffer  Hit   Ratio:       85.98
        Library Hit   Ratio:       99.94
        Redo   NoWait Ratio:       99.99
       In-memory Sort Ratio:       99.99
           Soft Parse Ratio:       99.84
            Latch Hit Ratio:       99.98

However, when I look at the top 5 wait events, the top 2 in the first case look like this:

Event                                               Waits  Time (cs)   Wt

Time

-------------------------------------------- ------------ ------------
-------
db file scattered read                             31,734       22,189
91.97
db file sequential read                            10,096        1,478

6.13

In the second case, they look like this:

Event                                               Waits  Time (cs)   Wt

Time

-------------------------------------------- ------------ ------------
-------
db file scattered read                            163,392      121,906
86.61
db file sequential read                         1,421,550       14,968

10.63

There are far more waits and more time spent waiting in the second case. Yet my hit ratios are much better the second time. Am I comparing apples and oranges here? What should I be more concerned about?

Bill Carle
AT&T
Database Administrator
816-995-3922
wcarle_at_att.com

--

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

Author: Carle, William T (Bill), NLCIO
INET: wcarle_at_att.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). Received on Mon Oct 22 2001 - 15:37:46 CDT