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: Steve Orr <sorr_at_arzoo.com>
Date: Tue, 11 Jul 2000 12:49:09 -0700
Message-Id: <10555.111714@fatcity.com>


> I personally prefer a system which has a 99% throughput factor
> than a 99% db buffer cache hit ratio.

Hmmm... I know how to calculate the latter but how do you calculate the former? :)

I have seen instances where tuning the SQL actually lowers the cache hit ratio because the queries are no longer wasting time repeatedly hitting on data that wasn't needed in the first place. Better "un-tune" that SQL so you can brag about achieving a high cache hit ratio. ;-)

Steve Orr

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Gaja Krishna Vaidyanatha
Sent: Tuesday, July 11, 2000 12:20 PM
To: Multiple recipients of list ORACLE-L Subject: RE: queries too slow

Friends,

Here we go again, the false correlation between db cache hit ratio and its effect on performance. We just got done with a similar thread a couple of weeks ago. Even without seeing anything in Prakash's environment, I would probably guess that the current size of the database buffer cache may be OK. A 79% cache hit ratio is by no means "WAY too low". WAY too low compared to what - 99%? Is there any guarantee that at 99% cache hit ratio that the queries will be screaming? I strongly doubt it.

There are way too many folks who pin all of their hopes in performance increase by throwing memory at the Oracle SGA, specically at the db buffer cache.

Myth : Quite possibly one of the biggest performance myths out there is, "If I do not run my Oracle database with a cache hit ratio of 90% or above, I have bad performance".

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.

While a reasonable cache hit ratio is required to avoid an inordinate amount of physical I/O, the bigger issue that needs to be tackled is "What is causing such a high amount of physical I/O?". 9.9 out of 10 times, it is an application problem. May be the application needs to perform a "full table scan" instead of an "index scan" or vice versa. May be the index used is not optimal. It does not have all the "required columns". A bad cache hit ratio is a "symptom", while a bad query is "the disease".

Another point to keep in mind is that on systems that are configured with mass storage devices (which have controller/disk level caches), a physical I/O from Oracle's perspective still does not mean "physical I/O from disk". It could mean "logical I/O from the storage array's cache". There is more to performance than the cache hit ratio of your db buffer cache.

Fact: Within reason and hardware limitation if the db buffer cache is configured optimally, the cache hit ratio is only a very small indicator/symptom of the bigger problem/disease.

Remember the 80-20 rule. 80% or more of our problems with Oracle is with bad SQL and no matter how much memory you throw at Oracle, if the disease is not cured, the symptoms will still exist. A 99% cache hit ratio may not mean much when measuring system performance and system throughput. I personally prefer a system which has a 99% throughput factor than a 99% db buffer cache hit ratio.

Don't get me wrong I am all for "logical I/O" instead of "physical I/O" and I am fully aware of the 3 orders of magnitude in performance difference between the two. I am all for giving Oracle "enough" memory. But only upto a certain point. I'd rather spend my time fixing, re-writing and tuning my queries. The performance difference that I get in tuning a query is many times higher than 3 orders of magnitude. I believe in curing diseases not symptoms. Let's go tune those awful queries.

Best Regards,

Gaja.


Gaja Krishna Vaidyanatha
Director, I-O Management Products
Quest Software Inc.
(972)-304-1170
gajav_at_yahoo.com

"Opinions and views expressed are my own and not of Quest"



Do You Yahoo!?
Get Yahoo! Mail Free email you can access from anywhere! http://mail.yahoo.com/
--
Author: Gaja Krishna Vaidyanatha
  INET: gajav_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
Received on Tue Jul 11 2000 - 14:49:09 CDT

Original text of this message

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