1 2 Previous Next 27 Replies Latest reply: Mar 7, 2013 1:09 PM by user646034 Go to original post RSS
      • 15. Re: need help on tuning materialized view refresh
        Jonathan Lewis
        user646034 wrote:

        Possibly this delete statement failed with snapshot too old and rolled back - note that the number of rows deleted is reported as zero, even though 190M have been identified for deletion. The time spent on single block reads, and the number occurring, is consistent with the summary stats.
        The Delete query during the materialized view refresh was taking very long time hence i kill it in between.

        GL_SETS_OF_BOOKS contains a set of books. we currently have 6 and in there is more than 450000000. one set_of_books_id is mapped to more than 100000000 hence here delete cause a problem.
        The KEY problem isn't really the plan chosen (and the hash join plan may be quicker) - it's in the fact that you're trying to delete 100M (or, in the original example) 190M+ rows) from the materialized view. I THINK this is because someone changed a couple of the set_of_books rows, though it may simply be a side effect of the four table join going through the sets_of_books table.

        What does the definition of the materialized view log on gl_sets_of_books look like ? (Give us the "create mv" statement)

        Regards
        Jonathan Lewis
        • 16. Re: need help on tuning materialized view refresh
          user646034
          Hi

          The KEY problem isn't really the plan chosen (and the hash join plan may be quicker) - it's in the fact that you're trying to delete 100M (or, in the original example) 190M+ rows) from the materialized view. I THINK this is because someone changed a couple of the set_of_books rows, though it may simply be a side effect of the four table join going through the sets_of_books table.

          Yes, it was changed. Whenever the set_of_books_id will get changed. It will changed the below number of rows.

          COUNT(*) SET_OF_BOOKS_ID
          ---------- ---------------
          180277562 1
          291277032 25
          202957 23
          61908 26


          As i said before Initially we were complete refresh which is taking more than 90 as it is inserting more than 45000000
          CREATE MATERIALIZED VIEW HDFC_REP_GL_TRIAL_BAL_DAN_MV1
          REFRESH FAST ON DEMAND
          ENABLE QUERY REWRITE
          AS
          SELECT GL.GL_CODE_COMBINATIONS21.ROWID C1, GL.GL_BALANCES21.ROWID C2, GL.GL_SETS_OF_BOOKS.ROWID C3, GL.GL_PERIODS.ROWID C4,
          "GL"."GL_BALANCES21"."ACTUAL_FLAG" M1, "GL"."GL_BALANCES21"."CURRENCY_CODE" M2, "GL"."GL_BALANCES21"."PERIOD_NUM" M3, 
          "GL"."GL_BALANCES21"."PERIOD_YEAR" M4,"GL"."GL_BALANCES21"."SET_OF_BOOKS_ID" M5, "GL"."GL_CODE_COMBINATIONS21"."CODE_COMBINATION_ID" M6, 
          "GL"."GL_CODE_COMBINATIONS21"."SEGMENT1" M7, "GL"."GL_CODE_COMBINATIONS21"."SEGMENT10" M8,
          "GL"."GL_CODE_COMBINATIONS21"."SEGMENT11" M9, "GL"."GL_CODE_COMBINATIONS21"."SEGMENT12" M10, 
          "GL"."GL_CODE_COMBINATIONS21"."SEGMENT13" M11, "GL"."GL_CODE_COMBINATIONS21"."SEGMENT14" M12, "GL"."GL_CODE_COMBINATIONS21"."SEGMENT2" M13,
          "GL"."GL_CODE_COMBINATIONS21"."SEGMENT3" M14, "GL"."GL_CODE_COMBINATIONS21"."SEGMENT4" M15, 
          "GL"."GL_CODE_COMBINATIONS21"."SEGMENT5" M16, "GL"."GL_CODE_COMBINATIONS21"."SEGMENT6" M17,
          "GL"."GL_CODE_COMBINATIONS21"."SEGMENT7" M18, "GL"."GL_CODE_COMBINATIONS21"."SEGMENT8" M19, 
          "GL"."GL_CODE_COMBINATIONS21"."SEGMENT9" M20, "GL"."GL_PERIODS"."PERIOD_NAME" M21, NVL("GL"."GL_BALANCES21"."BEGIN_BALANCE_CR",0) M22,
          NVL("GL"."GL_BALANCES21"."BEGIN_BALANCE_CR",0)+NVL("GL"."GL_BALANCES21"."PERIOD_NET_CR",0) M23,
           NVL("GL"."GL_BALANCES21"."BEGIN_BALANCE_DR",0) M24, NVL("GL"."GL_BALANCES21"."BEGIN_BALANCE_DR",0)+NVL("GL"."GL_BALANCES21"."PERIOD_NET_DR",0) M25,
          NVL("GL"."GL_BALANCES21"."BEGIN_BALANCE_DR",0)-NVL("GL"."GL_BALANCES21"."BEGIN_BALANCE_CR",0) M26, NVL("GL"."GL_BALANCES21"."BEGIN_BALANCE_DR",0)-NVL("GL"."GL_BALANCES21"."BEGIN_BALANCE_CR",0)+NVL("GL"."GL_BALANCES21"."PERIOD_NET_DR",0)-NVL("GL"."GL_BALANCES21"."PERIOD_NET_CR",0) M27,
          NVL("GL"."GL_BALANCES21"."PERIOD_NET_CR",0) M28, NVL("GL"."GL_BALANCES21"."PERIOD_NET_DR",0) M29 
          FROM GL.GL_CODE_COMBINATIONS2121, GL.GL_BALANCES2121, 
          GL.GL_SETS_OF_BOOKS, GL.GL_PERIODS 
          WHERE GL.GL_BALANCES21.CODE_COMBINATION_ID = GL.GL_CODE_COMBINATIONS21.CODE_COMBINATION_ID 
          AND GL.GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID = GL.GL_BALANCES21.SET_OF_BOOKS_ID 
          AND GL.GL_PERIODS.PERIOD_NUM = GL.GL_BALANCES21.PERIOD_NUM 
          AND GL.GL_PERIODS.PERIOD_YEAR = GL.GL_BALANCES21.PERIOD_YEAR 
          AND GL.GL_PERIODS.PERIOD_TYPE = GL.GL_BALANCES21.PERIOD_TYPE 
          AND GL.GL_PERIODS.PERIOD_NAME = GL.GL_BALANCES21.PERIOD_NAME 
          AND GL.GL_PERIODS.PERIOD_SET_NAME = GL.GL_SETS_OF_BOOKS.PERIOD_SET_NAME 
          and gl.GL_CODE_COMBINATIONS21.summary_flag!='Y'
          Thanks ,
          • 17. Re: need help on tuning materialized view refresh
            Jonathan Lewis
            user646034 wrote:

            Yes, it was changed. Whenever the set_of_books_id will get changed. It will changed the below number of rows.

            COUNT(*) SET_OF_BOOKS_ID
            ---------- ---------------
            180277562 1
            291277032 25
            202957 23
            61908 26


            As i said before Initially we were complete refresh which is taking more than 90 as it is inserting more than 45000000


            Sorry, I wanted the definition of the materialized view log, I wrote "create mv" when I meant "create mv log". The question really comes down to whether a change in the definition of that log can stop the refresh taking place when it doesn't need to.

            An alternative thought, if changing the mv log doesn't help - you could create a trigger on the gl_set_of_books table that called dbms_job to dispatch a fast refresh if the set of books id is 23 or 26 (i.e. small set of books), and a full refresh if it's 1 and 25 (i.e. large set of books).

            Regards
            Jonathan Lewis
            • 18. Re: need help on tuning materialized view refresh
              user646034
              Hi ,

              I am planning to range partition a base tables gl_balances21 and gl_code_combinations on column code_combination_id and then will partition a materialized view also. Does it help. I will create a local index on same column.
              I am thinking of subpartionining set_of_books_id also. But i don't understand base on what columns i partitioned set_of_books_id.
              will partition will improve performance.

              Thanks
              • 19. Re: need help on tuning materialized view refresh
                user646034
                Hi

                Create mview log statement.

                CREATE MATERIALIZED VIEW LOG ON "GL"."GL_CODE_COMBINATIONS21"
                WITH ROWID, SEQUENCE("CODE_COMBINATION_ID","SEGMENT2")
                INCLUDING NEW VALUES;

                CREATE MATERIALIZED VIEW LOG ON "GL"."GL_BALANCES21"
                WITH ROWID, SEQUENCE("SET_OF_BOOKS_ID","CODE_COMBINATION_ID","CURRENCY_CODE","PERIOD_NAME","ACTUAL_FLAG","TEMPLATE_ID")
                INCLUDING NEW VALUES;

                CREATE MATERIALIZED VIEW LOG ON "GL"."GL_SETS_OF_BOOKS" WITH ROWID ;
                ALTER MATERIALIZED VIEW LOG FORCE ON "GL"."GL_SETS_OF_BOOKS" ADD ROWID;

                Thanks
                • 20. Re: need help on tuning materialized view refresh
                  Jonathan Lewis
                  user646034 wrote:

                  CREATE MATERIALIZED VIEW LOG ON "GL"."GL_SETS_OF_BOOKS" WITH ROWID ;
                  ALTER MATERIALIZED VIEW LOG FORCE ON "GL"."GL_SETS_OF_BOOKS" ADD ROWID;
                  I thought I had some results that showed that Oracle had a bitmap showing which columns had caused a materialized view log entry to be created, and therefore was able to avoid a refresh if the log entry wasn't relevant to the materialized view. I've been trying to reproduce that result and can't - so I think I must have been wrong, sorry.

                  Partitioning won't help you - you will still have to delete all the GL entries relating to the set of books if you modify a set of books.
                  Just as a tablescan is sometimes faster than an index range scan, a complete refresh is sometimes faster than a fast refresh.

                  Regards
                  Jonathan Lewis
                  • 21. Re: need help on tuning materialized view refresh
                    user646034
                    Hi ,

                    Partition change tracking has a option of truncate first then insert also.

                    Please suggest.

                    Thanks
                    • 22. Re: need help on tuning materialized view refresh
                      user646034
                      Hi

                      I mean ton say that If we use Partition change tracking. During Refresh it will first truncate the partition which got affected followed by insert.
                      Please suggest.

                      Thanks
                      • 23. Re: need help on tuning materialized view refresh
                        Jonathan Lewis
                        user646034 wrote:
                        Hi

                        I mean ton say that If we use Partition change tracking. During Refresh it will first truncate the partition which got affected followed by insert.
                        Please suggest.
                        I'm not sure that you will be able to make this work. Apart from anything else, truncate then re-insert may not be considered as a valid option for fast refresh. (Even so, it might make the full refresh work faster).

                        I think the strategy you need to test would be to do list partition the main table (and the materialized view) on set_of_books_id, because it's a change to a "set of books" that can cause a massive refresh to take place. Obviously you would have to add the set of books id to the materialized view as well (and you may have to experiment to see which table it should come from in the view) but I don't know if that would allow Oracle to detect that it could truncate a partiion when you changed one of the "set of books" rows.

                        Regards
                        Jonathan Lewis
                        • 24. Re: need help on tuning materialized view refresh
                          rp0428
                          Have you eliminated this possibility that Jonathan mentioned earlier?
                          >
                          Perhaps the OP should simply remove the set of books from the materialized view, and rely on Oracle to do something sensible for queries that want to include the other tables and join to the set of books.
                          • 25. Re: need help on tuning materialized view refresh
                            Jonathan Lewis
                            rp0428 wrote:
                            Have you eliminated this possibility that Jonathan mentioned earlier?
                            >
                            Perhaps the OP should simply remove the set of books from the materialized view, and rely on Oracle to do something sensible for queries that want to include the other tables and join to the set of books.
                            >
                            Always difficult to work out the logic of what GL is trying to do, and the rationale that someone may have had for creating a materlized view, but (as Randolf pointed out) it doesn't look as if you could take gl_sets_of_books out on its own. However it's possible that taking out gl_sets_of_books and gl_periods would work, and still give suitably performance results to queries using this materialized view. (Provided query rewrite could recognise the 4-table joins that I think this is addressing as two small tables and one large MV).

                            Regards
                            Jonathan Lewis
                            • 26. Re: need help on tuning materialized view refresh
                              user646034
                              Hi Jonathan,

                              I am very big fan of your blog. I will go for complete refresh.
                              We have partitioned the tables which was not partitioned before when it was taking more than 90 mins.

                              Partition improves the performance. Insert query only takes which insert more than 48000000. Can we bring down the insert time less than 40 mins by using partition with parallel .

                              Please suggest.

                              Thanks
                              • 27. Re: need help on tuning materialized view refresh
                                user646034
                                Hi

                                Please suggest me on this.

                                Thanks
                                1 2 Previous Next