11 Replies Latest reply on Jan 15, 2018 7:20 PM by Andrew Sayer

    How does my DML affect current redo logs.

    mlov83

      I have recently created a  materialized view which refreshes roughly 7 million records every 30 minutes. I'm in a developer role and my DBA is less than happy with me since, I have neglected to recognize that my materialized view refresh is set to "refresh complete" which apparently would  delete and then reinsert into the target table every 30 minutes and subsequently blows the redo logs. In this instance the database is set to ARCHIVE_MODE. I'm humble enough to say sorry and redo my solution, however, the lesson I learned here, sadly enough, is that I need to be more aware of how the redo logs are being affected by my DML statements. I'm looking for some advice, I have tried reading the documentation and I'm just not finding the answer to my question.

      Is there a way to tell , before I run what my INSERT, UPDATE, DELETE , how that statement will affect the redo logs? I'm looking for a query to at least give me an estimate of how much the next dml would affect the redo logs. I have try selecting from the v$log_history but I can't quite come up with a good query. I have also tried reading the documentation and reading this http://www.dba-oracle.com/tips_oracle_v$_log_history.htm .

      Can someone give me a better explanation how my insert , update, delete are actually affecting these files? I can't for the life of me understand it.

        • 1. Re: How does my DML affect current redo logs.
          John Thorton

          mlov83 wrote:

           

          I have recently created a materialized view which refreshes roughly 7 million records every 30 minutes. I'm in a developer role and my DBA is less than happy with me since, I have neglected to recognize that my materialized view refresh is set to "refresh complete" which apparently would delete and then reinsert into the target table every 30 minutes and subsequently blows the redo logs. In this instance the database is set to ARCHIVE_MODE. I'm humble enough to say sorry and redo my solution, however, the lesson I learned here, sadly enough, is that I need to be more aware of how the redo logs are being affected by my DML statements. I'm looking for some advice, I have tried reading the documentation and I'm just not finding the answer to my question.

          Is there a way to tell , before I run what my INSERT, UPDATE, DELETE , how that statement will affect the redo logs? I'm looking for a query to at least give me an estimate of how much the next dml would affect the redo logs. I have try selecting from the v$log_history but I can't quite come up with a good query. I have also tried reading the documentation and reading this http://www.dba-oracle.com/tips_oracle_v$_log_history.htm .

          Can someone give me a better explanation how my insert , update, delete are actually affecting these files? I can't for the life of me understand it.


          INSERT, UPDATE, & DELETE each produce both REDO & UNDO in varying amounts.


          For INSERT the REDO will be much larger than the UNDO since REDO must contain every column new value while UNDO only required DELETE flag & ROWID.


          For DELETE the UNDO will be much larger than the REDO since UNDO must contain every column value in the row while REDO only requires DELETE flag & ROWID.


          For UPDATE, the size of both REDO & UNDO will be about the same size and more often than not will less than total size for either INSERT or DELETE.



          1 person found this helpful
          • 2. Re: How does my DML affect current redo logs.
            mlov83

            Thanks Jhon, Is there a way to tell , before I run my INSERT, UPDATE, DELETE , how that statement will affect the redo logs? or guestimate?

            • 3. Re: How does my DML affect current redo logs.
              rp0428

              I have recently created a materialized view which refreshes roughly 7 million records every 30 minutes.

              And why, exactly, are you doing that?

              I'm in a developer role and my DBA is less than happy with me since, I have neglected to recognize that my materialized view refresh is set to "refresh complete" which apparently would delete and then reinsert into the target table every 30 minutes and subsequently blows the redo logs.

              Ok - but that dba is NOT unhappy because you don't recognize the impact. They are unhappy because of the impact you are causing.

              however,  the lesson I learned here, sadly enough, is that I need to be more aware of how the redo logs are being affected by my DML statements.

              That 'awareness' alone isn't the answer. If the requirements call for a complete refresh of that table then that is going to have the effect you report. So you either change the requirements or the dba needs to make adjustments to the REDO process and log configuration being used.

              I have tried reading the documentation

              What documentation? Post a link to the docs and tell us what sections you have read.

              Is there a way to tell , before I run what my INSERT, UPDATE, DELETE , how that statement will affect the redo logs? I'm looking for a query to at least give me an estimate of how much the next dml would affect the redo logs.

              Don't you already have that information?

               

              1. get the size of the logs before your refresh

              2. get the size of the logs after your refresh

              3. subtract the value in #1 from the value in #2

               

              That will tell you how much your operation affected the size of the logs. Obviously you need to take into account any other DML being performed at the same time.

              I have also tried reading the documentation

              Again - WHAT DOCUMENTATION? Post the links and the sections you are reading.

              Can someone give me a better explanation how my insert , update, delete are  actually affecting these files?

              Can you explain how you plan to use that information? You either need to do a complete refresh or you don't. If you do then your dba already knows how the logs are affected and can adjust for that.

               

              If you don't need to do a complete refresh then stop doing it and determine if you can do an incremental refresh instead.

               

              There is nothing wrong with wanting to know how DML affects things but that isn't really going to help you solve your problem. The DBA should already have the info they need to solve the problem assuming you continue to need a complete refresh.

              1 person found this helpful
              • 4. Re: How does my DML affect current redo logs.
                JohnWatson2

                If when you do the refresh you set ATOMIC_REFRESH=>FALSE you should find that the redo generation is reduced hugely.

                I am not aware of any effective way to calculate in advance how much redo a given statement will generate.

                1 person found this helpful
                • 5. Re: How does my DML affect current redo logs.
                  JohnWatson2

                  John Thorton wrote:

                   

                  <snip>

                  INSERT, UPDATE, & DELETE each produce both REDO & UNDO in varying amounts.


                  For INSERT the REDO will be much larger than the UNDO since REDO must contain every column new value while UNDO only required DELETE flag & ROWID.


                  For DELETE the UNDO will be much larger than the REDO since UNDO must contain every column value in the row while REDO only requires DELETE flag & ROWID.


                  For UPDATE, the size of both REDO & UNDO will be about the same size and more often than not will less than total size for either INSERT or DELETE.



                  I did some simple tests a while ago here,

                  Redo and undo volumes | Oracle FAQ

                  redo is always greater than undo, and also greater than the volume of data affected. Usually several times greater. Oracle's mechanism of redo and undo is probably the best yet developed for ACID compliance, but it is not economical when it comes to IO volumes. 

                  1 person found this helpful
                  • 6. Re: How does my DML affect current redo logs.
                    SUPRIYO DEY

                    If you have a test server you can create materialized view and test it and use logminer tool to see what actually happening at that time of redolog and archived redologs

                     

                    https://docs.oracle.com/cd/B19306_01/server.102/b14215/logminer.htm

                     

                    Finding the cause of high archive generation

                    1 person found this helpful
                    • 7. Re: How does my DML affect current redo logs.
                      top.gun

                      Is a fast refresh not an option?

                      1 person found this helpful
                      • 8. Re: How does my DML affect current redo logs.
                        mlov83

                        The materialized view is made up of 5 tables therefore I would have to create 5 materialized view logs.  I was trying not to do that, but that might be the only way.

                        1 person found this helpful
                        • 9. Re: How does my DML affect current redo logs.
                          Andrew Sayer

                          mlov83 wrote:

                           

                          The materialized view is made up of 5 tables therefore I would have to create 5 materialized view logs. I was trying not to do that, but that might be the only way.

                          Why were you trying not to do that?

                          1 person found this helpful
                          • 10. Re: How does my DML affect current redo logs.
                            jgarry

                            It seems the redo change vectors for blocks and the related undo have several steps.  I would guess there is some summation of these when you change all the rows in the blocks, leading to much more redo. https://orainternals.files.wordpress.com/2010/11/update-to-column-value_v2.pdf

                            So one could perhaps use that as a way to predict dml redo volume, though it is much easier to just test and measure like your demo.

                            1 person found this helpful
                            • 11. Re: How does my DML affect current redo logs.
                              Andrew Sayer

                              jgarry wrote:

                               

                              It seems the redo change vectors for blocks and the related undo have several steps. I would guess there is some summation of these when you change all the rows in the blocks, leading to much more redo. https://orainternals.files.wordpress.com/2010/11/update-to-column-value_v2.pdf

                              So one could perhaps use that as a way to predict dml redo volume, though it is much easier to just test and measure like your demo.

                              Dont forget for every action, there is a highly imperformant way of Doing it, I can imagine that lots of insert values in a loop would write more redo than an insert select (or maybe even an insert with bulk array binds).

                               

                              But Of course, the select statement could also be generating its own redo thanks to delayed block clean out.

                               

                              Im sure I’ve seen comments from Jonathan Lewis about a few optimisations that can happen as a result of private redo.

                               

                              Then there‘s also supplimental logging, that could easily create a disaster (but maybe not with an insert).

                               

                              I think I’ll stick with “just measure it”

                              1 person found this helpful