探討實體化視圖的刷新機制
2024-07-21 02:06:46
供稿：網友
探討實體化視圖的刷新機制
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，那么這一步將被省略。所以如果決定使用完全刷新，那么就不要在基表上創建刷新日志了，省得無謂的資源消耗。
