This discussion is archived
4 Replies Latest reply: Nov 23, 2012 5:23 AM by 968311 RSS

To Integrate materialized view Refresh in DAC Execution Plan

968311 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Yes, using task level paramet it was able to to call required MV correctly.

    Thanks for the help!


    Cheers,

Legend

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