1 2 Previous Next 20 Replies Latest reply: May 15, 2012 5:17 PM by rp0428 RSS

    How can I reduce buffer cache usage?

    rahulras
      Hi All,

      I am on 11.2 on Linux.

      I have a complex PL/SQL data transforming+loading process, which reads data from several input tables and insert the final resultant records in a table (lets call it result table). At the end of processing, typically, this process produces 2million records in the result table.
      After the processing is complete, we create a REF CURSOR on the result table and pass the ref cursor to a Perl script, which generates a nice flat file from the data.

      My understanding is, all the inserts (about 2 million) I am doing to the result table, will consume Oracle's buffer cache. Am I correct?
      Also, the ref cursor which will read data from the result table ( select * from result_table ), will read all those 2 million records in SGA/Buffer cache. Correct me if I am wrong here.
      After the report is written in a file, we simply don't need this data. Next day we truncate the result table and start again.

      All the buffer cache usage by this process will obviously kick out lot of data from the buffer cache which might be usefull. Our system might end up reading that data back from db files (physical read).

      How can I reduce the buffer cache consumption by this report generation process? which will in trun save us lot of I/O.

      Thanks in advance.
        • 1. Re: How can I reduce buffer cache usage?
          sybrand_b
          The approach is fundamentally flawed.

          Fact: PL/SQL can write flat files.
          Fact: You don't need Perl to create flat file
          Fact: You likely don't even need the table.


          Conclusion: you need to fix the problem, instead of taking more symptom fighting measures which don't solve anything.
          If you insist on using a result table (you dion't need one), it should be a Globally Temporary, which bypasses the buffer cache completely.

          My bet is the 'complex PL/SQL data transforming+loading process' is a piece of disaster code. Your question already indicates this.
          It points to basic lack of understanding on how the buffer cache works. The buffer cache is not the cause. It is the culprit of suboptimal code.

          ------------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: How can I reduce buffer cache usage?
            Mark D Powell
            Even if you continue to use a permanent table it is unlikely the entire table will be stored in the buffer cache at once or for the entire length of the processing. As the table rows are inserted Oracle will flush the filled blocks to disk and when selected retrieve the rows (blocks) from disk into the buffer cache as necessary.

            If all the processing is done by one session you should look at using a GTT instead of a permanent table.

            If several sessions are involved then you may need to continue to use the permanent work table, but you can reduce the demand on the buffer cache by making sure each step in the process is an efficient as possible. It seems like you have extra steps.

            HTH -- Mark D Powell --

            Complete last sentence.

            Edited by: Mark D Powell on May 15, 2012 8:34 AM
            • 3. Re: How can I reduce buffer cache usage?
              Aman....
              Is the table used only for this purpose, data loading-unloading? Than how about making the table not to use the buffer cache but recycle cache?

              Aman....
              • 4. Re: How can I reduce buffer cache usage?
                rahulras
                I completly agree that, buffer cache is not the cause, rather the victim.

                If I am inserting and/or reading from Global temporary table, will buffer cache not be used? does insert/read from GTT bypass buffer cache?
                That would be cool.
                • 5. Re: How can I reduce buffer cache usage?
                  sb92075
                  rahulras wrote:
                  Hi All,

                  I am on 11.2 on Linux.

                  I have a complex PL/SQL data transforming+loading process, which reads data from several input tables and insert the final resultant records in a table (lets call it result table). At the end of processing, typically, this process produces 2million records in the result table.
                  After the processing is complete, we create a REF CURSOR on the result table and pass the ref cursor to a Perl script, which generates a nice flat file from the data.
                  Why write table at all?
                  Just write file directly using UTL_FILE from PL/SQL procedure
                  • 6. Re: How can I reduce buffer cache usage?
                    rahulras
                    Problem with using UTL_FILE is, Oracle sits in a Linux server and file is needed on a Windows machine. Perl script runs on the Windows machine where the file is needed. I know, transfering the file to windows machine won't be a big deal.
                    • 7. Re: How can I reduce buffer cache usage?
                      sybrand_b
                      Just install Samba and have a Windows drive map to the Linux server.
                      Problem solved. No need to use Perl established.

                      -------------
                      Sybrand Bakker
                      Senior Oracle DBA
                      • 8. Re: How can I reduce buffer cache usage?
                        rahulras
                        The quickest thing I can do is, use GTT instead of permanent table.
                        All my process runs in one session.
                        Inserting to/reading from GTT, does it not use buffer cache?
                        • 9. Re: How can I reduce buffer cache usage?
                          sybrand_b
                          Did you read what I wrote?
                          If so, why are you asking?
                          Apart from that: you can check easily, without effort.
                          You are wasting people's time!!!

                          But then still: there is no need for a table, there is no need for Perl, there is just need to hire some competent people, who don't mistrust seniors and like to follow sound advice.

                          ------------
                          Sybrand Bakker
                          Senior Oracle DBA
                          • 10. Re: How can I reduce buffer cache usage?
                            Dom Brooks
                            Inserting to/reading from GTT, does it not use buffer cache?
                            Yes it does go via buffer cache.
                            • 11. Re: How can I reduce buffer cache usage?
                              AdamMartin
                              All the buffer cache usage by this process will obviously kick out lot of data from the buffer cache which might be usefull.
                              No, not necessarily.
                              How can I reduce the buffer cache consumption by this report generation process? which will in trun save us lot of I/O.
                              Reducing buffer cache usage does not necessarily reduce I/O.
                              • 12. Re: How can I reduce buffer cache usage?
                                Dom Brooks
                                I would also advocate what sybrand, sb and others is saying.

                                Why write to any table, temporary or otherwise, if you don't need to?
                                All that unnecessary undo & redo...

                                Perhaps see this article by Adrian Billington, particularly the section "asynchronous data unloading with parallel pipelined functions":
                                http://www.oracle-developer.net/display.php?id=429
                                • 13. Re: How can I reduce buffer cache usage?
                                  rahulras
                                  Dom, Sybrand,

                                  Please accept my apologies if I am wasting your time.

                                  But to my question, "Inserting to/reading from GTT, does it not use buffer cache? ", you two gave opposite answers.
                                  I am slightly confused.
                                  As a student, I would like to learn things, if you don't mind.

                                  Thanks again.
                                  • 14. Re: How can I reduce buffer cache usage?
                                    Dom Brooks
                                    It's a misunderstood topic because usage of TEMP is generally associated with direct path read/write (temp) for when workareas spill to temp but the same is not true of temp tables.
                                    But to my question, "Inserting to/reading from GTT, does it not use buffer cache? ", you two gave opposite answers.
                                    Please don't take my word for it - test it yourself.

                                    Trace a simple usage of a temp table.

                                    Don't let these threads discourage you from testing it yourself, but:
                                    GTT access makes no waits in 10046 .
                                    Why are blocks from temporary tables placed in buffer cache?

                                    Of course in 11gR2, it might be interesting if you can get direct path reads due to seial adaptive direct path reads depending on the size of your temp table and your buffer cache but that's a different matter

                                    Edited by: Dom Brooks on May 15, 2012 5:40 PM
                                    1 2 Previous Next