This content has been marked as final. Show 11 replies
Your question is too general to answer effectively.
Generally, you should be able to reduce buffer gets by reducing read operations This can sometimes be done by indexed lookups, sometimes not. It depends on the situation.
You can work with a query's execution plan to decide if the query is reading efficently
Why would you want to reduce buffer gets? If I'm not mistaken Buffer gets means that the data block is in memory and Oracle is returning data from the buffer cache and NOT having to go to disk, which is bad...
I would think the only way to reduce buffer gets is to flush the buffer cache frequently and keep your users off the system.
I guess the point is; what would you rather want - a bad query with a bad execution plan, consuming 100 000 000 logical io running in 6 hours (still with not too much physical io), or a good query, perhaps using a nicely constructed index also, with a good execution plan, consuming only a few buffer gets and the specific step of process coming in under 2 seconds?
To balance the argument a bit, you could of course be looking at something running very frequently, driving up buffer gets by the number of executions. For example a kind of polling routine with a severe bug and lack of error handling, resulting in several database calls per second.
(Yup, the above is taken from actual systems....)
Message was edited by:
In that case the first thing to do is fix the SQL, the number one culprit of bad performance. I just have never heard of needing to reduce the number of buffer gets. The whole concept of maximizing the buffer hit ratios is based on reducing the disk I/O and maximizing the memory reads. I know that hit ratios are not in vogue in tuning these days, being pushed aside by wait events, but a high buffer hit ratio is still indicative of properly sized buffer pool or db_buffer_cache, what ever you want to call it.
In that case the first thing to do is fix the SQL,Maybe that's what Alok wanted to learn more about.....
the number one culprit of bad performance.
The whole concept of maximizing theDo you really want to maximize memory reads (for the same result)?
buffer hit ratios is based on reducing the disk I/O
and maximizing the memory reads
Either way, the ratio is irrelevant. Focus should be on maximizing user and business performance, right?
pushed aside by wait events, but a high buffer hit...or a really shitty piece of design, sql or plan, or all of the above.
ratio is still indicative of properly sized buffer
pool or db_buffer_cache, what ever you want to call
Why would you want to reduce buffer gets? If I'm notThis is a dangerous line of thinking without clarification. Chompora, I think what you are saying in the above quote is that you wouldn't want to trade buffer gets for physical reads.
mistaken Buffer gets means that the data block is in
memory and Oracle is returning data from the buffer
cache and NOT having to go to disk, which is bad...
I would think the only way to reduce buffer gets is
to flush the buffer cache frequently and keep your
users off the system.
However, you always want to strive to reduce buffer gets just like you want to strive to reduce physical reads. Whether you are getting data from the datafiles or the buffer cache, block touches can be a source of contention. Queries with excessive buffer gets (logical I/O) may result in buffer busy waits, high CPU usage, cache buffers chains latch waits, and other undesirable outcomes.
I've been told by many DBAs that they can't tune their database performance any further because they are 99% CPU bound, and in their minds that just means all you can do is buy more CPUs. However, being CPU bound can be and usually is a result of being logically I/O bound. Searching out and tuning high logical I/O queries that are executed frequently will help in these situations tremendously.
That being said, for the original poster: tune your SQL! Reduce block touches. Choose optimal join orders, join methods, index usage, etc using the many resources out there on SQL tuning. Never accept that a query performing 50,000 db block gets and millions of consistent gets is 'okay'!
My point was just that without looking at tuning the SQL, it is more effecient for Oracle to read the data from cache than from disk. Yes, you are absolutely correct that it is best to attempt to minimize the number of blocks read to return the necessary rows, but taking the SQL out of the equation it's still best to read from cache.
I work in a shop that is a complete end-user environment. We have no control over the code or SQL. I can only improve performance by speeding I/O through disk layout and management, kernel tuning, managing Oracle object distribution on disk and memory configurations through Oracle init parameters. I am sure there are other DBA in my position.
The original question was not specific, so if the trade off for wanting to reduce buffer gets rsulted in higher logical reads...don't.
When Oracle reads a block from disk, that counts as a physical read. That block is read from disk into the buffer (that is what a buffer is for). The query then gets that block from the buffer, counting one buffer get. So, you are going to get a buffer get whether or not there is a physical read. As far as I understand things, a buffer get is a logical read so I do not see how reducing buffer gets could result in more logical reads.
So, reducing buffer gets is a legitimate goal in tuning sql statements.
Yes, in SQL tuning that is correct, but if you will notice first statement "My point was just that WITHOUT looking at tuning the SQL" and as I said if you work in a shop where you can NOT tune the SQL, all the DBA can do is balance memory reads and disk reads.
and I mispoken when I said logical read, I meant physical read....