| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: queries too slow
Jack,
Comments included. This is a long one. Hope you enjoy the read.
Gaja.
My point is "what is low? compared to what?".
> 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.
>
Not at all true. The environments that I am referring to are databases with 200-400 Gb of data with true hybrid OLTP environment comprising of a varied transaction mix and batch jobs that run almost on an hourly basis, crunching numbers, issuing invoices and performing various manufacturing and financial functions that are the "lifeblood" of the business. The user population comprised of anything between 200-800 users depending on the system in question. The transactions, queries and jobs here, were by no means "small fry".
> 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.
>
Sounds good. But who is to determine what is a "low" cache hit ratio? OK so it is 79%, but life does not become terrible at 79%. I fully understand the rudiments and mechanics of logical I/O vs. physical I/O, but one cannot arrive at the indomitable conclusion that a 69% or 79% cache hit ratio is a "fundamental performance issue". Because it is not. Here is why.
Say I have a "correlated sub-query" in my application and this query traverses through the same set of index/data blocks on execution. This can and will "artificially" increase the db buffer cache hit ratio from say 70% to 95%. But that query might take 45 minutes to execute, performing an ungodly amount of logical I/O and it might run away with all of the processing power of 1 CPU.
On the other hand, if I re-write this query using "inline views", it runs in 45 secs using 65% of 1 CPU, performing some physical and logical I/O. The re-written query does not traverse through the same set of index/data blocks. Let's even say that a good number of blocks were read from disk. So what?
This query leaves the cache hit ratio where it was, at 70%, but the important thing here is that, the query completed in 45 secs using 1/3rd of the computing power of 1 CPU vs. its predecessor ran for 45 minutes using 100% of a CPU. Which query is more "scalable"? Well, the answer is pretty obvious. So what is the big deal with the 95% cache hit ratio in the previous case? Nothing.
> 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?
>
Well my friend Jack, a lot of people at Oracle Corporation and other "Expert Consulting Firms" have been "suggesting" that OLTP hit ratios should be in the 90% range. This is "old stuff". We need to talk to the "right people" at Oracle. Well it begins with what is taught in the classroom. Plus, the amount of "published material" which is out there that preach about this like gospel, is not even funny. And so called "experts" talk about these ratios at conferences at great lengths, without dealing with the "core issues". Makes my blood boil.
Very few people who do Oracle Performance Management for a living, consider the key factor in system performance - system throughput. If your system is NOT doing its work in a timely fashion, the hit ratios are absolutely meaningless. YES, I can and will stand up in front of "anyone" and say that they are "mistaken" if they live their performance lives just on "recommended hit ratios".
I am not in any way suggesting that you should not pay attention to a 30% cache hit ratio. But in doing that we still need to keep the "big picture" in mind. Very recently I was at a customer site where the shared pool cache hit ratios were at 33%. They had 128 Mb shared pool. Just to prove a point, I increased the shared pool to 256 Mb. The hit ratio went up to 36%. The obvious problem was that the application did not use "bind variables" and had "hard-coded" values. I brought the shared pool back to 128 Mb. and asked the DBA not to monitor shared pool cache hit ratios. I was trying to reduce his "heart burn". I told him to "look away"...;-)
I'd urge you to read and study the material published by folks like Cary Millsap, Craig Shallahammer, Gary Dodge and Tim Gorman, to get some more insight. I am from their school of thought, which subscribes that "Performance Management is a System-Wide effort". Throwing gobs of memory at Oracle just to get the "recommended cache hit ratios", will do nothing to performance, if the application/design is terrible. Conversely, if the system throughput is at desired levels, recommended cache hit ratios (90% and above) are no longer that meaningful by themselves.
Performance tuning is much more than worrying about hit ratios. It is looking at the "big picture", it is a system-wide effort. It is determining the workload on your system, finding out the transaction service rate and its co-existence with batch jobs.
A 90% cache hit ratio is definitely great stuff, but I will be very "cautious" to arrive at the conclusion that anything moderately less is "bad peformance". This "utopic" number may not even be a reality in most good-sized hybrid OLTP production systems (> 300 Gb.), unless the transaction and query mix is predictable and repeated many times. And if that is the case, great. But if it is not, and life goes on "business as usual", don't worry about it.
Almost every OLTP system which is out there, has some kind of batch job schedule. Some have "batch windows", others may not have any windows and may run it throughout the day. Batch jobs tend to cause cache hit ratios to go down. But it is not a big deal.
> 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.
>
I hope I have given you "some statistics" and a "boat load of reasoning" in this posting. My philosophy in life about learning is : If someone can show me something "in a light that I have not seen before", I would learn from them in a heartbeat. It is all connected to "honing one's skills". I have learnt quite a few things just from this list from so many people. The day I stop learning, I might as well be dead.
> Without this evidence, it seems that challenging the
> traditional view of buffer cache hit ratios would be difficult
> at best.
There is evidence my friend....and lots of it, there are scores of productions systems, there are great articles written by some of these folks. Check out www.hotsos.com, www.orapub.com and www.evergreen-database.com.
Moral : Tune your queries, balance your I/O, give enough memory to Oracle but not so much that the system is excessively paging and swapping...don't worry too much about cache hit ratios and more importantly...Go and enjoy life!
Gaja.
>
> Jack Silvey
> OCP x 2
> Senior @ xol.com
>
>
--stuff deleted--
"Opinions and views expressed are my own and not of Quest" Received on Thu Jul 13 2000 - 12:37:35 CDT
 |
 |