1 2 Previous Next 27 Replies Latest reply: Mar 7, 2013 1:09 PM by user646034 RSS

    need help on tuning materialized view refresh

    user646034
      Hi All,

      I am working on materialized view refresh tuning.Initially it was complete refresh and used to take more than 90 mins to complete.

      I changed it to fast refresh now it is completing fast. Now i have partitioned the base tables gl_balances and gl_code_combinations of column code_combination_id and created a local index on column code_combination_id then i am trying to partition the materialized on the same column to take advantage of partition change tracking.
      Size of gl_balances base tables is 40Gb and all others tables sizes are small. In where clause there all the 4 tables are mapped. If i will create the partition only on code_combination_id will i the materialized will become the candidate for partition change tracking. As i know it will be applicable for PCT. I need expert advice on this.
      While doing a fast refresh. the refresh takes less time. when there is a change in gl_balances , gl_code_combinations or gl_periods it completes in 20-30 mins. When there is a change in gl_set_of_books tables. It creates a problem here.DEL query takes more than 48 hours to complete.
      CREATE MATERIALIZED VIEW apps.BAL_PART
      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_PERIOD.ROWID C4,
      "GL"."GL_BALANCES21"."ACTUAL_FLAG" ,
      "GL"."GL_BALANCES21"."CURRENCY_CODE" ,
      "GL"."GL_BALANCES21"."PERIOD_NUM" ,
      "GL"."GL_BALANCES21"."PERIOD_YEAR" ,
      "GL"."GL_BALANCES21"."SET_OF_BOOKS_ID" "SOB_ID",
      "GL"."GL_CODE_COMBINATIONS21"."CODE_COMBINATION_ID" "CCID",
      "GL"."GL_CODE_COMBINATIONS21"."SEGMENT1" ,
      "GL"."GL_CODE_COMBINATIONS21"."SEGMENT10" ,
      "GL"."GL_CODE_COMBINATIONS21"."SEGMENT11" ,
      "GL"."GL_CODE_COMBINATIONS21"."SEGMENT12" ,
      "GL"."GL_CODE_COMBINATIONS21"."SEGMENT13" ,
      "GL"."GL_CODE_COMBINATIONS21"."SEGMENT14" ,
      "GL"."GL_CODE_COMBINATIONS21"."SEGMENT2" ,
      "GL"."GL_CODE_COMBINATIONS21"."SEGMENT3" ,
      "GL"."GL_CODE_COMBINATIONS21"."SEGMENT4" ,
      "GL"."GL_CODE_COMBINATIONS21"."SEGMENT5" ,
      "GL"."GL_CODE_COMBINATIONS21"."SEGMENT6" ,
      "GL"."GL_CODE_COMBINATIONS21"."SEGMENT7" ,
      "GL"."GL_CODE_COMBINATIONS21"."SEGMENT8" ,
      "GL"."GL_CODE_COMBINATIONS21"."SEGMENT9" ,
      "GL"."GL_PERIODS"."PERIOD_NAME" ,
      NVL("GL"."GL_BALANCES21"."BEGIN_BALANCE_CR", 0) Open_Bal_Cr,
      NVL("GL"."GL_BALANCES21"."BEGIN_BALANCE_CR", 0) +
      NVL("GL"."GL_BALANCES21"."PERIOD_NET_CR", 0) Close_Bal_Cr,
      NVL("GL"."GL_BALANCES21"."BEGIN_BALANCE_DR", 0) Open_Bal_Dr,
      NVL("GL"."GL_BALANCES21"."BEGIN_BALANCE_DR", 0) +
      NVL("GL"."GL_BALANCES21"."PERIOD_NET_DR", 0) Close_Bal_Dr,
      NVL("GL"."GL_BALANCES21"."BEGIN_BALANCE_DR", 0) -
      NVL("GL"."GL_BALANCES21"."BEGIN_BALANCE_CR", 0) Open_Bal,
      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) Close_Bal,
      NVL("GL"."GL_BALANCES21"."PERIOD_NET_CR", 0) Period_Cr,
      NVL("GL"."GL_BALANCES21"."PERIOD_NET_DR", 0) Period_Dr
      FROM GL.GL_CODE_COMBINATIONS21,
      GL.GL_BALANCES21,
      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'
      TRACE 1046 del statement
      DELETE FROM "APPS"."apps.BAL_PART" SNA$ 
      WHERE "C3" IN (SELECT /*+ NO_MERGE  */ * FROM (SELECT  
        CHARTOROWID("MAS$"."M_ROW$$") RID$     FROM "GL"."MLOG$_GL_SETS_OF_BOOKS" 
        "MAS$"   WHERE "MAS$".SNAPTIME$$ > :B_ST1 ) AS OF SNAPSHOT(:B_SCN) MAS$) 
      
      call     count       cpu    elapsed       disk      query    current        rows
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      Parse        1      0.00       0.00          0          0          0           0
      Execute      1  17759.00  171782.99  159422121    1267371 2564144739           0
      Fetch        0      0.00       0.00          0          0          0           0
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      total        2  17759.00  171782.99  159422121    1267371 2564144739           0
      
      Misses in library cache during parse: 1
      Misses in library cache during execute: 1
      Optimizer mode: ALL_ROWS
      Parsing user id: 175  (APPS)   (recursive depth: 1)
      
      Rows     Row Source Operation
      -------  ---------------------------------------------------
            0  DELETE  apps.BAL_PART (cr=0 pr=0 pw=0 time=0 us)
      193128740   NESTED LOOPS  (cr=592437 pr=592422 pw=0 time=945244160 us cost=339302 size=168 card=1)
            3    SORT UNIQUE (cr=7 pr=0 pw=0 time=15832 us cost=2 size=138 card=1)
           24     TABLE ACCESS FULL MLOG$_GL_SETS_OF_BOOKS (cr=7 pr=0 pw=0 time=19 us cost=2 size=138 card=1)
      193128740    INDEX RANGE SCAN C3BOOKS (cr=592430 pr=592422 pw=0 time=789499200 us cost=339299 size=3318314250 card=110610475)(object id 2114736)
      
      error during execute of EXPLAIN PLAN statement
      ORA-08187: snapshot expression not allowed here
      
      parse error offset: 314
      
      Elapsed times include waiting on following events:
        Event waited on                             Times   Max. Wait  Total Waited
        ----------------------------------------   Waited  ----------  ------------
        db file sequential read                   159520897        2.12     144415.96
        latch: cache buffers chains                   134        0.06          0.68
        latch: undo global data                        33        0.02          0.15
        latch: object queue header operation          521        0.02          0.53
        log file switch (private strand flush incomplete)
                                                      532        0.31         28.26
        resmgr:cpu quantum                            155        1.40         13.49
        resmgr:internal state change                   25        0.11          2.21
        latch free                                     10        0.00          0.00
        latch: cache buffers lru chain                  4        0.00          0.00
        rdbms ipc reply                               489        0.02          0.54
        reliable message                              587        0.00          0.56
        latch: row cache objects                        3        0.00          0.00
      ********************************************************************************
      GL_SETS_OF_BOOKS has only 6 rows. I know there is complete refresh as a option which will again take more than 90 mins.
      I want to do the fast refresh. Tables rows details below.
      SQL> select count(*) from gl.gl_code_combinations21;
      COUNT(*)
      ----------
      3075255
      
      SQL> select count(*) from gl.GL_PERIODS;
      COUNT(*)
      ----------
      1160
      SQL> select count(*) from gl.gl_balances21;
      
      COUNT(*)
      ----------
      477613527
      SQL> select count(*) from gl.gl_sets_of_books;
      COUNT(*)
      ----------
      6
      gl_sets_of_books has less rows. Whenever there is a change then it mapped to huge rows hence during materialized view has delete huge number of rows.

      select count(*) from apps.BAL_PART group by C3;
      C3 is the rowid which is present in create materialized statement.
      COUNT(*)
      ----------
      292927011
      210215
      69330
      184406971

      Is there any way to improve the plan. As i created a partition on code_combination_id and local index on code_combination_id which will not help in set_of_books_id case. I dont PCT will help here or not. Is it possible to use PCT refresh by equipartitioning only one column in where clause.

      Please assist me in improving refresh of materialized view using fast refresh.

      Thanks and Regards,

      Edited by: user646034 on Feb 23, 2013 11:13 PM

      Edited by: user646034 on Feb 23, 2013 11:19 PM

      Edited by: user646034 on Feb 23, 2013 11:46 PM

      Edited by: user646034 on Feb 25, 2013 11:46 AM
        • 1. Re: need help on tuning materialized view refresh
          sb92075
          HOW To Make TUNING request
          SQL and PL/SQL FAQ
          • 2. Re: need help on tuning materialized view refresh
            Osama_Mustafa
            please use
             to make your post readable                                                                                                                                                                                                                    
            • 3. Re: need help on tuning materialized view refresh
              user646034
              Hi
              I have used the code now.

              Thanks
              • 4. Re: need help on tuning materialized view refresh
                user646034
                Hi All,

                Please help me on this.
                • 5. Re: need help on tuning materialized view refresh
                  User477708-OC
                  your post is still not in code so its difficult to read, not withstanding that, explain the query outside of the view refresh and post the result of that.... follow sb's link above. Help us to help you.

                  also, you have fast refresh in the create statement with multi join, did you create the mv logs on all tables in the join?
                  • 6. Re: need help on tuning materialized view refresh
                    riedelme
                    The DELETE is taking more than 48 hours but the plan is using an indexed lookup but a full table scan against one table. The DELETE plan listed an error (?). The WAITS listed a high value for DB FILE SEQUENTIAL READ which is related to index usage - I am not sure if the INSERT, DELETE, or both caused this. The row counts you listed were large but not astronomical.

                    Do an internet search for DB FILE SEQUENTIAL READ to learn more about this wait event. One option for this is to tune the SQL which I will make suggestions for below.

                    I saw a plan for the DELETE but not for the SELECT behind the materialized view creation. I personally prefer complete refreshes over fast (too many rules to keep track of!!!) but your complete refresh was taking a long time. Post a n exectuion plan for the SQL behind the materialized view creation.

                    Post an execution plan for the SQL used in the MV. Indexing join columns may/may not help because you are not restricing the rows in the SELECT except for the 'Y' value.

                    Do you control the SQL for the MV and/or the delete? If you can control the SQL on the delete
                    1) This looks like an apps table so changing the table structure (say, partitioned for partition elimination) will not be possible
                    2) A correlated EXISTS subquery might offer better performance than the IN subquery if the subquery is indexed on the join column. Joiin the SELECT column in the subquery table to the target coluimn in the IN clause making sure that the inner table column is indexed.
                    3) Is the AS OF clause necessary (probably), but since you are deleting anyway don't you want to delete the current contents? AS OF is usually used to select older data, not for DML statements
                    4) Is the NO_MERGE hint actually helping? Hints can hurt performance under some circumstances. What happens if you remove it?
                    5) Is the inline view in the IN subquery necessary? Inline views add complexity to SQL and can hurt performance. If you care not gaining functionality by using it (like in a TOP-N query, which can be done other ways) then it is best not to use them. Using the WITH clause offers better control over inline view execution with the possiblity of using the INLINE and MATERIALIZE hints which can help (or hurt) performance under certain contitions

                    Good luck!
                    • 7. Re: need help on tuning materialized view refresh
                      user646034
                      Hi

                      also, you have fast refresh in the create statement with multi join, did you create the mv logs on all tables in the join?

                      yes, I have created the mv logs on all the tables in the join.

                      The DELETE is taking more than 48 hours but the plan is using an indexed lookup but a full table scan against one table. The DELETE plan listed an error (?). The WAITS listed a high value for DB FILE SEQUENTIAL READ which is related to index usage - I am not sure if the INSERT, DELETE, or both caused this. The row counts you listed were large but not astronomical.

                      DEL query during materialized view refresh was taking more than 48 hours hence i have to manually kill it. Del query was using index as index is created on c3 column which is rowid of gl_set_of_books tables. It is going for dbfile sequential read as it is using index and trying to delete 193128740 rows.

                      I saw a plan for the DELETE but not for the SELECT behind the materialized view creation. I personally prefer complete refreshes over fast (too many rules to keep track of!!!) but your complete refresh was taking a long time. Post a n exectuion plan for the SQL behind the materialized view creation.

                      DEL query was taking time hence i pasted the plan of delete query. My complete refresh is taking more than 90 mins. sometimes more than 2 hrs also.I will post an execution of the select materialized query. Initially we were doing complete refresh.

                      Recently I created a partition on column of code_combination_id of gl_balances21 and gl_code_combinations21. This two tables are big and mapped using code_combination_id hence i did not get any benefit from partition. I decided to do fast refresh which rans ok when there is a lesser number of changes in the base tables. When there is a large change in the base then it ran for more than 2-3 days. Oracle suggest if there is a small change in the base tables then go for fast refresh else go for complete refresh.
                      Now i am planning to partition a materialized view refresh on same column so that i can take benefit of partition change tracking.
                      Will PCT will help me in this case.
                      Tkprof report of materialized view fast refresh.
                      DELETE FROM "APPS"."BAL_PART" SNA$ 
                      WHERE
                       "C3" IN (SELECT /*+ NO_MERGE  */ * FROM (SELECT  
                        CHARTOROWID("MAS$"."M_ROW$$") RID$     FROM "GL"."MLOG$_GL_SETS_OF_BOOKS" 
                        "MAS$"   WHERE "MAS$".SNAPTIME$$ > :B_ST1 ) AS OF SNAPSHOT(:B_SCN) MAS$) 
                      
                      
                      call     count       cpu    elapsed       disk      query    current        rows
                      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                      Parse        1      0.00       0.00          0          0          0           0
                      Execute      1  17759.00  171782.99  159422121    1267371 2564144739           0
                      Fetch        0      0.00       0.00          0          0          0           0
                      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                      total        2  17759.00  171782.99  159422121    1267371 2564144739           0
                      
                      Misses in library cache during parse: 1
                      Misses in library cache during execute: 1
                      Optimizer mode: ALL_ROWS
                      Parsing user id: 175  (APPS)   (recursive depth: 1)
                      
                      Rows     Row Source Operation
                      -------  ---------------------------------------------------
                            0  DELETE  BAL_PART (cr=0 pr=0 pw=0 time=0 us)
                      193128740   NESTED LOOPS  (cr=592437 pr=592422 pw=0 time=945244160 us cost=339302 size=168 card=1)
                            3    SORT UNIQUE (cr=7 pr=0 pw=0 time=15832 us cost=2 size=138 card=1)
                           24     TABLE ACCESS FULL MLOG$_GL_SETS_OF_BOOKS (cr=7 pr=0 pw=0 time=19 us cost=2 size=138 card=1)
                      193128740    INDEX RANGE SCAN C3BOOKS (cr=592430 pr=592422 pw=0 time=789499200 us cost=339299 size=3318314250 card=110610475)(object id 2114736)
                      
                      error during execute of EXPLAIN PLAN statement
                      ORA-08187: snapshot expression not allowed here
                      
                      parse error offset: 314
                      
                      Elapsed times include waiting on following events:
                        Event waited on                             Times   Max. Wait  Total Waited
                        ----------------------------------------   Waited  ----------  ------------
                        db file sequential read                   159520897        2.12     144415.96
                        latch: cache buffers chains                   134        0.06          0.68
                        latch: undo global data                        33        0.02          0.15
                        latch: object queue header operation          521        0.02          0.53
                        log file switch (private strand flush incomplete)
                                                                      532        0.31         28.26
                        resmgr:cpu quantum                            155        1.40         13.49
                        resmgr:internal state change                   25        0.11          2.21
                        latch free                                     10        0.00          0.00
                        latch: cache buffers lru chain                  4        0.00          0.00
                        rdbms ipc reply                               489        0.02          0.54
                        reliable message                              587        0.00          0.56
                        latch: row cache objects                        3        0.00          0.00
                      ********************************************************************************
                      
                      SQL ID: 73f1fw36jpcd5
                      Plan Hash: 121450072
                      update "GL"."MLOG$_GL_BALANCES21" set snaptime$$ = :1 
                      where
                       rowid in  (select rowid from "GL"."MLOG$_GL_BALANCES21" AS OF SNAPSHOT (:2) 
                        log$   where snaptime$$ > to_date('2100-01-01:00:00:00',
                        'YYYY-MM-DD:HH24:MI:SS'))
                      
                      
                      call     count       cpu    elapsed       disk      query    current        rows
                      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                      Parse        1      0.00       0.01          0          0          0           0
                      Execute      1    258.96     586.08     151461    4392162   63876354     8315209
                      Fetch        0      0.00       0.00          0          0          0           0
                      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                      total        2    258.96     586.09     151461    4392162   63876354     8315209
                      
                      Misses in library cache during parse: 1
                      Misses in library cache during execute: 1
                      Optimizer mode: ALL_ROWS
                      Parsing user id: SYS   (recursive depth: 1)
                      
                      Rows     Row Source Operation
                      -------  ---------------------------------------------------
                            0  UPDATE  MLOG$_GL_BALANCES21 (cr=4398242 pr=151466 pw=0 time=0 us)
                      8315209   NESTED LOOPS  (cr=273908 pr=144598 pw=0 time=185839040 us cost=1 size=42 card=1)
                      8315209    INDEX RANGE SCAN MLOG$_GL_BALANCES21_IDX (cr=154239 pr=84764 pw=0 time=113009184 us cost=0 size=21 card=1)(object id 2113913)
                      8315209    TABLE ACCESS BY USER ROWID MLOG$_GL_BALANCES21 (cr=119669 pr=59834 pw=0 time=0 us cost=1 size=21 card=1)
                      
                      
                      Elapsed times include waiting on following events:
                        Event waited on                             Times   Max. Wait  Total Waited
                        ----------------------------------------   Waited  ----------  ------------
                        db file sequential read                    141031        0.39        134.49
                        latch: cache buffers chains                    23        0.01          0.04
                        Disk file operations I/O                       17        0.00          0.00
                        buffer busy waits                               1        0.00          0.00
                        log file switch (private strand flush incomplete)
                                                                       16        0.05          0.37
                        latch: object queue header operation            1        0.00          0.00
                        latch: cache buffers lru chain                  3        0.00          0.00
                      ********************************************************************************
                      
                      SQL ID: 243cmkhfxutsa
                      Plan Hash: 2437489837
                      SELECT OLD_NEW$$, COUNT(*)  
                      FROM
                       "GL"."MLOG$_GL_BALANCES21"   WHERE SNAPTIME$$ > :1 AND SNAPTIME$$ <= :2  GROUP 
                        BY OLD_NEW$$
                      
                      
                      call     count       cpu    elapsed       disk      query    current        rows
                      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                      Parse        2      0.00       0.00          0          0          0           0
                      Execute      2      0.00       0.00          0          0          0           0
                      Fetch        6     34.74     118.79     163326     197402          0           4
                      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                      total       10     34.74     118.80     163326     197402          0           4
                      
                      Misses in library cache during parse: 1
                      Misses in library cache during execute: 1
                      Optimizer mode: ALL_ROWS
                      Parsing user id: 175  (APPS)   (recursive depth: 2)
                      
                      Rows     Row Source Operation
                      -------  ---------------------------------------------------
                            2  HASH GROUP BY (cr=98701 pr=81663 pw=0 time=12 us cost=1 size=11 card=1)
                      8327908   FILTER  (cr=98701 pr=81663 pw=0 time=56414476 us)
                      8327908    TABLE ACCESS BY INDEX ROWID MLOG$_GL_BALANCES21 (cr=98701 pr=81663 pw=0 time=51783920 us cost=0 size=11 card=1)
                      8327908     INDEX RANGE SCAN MLOG$_GL_BALANCES21_IDX (cr=38769 pr=32616 pw=0 time=21324256 us cost=0 size=0 card=1)(object id 2113913)
                      
                      
                      Rows     Execution Plan
                      -------  ---------------------------------------------------
                            0  SELECT STATEMENT   MODE: ALL_ROWS
                            2   HASH (GROUP BY)
                      8327908    FILTER
                      8327908     TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 
                                      'MLOG$_GL_BALANCES21' (TABLE)
                      8327908      INDEX   MODE: ANALYZED (RANGE SCAN) OF 
                                       'MLOG$_GL_BALANCES21_IDX' (INDEX)
                      
                      
                      Elapsed times include waiting on following events:
                        Event waited on                             Times   Max. Wait  Total Waited
                        ----------------------------------------   Waited  ----------  ------------
                        asynch descriptor resize                        2        0.00          0.00
                        db file sequential read                    163326        0.45         66.13
                        resmgr:cpu quantum                              8        0.16          0.47
                        resmgr:internal state change                    1        0.10          0.10
                      ********************************************************************************
                      
                      SQL ID: 1y75ysg6sp3zv
                      Plan Hash: 1035250681
                      select dmltype$$, max(snaptime$$) 
                      from
                       "GL"."MLOG$_GL_BALANCES21"  where snaptime$$ <= :1  group by dmltype$$
                      
                      
                      call     count       cpu    elapsed       disk      query    current        rows
                      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                      Parse        1      0.00       0.00          0          0          0           0
                      Execute      1      0.00       0.00          0          0          0           0
                      Fetch        3     17.28      63.86      72700     197243          0           2
                      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                      total        5     17.28      63.86      72700     197243          0           2
                      
                      Misses in library cache during parse: 1
                      Misses in library cache during execute: 1
                      Optimizer mode: CHOOSE
                      Parsing user id: SYS   (recursive depth: 2)
                      
                      Rows     Row Source Operation
                      -------  ---------------------------------------------------
                            2  SORT GROUP BY (cr=197243 pr=72700 pw=0 time=2 us cost=1 size=11 card=1)
                      8327908   TABLE ACCESS BY INDEX ROWID MLOG$_GL_BALANCES21 (cr=197243 pr=72700 pw=0 time=55702076 us cost=0 size=11 card=1)
                      8327908    INDEX RANGE SCAN MLOG$_GL_BALANCES21_IDX (cr=77477 pr=33505 pw=0 time=26916048 us cost=0 size=0 card=1)(object id 2113913)
                      
                      
                      Elapsed times include waiting on following events:
                        Event waited on                             Times   Max. Wait  Total Waited
                        ----------------------------------------   Waited  ----------  ------------
                        db file sequential read                     72700        0.06         36.45
                        log file switch (private strand flush incomplete)
                                                                        1        0.03          0.03
                      ********************************************************************************
                      
                      SQL ID: bsa0wjtftg3uw
                      Plan Hash: 690176192
                      select file# 
                      from
                       file$ where ts#=:1
                      
                      
                      call     count       cpu    elapsed       disk      query    current        rows
                      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                      Parse     7408      0.30       0.82          0          0          2           0
                      Execute   7408      0.35       1.25          0          0          0           0
                      Fetch   163991      0.85       2.25          4     335705          0      156583
                      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                      total   178807      1.50       4.33          4     335705          2      156583
                      
                      Misses in library cache during parse: 2
                      Misses in library cache during execute: 2
                      Optimizer mode: CHOOSE
                      Parsing user id: SYS   (recursive depth: 2)
                      
                      Rows     Row Source Operation
                      -------  ---------------------------------------------------
                           21  TABLE ACCESS BY INDEX ROWID FILE$ (cr=45 pr=0 pw=0 time=133 us cost=2 size=42 card=7)
                           21   INDEX RANGE SCAN I_FILE2 (cr=22 pr=0 pw=0 time=69 us cost=1 size=0 card=1)(object id 42)
                      
                      
                      Elapsed times include waiting on following events:
                        Event waited on                             Times   Max. Wait  Total Waited
                        ----------------------------------------   Waited  ----------  ------------
                        db file sequential read                         4        0.00          0.00
                      ********************************************************************************
                      
                      SQL ID: 86708bvah4akq
                      Plan Hash: 1363035325
                      select name 
                      from
                       undo$ where file#=:1 and block#=:2 and ts#=:3 and       status$ != 1
                      
                      
                      call     count       cpu    elapsed       disk      query    current        rows
                      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                      Parse      415      0.02       0.39          0          0          4           0
                      Execute    415      0.04       0.28          0          0          0           0
                      Fetch      415      0.08       0.21        183       1660          0         415
                      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                      total     1245      0.14       0.89        183       1660          4         415
                      
                      Misses in library cache during parse: 4
                      Misses in library cache during execute: 4
                      Optimizer mode: CHOOSE
                      Parsing user id: SYS   (recursive depth: 2)
                      
                      Rows     Row Source Operation
                      -------  ---------------------------------------------------
                            1  TABLE ACCESS FULL UNDO$ (cr=4 pr=2 pw=0 time=0 us cost=5 size=28 card=1)
                      
                      
                      Elapsed times include waiting on following events:
                        Event waited on                             Times   Max. Wait  Total Waited
                        ----------------------------------------   Waited  ----------  ------------
                        db file sequential read                       183        0.01          0.09
                      ********************************************************************************
                      
                      SQL ID: 57guu81bd4bc5
                      Plan Hash: 1961611972
                      select name,online$,contents$,undofile#,undoblock#,blocksize,dflmaxext,
                        dflinit,dflincr,dflextpct,dflminext, dflminlen, owner#,scnwrp,scnbas, 
                        NVL(pitrscnwrp, 0), NVL(pitrscnbas, 0), dflogging, bitmapped, inc#, flags, 
                        plugged, NVL(spare1,0), NVL(spare2,0), affstrength 
                      from
                       ts$ where ts#=:1
                      
                      
                      call     count       cpu    elapsed       disk      query    current        rows
                      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                      Parse        4      0.00       0.34          0          0          6           0
                      Execute      4      0.06       0.33          0          0          0           0
                      Fetch        4      0.00       0.00          2          8          0           4
                      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                      total       12      0.06       0.68          2          8          6           4
                      
                      Misses in library cache during parse: 4
                      Misses in library cache during execute: 4
                      Optimizer mode: CHOOSE
                      Parsing user id: SYS   (recursive depth: 2)
                      
                      Rows     Row Source Operation
                      -------  ---------------------------------------------------
                            1  TABLE ACCESS CLUSTER TS$ (cr=2 pr=2 pw=0 time=0 us cost=1 size=83 card=1)
                            1   INDEX UNIQUE SCAN I_TS# (cr=1 pr=1 pw=0 time=0 us cost=0 size=0 card=1)(object id 7)
                      Do you control the SQL for the MV and/or the delete? If you can control the SQL on the delete
                      No, I can't control the sql for the MV.

                      This is an apps tables and we can partition the apps tables. But partition with parallel is not helping here.

                      Please suggest on this and let me know if there is any other suggestions to improve this refresh.

                      Thanks
                      • 8. Re: need help on tuning materialized view refresh
                        riedelme
                        Sorry, no new ideas beyond what I already posted :(
                        • 9. Re: need help on tuning materialized view refresh
                          user646034
                          Hi ,

                          Please if somebody have any idea on this.
                          • 10. Re: need help on tuning materialized view refresh
                            Hemant K Chitale
                            Why do you want the index "C3BOOKS" ? How about removing it ?


                            BTW the Row Source Operations statistics (e.g. cr [592437 blocks] and time [945seconds]) do not match the execution statistics.


                            Hemant K Chitale
                            • 11. Re: need help on tuning materialized view refresh
                              Jonathan Lewis
                              Hemant K Chitale wrote:
                              Why do you want the index "C3BOOKS" ? How about removing it ?

                              BTW the Row Source Operations statistics (e.g. cr [592437 blocks] and time [945seconds]) do not match the execution statistics.
                              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 odd thing (to my mind) about that particular statement is that Oracle seems to have a reasonable estimate of the number of rows, and STILL decides to use an indexed access path.

                              I think this whole problem may be highlighting a generic issue with join materialized views - if one of your tables in the join has a very small number of rows, so that some rows join to a very large number of rows from another table, then a (relevant) change to one of the rows in the small table will HAVE to result in a large number of deletes from, and re-inserts to, the materialized view. (I'll have to create a model to confirm that.)

                              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.

                              Regards
                              Jonathan Lewis
                              • 12. Re: need help on tuning materialized view refresh
                                Randolf Geist
                                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.
                                Looking at the Materialized View definition, it occurs to me that GL_SET_OF_BOOKS is actually not showing up in the projected column list, and only used as part of the joins.

                                So does that mean that the join to GL_SET_OF_BOOKS is required to filter for relevant data? Or is this supposed to be a foreign key / primary key relationship where the join is rather pointless?

                                It looks like the table may be required to establish the relationship between GL_BALANCES21 and GL_PERIODS on PERIOD_SET_NAME...

                                Randolf
                                • 13. Re: need help on tuning materialized view refresh
                                  Jonathan Lewis
                                  Randolf Geist wrote:
                                  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.
                                  It looks like the table may be required to establish the relationship between GL_BALANCES21 and GL_PERIODS on PERIOD_SET_NAME...
                                  I was hoping that I wouldn't have to look too closely at the SQL - but you're right, it's one of those awful Oracle Applications GL things.

                                  However, I think this means that either he has actually changed the period_set_name for a row in gl_sets_of_books before doing the refresh, or there are more columns in the MV Log for gl_sets_of_books than appear in this MV and he's changed one of those.

                                  I suppose that there may be some detail in the refresh code that makes Oracle cascade down to the GL_PERIODS table and do this if he's changed one of the GL_PERIODS rows on a column that's either in the MV or the MV Log on GL_PERIODS.


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

                                    The below explain without index and with index.
                                    /* MV_REFRESH (DEL) */ DELETE FROM "APPS"."BAL_PART                                                                                                                                                                                                                                                                                                                                                                                                                 
                                    " SNA$ WHERE "C3" IN (SELECT /*+ NO_MERGE  */ * FROM (SELECT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                    CHARTOROWID("MAS$"."M_ROW$$") RID$     FROM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
                                    "GL"."MLOG$_GL_SETS_OF_BOOKS" "MAS$"   WHERE "MAS$".SNAPTIME$$ > :B_ST1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
                                    ) AS OF SNAPSHOT(:B_SCN) MAS$)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                    Plan hash value: 2704021294                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           | Id  | Operation            | Name                          | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
                                    -------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
                                    |   0 | DELETE STATEMENT     |                               |        |       |   339K(100)|          |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
                                    |   1 |  DELETE              | BAL_PART                          |        |       |            |          |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
                                    |   2 |   NESTED LOOPS       |                               |      1 |   168 |   339K  (1)|999:59:59 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
                                    |   3 |    SORT UNIQUE       |                               |      1 |   138 |     2   (0)| 00:02:31 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
                                    |   4 |     TABLE ACCESS FULL| MLOG$_GL_SETS_OF_BOOKS        |      1 |   138 |     2   (0)| 00:02:31 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
                                    |   5 |    INDEX RANGE SCAN  | C3BOOKS                       |    110M|  3164M|   339K  (0)|999:59:59 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
                                    -------------------------------------------------------------------------------------------------------  
                                    
                                    If i will not use the C3 index then the query will use the belolw plan, I guess this will also take same time or more time.
                                    |   0 | DELETE STATEMENT       |                               |        |       |  9743K(100)|          |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                                    |   1 |  DELETE                | BAL_PART                          |       |            |          |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                                    |   2 |   HASH JOIN RIGHT SEMI |                               |    101M|    15G|  9743K  (1)|999:59:59 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                                    |   3 |    TABLE ACCESS FULL   | MLOG$_GL_SETS_OF_BOOKS        |      1 |   138 |     2   (0)| 00:02:31 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                                    
                                    PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                                    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                    |   4 |    MAT_VIEW ACCESS FULL| HDFC_REP_GL_TRIAL_BAL_DAN_MV1 |    404M|    11G|  9743K  (1)|999:59:59 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                                    Period_set_name column is present in GL_PERIODS but it is not present in GL_BALANCES21.
                                    SQL> desc gl.gl_periods
                                     Name                                      Null?    Type
                                     ----------------------------------------- -------- ----------------------------
                                     PERIOD_SET_NAME                           NOT NULL VARCHAR2(15)
                                     PERIOD_NAME                               NOT NULL VARCHAR2(15)
                                     LAST_UPDATE_DATE                          NOT NULL DATE
                                     LAST_UPDATED_BY                           NOT NULL NUMBER(15)
                                     START_DATE                                NOT NULL DATE
                                     END_DATE                                  NOT NULL DATE
                                     YEAR_START_DATE                                    DATE
                                     QUARTER_START_DATE                                 DATE
                                     PERIOD_TYPE                               NOT NULL VARCHAR2(15)
                                     PERIOD_YEAR                               NOT NULL NUMBER(15)
                                     PERIOD_NUM                                NOT NULL NUMBER(15)
                                     QUARTER_NUM                               NOT NULL NUMBER(15)
                                     ENTERED_PERIOD_NAME                       NOT NULL VARCHAR2(15)
                                     ADJUSTMENT_PERIOD_FLAG                    NOT NULL VARCHAR2(1)
                                     CREATION_DATE                                      DATE
                                     CREATED_BY                                         NUMBER(15)
                                     LAST_UPDATE_LOGIN                                  NUMBER(15)
                                     DESCRIPTION                                        VARCHAR2(240)
                                     ATTRIBUTE1                                         VARCHAR2(150)
                                     ATTRIBUTE2                                         VARCHAR2(150)
                                     ATTRIBUTE3                                         VARCHAR2(150)
                                     ATTRIBUTE4                                         VARCHAR2(150)
                                     ATTRIBUTE5                                         VARCHAR2(150)
                                     ATTRIBUTE6                                         VARCHAR2(150)
                                     ATTRIBUTE7                                         VARCHAR2(150)
                                     ATTRIBUTE8                                         VARCHAR2(150)
                                     CONTEXT                                            VARCHAR2(150)
                                    
                                    SQL> desc gl.gl_balances21
                                     Name                                      Null?    Type
                                     ----------------------------------------- -------- ----------------------------
                                     SET_OF_BOOKS_ID                           NOT NULL NUMBER(15)
                                     CODE_COMBINATION_ID                       NOT NULL NUMBER(15)
                                     CURRENCY_CODE                             NOT NULL VARCHAR2(15)
                                     PERIOD_NAME                               NOT NULL VARCHAR2(15)
                                     ACTUAL_FLAG                               NOT NULL VARCHAR2(1)
                                     LAST_UPDATE_DATE                          NOT NULL DATE
                                     LAST_UPDATED_BY                           NOT NULL NUMBER(15)
                                     BUDGET_VERSION_ID                                  NUMBER(15)
                                     ENCUMBRANCE_TYPE_ID                                NUMBER(15)
                                     TRANSLATED_FLAG                                    VARCHAR2(1)
                                     REVALUATION_STATUS                                 VARCHAR2(1)
                                     PERIOD_TYPE                                        VARCHAR2(15)
                                     PERIOD_YEAR                                        NUMBER(15)
                                     PERIOD_NUM                                         NUMBER(15)
                                     PERIOD_NET_DR                                      NUMBER
                                     PERIOD_NET_CR                                      NUMBER
                                     PERIOD_TO_DATE_ADB                                 NUMBER
                                     QUARTER_TO_DATE_DR                                 NUMBER
                                     QUARTER_TO_DATE_CR                                 NUMBER
                                     QUARTER_TO_DATE_ADB                                NUMBER
                                     YEAR_TO_DATE_ADB                                   NUMBER
                                     PROJECT_TO_DATE_DR                                 NUMBER
                                     PROJECT_TO_DATE_CR                                 NUMBER
                                     PROJECT_TO_DATE_ADB                                NUMBER
                                     BEGIN_BALANCE_DR                                   NUMBER
                                     BEGIN_BALANCE_CR                                   NUMBER
                                     PERIOD_NET_DR_BEQ                                  NUMBER
                                     PERIOD_NET_CR_BEQ                                  NUMBER
                                     BEGIN_BALANCE_DR_BEQ                               NUMBER
                                     BEGIN_BALANCE_CR_BEQ                               NUMBER
                                     TEMPLATE_ID                                        NUMBER(15)
                                     ENCUMBRANCE_DOC_ID                                 NUMBER(15)
                                     ENCUMBRANCE_LINE_NUM                               NUMBER(15)
                                     QUARTER_TO_DATE_DR_BEQ                             NUMBER
                                     QUARTER_TO_DATE_CR_BEQ                             NUMBER
                                     PROJECT_TO_DATE_DR_BEQ                             NUMBER
                                     PROJECT_TO_DATE_CR_BEQ                             NUMBER
                                    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.

                                    Please suggest.

                                    Thanks,
                                    1 2 Previous Next