探討實體化視圖的重新整理機制
來源：互聯網
上載者：User
視圖|重新整理
探討實體化視圖的重新整理機制
Author：Kamus
Mail：kamus@itpub.net
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，那麼這一步將被省略。所以如果決定使用完全重新整理，那麼就不要在基表上建立重新整理日誌了，省得無謂的資源消耗。
