This is just intended to demonstrate redo usage of various insert
operations rather than answer the whole question. Results on my 10g instance are not 100% deterministic, but the broad picture remained the same each time I ran through.
For the heap tables, I do not know why the insert /*+ append */
generated more redo.
testbed:
create table heap_noappend(id integer, dummy char(500));
create table heap_append(id integer, dummy char(500));
create global temporary table gtt_noappend(id integer, dummy char(500));
create global temporary table gtt_append(id integer, dummy char(500));
create global temporary table gtt_results(stage integer, val integer);
test:
insert into gtt_results(stage, val)
select 0, value from v$statname join v$sesstat using(statistic#)
where sid=sys_context('userenv','sid') and name='redo size';
insert into heap_noappend(id, dummy)
select level, 'A' from dual connect by level<1000;
insert into gtt_results(stage, val)
select 1, value from v$statname join v$sesstat using(statistic#)
where sid=sys_context('userenv','sid') and name='redo size';
insert /*+ append */ into heap_append(id, dummy)
select level, 'A' from dual connect by level<1000;
insert into gtt_results(stage, val)
select 2, value from v$statname join v$sesstat using(statistic#)
where sid=sys_context('userenv','sid') and name='redo size';
insert into gtt_noappend(id, dummy)
select level, 'A' from dual connect by level<1000;
insert into gtt_results(stage, val)
select 3, value from v$statname join v$sesstat using(statistic#)
where sid=sys_context('userenv','sid') and name='redo size';
insert /*+ append */ into gtt_append(id, dummy)
select level, 'A' from dual connect by level<1000;
insert into gtt_results(stage, val)
select 4, value from v$statname join v$sesstat using(statistic#)
where sid=sys_context('userenv','sid') and name='redo size';
result:
select *
from( select decode(stage,1,'heap noappend',
2,'heap append',
3,'gtt noappend',
4,'gtt append') as operation,
val-lag(val) over(order by stage) as redo
from gtt_results)
where redo is not null;
OPERATION REDO
------------- ----------------------
heap noappend 606932
heap append 690768
gtt noappend 41488
gtt append 256
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
Best Answer
A fast-refreshable materialized view cannot contain a non-deterministic function like
current_timestamp
. So if you want to materialize the data from the last 24 hours in a materialized view, the materialized view would need to do a complete refresh every time.Do you need a materialized view? Could you maintain your own staging table and create a custom job that runs every few minutes, deletes the data that is now more than 24 hours old, and inserts the new data (either by directly querying the table or creating a trigger that writes the new data to a different table)? That's likely a non-starter if you need to use the query rewrite functionality of a materialized view but if you just want the benefit of having a smaller table to query, custom code may be more efficient.
Or could you partition the table by day so that queries for the past 24 hours always just have to hit the two most recent partitions?