4 Replies Latest reply: Nov 23, 2012 7:23 AM by 968311 RSS

    To Integrate materialized view Refresh in DAC Execution Plan

    968311
      We have couple of materialized views which we want to get refreshed in DAC execution plan.

      We have referred Technote "BI_Apps796_Perf_Tech_Note_V8" which detailed how materialized view can be refreshed in DAC Execution Plan.

      We have followed below steps but our materized view is not getting refreshed.

      1. Create new Task Actions in DAC using "Tools -> Seed Data -> Actions -> Task Actions"
      with SQL Statement link

      BEGIN
      DBMS_MVIEW.REFRESH('getTableName()', 'C');
      DBMS_STATS.GATHER_TABLE_STATS(ownname => 'getTableOwner()', tabname=> 'getTableName()', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', degree => DBMS_STATS.DEFAULT_DEGREE);
      END;

      2. Defined custom MV as a table in DAC

      3. Added custom MV as the related table to the original Fact.

      4. Reassembled corresponding Subject Areas and rebuilt Execution plan

      But the MV is not getting refershed. Neither could locate any task in Ordered Task which will refresh this MV.
      Have we missed any step? Any pointers in this direction would be appericiated?

      Do we have to specify action .. to task "Load Fact table tas" and specify Success action as Refresh MV?

      How can we trace task in All tasks/Ordered tasks in DAC EP to locate task corresponding to MV refresh?
        • 1. Re: To Integrate materialized view Refresh in DAC Execution Plan
          Srini VEERAVALLI
          Try to add this task action to task->Actions->
          Make sure Load Type is both and Action Type Success Action

          If helps pls mark
          • 2. Re: To Integrate materialized view Refresh in DAC Execution Plan
            968311
            Thanks Veeravalli for Suggesion!

            Tried adding Task action Refresh MV as action type "Success Action" for Fact task .. It did add a step of Refresh MV for load Fact Task in EP

            But it is passing the Orgininal Factable name as to be refreshed as MV which is giving DB error.


            "Refresh MV" for WC_B_TEST_F failed on "DataWarehouse"
            sql: BEGIN
            DBMS_MVIEW.REFRESH('WC_B_TEST_F', 'C');
            DBMS_STATS.GATHER_TABLE_STATS(ownname => 'olap', tabname=> 'WC_B_TEST_F',cascade => FALSE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FORALL COLUMNS SIZE AUTO', degree => DBMS_STATS.DEFAULT_DEGREE);
            END;

            Error: MESSAGE:::ORA-23401: materialized view "OLAP"."WC_B_TEST_F" does not exist

            ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2563
            ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2776
            ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2745
            ORA-06512: at line 2


            instead of WC_B_TEST_F, we wanted "OLAP"."WC_B_TEST_MV" to be refreshed

            Do we need to create another task for MV refresh rather than task action? and add Refresh MV as task action there?

            Thanks,
            • 3. Re: To Integrate materialized view Refresh in DAC Execution Plan
              772481
              No dac functions are available to get the related tables, try defining a task (Fact load task) level parameter and use the same
              in the action template.

              For eg.,
              Task level parameter
              Name Value
              MV_NAME_1 WC_B_TEST_MV

              And change the action template to refer the parameter as below and include the action in the task with action type as success

              BEGIN
              DBMS_MVIEW.REFRESH('@DAC_MV_NAME_1', 'C');
              DBMS_STATS.GATHER_TABLE_STATS(ownname => 'getTableOwner()', tabname=> '@DAC_MV_NAME_1', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', degree => DBMS_STATS.DEFAULT_DEGREE);
              END;

              pls mark if helps
              • 4. Re: To Integrate materialized view Refresh in DAC Execution Plan
                968311
                Yes, using task level paramet it was able to to call required MV correctly.

                Thanks for the help!


                Cheers,