1 2 Previous Next 26 Replies Latest reply: Sep 6, 2013 4:10 PM by rp0428 Go to original post RSS
      • 15. Re: daily batch job
        rp0428

         

        Although we will do this when there are very few DML going on, what is the best way to loop the "alter table XYZ read only" in case there is a uncommitted DML going on? Something like loop every 10 seconds for 5 minutes until it can change the table to read only. Any best practice on this?

         

        You are missing the point. You need to define and document the business rules associated with your extract process. If there is 'uncommitted DML going on' at the time you want to perform your extract you either need to wait for it to complete or you need to terminate those sessions.

         

        Neither of those options lends itself to pure automation with no user input. Don't loop and wait. Create a DBMS_SCHEDULER job and if it detects ongoing DML then the code can simple reschedule the job for 5 minutes later and stop. You should not do this indefinitely; after one or two tries you should send an alert to the DBA or someone so they can take manual action.

         

        Many of the extract processes people use will simply NOT work properly at all if there is DML going on while the extract is being done. The minimum you need to do is:

         

        1. Create a window with NO DML

        2. Begin the extract

         

        The NO DML is needed to make sure that a COMMIT cannot occur that uses a timestamp that is earlier than your extract period as already explained. Whether that can happen and how likely it is to happen depend on how the timestamps are determined.

         

        Once you begin a SELECT statement (e.g. SELECT * FROM myTable) Oracle will ensure that you get the correct versioning of the data even if other users begin making changes to the data AFTER you issue your SELECT.

         

        When you want to extract data for a given time period the ONLY thing you need to make sure of is that there are uncommitted transactions that, if committed, would modify data in the time period you are extracting.

        Regarding the use of materialized view logs, any issue with two sessions updating the same record/ getting an error or a rollback? How do situations like these get handled through the internal triggers?

        The log is just a table like any other. Those 'situations' get handled the same way as for other tables.

        Also, the last comment on your post https://forums.oracle.com/forums/thread.jspa?messageID=11034366&#11034366

        somebody mentioned that the materialized view log wasn't designed for this and puts an example when this would fail that I couldn't understand.

        IMHO that comment is a red-herring. The "log wasn't designed for this" means Oracle didn't implement MV logs for users to use for replication purposes.

         

        Well, of course not. Oracle designed MV logs so that Oracle could use them for replication purposes. When Oracle uses them it uses the logs to replicate table data to materialized views.

         

        So what? You can use them too. You just don't need the actual MV, just the log.

         

        MV Logs were ABSOLUTELY designed to capture the changes made to tables.

         

        So the 'warning' was that if someone comes along later and actually wants to create an MV for the table they may not realize that the MV log on the table is being used for USER REPLICATION.

         

        Again, so what? All that means is that developers need to understand how your system is being used and NOT DO THAT!

         

        If someone wants to add an MV that uses a table as a base table and there is already an MV log on that base table then that developer needs to STOP and THINK and ASK why is there an MV log on that table where there aren't any MVs using that log?

         

        That is only common sense! If the developer just bulls ahead without thinking and assumes they can use that MV log for their own purposes then, yes, there could be consequences. But that applies to almost ANYTHING you implement in your system.

         

        Edited to add missing text.

        • 16. Re: daily batch job
          970021

          Great. What about

           

          SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

           

          or

           

          ALTER SESSION SET ISOLATION_LEVEL = SERIALIZABLE;

           

          Is there a way to make sure there are no uncommitted transaction at the time when my transaction starts, but after I execute my transaction, everybody else can do anything they want.

           

          For example, I want to make sure there are no uncommitted transactions when I execute 'create table t1 as select ...' but as soon as I do that, I don't care what anybody else does. I just want to make sure that MODIFIED_DATE gets update for a period outside of the range I am using.

          • 17. Re: daily batch job
            970021

            rp0428, understood and agreed. I posted before I read your last comment, but it was a slightly different discussion.

             

            Did you text get cut? Not sure if I understand this part:

            Once you begin a SELECT statement (e.g. SELECT * FROM myTable) Oracle will ensure that you get the correct versioning of the data even if other users begin making changes to the data AFTER you issue your SELECT.

            For many extract processes you  When you want to extract data for a given time period the ONLY thing you need to make sure of is that there are

             

            I'm hoping I can use streams or CDC anyway, if they are different... will need to check with the DBA's. But otherwise, materialized view log will work. Thanks!

            • 18. Re: daily batch job
              rp0428

              Did you text get cut?

              Yep! Not sure what happened. I edited my reply to add the missing text.

              Not sure if I understand this part:

              Once you begin a SELECT statement (e.g. SELECT * FROM myTable) Oracle will ensure that you get the correct versioning of the data even if other users begin making changes to the data AFTER you issue your SELECT.

              I'm referring to Oracle's 'Multiversion Concurrency Control' as explained in the Database Concepts doc

              http://docs.oracle.com/cd/B28359_01/server.111/b28318/consist.htm#i17881

              Multiversion Concurrency Control

              Oracle Database automatically provides read consistency to a query so that all the data that the query sees comes from a single point in time (statement-level read consistency).

              . . .

              As a query enters the execution stage, the current system change number (SCN) is determined. In Figure 13-1, this system change number is 10023. As data blocks are read on behalf of the query, only blocks written with the observed SCN are used. Blocks with changed data (more recent SCNs) are reconstructed from data in the rollback segments, and the reconstructed data is returned for the query. Therefore, each query returns all committed data with respect to the SCN recorded at the time that query execution began. Changes of other transactions that occur during a query's execution are not observed, guaranteeing that consistent data is returned for each query.

              When the extract's SELECT statement begins execution that current SCN will be determined and used as explained above.

              • 19. Re: daily batch job
                970021

                I was playing with MATERIALIZED VIEW LOG and realized that it saves the record that has changed but only shows the PK value. Is there a way to save the full record for all the columns instead of just the PK or ROWID? If I have all the columns in the log, I won't need to join to the source table to apply the changes to the target table.

                 

                For example, if the source table has columns COL1, COL2, COL3 and COL1 is the PK, the materialized view log shows the value of COL1 (PK) and indicates it has changed. With this design, I will have to join the log to the source table to get COL1, COL2, COL3 and apply the changes to the target table. Is there a way to populate the log with COL1, COL2, COL3 so I don't need to join to the source table?

                • 20. Re: daily batch job
                  rp0428
                  Is there a way to save the full record for all the columns instead of just the PK or ROWID?

                  Oh common, be serious!

                   

                  It's followup questions like that that really show how lazy some people are. You OBVIOUSLY didn't even look at the example code I posted in the link I provided or you wouldn't even be asking that question.

                   

                  Show some initiative and actually read peoples replies!

                  • 21. Re: daily batch job
                    970021

                    Oops, I see it. Sorry!

                     

                    I actually read it twice. Then I got side tracked and googled how MV logs work and tested it based on that documentation, which didn't have an example to log changes to all columns.

                     

                    I'll have to experiment with it a little more tomorrow regarding commit and sessions and how they affect the logs. I can actually use the materialized views fast refresh! It will have to be one materialized view based on multiple materialized view logs based on multiple tables. I will also have to experiment with purging, but is PURGE_MVIEW_FROM_LOG to be used to purge records from the materialized view log records that made it to the materialized view? Is there anything I need to be aware of in terms of data integrity/ timing?

                    • 22. Re: daily batch job
                      rp0428
                      I can actually use the materialized views fast refresh! It will have to be one materialized view based on multiple materialized view logs based on multiple tables

                      Well that certainly is NOT what I have been talking about. For the THIRD TIME:

                      I said use an MV log; I didn't say use a materializled view.

                      If you want to use an MV go ahead. But that will NOT prevent the problem being discussed in this thread where changes might be missed because of when a COMMIT happened relative to the date used in a column such as MODIFIED_DATE.

                       

                      Use only an MV log and pull data the way I suggested in that thread:

                      Just pull the data that you haven't mined/processed yet based on SNAPTIME$$ and then delete those rows when you are done with them.
                      • 23. Re: daily batch job
                        970021

                        I said use an MV log; I didn't say use a materializled view.

                         

                        I certainly will be using the materialized view logs. If I use the materialized view on top of the MV logs and use fast refresh to make use of the materialized view logs, will I still have the problem of commit?

                        If you want to use an MV go ahead. But that will NOT prevent the problem being discussed in this thread where changes might be missed because of when a COMMIT happened relative to the date used in a column such as MODIFIED_DATE.

                         

                        If I use the MV logs, I don't need to track MODIFIED_DT anymore, no? The whole purpose of using MODIFIED_DT was if I didn't use the MV log. But with the functionality provided by MV log, I don't think I need to use MODIFIED_DT anymore.

                        • 24. Re: daily batch job
                          rp0428
                          If I use the materialized view on top of the MV logs and use fast refresh to make use of the materialized view logs, will I still have the problem of commit?

                          How can you even ask that question if you have read the quote of mine that you, yourself, just included in your reply?

                           

                          An MV is a table. NOTHING in a table is visible to other sessions until it has been COMMITTED. Like any other table if you do NOT use a column such as MODIFIED_DATE then how do you plan to extract data from the MV based on a desired time period?

                           

                          And if you DO use such a column you are back to the same issue you started with: uncommitted DML that, when COMMITTED populates that MODIFIED_DATE column with a value that precedes your data pull. And so on, and so on.

                          • 25. Re: daily batch job
                            970021

                            If I use the materialized view on top of the MV logs and use fast refresh to make use of the materialized view logs, will I still have the problem of commit?

                            You've been very good and clear with your explanation so far and I certainly appreciate it. I realize I'm making it very confusing with my statements. I didn't mean to create MV log on top of the MV like I state above, which is confusing. Let me try one more time since I want to get your feedback on this approach:

                             

                            I have two base tables (SRC_TABLE1, SRC_TABLE2):

                            Create table SRC_TABLE1 (

                            COL1 number(10) primary key,

                            COL2 varchar2(40));

                             

                            Create table SRC_TABLE2 (

                            ABC1 number(10) primary key,

                            ABC2 varchar2(40));

                             

                            I would create a materialized view logs on these tables:

                             

                            CREATE MATERIALIZED VIEW LOG ON SRC_TABLE1

                               WITH ROWID, SEQUENCE (COL2),  PRIMARY KEY

                               INCLUDING NEW VALUES;

                             

                            CREATE MATERIALIZED VIEW LOG ON SRC_TABLE2

                               WITH ROWID, SEQUENCE (ABC2),  PRIMARY KEY

                               INCLUDING NEW VALUES;

                             

                            Then I have a materialized view as:

                            CREATE MATERIALIZED VIEW TEST_MV

                            REFRESH FAST AS

                            SELECT SRC1.COL1, SRC1.COL2, SRC2.ABC1, SRC2.ABC2, SRC1.ROWID SRC1_ROWID, SRC2.ROWID SRC2_ROWID

                            FROM SRC_TABLE1 SRC1, SRC_TABLE2 SRC2

                            where SRC1.COL1 = SRC2.ABC1;

                             

                            Then I would use refresh to move the changes to the materialized view from the materialized view log:

                            EXEC DBMS_MVIEW.REFRESH('T_MV');

                             

                            I believe this way, using the mv logs and using the MV, I take care of the original issue of timing. The whole purpose of using MODIFIED_DATE was a custom solution and MV logs + MV take care of the issue of timing. Can you advise?

                            • 26. Re: daily batch job
                              rp0428
                              I didn't mean to create MV log on top of the MV like I state above

                              What? That is NOT what you stated above.

                               

                              You stated you would create an MV on top of the MV log and you just now repeated that.

                              I believe this way, using the mv logs and using the MV, I take care of the original issue of timing. The whole purpose of using MODIFIED_DATE was a custom solution and MV logs + MV take care of the issue of timing.

                              Well if you believe that then mark the thread ANSWERED. Good luck with that!

                               

                              I don't believe that at all because this is what you said your problem was when  you created the thread

                              If I have a daily batch job that need to pick up the records that have been modified during the past 24 hours and do some processing

                              That MV has NO information WHEN anything was modified. And since it will be updated simultaneously with the MV LOGs using the MV adds NO VALUE at all.

                               

                              It is the LOGs that have the information about the changes, when they occured and in what order they occured.

                              Can you advise?

                              Nope! You don't really seem to want advice and you just claimed that using that MV will solve your problem. So there is nothing more you need from us.

                              1 2 Previous Next