0

I've a problem to Automatically Refresh Materialized View, if the stallness condition is NEED_COMPILE / STALE / UNUSABLE OR NOT FRESH.

I've MV with this PL/SQL :

CREATE MATERIALIZED VIEW "some_schema"."MV_TEST_BRO" ("some_field", "some_field", "some_field")
ORGANIZATION HEAP PCTFREE 10 PCTUSED 0 INITRANS 2 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "some_tablespace" 
BUILD IMMEDIATE
USING INDEX 
REFRESH COMPLETE ON DEMAND
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS SELECT * FROM some_table;

btw, my MV is too complex to put it here so I made it simple in here. After I got an update in table master I test it with below query to get status stallness of my MV.

SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS ORDER BY MVIEW_NAME;

And, I create PL/SQL to refresh that MV like this :

BEGIN
DBMS_REFRESH.make(
    name                 => 'some_schema.MINUTE_REFRESH',
    list                 => '',
    next_date            => SYSDATE,
    interval             => '/*1:Mins*/ SYSDATE + 1/(60*24)',
    implicit_destroy     => FALSE,
    lax                  => FALSE,
    job                  => 0,
    rollback_seg         => NULL,
    push_deferred_rpc    => TRUE,
    refresh_after_errors => TRUE,
    purge_option         => NULL,
    parallelism          => 4,
    heap_size            => NULL);
END;

BEGIN
DBMS_REFRESH.add(
    name => 'some_schema.MINUTE_REFRESH',
    list => 'some_schema.MV_TEST_BRO',
    lax  => TRUE);
END;

But the interval of that dbms_refresh is not working at all. So I created some scheduler to do the job, this is my PL/SQL :

BEGIN
    sys.dbms_scheduler.create_job(
    job_name => '"some_schema"."REFRESH_MV"',
    job_type => 'PLSQL_BLOCK',
    job_action => 'begin
       DBMS_REFRESH.REFRESH(name => ''some_schema.MINUTE_REFRESH'');
    end;',
    schedule_name => '"some_schema"."EJS_MV_REFRESH"',
    job_class => '"DEFAULT_JOB_CLASS"',
    comments => 'Scheduler untuk merefresh MV pada schema some_schema',
    auto_drop => FALSE,
    enabled => FALSE);
    sys.dbms_scheduler.set_attribute( name => '"some_schema"."REFRESH_MV"', attribute => 'raise_events', value => dbms_scheduler.job_failed);
    sys.dbms_scheduler.set_attribute( name => '"some_schema"."REFRESH_MV"', attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_FAILED_RUNS);
    sys.dbms_scheduler.set_attribute( name => '"some_schema"."REFRESH_MV"', attribute => 'restartable', value => TRUE);
    sys.dbms_scheduler.enable( '"some_schema"."REFRESH_MV"' );
END;

So far this solve my problem to automatically refresh MV every 1 minute. But it's not effective at all. So, My Problem is :

How to make this job_action to execute DBMS_REFRESH.REFRESH(name => ''some_schema.MINUTE_REFRESH''); every 1 minute if the status of stallness is not FRESH ?

I don't know much about how to use pointer or whatever the name to get the result of query to check SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS ORDER BY MVIEW_NAME; and use this status to trigger the refresh, it's just like IF-THEN condition.

Please help me, if you know how to get that...

1 Answer 1

0

I'm successfully able to create a scheduler for my MV to refresh every minutes if the status stallness is not FRESH. Here's my solutions :

I created a schedules to be used in my scheduler:

BEGIN
    sys.dbms_scheduler.create_schedule(
    repeat_interval => 'FREQ=MINUTELY',
    start_date => to_timestamp_tz('2015-06-09 01:41:55 Asia/Jakarta', 'YYYY-MM-DD HH24:MI:SS TZR'),
    comments => 'Interval refresh 1 menit',
    schedule_name => '"SOME_SCHEMA"."REFRESH_MV"');
END;

After that I create DBMS_SCHEDULER to create a job like this :

BEGIN
sys.dbms_scheduler.create_job(
job_name => '"SOME_SCHEMA"."REFRESH_MV"',
job_type => 'PLSQL_BLOCK',
job_action => 'DECLARE
    mv_name USER_MVIEWS.MVIEW_NAME%type;
    mv_status USER_MVIEWS.STALENESS%type;
    mv_last USER_MVIEWS.LAST_REFRESH_TYPE%type;
    mv_state USER_MVIEWS.COMPILE_STATE%type;

    CURSOR check_mv
    IS SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS WHERE MVIEW_NAME = ''MV_TEST_BRO'' ORDER BY MVIEW_NAME;

    BEGIN
        OPEN check_mv;

        LOOP
            FETCH check_mv INTO mv_name, mv_status, mv_last, mv_state;
            IF mv_status != ''FRESH'' THEN
                DBMS_REFRESH.REFRESH(name => ''SOME_SCHEMA.MINUTE_REFRESH'');
            ELSE
                EXIT;
            END IF;
            EXIT WHEN check_mv%NOTFOUND;

        END LOOP;

    CLOSE check_mv;
END;',
schedule_name => '"SOME_SCHEMA"."REFRESH_MV"',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'Refresh MV every 1 Minutes',
auto_drop => FALSE,
enabled => FALSE);
sys.dbms_scheduler.set_attribute( name => '"SOME_SCHEMA"."REFRESH_MV"', attribute => 'raise_events', value => dbms_scheduler.job_failed);
sys.dbms_scheduler.set_attribute( name => '"SOME_SCHEMA"."REFRESH_MV"', attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_FAILED_RUNS);
sys.dbms_scheduler.set_attribute( name => '"SOME_SCHEMA"."REFRESH_MV"', attribute => 'restartable', value => TRUE);
sys.dbms_scheduler.enable( '"SOME_SCHEMA"."REFRESH_MV"' );
END;

In the job action, I checked stallness status for specified MV like MV_TEST_BRO but you can check the status for every MV you had. And I activated some attribut to raise event if the job failed, logging level, and restartable.

This job was running for a week now and no problem at all. My MV always FRESH every time. May be this post will help another who face the same problem like me. :D

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.