13 Replies Latest reply: Feb 18, 2013 12:50 PM by Avadhut RSS

    Complete mv refresh taking long time

    Avadhut
      HI all,

      Complete refresh of materialized view is taking more than 1 hr. Insert query while refreshing mv is the bottleneck.

      It is inserting 39 crores in MV. It is joining 4 tables out of which gl_balances is the biggest. which is about 22gb in size.

      MV query definition.

      CREATE MATERIALIZED VIEW GL_BAL_MV
      REFRESH FORCE ON DEMAND
      NEXT NULL
      AS
      SELECT * FROM GL_BAL_V;

      View definition.

      SELECT gcc.segment23
      , gcc.segment24
      , gcc.segment25
      , gcc.segment26
      , gcc.segment27
      , gcc.segment28
      , gcc.segment29
      , gcc.segment30*/
      , gb.currency_code
      , gb.period_year
      , gb.period_num
      , (NVL(gb.BEGIN_BALANCE_DR,0)-NVL(gb.BEGIN_BALANCE_CR,0)) Open_Bal
      , NVL(gb.PERIOD_NET_DR,0) Period_Dr
      , NVL(gb.PERIOD_NET_CR,0) Period_Cr
      , (NVL(gb.BEGIN_BALANCE_DR,0)-NVL(gb.BEGIN_BALANCE_CR,0)+NVL(gb.PERIOD_NET_DR,0)-NVL(gb.PERIOD_NET_CR,0)) Close_Bal
      , NVL(gb.BEGIN_BALANCE_DR,0) Open_Bal_Dr
      , NVL(gb.BEGIN_BALANCE_CR,0) Open_Bal_Cr
      , NVL(gb.BEGIN_BALANCE_DR,0) + NVL(gb.PERIOD_NET_DR,0) Close_Bal_Dr
      , NVL(gb.BEGIN_BALANCE_CR,0) + NVL(gb.PERIOD_NET_CR,0) Close_Bal_Cr
      FROM gl_balances gb
      , gl_code_combinations gcc
      , gl_periods gp
      , gl_sets_of_books gsob
      WHERE gcc.CODE_COMBINATION_ID = gb.CODE_COMBINATION_ID
      AND gb.period_year = gp.period_year
      AND gb.period_num = gp.period_num
      AND gb.period_name = gp.period_name
      AND gb.period_type = gp.period_type
      AND gb.set_of_books_id = gsob.set_of_books_id
      AND gp.PERIOD_SET_NAME = gsob.PERIOD_SET_NAME
      AND gcc.summary_flag != 'Y'

      I created a range partition on Period_num of gl_tables still its takes the same time and then generated the trace and found that it is going for full table scan and mostly waiting on dbfile sequential read and dbfile scattered read wait event.

      below insert is the bottleneck.
      INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND */ INTO GL_BAL_MV" SELECT * FROM
      GL_BAL__V


      Raw trace

      Rows Row Source Operation
      ------- ---------------------------------------------------
      0 LOAD AS SELECT (cr=5498950 pr=8963693 pw=11678556 time=0 us)
      399298265 HASH JOIN (cr=5295667 pr=8963503 pw=3737515 time=3312033280 us cost=5025352 size=347692416 card=1810898)
      3023993 PARTITION RANGE ALL PARTITION: 1 1048575 (cr=64126 pr=33359 pw=0 time=16801108 us cost=60180 size=136079370 card=1511993)
      3023993 TABLE ACCESS FULL GL_CODE_COMBINATIONS PARTITION: 1 1048575 (cr=64126 pr=33359 pw=0 time=12596043 us cost=60180 size=136079370 card=1511993)
      399298265 HASH JOIN (cr=5231541 pr=5192629 pw=0 time=2293442560 us cost=4843142 size=355949910 card=3489705)
      4240 HASH JOIN (cr=30 pr=27 pw=0 time=3116 us cost=32 size=120960 card=2240)
      6 TABLE ACCESS FULL GL_SETS_OF_BOOKS (cr=7 pr=6 pw=0 time=0 us cost=7 size=108 card=6)
      1160 TABLE ACCESS FULL GL_PERIODS (cr=23 pr=21 pw=0 time=115 us cost=24 size=40320 card=1120)
      399298265 PARTITION RANGE ALL PARTITION: 1 40 (cr=5231511 pr=5192602 pw=0 time=1217138816 us cost=4843109 size=19167454080 card=399321960)
      399298265 TABLE ACCESS FULL GL_BALANCES PARTITION: 1 40 (cr=5231511 pr=5192602 pw=0 time=829470464 us cost=4843109 size=19167454080 card=399321960)

      Elapsed times include waiting on following events:
      Event waited on Times Max. Wait Total Waited
      ---------------------------------------- Waited ---------- ------------
      db file sequential read 11083 0.35 21.72
      db file scattered read 56609 2.26 584.36
      direct path write temp 727 1.79 34.90
      asynch descriptor resize 11 0.00 0.00
      direct path read temp 6229 0.38 53.30
      Disk file operations I/O 4 0.00 0.00
      direct path write 1262 0.88 35.04
      latch: cache buffers chains 5 0.00 0.00
      latch free 1 0.00 0.00
      buffer exterminate 4 0.01 0.04
      ********************************************************************************

      Please help me on this to improve the improve the performance of materialized view.

      Thanks
        • 1. Re: Complete mv refresh taking long time
          sb92075
          Avadhut wrote:
          HI all,

          Complete refresh of materialized view is taking more than 1 hr. Insert query while refreshing mv is the bottleneck.

          It is inserting 39 crores in MV. It is joining 4 tables out of which gl_balances is the biggest. which is about 22gb in size.

          MV query definition.

          CREATE MATERIALIZED VIEW GL_BAL_MV
          REFRESH FORCE ON DEMAND
          NEXT NULL
          AS
          SELECT * FROM GL_BAL_V;

          View definition.

          SELECT gcc.segment23
          , gcc.segment24
          , gcc.segment25
          , gcc.segment26
          , gcc.segment27
          , gcc.segment28
          , gcc.segment29
          , gcc.segment30*/
          , gb.currency_code
          , gb.period_year
          , gb.period_num
          , (NVL(gb.BEGIN_BALANCE_DR,0)-NVL(gb.BEGIN_BALANCE_CR,0)) Open_Bal
          , NVL(gb.PERIOD_NET_DR,0) Period_Dr
          , NVL(gb.PERIOD_NET_CR,0) Period_Cr
          , (NVL(gb.BEGIN_BALANCE_DR,0)-NVL(gb.BEGIN_BALANCE_CR,0)+NVL(gb.PERIOD_NET_DR,0)-NVL(gb.PERIOD_NET_CR,0)) Close_Bal
          , NVL(gb.BEGIN_BALANCE_DR,0) Open_Bal_Dr
          , NVL(gb.BEGIN_BALANCE_CR,0) Open_Bal_Cr
          , NVL(gb.BEGIN_BALANCE_DR,0) + NVL(gb.PERIOD_NET_DR,0) Close_Bal_Dr
          , NVL(gb.BEGIN_BALANCE_CR,0) + NVL(gb.PERIOD_NET_CR,0) Close_Bal_Cr
          FROM gl_balances gb
          , gl_code_combinations gcc
          , gl_periods gp
          , gl_sets_of_books gsob
          WHERE gcc.CODE_COMBINATION_ID = gb.CODE_COMBINATION_ID
          AND gb.period_year = gp.period_year
          AND gb.period_num = gp.period_num
          AND gb.period_name = gp.period_name
          AND gb.period_type = gp.period_type
          AND gb.set_of_books_id = gsob.set_of_books_id
          AND gp.PERIOD_SET_NAME = gsob.PERIOD_SET_NAME
          AND gcc.summary_flag != 'Y'
          are all the columns in the WHERE clause indexed & have current statistics?
          • 2. Re: Complete mv refresh taking long time
            Avadhut
            Hi ,
            Thanks for the update.
            yes all the columns in the where clause have indexes and current statistics.

            Thanks
            • 3. Re: Complete mv refresh taking long time
              Etbin
              Everything looks like expected so parallel processing (let the Optimizer choose the degree of parallelism) seems the next step worth trying http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements006.htm#SQLRF50801
              select /*+ parallel */
                     gcc.segment23,
                     gcc.segment24,
                     gcc.segment25,
                     gcc.segment26,
                     gcc.segment27,
                     gcc.segment28,
                     gcc.segment29,
                     gcc.segment30,
                     gb.currency_code,
                     gb.period_year,
                     gb.period_num,
                     nvl(gb.begin_balance_dr,0) - nvl(gb.begin_balance_cr,0) open_bal,
                     nvl(gb.period_net_dr,0) period_dr,
                     nvl(gb.period_net_cr,0) period_cr,
                     nvl(gb.begin_balance_dr,0) - nvl(gb.begin_balance_cr,0) + nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0) close_bal,
                     nvl(gb.begin_balance_dr,0) open_bal_dr,
                     nvl(gb.begin_balance_cr,0) open_bal_cr,
                     nvl(gb.begin_balance_dr,0) + nvl(gb.period_net_dr,0) close_bal_dr,
                     nvl(gb.begin_balance_cr,0) + nvl(gb.period_net_cr,0) close_bal_cr
                from gl_balances gb,
                     gl_code_combinations gcc,
                     gl_periods gp,
                     gl_sets_of_books gsob
               where gb.period_year = gp.period_year
                 and gb.period_num = gp.period_num
                 and gb.period_name = gp.period_name
                 and gb.period_type = gp.period_type
                 and gb.set_of_books_id = gsob.set_of_books_id
                 and gp.period_set_name = gsob.period_set_name
                 and gb.code_combination_id = gcc.code_combination_id
                 and gcc.summary_flag != 'Y'
              
                      0 LOAD AS SELECT                                                (cr=5498950 pr=8963693 pw=11678556 time=0 us)
              399298265 HASH JOIN                                                     (cr=5295667 pr=8963503 pw=3737515 time=3312033280 us cost=5025352 size=347692416 card=1810898)
                3023993   PARTITION RANGE ALL PARTITION:                    1 1048575 (cr=64126 pr=33359 pw=0 time=16801108 us cost=60180 size=136079370 card=1511993)
                3023993   TABLE ACCESS FULL GL_CODE_COMBINATIONS PARTITION: 1 1048575 (cr=64126 pr=33359 pw=0 time=12596043 us cost=60180 size=136079370 card=1511993)
              399298265   HASH JOIN                                                   (cr=5231541 pr=5192629 pw=0 time=2293442560 us cost=4843142 size=355949910 card=3489705)
                   4240     HASH JOIN                                                 (cr=30 pr=27 pw=0 time=3116 us cost=32 size=120960 card=2240)
                      6       TABLE ACCESS FULL GL_SETS_OF_BOOKS                      (cr=7 pr=6 pw=0 time=0 us cost=7 size=108 card=6)
                   1160       TABLE ACCESS FULL GL_PERIODS                            (cr=23 pr=21 pw=0 time=115 us cost=24 size=40320 card=1120)
              399298265     PARTITION RANGE ALL PARTITION:                    1    40 (cr=5231511 pr=5192602 pw=0 time=1217138816 us cost=4843109 size=19167454080 card=399321960)
              399298265     TABLE ACCESS FULL GL_BALANCES PARTITION:          1    40 (cr=5231511 pr=5192602 pw=0 time=829470464 us cost=4843109 size=19167454080 card=399321960)
              Regards

              Etbin
              • 4. Re: Complete mv refresh taking long time
                Avadhut
                Hi ,


                I have already tried using append hint. But i tried before partitioning.

                Now i will try again. I have one confusion. MBRC is set to 124.
                If i will use only parallel hint. or i need to define something.

                Thanks
                • 5. Re: Complete mv refresh taking long time
                  Etbin
                  If i will use only parallel hint. or i need to define something.
                  To begin with, just the hint and ... something to read while you wait ... http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:39946845137685

                  Regards

                  Etbin
                  • 6. Re: Complete mv refresh taking long time
                    Avadhut
                    Hi Etbin,

                    Thanks for the update.

                    I tried using parallel. it still slow.

                    I can gl_balances and gl_code combinations is mostly waiting dbfile sequential read and dbfile scattered read.

                    We can see this side also. IF we recreate the mv with more pctfree. Will it help.

                    Thanks
                    • 7. Re: Complete mv refresh taking long time
                      Etbin
                      IF we recreate the mv with more pctfree. Will it help.
                      You're talking about complete refresh - it's like truncate followed by insert. If all your refreshes are complete refreshes you don't need any free space so increasing it would only make the mv bigger.
                      You didn't give us your database version.
                      Anyway I think I'm not the most appropriate to take advice from as I'm working mostly using tables directly (no views or materialized views) lately exclusively on Exadata so I somehow lost the habit considering problems of this kind.
                      I proposed using parallel processing as the balances table cardinality seems to be the only problem.
                      The select is pretty straightforward, balances table fts is mamdatory, only hash joins are used ... what else could be done?
                      Maybe <tt> alter materialized view gl_bal_mv parallel </tt>and/or not refreshing it from the <tt> gl_bal_v </tt> view but using <tt> select /*+ parallel */ gcc.segment23, ... </tt> the one used to define the <tt> gl_bal_v </tt> view (ther's only remote chance the latter can make any difference)

                      Regards

                      Etbin
                      • 8. Re: Complete mv refresh taking long time
                        Avadhut
                        Hi ,

                        Thanks for the update.

                        DB version - 11.2.0.1

                        It is first truncating the data and then inserting.

                        I don't know how to optimize the performance of this MV. Somebody can suggest me something in OTN if somebody have worked on MV performance.

                        Thanks
                        • 9. Re: Complete mv refresh taking long time
                          Avadhut
                          Please somebody help me on this.

                          Thanks
                          • 10. Re: Complete mv refresh taking long time
                            jihuyao
                            It is curious that the MV does almost nothing except some joins and is almost helpless if no index exists.

                            The parallel insert and select will definitely demonstrate more CPU powers (check execution plans for difference).

                            But both gl_balances and gl_code_combinations had better to partition on column CODE_COMBINATION_ID and also it is helpful to have the MV partitioned on CODE_COMBINATION_ID.
                            • 11. Re: Complete mv refresh taking long time
                              Avadhut
                              Hi

                              Range partition is already done on column period_num of gl_balances table.

                              I tried parallel hint also still no help.

                              I have created a fast refresh materialized let see whether it helps or not. If it doesn't help then i will create partition on CODE_COMBINATION_ID on gl_balances and gl_code_combination.

                              do i create a range partition or any other partition also help me to create a Partition mview.

                              Thanks
                              • 12. Re: Complete mv refresh taking long time
                                jihuyao
                                You need parallel direct path insert and parallel select. But first check the execution plan with a fairly small collection of CODE_COMBINATION_ID defined in the where clause. Either doing range partition or hash partition depends on the scope of CODE_COMBINATION_ID values.
                                • 13. Re: Complete mv refresh taking long time
                                  Avadhut
                                  Hi,

                                  Apologies for the delay. I was on leave due to bad health.

                                  I am planing to create a range partition a column of Code_combination_id of Gl_balances and GL_CODE_combination.
                                  and subpartition by column period_name of gl_balances and gl_periods. I have code_combination_id values between 2000 to 3000000 hence range partition will work. Incase of period_name, values are different like mar-00, adj-00, cla-00 in this way till 2013.
                                  Which partition will be good here.

                                  Please suggest.

                                  Thanks