1 2 Previous Next 26 Replies Latest reply on Oct 28, 2017 4:41 AM by connor_mc_d-Oracle Go to original post
      • 15. Re: Using Materialized view logs to track data changes
        connor_mc_d-Oracle

        MOS note 1313291.1 is a good reference for anyone having slow refresh issues.

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

          hi Connor,

          I have a few questions regarding the current mview implementation, some of them not related with OP's question and I apologize for that.

          1.If a mview log is created using “with sequence” option, does it mean the sequence$$ in the mview log table will always be increasing? Then in OP's situation we can easily find the max sequence$$ before our processing to identify which entries need to be deleted afterwards. I have this concern because I guess it's populated from a sequence and in the RAC environment each node will have its own sequence cache.

          2.We have some mviews with joins, and we found out sometimes an update on the base table will result in insert+delete on the mview, not delete+insert. Because insert comes first, we have to make the primary key or unique constraint deferrable. This is fine, but in case the mview is replicated by GG, on the target site it will delete by primary key, and after the refresh it deletes  not only the old data but also the new one. If delete comes before insert then this will not be an issue.  What is the reason behind this implementation?

          3.In 12c there's a out_of_place option for mview refresh, and we found the stats on the mview are gone after a full refresh. Is there a way to let it collect the stats while running the refresh?

          4.Earlier this year, the plsqlchallenge site was down for quite a few days because of the mview refresh issue and you helped to solve this problem. Could you write an article to share this experience?  Thank you!

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

            James Su wrote:

            ...

            3.In 12c there's a out_of_place option for mview refresh, and we found the stats on the mview are gone after a full refresh. Is there a way to let it collect the stats while running the refresh?

            ...

            I'm not sure how the stats are collected during an out_of_place refresh. It might depend what method is used. You already mentioned a full refresh.

             

            The new out_of_place refresh is very similar to using a prebuild table. The prebuild table approach was already possible before out_of_place refresh existed. You could consider if the older method would be a feasible alternative for you.

            It is possible to create stats on this prebuild table, before connecting it with the MV.  It is by far more development work compared to the out_of_place refresh and there is a small point when you can't access the MV (because the ALTER MVIEW statement switches it to the prebuild table). So I'm not sure how pressing that issue is for you.

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

              hi Sven, yes we do have alternative approach by creating a staging table with indexes/stats, then do partition exchange with stats. In this case we don't need the mview since all we want is the data. But that's a lot more work than a simple out of place refresh. When we create a new index the stats on the index will be auto collected, so I think maybe Oracle can do the same magic in mview refresh.

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

                Re 1), A quick poke around the dictionary suggests this is the sequence of interest, and you can that the ORDER flag is set, so you should be OK for RAC

                 

                SQL> select * from dba_sequences where sequence_name = 'CDC_RSID_SEQ$'

                  2  @pr

                ==============================

                SEQUENCE_OWNER : SYS

                SEQUENCE_NAME : CDC_RSID_SEQ$

                MIN_VALUE : 1

                MAX_VALUE : 9999999999999999999999999999

                INCREMENT_BY : 1

                CYCLE_FLAG : N

                ORDER_FLAG : Y

                CACHE_SIZE : 10000

                LAST_NUMBER : 20001

                SCALE_FLAG                    : N

                EXTEND_FLAG : N

                SESSION_FLAG : N

                KEEP_VALUE : N

                 

                Re 3)  perhaps thats a bug that has been fixed.  I cant replicate in 12.2.0.1

                 

                 

                 

                SQL> create table t (

                  2  x int ,

                  3  y int );

                 

                Table created.

                 

                SQL>

                SQL> insert into t

                  2  select rownum, rownum

                  3  from dual

                  4  connect by level <= 10;

                 

                10 rows created.

                 

                SQL>

                SQL> commit;

                 

                Commit complete.

                 

                SQL>

                SQL> create materialized view mv_t

                  2  build immediate

                  3  refresh on demand

                  4  as select * from t;

                 

                Materialized view created.

                 

                SQL>

                SQL> select num_rows from user_tables where table_name = 'MV_T';

                 

                  NUM_ROWS

                ----------

                        10

                 

                1 row selected.

                 

                SQL> exec DBMS_MVIEW.REFRESH('mv_t', method => '?', atomic_refresh => FALSE, out_of_place => TRUE);

                 

                PL/SQL procedure successfully completed.

                 

                SQL>

                SQL> select num_rows from user_tables where table_name = 'MV_T';

                 

                  NUM_ROWS

                ----------

                        10

                 

                1 row selected.

                 

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

                  Re 2, perhaps you could lob a test case on AskTOM for us :-)

                  Re 4, it was basically standard SQL tuning - but I'll speak to Steven and see what we can put together.

                   

                  Anything that keeps from having to use this terrible text editor on the forums is a welcome distraction :-)

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

                    Thanks Connor, I already submitted a test case on the asktom site.

                    I'm surprised to know that Oracle uses one single ordered sequence for all mview logs. Won't it cause performance issue on a busy system?

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

                      James Su wrote:

                       

                      hi Connor,

                      I have a few questions regarding the current mview implementation, some of them not related with OP's question and I apologize for that.

                      1.If a mview log is created using “with sequence” option, does it mean the sequence$$ in the mview log table will always be increasing? Then in OP's situation we can easily find the max sequence$$ before our processing to identify which entries need to be deleted afterwards. I have this concern because I guess it's populated from a sequence and in the RAC environment each node will have its own sequence cache.

                      2.We have some mviews with joins, and we found out sometimes an update on the base table will result in insert+delete on the mview, not delete+insert. Because insert comes first, we have to make the primary key or unique constraint deferrable. This is fine, but in case the mview is replicated by GG, on the target site it will delete by primary key, and after the refresh it deletes not only the old data but also the new one. If delete comes before insert then this will not be an issue. What is the reason behind this implementation?

                      3.In 12c there's a out_of_place option for mview refresh, and we found the stats on the mview are gone after a full refresh. Is there a way to let it collect the stats while running the refresh?

                      4.Earlier this year, the plsqlchallenge site was down for quite a few days because of the mview refresh issue and you helped to solve this problem. Could you write an article to share this experience? Thank you!

                      1:

                      The sequence value is generated by the DML, it is not generated by the commit. So you can have a situation like this. I've already done an insert into my table in another session but I did not commit yet:

                       

                      ANDY@pdb1>select * from mlog$_as_test_mv;

                      no rows selected

                      ANDY@pdb1>insert into as_Test_mv values (3);

                      1 row created.

                      ANDY@pdb1>select * from mlog$_as_test_mv;

                            COL1 SEQUENCE$$ SNAPTIME$$          D O
                      ---------- ---------- ------------------- - -
                      CHANGE_VECTOR$$
                      ------------------------------------------------------------
                           XID$$
                      ----------
                               3    2280002 01/01/4000 00:00:00 I N
                      FE
                      8.4452E+14


                      ANDY@pdb1>commit;

                      Commit complete.

                      ANDY@pdb1>select * from mlog$_as_test_mv;

                            COL1 SEQUENCE$$ SNAPTIME$$          D O
                      ---------- ---------- ------------------- - -
                      CHANGE_VECTOR$$
                      ------------------------------------------------------------
                           XID$$
                      ----------
                               3    2280002 01/01/4000 00:00:00 I N
                      FE
                      8.4452E+14

                       

                      At this point, my other session commits.


                      ANDY@pdb1>select * from mlog$_as_test_mv;

                            COL1 SEQUENCE$$ SNAPTIME$$          D O
                      ---------- ---------- ------------------- - -
                      CHANGE_VECTOR$$
                      ------------------------------------------------------------
                           XID$$
                      ----------
                               3    2280002 01/01/4000 00:00:00 I N
                      FE
                      8.4452E+14

                               2    2280001 01/01/4000 00:00:00 I N
                      FE
                      1.1259E+15

                       

                      Note how the newly visible row has a lower value than the one I could previously see

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

                        You are right, I just recalled a similar issue "A Common Data Warehousing Technique That Fails" mentioned in Tom's book . Thank you!

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

                          SQL> create sequence seq cache 10000;

                           

                          Sequence created.

                           

                          SQL> set timing on

                          SQL> declare

                            2    x number;

                            3  begin

                            4    for i in 1 .. 1000000 loop

                            5       x:= seq.nextval;

                            6    end loop;

                            7  end;

                            8  /

                           

                          PL/SQL procedure successfully completed.

                           

                          Elapsed: 00:00:10.64

                           

                          That suggests around 100,000 per second, but of course, much of that is more likely context switching between SQL and PL/SQL. We can compare the following two blocks:

                           

                          SQL> begin

                            2  for i in (

                            3    select 1 x

                            4    from dual connect by level <= 2000000 )

                            5  loop

                            6    null;

                            7  end loop;

                            8  end;

                            9  /

                           

                          PL/SQL procedure successfully completed.

                           

                          Elapsed: 00:00:03.63

                          SQL>

                          SQL> begin

                            2  for i in (

                            3    select seq.nextval x

                            4    from dual connect by level <= 2000000 )

                            5  loop

                            6    null;

                            7  end loop;

                            8  end;

                            9  /

                           

                          PL/SQL procedure successfully completed.

                           

                          Elapsed: 00:00:06.19

                           

                           

                          Which suggests the sequence consumption cost is around 600,000 per second.  I’d contend that if you are putting 600,000 rows per second into a materialized view log (for a fast refresh) you perhaps are not using the right implementation :-)

                           

                          Don’t get me wrong – I’m not dismissing this out of hand. The “ORDER” on the sequence definitely does mean that care should be taken in a RAC environment because this will increase cross-instance chatter.

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

                            That suggests around 100,000 per second, but of course, much of that is more likely context switching between SQL and PL/SQL.

                            How many context switches are there?

                            SQL> create sequence seq cache 10000;

                            . . .

                            5       x:= seq.nextval;

                            The above is a PL/SQL assignment statement above and the sequence uses a cache of 10000 so I would expect only 100 or so context switches.

                             

                            My understanding of 'cache' is that the size of the cache has no effect at all on the amount of memory used. (see my reply #12 in this thread and the quote from an AskTom blog - Two basic questions on Sequences

                            (where only the current value

                            So if the sequence variables are cached why would SQL be used to grab one?

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

                              Throw a trace on "x := seq.nextval".  You'll see it is a *coding* convenience.  Internally, it will be processed as:   select seq.nextval from dual

                               

                              Hence the context switch.

                              1 2 Previous Next