This discussion is archived
13 Replies Latest reply: Feb 18, 2013 10:50 AM by Avadhut RSS

Complete mv refresh taking long time

Avadhut Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Please somebody help me on this.

    Thanks
  • 10. Re: Complete mv refresh taking long time
    jihuyao Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points