11 Replies Latest reply: Jun 12, 2008 12:39 PM by chompora RSS

    Reduce buffer gets.

    AlokKumar
      How to go abuot reducing the buffer gets ? I mean, is there any way, where we can know that the alogrith is an issue?

      hare krishna
      Alok
        • 1. Re: Reduce buffer gets.
          Aman....
          Alok,
          I didnt get you?Can you rephrase your question?Which algorithm you think is an issue,oracle's?
          Aman....
          • 2. Re: Reduce buffer gets.
            riedelme
            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
            • 3. Re: Reduce buffer gets.
              chompora
              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.
              • 4. Re: Reduce buffer gets.
                orafad
                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?

                Scalability?

                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:
                orafad
                • 5. Re: Reduce buffer gets.
                  chompora
                  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.
                  • 6. Re: Reduce buffer gets.
                    orafad
                    In that case the first thing to do is fix the SQL,
                    the number one culprit of bad performance.
                    Maybe that's what Alok wanted to learn more about.....

                    The whole concept of maximizing the
                    buffer hit ratios is based on reducing the disk I/O
                    and maximizing the memory reads
                    Do you really want to maximize memory reads (for the same result)?

                    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
                    ratio is still indicative of properly sized buffer
                    pool or db_buffer_cache, what ever you want to call
                    ...or a really shitty piece of design, sql or plan, or all of the above.
                    • 7. Re: Reduce buffer gets.
                      SteveKaram
                      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.
                      This 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.

                      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'!
                      • 8. Re: Reduce buffer gets.
                        AlokKumar
                        Thanks all you for your valuable suggestions.


                        Thanks
                        Alok
                        • 9. Re: Reduce buffer gets.
                          chompora
                          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.
                          • 10. Re: Reduce buffer gets.
                            John Spencer
                            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.

                            John
                            • 11. Re: Reduce buffer gets.
                              chompora
                              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....
                              Tom