Re: Statspack Question

From: Gaja Krishna Vaidyanatha <oraperfman_at_yahoo.com>
Date: Mon, 22 Oct 2001 14:00:31 -0700
Message-ID: <F001.003B1BBB.20011022141018@fatcity.com>

Hi Bill,

Cache-hit ratios are probably one of the most irrelevant and misleading metrics that one has to deal with in the Oracle performance tuning space. You have real proof in your hands in the form of 2 statspack reports. Yes you are comparing apples to oranges, as the performance health of an Oracle database, has nothing to do with cache-hit ratios.

You need to focus on the wait events, find out what they are (which you know already) and further investigate what is causing it (the SQL). The two wait events that you have as your "top 2" are both I/O related wait events. db file scattered read relates to waits associated with full-table scans and db file sequential read relates to waits associated with index scans.

A very high cache-hit ratio is normally an indication of 1 of 2 things:

The entire database fits in memory (highly unlikely)
The applications (SQL) leave a lot to be desired, in that they perform too many repeated logical I/Os.

One such type of query that can do that is a "correlated sub-query". It will touch the same set of blocks, over and over again, thus inflating the cache-hit ratio, but will provide very poor throughput, although it will consumes inordinate amounts of CPU (due to high amounts of logical I/O). And there are many more such CPU-hoggers which perform large amounts of logical I/O with no end in sight.

I would go as far as saying that I could care less if you did not look at another cache-hit ratio in your life, so long as you look at the wait events, you will be looking at your database's bottlenecks, and in the world of Oracle Performance Tuning, that is all that counts.

Hope that helps,

Gaja

"Carle, William T (Bill), NLCIO" <wcarle_at_att.com> wrote:
> 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
>

Gaja Krishna Vaidyanatha
Director, Storage Management Products,
Quest Software, Inc.
Co-author - Oracle Performance Tuning 101 http://www.osborne.com/database_erp/0072131454/0072131454.shtml

Do You Yahoo!?
Make a great connection at Yahoo! Personals. http://personals.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gaja Krishna Vaidyanatha
  INET: oraperfman_at_yahoo.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 - 16:00:31 CDT