7 Replies Latest reply on Jan 18, 2015 8:42 PM by sybrand_b

    Commit logic for update queries

    user10698496

      Hi,

      Oracle version - 11.2.0.4

      Platform - IBM AIX

       

      I have few Informatica batch jobs running which fires many update queries in night time.Our batch window is 9 hours but the batches are running for 12-15 hours.

            Update queries are processing a minimum of 70 million records.Informatica developers are using single commit after all 70 million records are updated to reduce REDO process.My question is -shouldnt we introduce LUW concept ,say one commit per 5 million records? Doubt is - isnt it an overhead for Lgwr to write all 70 million in one shot from redo buffer to redo logs and again for dbwr to write to datafiles?Pls throw some light.

       

      Thanks

        • 1. Re: Commit logic for update queries

          Redo buffers are NOT written to redo log at commit, but

          - every 30 seconds

          - one 1 Mb buffer is dirty

          - when 1/3 of this buffer is dirty

          whichever occurs first.

          COMMIT only writes a marker in the redo log stream.

          Database writes are independent (but after redo log writes)

           

          So: introducing a COMMIT every 5 million records

          - will break the ACID principle of this transaction, it will not only you to restart it when it fails, because you need to find out what has been done

          - will make things SLOWER

          - will create MORE redo

          AND will increase the likelihood of ORA-1555

           

          You will need to study the Concepts Manual a little bit more, this kind of COMMIT has NO place in Oracle.

           

          -------------------------

          Sybrand Bakker

          Senior Oracle DBA

          1 person found this helpful
          • 2. Re: Commit logic for update queries
            JohnWatson2

            Point of information: the redo log buffer IS written to the online logfiles on commit.

            • 3. Re: Commit logic for update queries
              JohnWatson2

              There is minimal difference in redo between updating 70 million rows in one transaction, and updating 70 million rows in 70 million transactions. The LGWR process is streaming the change vectors to disc in near real time in either case. More frequent commit is likely to reduce performance rather than improve it, because your session(s) will have to wait until LGWR has completed the write each time. The algorithm DBWR uses to select buffers for writing to disc is not in any way related to commit processing.

              You will probably need to look at factors other than redo and commit processing to fix this problem. Ask your DBA for a statspack report (or an AWR report if you have the licence) that covers the batch run and post it here. THen perhaps someone can advise.

              1 person found this helpful
              • 4. Re: Commit logic for update queries

                >Pls throw some light.

                 

                assume single session does REALLY, Really, really  massive DML such that it changes more data rows which exceed total RAM,plus all REDO log file total size. without COMMIT

                 

                What happens to this session & every other session in this database?

                • 5. Re: Re: Commit logic for update queries
                  JohnWatson2

                  I've just blogged a little test that demonstrates how much performance can degrade and how much additional redo and undo is generated by frequent commit, Frequent COMMIT demo

                  • 6. Re: Commit logic for update queries

                    sybrand_b wrote:

                     

                    Redo buffers are NOT written to redo log at commit

                    Not correct - They most certainly ARE written at commint.

                     

                    Until the redo buffers for a transaction are actually written to disk the user is NOT notified that the COMMIT has happened.

                    http://docs.oracle.com/cd/E11882_01/server.112/e25494/onlineredo.htm

                    Whenever a transaction is committed, LGWR writes the transaction redo records from the redo log buffer of the SGA to a redo log file, and assigns a system change number (SCN) to identify the redo records for each committed transaction. Only when all redo records associated with a given transaction are safely on disk in the online logs is the user process notified that the transaction has been committed.

                    If you disagree please post links to documentation or statements supporting your position.

                     

                    Perhaps you meant that it is the DATABASE BUFFERS that are not written to disk when a commit happens. The database writer works independently of the log writer.

                    1 person found this helpful
                    • 7. Re: Commit logic for update queries

                      The OP seems to state Oracle writes ALL redolog info to the redolog files at COMMIT.

                      This is most definitely not true.

                      The corresponding section in the 10gR2 documentation is much clearer

                       

                      https://docs.oracle.com/cd/B19306_01/server.102/b14220/process.htm#i7261

                       

                      (Personally, my general impression is the Tom Kyte rewrite of the Concepts Manual made things much more unclear).

                      It may be that the last remaining bytes are written at COMMIT, but in a transaction of 70 million records, writes to the online redolog file MUST have happened.

                      Also I can not imagine the SCN is assigned at COMMIT. It would mean the redolog records need to be updated. I seem to remember Jonathan Lewis has demonstrated the SCN functions as internal clock and is incremented all the time.

                       

                      -------------

                      Sybrand Bakker

                      Senior Oracle DBA

                      1 person found this helpful