** 探讨实体化视图的刷新机制 ** ** **
** Author ** ** ： ** ** Kamus **
** Mail ** ** ： ** ** [email protected] **
** Date ** ** ： ** ** 2004 ** ** 年 ** ** 10 ** ** 月 ** ** **
今天给客户搭建历史查询服务器，用 oracle8i 的 snapshot 实现，也就是 9i 的实体化视图。
顺手做了一下 trace ，看了一下刷新时候 oracle 后台是怎么工作的。
前期准备，使用 DBMS_SUPPORT 包，这个包默认是没有安装的，需要运行下面的命令来手动安装。
SQL>conn / as sysdba
SQL>@?\rdbms\admin\dbmssupp.sql
SQL>GRANT execute ON dbms_support TO kamus;
SQL>CREATE PUBLIC SYNONYM dbms_support FOR dbms_support;
先看快速刷新，测试表是 T1 ，创建了快照日志，用于刷新的视图是 MV_T1 ，用户是 KAMUS
执行 trace ：
SQL>conn kamus
SQL>exec dbms_support.start_trace(waits=>TRUE,binds=>TRUE);
SQL>exec dbms_mview.refresh(list => 'MV_T1');
SQL>exec dbms_support.stop_trace;
然后 tkprof 生成 trace 结果的报表，下面只是节选了其中一部分。
执行一次 dbms_mview.refresh ， Oracle 后台会执行 13 个 user SQL 和 92 个 internal SQL ，实在是一个繁杂的工作。
1 。开始刷新
BEGIN dbms_mview.refresh(list => 'MV_T1'); END;
2 。检查 SNAP$ 表，确认当前用户是否有需要刷新的视图
3 。在 DBMS_LOCK_ALLOCATED 数据字典中更新记录，设置过期时间
UPDATE DBMS_LOCK_ALLOCATED SET EXPIRATION = SYSDATE + (:B1 /86400)
WHERE
ROWID = :B2
4 。检查可能会用到的 dblink 和一些高级队列的数据字典
5 。检查表的相关约束
6 。检查几个初始化参数的值，包括 _enable_refresh_schedule ， _delay_index_maintain ， compatible
7 。将 MLOG 中所有没有标志为定时刷新的记录更新为立刻刷新
update "KAMUS"."MLOG$_T1" set snaptime$$ = :1
where
snaptime$$ > to_date(' 2100-01-01 :00:00:00','YYYY-MM-DD:HH24:MI:SS')
8 。重新编译 MV_T1 实体化视图
ALTER SUMMARY "KAMUS"."MV_T1" COMPILE
这一步比较可疑， SQL 中是没有 alter summary 找个命令的，如果是编译的话，那么就可能锁定对象，就有可能产生 library cache lock
9 。检查要执行的 SQL 文，这一步比较有趣
SELECT operation#, cols, sql_txt, tabnum, fcmaskvec, ejmaskvec, setnum
FROM
sys.snap_refop$ WHERE ((operation# >= 0 AND operation# <= 6) OR operation#
IN (10, 12, 13)) AND sowner = :1 AND vname = :2 AND instsite = :3 ORDER
BY tabnum, setnum, operation#
对于一个 MV 刷新将会使用到 SQL 全部存在这张表中。
如果是 fast 刷新，那么对于查询 mlog 表，查询基表的数据， insert 、 update 、 delete 实体化视图都分别有一句 SQL 。
其中 operation# 字段值的常见含义如下：
0 ：查询 mlog 表
1 ：对于实体化视图的 delete 操作
2 ：查询基表的最新数据
3 ：对于实体化视图的 update 操作
4 ：对于实体化视图的 insert 操作
如果是 complete 刷新，那么只有一条记录，是基于基表的全表 insert 操作， operation# 是 7 。
此处的执行计划显示是对于 snap_refop$ 的全表扫描，如果系统中存在大量需要 refresh 的实体化视图，无疑是影响性能的。
10 。取得需要更新的记录主键
SELECT DISTINCT LOG$."IDATE"
FROM
(SELECT MLOG$."IDATE" FROM "KAMUS"."MLOG$_T1" MLOG$ WHERE "SNAPTIME$$" > :1
AND ("DMLTYPE$$" != 'I')) LOG$ WHERE (LOG$."IDATE") NOT IN (SELECT
MAS_TAB$."IDATE" FROM "T1" "MAS_TAB$" WHERE LOG$."IDATE" = MAS_TAB$."IDATE")
注意到这里使用了 distinct ，也就是我们可以猜测，如果在一次刷新之前对于同一条记录作了多次的修改，那么刷新操作只需要作一次，就是获得基表中该条记录的最新值就可以了。
IDATE 字段是我的测试表中的主键。
"DMLTYPE$$" != 'I' 表示不是 insert 的操作。
此处的执行计划显示对于 mlog 表进行了一次全表扫描，如果有大量的更新操作，无疑又是影响性能的一步。
11 。取得基表中当前需要刷新的记录所有字段的最新值
SELECT CURRENT$."IDATE",CURRENT$."C"
FROM
(SELECT "T1"."IDATE" "IDATE","T1"."C" "C" FROM "T1" "T1") CURRENT$, (SELECT
DISTINCT MLOG$."IDATE" FROM "KAMUS"."MLOG$_T1" MLOG$ WHERE "SNAPTIME$$" >
:1 AND ("DMLTYPE$$" != 'D')) LOG$ WHERE CURRENT$."IDATE" = LOG$."IDATE"
这一步操作表示， mlog 中只存储修改操作涉及到的记录主键，其它的字段值仍然会到基表中去作查询。
此处的执行计划显示对于 mlog 表再一次作了全表扫描。
12 。用取得的最新值更新实体化视图
UPDATE "KAMUS"."MV_T1" SET "IDATE" = :1,"C" = :2
WHERE
"IDATE" = :1
这一步仍然比较奇怪，因为我的测试中只作了 insert ，并没有 update 的操作，莫非 oracle 在刷新时，并不管是否存在 update 的操作，都会例行作一次视图数据的更新？不过此处更新会使用实体化视图中的主键，速度应该时很快的。
13 。将取得的最新值插入到实体化视图中
INSERT INTO "KAMUS"."MV_T1" ("IDATE","C")
VALUES
(:1,:2)
这步才到了真正要实现的目的上，呵呵。
14 。更新一批数据字典，表明刷新已经完成
15 。删除 mlog 表中已经刷新过的记录
delete from "KAMUS"."MLOG$_T1"
where
snaptime$$ <= :1
这一步操作是比较耗费资源的，使用 delete ，产生 redo 和 undo ，无法降低 mlog 表的 HWM 标志，同时又是一次全表扫描，如果经常有大量更新发生，最好能定时作 mlog 表的 truncate 动作，否则这一步操作可能会越来越慢。
至此，一次实体化视图的快速刷新算是完全结束了。
我们继续看一下完全刷新的后台机制。
1-8 步跟快速刷新基本相同。
9 。 检查要执行的 SQL 文
SELECT operation#, cols, sql_txt
FROM
sys.snap_refop$ WHERE operation# = 7 AND sowner = :1 AND vname = :2 AND
instsite = :3
可以看到直接去找 operation# = 7 的 SQL 了，这就是完全刷新需要使用的 SQL 。
10 。检查完全刷新涉及到的约束，索引，触发器
11 。删除实体化视图中的原有数据
delete from "KAMUS"."MV_T"
这一步让我很诧异，记得文档中说应该是 truncate 操作，但是此处显示的是 delete ？这样的话，完全刷新的代价实在是很大了。
12 。插入基表中所有数据
INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "KAMUS"."MV_T"("X") SELECT "T"."X"
FROM "T" "T"
这里使用到的提示 /*+ BYPASS_RECURSIVE_CHECK */ ，是不是在实际应用中可以提高 INSERT 的效率呢？
13 。更新一批数据字典，表明刷新已经完成
14 。如果在基表上创建了刷新日志 mlog 表，那么 Oracle 不管这次刷新是不是完全刷新，都会去作一次删除 mlog 表中数据的操作。如果没有创建过 mlog ，那么这一步将被省略。所以如果决定使用完全刷新，那么就不要在基表上创建刷新日志了，省得无谓的资源消耗。