江边潮未尽，枫红一季秋

在OraFAQ上看到一个关于物化视图快速刷新的问题，由于同一个mv log被多个物化视图使用，不同的物化视图使用不同的刷新间隔，导致mv log中记录过多而使得快速刷新变得缓慢。

下面通过一个实际的例子来演示这种情况：

创建测试表

NING@ning>create table master as select * from all_objects where object_id<100;

Table created.

NING@ning>alter table master add primary key(object_id);

Table altered.


创建mv log

NING@ning>create materialized view log on master with primary key;

Materialized view log created.

创建第一个mv，on commit刷新

NING@ning>create materialized view mv_commit
2 refresh fast
3 on commit
4 as
5 select object_id,object_type,object_name
6 from master;

Materialized view created.

创建第二个mv，on demand刷新

NING@ning>create materialized view mv_demand
2 refresh fast
3 on demand
4 as
5 select object_id,LAST_DDL_TIME,STATUS
6 from master;

Materialized view created.

查看目前mv和mv log中的数据量

NING@ning>select count(*) from mv_commit;

COUNT(*)
———-
98

NING@ning>select count(*) from mv_demand;

COUNT(*)
———-
98

NING@ning>select count(*) from MLOG$_MASTER;

COUNT(*)
———-
0

往master表插入新的记录并commit

NING@ning>insert into master select * from all_objects where object_id>=100 and object_id<200;

99 rows created.

NING@ning>commit;

Commit complete.

查看mv和mv log中的数据量

NING@ning>select count(*) from mv_commit;

COUNT(*)
———-
197

NING@ning>select count(*) from mv_demand;

COUNT(*)
———-
98

NING@ning>select count(*) from mlog$_master;

COUNT(*)
———-
99

往master表插入新的记录并commit

NING@ning>insert into master select * from all_objects where object_id>=200 and object_id<300;

82 rows created.

NING@ning>commit;

Commit complete.

查看mv和mv log中的数据量

NING@ning>select count(*) from mv_commit;

COUNT(*)
———-
279

NING@ning>select count(*) from mv_demand;

COUNT(*)
———-
98

NING@ning>select count(*) from mlog$_master;

COUNT(*)
———-
181

可以看到，随着master不停的插入新的数据，mv_commit不停的被刷新，但是mv log中的记录了却没有在mv_commit刷新后清空，而是不停的增长。这是由于mv log中的数据对于mv_demand来说还是必须的，这样就导致mv_commit的刷新会越来越慢，因为快速刷新每次都是根据mv log中的记录来执行刷新的。

手动刷新一次mv_demand，可以发现mv log中的记录被清空了

NING@ning>exec dbms_mview.refresh(‘MV_DEMAND’,'F’);

PL/SQL procedure successfully completed.

NING@ning>select count(*) from mv_demand;

COUNT(*)
———-
279

NING@ning>select count(*) from mlog$_master;

COUNT(*)
———-
0

查看一个master表上有多少个mv及其最新刷新时间

NING@ning>SELECT owner, NAME, snapshot_site, TO_CHAR(current_snapshots,’mm/dd/yyyy hh24:mi’) current_snapshots
2 FROM dba_registered_snapshots, dba_snapshot_logs
3 WHERE dba_registered_snapshots.snapshot_id = dba_snapshot_logs.snapshot_id (+)
4 AND dba_snapshot_logs.MASTER=upper(‘&table_name’);
Enter value for table_name: master
old 4: AND dba_snapshot_logs.MASTER=upper(‘&table_name’)
new 4: AND dba_snapshot_logs.MASTER=upper(‘master’)

OWNER NAME SNAPSHOT_S CURRENT_SNAPSHOTS
———- ——————– ———- ——————–
NING MV_COMMIT NING 07/16/2007 10:24
NING MV_DEMAND NING 07/16/2007 10:28