1 2 Previous Next 26 Replies Latest reply on Oct 28, 2017 4:41 AM by connor_mc_d-Oracle

    Using Materialized view logs to track data changes

    johnnie.billings

      Hello, We have a solution put in place by a consultant and I am curious what some of the experts out there think of this use of MV's.

       

      We are using Oracle EBS and the requirement was to track order changes by week.  The consultant setup materialized views on the EBS tables that we needed to track changes on.  Every few hours a batch job queries the materialized view logs to see what changes have occurred and save the changes in custom tables.  After all of the changes have been processed the materialized views are refreshed.

       

      I don't have a specific issue I am trying to solve, I'm just curious if you think this is valid use of materialized views and possibly what are the some of the limitations or issues I should keep an eye out for.  If you need more details let me know.

       

      DB version: 12.1.0.2.0

        • 2. Re: Using Materialized view logs to track data changes

          Hello, We have a solution put in place by a consultant and I am curious what some of the experts out there think of this use of MV's.

          Ok - but to be clear - you haven't posted any info about 'use of MVs'. What you posted is about use of MV logs.

           

          They are two DIFFERENT, but related, things.

          We are using Oracle EBS and the requirement was to track order changes by week. The consultant setup materialized views on the EBS tables that we
          needed to track changes on. Every few hours a batch job queries the materialized view logs to see what changes have occurred and save the changes in custom tables.

          That is a use, and a common one, of MV logs. An MV log is the simplest and easiest way I know to capture DML changes to tables.

          After all of the changes have been processed the materialized views are refreshed.

          Now you are talking about MVs. Are you sure you actually have any MVs? Just because you have MV logs doesn't mean you have MVs.

           

          I'm just curious if you think this is valid use of materialized views and possibly what are the some of the limitations or issues I should keep an eye out for.

          That has NOTHING to do with 'use of materialized views'.

           

          A PHYSICAL materialized view is just a table like any other table that  you might create. But associated with that table are special data dictionary entries that tell Oracle about the query used to populate that table.

           

          Also associated with an MV is an MV log table (associated with a 'base' table) that Oracle CAN use to refresh the actual MV physical table. It can do the refresh ON DEMAND or can do a FAST or COMPLETE refresh when data is commited.

           

          1. you add an MV log to a base table

          2. you create an MV based on a query you provide

          3. you/Oracle refreshes the MV

           

          The Oracle MV process can use the info in the MV log table to refresh the MV. When it does it then deletes the change info it uses.

           

          For an ON DEMAND MV you can also use the info in the MV log table if you wish. It appears that is what your consultant's did. They query the log table to populate change tables.

           

          Somebody (you or Oracle) has to delete the MV log info when they are done using it.

           

          After you (your consultants) use the info they would either delete the info themselves or refresh the associated MV which will cause Oracle to delete that info.

           

          My guess is:

           

          1. The MVs are ON DEMAND

          2. Periodically a process is run to query the info from the MV log and populate change tables. The MV log info is NOT deleted.

          3. The MVs are refreshed which will update the MV table and cleanse the MV log table of the changes that were applied.

           

          So yes - that is a common use of MV logs.

          1 person found this helpful
          • 3. Re: Using Materialized view logs to track data changes
            James Su

            Can you demonstrate the definition of the mview?

            If the only purpose of the mview refresh is to clear the log, then you are probably on the wrong way, because new entries may be created in the log during your "process".

            1 person found this helpful
            • 4. Re: Using Materialized view logs to track data changes
              johnnie.billings

              1. Yes the MVs are on demand.

              2. The process does query the info from the MV logs and populates the custom tables.

              3. The last thing the process does is refresh the MV table to clear the MV logs.

              • 5. Re: Using Materialized view logs to track data changes
                johnnie.billings

                Yes James the only purpose of the MV refresh is to clear the log. I share your concern that any activity that occurs while the process is running could be missed.  The way the batch process works:

                 

                1. The MV logs are queried to identify the data that has been modified.
                2. The identified data is then processed into the history tables.
                3. At the very end the MV are refreshed to clear the logs.

                 

                It would seem that any changes that occur to the MV while this batch process is running could be missed.  Any thoughts on how to minimize this without a complete re-write?

                Thanks.

                • 6. Re: Using Materialized view logs to track data changes
                  James Su

                  We have to run DML on the mview log table to do a manual delete. During the process you need to find a way to remember the log records that you have read.

                  • 7. Re: Using Materialized view logs to track data changes
                    James Su

                    Here's one way:

                    Make a copy of the miew log table: create table mlog_bak as select log.*,rowid rid from mlog$_my_base_table log where 1=0;

                     

                     

                    At the beginning of your process:

                    insert into mlog_bak select log.*,rowid from mlog$_my_base_table log;

                    delete mlog$_my_base_table where rowid in (select rid from mlog_bak);

                     

                     

                    Then in your process procedure, reference mlog_bak instead of the mlog table. At the end you can truncate or delete from mlog_bak.

                    1 person found this helpful
                    • 8. Re: Using Materialized view logs to track data changes

                      It would seem that any changes that occur to the MV while this batch process is running could be missed.

                      Huh?

                       

                      You said the fresh was ON DEMAND. So the only other way changes can occur to the MV itself is if you let users make changes directly by performing DML on the MV.

                       

                      Any thoughts on how to minimize this without a complete re-write?

                      Don't let people perform DML on the MV.

                       

                      You won't miss any changes made to the base tables since those are captured to the MV logs on the base tables.

                       

                      And your 'batch process' SHOULD BE processing log data BETWEEN specifiec sequence values.

                       

                      See my example code in this 5 year old thread.

                      https://forums.oracle.com/forums/thread.jspa?messageID=11034366&#11034366

                      • 9. Re: Using Materialized view logs to track data changes
                        Sven W.
                        ...The way the batch process works:

                         

                        1. The MV logs are queried to identify the data that has been modified.
                        2. The identified data is then processed into the history tables.
                        3. At the very end the MV are refreshed to clear the logs.

                         

                        It would seem that any changes that occur to the MV while this batch process is running could be missed. Any thoughts on how to minimize this without a complete re-write?

                        You are right to be concerned about potential data losses during that process.

                        And additionally data is duplicated into the MVs which is not really neccessary.

                         

                        It might depend on the specifics of that process.

                        If for example in step 1 the logs are accessed AND LOCKED (using select for update), then this can potentially avoid data losses, but at the very high cost of stalling active DMLs that run against the base tables. I never tested if we are allowed to LOCK the MV-Logs. But we could lock the base tables.

                         

                        Some more comments.

                         

                        Be aware that direct path loads/inserts are not included the MV logs.

                        So if you have such operations on your base tables, then those records will not be shown in the MV logs.

                         

                        You can avoid the data duplication by calling dbms_mview.purge_log and dbms_mview.purge_direct_load_log

                         

                        I would have suggested Oracle (Advanced) Replication, but unfortunatly it is deprecated (12.1) and desupported (12.2).

                        1 person found this helpful
                        • 10. Re: Using Materialized view logs to track data changes
                          AndrewSayer

                          johnnie.billings wrote:

                           

                          Yes James the only purpose of the MV refresh is to clear the log. I share your concern that any activity that occurs while the process is running could be missed. The way the batch process works:

                           

                          1. The MV logs are queried to identify the data that has been modified.
                          2. The identified data is then processed into the history tables.
                          3. At the very end the MV are refreshed to clear the logs.

                           

                          It would seem that any changes that occur to the MV while this batch process is running could be missed. Any thoughts on how to minimize this without a complete re-write?

                          Thanks.

                          Your MV logs will be purged using the point in time that your MV was refreshed, if you're doing your own queries on the MV logs before this then yes you will miss data.

                           

                          Why bother with the MVs though? Do your own purging, it's surprisingly easy to mimic what Oracle is doing for you.

                           

                          create table track_me (pk_col number primary key, stuff varchar2(30));

                          create materialized view log on track_me with primary key, sequence (stuff) including new values;

                          create sequence track_me_seq;

                          insert into track_me values (track_me_seq.nextval, 'Start 1');

                          insert into track_me values (track_me_seq.nextval, 'Start 2');

                          commit;

                           

                          update track_me set stuff = 'Updated 2' where pk_col = 2;

                           

                          prompt NOT COMMITTED YET

                           

                          Separate session:

                          ANDY@pdb1>select * from mlog$_track_me;

                              PK_COL STUFF                          SEQUENCE$$ SNAPTIME$$          D O
                          ---------- ------------------------------ ---------- ------------------- - -
                          CHANGE_VECTOR$$
                          ------------------------------------------------------------------------------------------------------------------------------------------
                              XID$$
                          ----------
                                  1 Start 1                          2270001 01/01/4000 00:00:00 I N
                          FE
                          1.1260E+15

                                  2 Start 2                          2270002 01/01/4000 00:00:00 I N
                          FE
                          1.1260E+15


                          ANDY@pdb1>var snapDate varchar2(12)
                          ANDY@pdb1>exec :snapDate := to_char(sysdate,'yyyymmddhh24mi:ss')

                          PL/SQL procedure successfully completed.

                          ANDY@pdb1>update mlog$_track_me set snaptime$$ = to_date(:snapDate,'yyyymmddhh24mi:ss');

                          2 rows updated.

                          PROMPT At this point I've marked all the tracked changes I can use, for my processes, whatever they are.
                          PROMPT The other session commits the open transaction (the update), maybe other sessions do some work too but this is just a quick demo

                          ANDY@pdb1>select * from mlog$_track_me where snaptime$$ = to_date(:snapDate,'yyyymmddhh24mi:ss');

                              PK_COL STUFF                          SEQUENCE$$ SNAPTIME$$          D O
                          ---------- ------------------------------ ---------- ------------------- - -
                          CHANGE_VECTOR$$
                          ------------------------------------------------------------------------------------------------------------------------------------------
                              XID$$
                          ----------
                                  1 Start 1                          2270001 25/10/2017 22:59:00 I N
                          FE
                          1.1260E+15

                                  2 Start 2                          2270002 25/10/2017 22:59:00 I N
                          FE
                          1.1260E+15

                          PROMPT Now I can purge exactly what I've used and only that.

                          ANDY@pdb1>delete mlog$_track_me where snaptime$$ = to_date(:snapDate,'yyyymmddhh24mi:ss');

                          2 rows deleted.

                          ANDY@pdb1>select * from mlog$_track_me;

                              PK_COL STUFF                          SEQUENCE$$ SNAPTIME$$          D O
                          ---------- ------------------------------ ---------- ------------------- - -
                          CHANGE_VECTOR$$
                          ------------------------------------------------------------------------------------------------------------------------------------------
                              XID$$
                          ----------
                                  2 Start 2                          2270003 01/01/4000 00:00:00 U O
                          04
                          1.4074E+15

                                  2 Updated 2                        2270004 01/01/4000 00:00:00 U N
                          04
                          1.4074E+15

                          PROMPT Now I can see only new changes since I did some processing. Nothing missed.

                          1 person found this helpful
                          • 11. Re: Using Materialized view logs to track data changes
                            connor_mc_d-Oracle

                            Well...that's an interesting approach.

                             

                            I'll be curious to see what happens when:

                             

                            a) we change the implementation where materialized view logs are buffered in memory and all data is not visible in a transactionally consistent manner, or

                            b) we hide the table from query access, or
                            c) we remove the table altogether and have a goldengate stub that mines the info we need from the redo logs, or

                            d) any other thing else to comes to mind in future releases of the database

                             

                            If it works for you ... fine, but be aware that the moment you build something that has a dependency on how we implement *internal* structures, then you're sitting on a house of cards.

                             

                            Just my $0.02 worth.

                            1 person found this helpful
                            • 12. Re: Using Materialized view logs to track data changes

                              Well...that's an interesting approach.

                              What 'approach' of OPs are you referring to. Please be specific.

                               

                              If it works for you ... fine, but be aware that the moment you build something that has a dependency on how we implement *internal* structures, then you're sitting on a house of cards.

                               

                              An MV log isn't an 'internal structure as far as I know. The MV is on demand so Oracle doesn't do ANYTHING with the contents of the log table until/unless directed to do so by a manual command (e.g. REFRESH).

                               

                              I don't see any dependency on any internal structure.

                               

                              What 'dependency' on internal structures are you referring to?

                              • 13. Re: Using Materialized view logs to track data changes
                                connor_mc_d-Oracle

                                A *materialized view log* is not an internal structure, but the *table* that *we* create to support its functionality definitely is.

                                 

                                It's similar to a Text index.  A *text index* is a supported structure that people are welcome and encouraged to use, but the fact that we create a whole stack of DR$ prefixed objects to support it isn't an endorsement that you can *in any way* depend on those tables being there, or changing between releases, or anything else.

                                 

                                We will always have the *function* of a materialized view log, ie, tracking changes.

                                 

                                But in 18c, we might *implement* that with

                                 

                                a) no MLOG$ table

                                b) two MLOG$ tables

                                c) 100 MLOG$ tables

                                d) the same MLOG$ tables but all of the columns encrypted from public view

                                e) any other permutation that works best *for us*

                                 

                                 

                                We don't stop anyone from querying the *underlying structures* of a materialized view log.... but if you build an entire application infrastructure assuming that there is always going to be (say) a column called SNAPTIME$$, then please don't be upset if we remove that column in a future release and break everything you've built.

                                1 person found this helpful
                                • 14. Re: Using Materialized view logs to track data changes
                                  AndrewSayer

                                  connor_mc_d-Oracle wrote:

                                   

                                  A *materialized view log* is not an internal structure, but the *table* that *we* create to support its functionality definitely is.

                                   

                                  It's similar to a Text index. A *text index* is a supported structure that people are welcome and encouraged to use, but the fact that we create a whole stack of DR$ prefixed objects to support it isn't an endorsement that you can *in any way* depend on those tables being there, or changing between releases, or anything else.

                                   

                                  We will always have the *function* of a materialized view log, ie, tracking changes.

                                   

                                  But in 18c, we might *implement* that with

                                   

                                  a) no MLOG$ table

                                  b) two MLOG$ tables

                                  c) 100 MLOG$ tables

                                  d) the same MLOG$ tables but all of the columns encrypted from public view

                                  e) any other permutation that works best *for us*

                                   

                                   

                                  We don't stop anyone from querying the *underlying structures* of a materialized view log.... but if you build an entire application infrastructure assuming that there is always going to be (say) a column called SNAPTIME$$, then please don't be upset if we remove that column in a future release and break everything you've built.

                                  I agree with the warnings about support and future usage. It wouldn’t be the first time a free replication method stopped being usable (We’re currently paying for GG since CDC no longer works with different charactersets in source and target in 12c)

                                   

                                  The current implementation that Johnnie is using is something I’ve spotted in some documentation for external ETL tools (I thought it was Informatica but I can’t find the note googling it now). While it has the problem of consistency (as discussed), would a large company recommending the use of it to multiple clients mean that Oracle would feel a lot more pressure to maintain support?

                                   

                                  If something were to happen with the implementation, replacing the mview log with a table populated by a trigger *gasp* would be a work around enabling the method I shared.

                                   

                                  As a side rant, if Oracle were to do anything to improve the performance of a mview refresh using an mview log, it should start by looking at the SQL and execution plans that it comes up with - hints forcing fast refreshes to do full tablescans of the mview and source table is a bit weird. When I last looked into it, there were bug notes and superseded big notes covering most versions.

                                  1 2 Previous Next