14 Replies Latest reply: Jul 3, 2010 6:47 AM by sybrand_b RSS

    Commit and Online Redo Log

    user13080027
      Hello,

      If we have got a process that makes inserts to a table of many many rows, for instance millions of rows, what considerations one should take in order to make a decision at what portions to issue commit?

      "The online redo log is a set of two or more online redo log files that record all changes made to the database, including *uncommitted* and committed changes" - this sentence tells us that my changes to the database (in this particular case these changes are inserts) are going to be recorded on the redo log files.

      But what if the free space on these files "ends" and at the same time I still have not issued commit?

      My question is totally theoretical. I am not going to do these inserts since there many different efficient methods of collecting large volumes of data.

      I just would like to know what would happen if the size of the files appear to be too "small" for such transaction? And in what way one could calculate the upper boundary providing that one knows the size in KB of each row (at least approximately)?
      Or the whole process is totally different logically?

      Please, explain!
        • 1. Re: Commit and Online Redo Log
          Fahd.Mirza
          Hi, here is the best explaination of what you are asking:

          http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/onlineredo001.htm#ADMIN11304

          regards
          • 2. Re: Commit and Online Redo Log
            user13080027
            Extremely interesting to me article and very well the things are explained in it!

            Thank you very much!
            • 3. Re: Commit and Online Redo Log
              618702
              Dear user13080027,

              Any uncommited data stays in the redolog buffer, waiting to be written in to the online redologs. If database crashes during that period, any uncommited data will be rollbacked and commited data will be written to the physical datafiles during the instance startup recovery, thanks to the SMON.

              Your uncommited data will stay in the redolog buffer and when redolog is full, checkpoints is triggered by the Oracle database and data in the redologs will be written to the physical datafiles.

              Do not over commit, do not wait for a commit. The application's logic is up to you. Please also read articles about Oracle Transactions and Data Concurrency & Consistency. You can go to the http://tahiti.oracle.com for further information. I hope i did understand your question in a right way.

              Hope it helps,

              Ogan
              • 4. Re: Commit and Online Redo Log
                Girish Sharma
                Do'nt miss to read this thread :
                Commit/Rollback and Redo Log files....

                One of the great thread on Commit and Redo log.

                HTH
                Girish Sharma
                • 5. Re: Commit and Online Redo Log
                  Aman....
                  user13080027 wrote:
                  Hello,

                  If we have got a process that makes inserts to a table of many many rows, for instance millions of rows, what considerations one should take in order to make a decision at what portions to issue commit?
                  As soon as you think that what you have done is correct. There is no set size of the rows which should come under a commit. Whenever you find that your work doens't need anymore thoughts to do, commit it.
                  "The online redo log is a set of two or more online redo log files that record all changes made to the database, including *uncommitted* and committed changes" - this sentence tells us that my changes to the database (in this particular case these changes are inserts) are going to be recorded on the redo log files.

                  But what if the free space on these files "ends" and at the same time I still have not issued commit?
                  If you mean to say that your doing the transactions and (assuming two log groups with one file each) both the log files are full, your database would go in a hang stage. There is no chance that Oracle would let you work if you don't have the space available in the redo log files to maintain your changes.
                  My question is totally theoretical. I am not going to do these inserts since there many different efficient methods of collecting large volumes of data.

                  I just would like to know what would happen if the size of the files appear to be too "small" for such transaction?
                  If the size of the files is going to be too small, you would end up filling them much more faster thus making LGWR switching back and forth over the groups much faster. This surely would lead to a point where all the log files would be filled up and there won't be a space available for the next trandaction entries to be maintained.
                  And in what way one could calculate the upper boundary providing that one knows the size in KB of each row (at least approximately)?
                  You mean the size of the log files or some thing else?
                  Or the whole process is totally different logically?
                  HTH
                  Aman....
                  • 6. Re: Commit and Online Redo Log
                    Charles Hooper
                    Ogan Ozdogan wrote:
                    Dear user13080027,

                    Any uncommited data stays in the redolog buffer, waiting to be written in to the online redologs. If database crashes during that period, any uncommited data will be rollbacked and commited data will be written to the physical datafiles during the instance startup recovery, thanks to the SMON.

                    Your uncommited data will stay in the redolog buffer and when redolog is full, checkpoints is triggered by the Oracle database and data in the redologs will be written to the physical datafiles.

                    Do not over commit, do not wait for a commit. The application's logic is up to you. Please also read articles about Oracle Transactions and Data Concurrency & Consistency. You can go to the http://tahiti.oracle.com for further information. I hope i did understand your question in a right way.

                    Hope it helps,

                    Ogan
                    Ogan,

                    You make a couple of interesting statements above:
                    * Your uncommitted data will remain in the redo log buffer (I assume that you imply until the user issues a commit).

                    The above seems to be in sharp contrast to my understanding of how the data is flushed from the redo log buffer - please provide a source for your information.

                    My understanding is that the redo log buffer will be completely flushed:
                    * Due to a commit in any session.
                    * Every 3 seconds
                    * When the redo log buffer is 1/3 full
                    * When the redo log buffer contains 1MB of data
                    * (Seems like there was one other reason)

                    This might be an interesting article:
                    http://hoopercharles.wordpress.com/2010/06/16/log-file-sync-what-is-wrong-with-this-quote/

                    In the comment section I copied in a quote from page 297 of the book "Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions":
                    "As you can see, there is very little to do to process a COMMIT. The lengthiest operation is, and always will be, the activity performed by LGWR, as this is physical disk I/O. The amount of time spent by LGWR here will be greatly reduced by the fact that it has already been flushing the contents of the redo log buffer on a recurring basis…”

                    Charles Hooper
                    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
                    http://hoopercharles.wordpress.com/
                    IT Manager/Oracle DBA
                    K&M Machine-Fabricating, Inc.
                    • 7. Re: Commit and Online Redo Log
                      618702
                      Hi Charles Hooper,

                      Yes, you are right about your statements here as i was trying to mention it based on the commit :) Sorry for misunderstaing because i was not trying to anticipate another "reason" for it rather than you have just explained;
                      * Due to a commit in any session.
                      * Every 3 seconds
                      * When the redo log buffer is 1/3 full
                      * When the redo log buffer contains 1MB of data
                      Thank you for additional information for the OP.

                      Ogan
                      • 8. Re: Commit and Online Redo Log
                        user13080027
                        Thanks to all of you for the answers! It was very helpful.

                        But a new question arised to me. It is proobably obvious for you, but it is still unclear for me, so please, be patient.

                        This is a quote from Oracle Database Administrator's Guide 11g1:

                        >
                        Redo records can also be written to a redo log file before the corresponding transaction is committed. If the redo log buffer fills, or another transaction commits, LGWR flushes all of the redo log entries in the redo log buffer to a redo log file, even though some redo records may not be committed. If necessary, the database can roll back these changes.
                        >

                        My question is whether the only thing that happens in such case is that LGWR flushes to a redo log file or in second place after the flushing the change records from the log file are also written to the datafiles? Or the process of writing the change records to the datafiles will start when and only when the corresponding transaction is committed?
                        • 9. Re: Commit and Online Redo Log
                          Aman....
                          >
                          This is a quote from Oracle Database Administrator's Guide 11g1:

                          >
                          Redo records can also be written to a redo log file before the corresponding transaction is committed. If the redo log buffer fills, or another transaction commits, LGWR flushes all of the redo log entries in the redo log buffer to a redo log file, even though some redo records may not be committed. If necessary, the database can roll back these changes.
                          >

                          My question is whether the only thing that happens in such case is that LGWR flushes to a redo log file or in second place after the flushing the change records from the log file are also written to the datafiles?
                          From the above quote, you must understand that the onyl writing that would be done in this event would be from the LGWR to the log file. I didn't understand what's your 2nd line means?
                          Or the process of writing the change records to the datafiles will start when and only when the corresponding transaction is committed?
                          Writing to the datafiles would be done by DBWR which doesn't work when LGWR works. The writing of the DBWR is limited to certain events which are different from LGWR's working. At commit, only the LGWR writes. Please read this following section of the docs to know about that when each process does what?
                          http://download.oracle.com/docs/cd/E11882_01/server.112/e10713/process.htm#BABHDAGI

                          HTH
                          Aman....
                          • 10. Re: Commit and Online Redo Log
                            user13080027
                            Hello Aman,

                            Yes, I read it. As a matter of fact I had to read it several times to digest it. But if it was simple I wouldn't have asked about it :)

                            Thanks for the reply!
                            • 11. Re: Commit and Online Redo Log
                              KSG
                              Hi Aman Sir,

                              I read your thread Commit/Rollback and Redo Log files....

                              Q:Guess which do you think would be quicker to write to disk?
                              A:Redo Log files

                              Is this the primary reason for using redo file?

                              Please explain me..

                              Thanks
                              KSG
                              • 12. Re: Commit and Online Redo Log
                                Aman....
                                user13080027 wrote:
                                Hello Aman,

                                Yes, I read it. As a matter of fact I had to read it several times to digest it. But if it was simple I wouldn't have asked about it :)

                                Thanks for the reply!
                                Yes, one needs to read this concept several times. Its really among one of the most trickiest topics in the database.

                                HTH
                                Aman....
                                • 13. Re: Commit and Online Redo Log
                                  Aman....
                                  Q:Guess which do you think would be quicker to write to disk?
                                  A:Redo Log files

                                  Yes Girish. As mentioned in the thread, using redo doesn't make one read teh file like a tape where you have to scroll around the entire tap to go to a specific song. Using redo is like selecting and listening from compact disk, you just jump over it, which is much faster than using the tape(undo).

                                  HTH
                                  Aman....
                                  • 14. Re: Commit and Online Redo Log
                                    sybrand_b
                                    Redolog in Oracle 6 (1989) and beyond is the successor of 'After Image Journalling' in Oracle 5.
                                    You simply write what changes have been made to a separate file.
                                    You do this for three reasons
                                    - crash recovery
                                    - recovery of media failure
                                    - to be able to 'piggyback' transactions.
                                    Let's assume several sessions update one single block.
                                    You don't want dbwr to write that block every time it changes. You want recoverability.
                                    Here comes in redo log which always writes ahead of dbwr.

                                    ------------
                                    Sybrand Bakker
                                    Senior Oracle DBA