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

Home -> Community -> Mailing Lists -> Oracle-L -> RE: queries too slow

RE: queries too slow

From: Jack Silvey <JSilvey_at_XOL.com>
Date: Wed, 12 Jul 2000 15:16:48 -0500
Message-Id: <10556.111870@fatcity.com>


Gents,

I never said that a 99% cache hit ratio was a guarantee of good performance. I suggested that the TKRPOF indicated that it might be low, and it was.

Of course you can see performance of individual queries that are "steller" on systems with low hit ratios. These queries are either (1) not retrieving that much information, or (2) retrieving that information that is in memory already.

A low cache hit ratio is a fundamental performance issue. It means, by definition, that some of the queries are having to retrieve data from disk. To fit these blocks from disk into cache means that some existing blocks are aged out. This not only slows performance of the current query but also of any query that might need the blocks that were just aged out. This does impact system performance, since you must spin the hard drive to get the information. It is widely agreed that disk retrieval is the antithesis of good performance.

Oracle Corporation suggests that OLTP hit ratios be in the >90% range.

Gaja, surely you are not suggesting that Oracle Corporation is mistaken, and that the single most popular statistic for measuring database performance is not as valuable as they say that it is?

If you have the statistics and reasoning to back this up I would love to hear it. I am always willing to learn, although I will admit that I do not always like to be taught.

Without this evidence, it seems that challenging the traditional view of buffer cache hit ratios would be difficult at best.

Jack Silvey
OCP x 2
Senior @ xol.com

-----Original Message-----
From: Jared Still [mailto:jkstill_at_bcbso.com] Sent: Wednesday, July 12, 2000 2:02 PM
To: Multiple recipients of list ORACLE-L Subject: RE: queries too slow

On Tue, 11 Jul 2000, Gaja Krishna Vaidyanatha wrote:

> Performance needs to be measured by "system throughput", how
> much work has the system done for you. We have even dealt with
> systems with 65-70% cache hit ratios where the performance of
> the transactions and the queries was steller. The incremental
> increase in cache hit ratio that one achieves by configuring the
> db buffer cache with more memory, does not do much to system
> performance.
>
> Hence the memory should be probably utilized for something else
> (e.g. sort_area_size, hash_area_size etc.). Like anything in
> life, the law of diminishing returns will come to play sooner or
> later. Further, the reference to a 99% cache hit ratio and the
> assumption that performance is great as a result, it not
> correct. Not by a long shot.
>

When I see >= 95% hit ratios, high physical reads and low logical reads, I assume a cartesian join and tell the duhveloper to take another look at the WHERE clause.

Jared Still
Certified Oracle DBA and Part Time Perl Evangelist ;-) Regence BlueCross BlueShield of Oregon
jkstill_at_bcbso.com - Work - preferred address jkstill_at_teleport.com - private

-- 
Author: Jared Still
  INET: jkstill_at_bcbso.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
Received on Wed Jul 12 2000 - 15:16:48 CDT

Original text of this message

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