本文詳細介紹oracle10g新特性——物化檢視[@more@]Advisor

　　在10g將查詢重寫並且引進了新的強大的調優建議者使管理物化檢視變得容易多了。

　　物化檢視(Materialized Views MVs)，也被稱為快照，現在已經被廣泛應用了。MV將一個查詢的結果儲存在一個段中，並且當使用者提交查詢時返回查詢結果，而不需要重新執行查詢——如果查詢會被執行多次（經常出現在資料倉儲環境中），這就會非常有效。MV可以從基礎表中完全重新整理或透過使用快速重新整理機制增量重新整理。

　　假如你有如下定義的MV：

create materialized view mv_hotel_resv refresh fast enable query rewrite as select distinct city, resv_id, cust_name from hotels h, reservations r where r.hotel_id = h.hotel_id';　　

你如何知道使這個MV正常工作的所有必須物件都已經被建立呢？在10g之前，這一檢測是透過包DBMS_MVIEW的儲存過程EXPLAIN_MVIEW和EXPLAIN_REWRITE實現的。這些儲存過程在10g還存在，它們的功能很簡單——檢測MV是否具備快速重新整理能力和查詢重新能力，但它們並不提供如何使這些能力有效的建議。相反，要求對於每個MV的結構都做檢查是不切實際的。

　　在10g中，有一個新的包DBMS_ADVISOR，它有一個儲存過程TUNE_MVIEW使這項工作變得非常容易：你可以在呼叫這個包時輸入一個輸入引數，引數內容為建立MV的整個指令碼。這個儲存過程建立了一個建議者任務(Advisor Task)，它的名字會通常儲存過程唯一的輸出引數返回給使用者。

　　這有一個例子。由於第一個引數是一個輸出引數，所以你必須定義一個變數：

SQL> -- first define a variable to hold the OUT parameter SQL> var adv_name varchar2(20) 1 SQL> begin 2 dbms_advisor.tune_mview 3 ( 4 :adv_name, 5 'create materialized view mv_hotel_resv refresh fast enable query rewrite as select distinct city, resv_id, cust_name from hotels h, reservations r where r.hotel_id = h.hotel_id'); 6* end; Now you can find out the name of the Advisor from the variable.

SQL> print adv_name ADV_NAME ----------------------- TASK_117　　

接下來，可以透過一個新檢視DBA_TUNE_MVIEW從Advisor那獲取到所提供的建議。在執行查詢前記得先執行設定SET LONG 999999，因為這個檢視中的這個欄位是一個CLOB型別，而預設知會顯示80個字元。

SQL> select script_type, statement 2 from dba_tune_mview 3 where task_name = 'TASK_117' 4 order by script_type, action_id;


SCRIPT_TYPE STATEMENT
-------------- ------------------------------------------------------------
IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON "ARUP"."HOTELS" WITH ROWID,
SEQUENCE ("HOTEL_ID","CITY") INCLUDING NEW VALUES
IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON "ARUP"."HOTELS" ADD
ROWID, SEQUENCE ("HOTEL_ID","CITY") INCLUDING NEW VALUES
IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON "ARUP"."RESERVATIONS" WITH
ROWID, SEQUENCE ("RESV_ID","HOTEL_ID","CUST_NAME")
INCLUDING NEW VALUES
IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON "ARUP"."RESERVATIONS"
ADD ROWID, SEQUENCE ("RESV_ID","HOTEL_ID","CUST_NAME")
INCLUDING NEW VALUES
IMPLEMENTATION CREATE MATERIALIZED VIEW ARUP.MV_HOTEL_RESV REFRESH FAST
WITH ROWID ENABLE QUERY REWRITE AS SELECT
ARUP.RESERVATIONS.CUST_NAME C1, ARUP.RESERVATIONS.RESV_ID
C2, ARUP.HOTELS.CITY C3, COUNT(*) M1 FROM ARUP.RESERVATIONS,
ARUP.HOTELS WHERE ARUP.HOTELS.HOTEL_ID =
ARUP.RESERVATIONS.HOTEL_ID GROUP BY
ARUP.RESERVATIONS.CUST_NAME, ARUP.RESERVATIONS.RESV_ID,
ARUP.HOTELS.CITY
UNDO DROP MATERIALIZED VIEW ARUP.MV_HOTEL_RESV
欄位SCRIPT_TYPE的內容就是建議。大多數行都是要被實施的，因此被命名成IMPLEMENTATION。如果接受了這些建議，需要從欄位ACTION_ID中得到一個特殊的序列號。
如果重新仔細檢查一下這些自動產生的建議，你會發現它們和你自己分析得出需要做的操作很相似。這些建議是邏輯上的；如果存在快速重新整理，那就需要透過包括這些新值的子句在基礎表上建立物化檢視日誌(MATERIALIZED VIEW LOG)。STATEMENT欄位甚至提供了一個實施這些建議的準確的SQL語句。
　　在實施的最後步驟，Advisor建議對MV的建立方式做一些修改。注意我們例子中的一個不同點：在MV上加了一個count(*)。由於我們定義這個MV是快速重新整理，而count(*)又是必須的，所以Advisor修正了這一冗餘。
　　儲存過程TUNE_MVIEW與EXPLAIN_MVIEW和EXPLAIN_REWRITE的不同之處不僅僅在於建議，它還能更容易鑑別出並提供一個效率更好的方式建立相同的MV。有時候Advisor能建議比使用一個單一的MV效率更高的的查詢。
　　你可能會問，如果一個經驗豐富的DBA能找出MV建立指令碼中的卻些並且能自己調整它，那這些有什麼用？當然，Advisor就是一個經驗豐富、精力充沛、機器人似的的DBA，它能給出和人差不多的建議。但是和人有一個很大的不同：它可以隨時工作而不需要假期和漲薪。這一好處可以使有經驗的DBA從日常任務中解放出來，把這些工作留給普通的DBA去做。而把它們自己的經驗發揮到更具戰略意義的任務中。
　　你也可以在嗲用TUNE_MVIEW時傳入Advisor的名字，這樣就不會使用系統自己生產的名字了。
　　更容易實施
　　既然你知道了這些建議，你當然希望去實施它們了。一個方法就是將欄位STATEMENT中內容取出，存到一個指令碼檔案中，並執行它。另外一個方法就是執行一個包裡面的儲存過程：
begin
dbms_advisor.create_file (
dbms_advisor.get_task_script ('TASK_117'),
'MVTUNE_OUTDIR',
'mvtune_script.sql'
);
end;
/
這一儲存過程是假定你已經定義了一個目錄物件的情況下呼叫的，如：
create directory mvtune_outdir as '/home/oracle/mvtune_outdir';　　呼叫包dbms_advisor的這個儲存過程會在目錄/home/oracle/mvtune_outdir中生成一個名叫mvtune_script.sql的指令碼檔案。如果檢視檔案，它有如下內容：
Rem SQL Access Advisor: Version 10.1.0.1 - Production
Rem
Rem Username: ARUP
Rem Task: TASK_117
Rem Execution date:
Rem
set feedback 1
set linesize 80
set trimspool on
set tab off
set pagesize 60
whenever sqlerror CONTINUE
CREATE MATERIALIZED VIEW LOG ON
"ARUP"."HOTELS"
WITH ROWID, SEQUENCE("HOTEL_ID","CITY")
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON
"ARUP"."HOTELS"
ADD ROWID, SEQUENCE("HOTEL_ID","CITY")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON
"ARUP"."RESERVATIONS"
WITH ROWID, SEQUENCE("RESV_ID","HOTEL_ID","CUST_NAME")
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON
"ARUP"."RESERVATIONS"
ADD ROWID, SEQUENCE("RESV_ID","HOTEL_ID","CUST_NAME")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW ARUP.MV_HOTEL_RESV
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT ARUP.RESERVATIONS.CUST_NAME C1,
RUP.RESERVATIONS.RESV_ID C2, ARUP.HOTELS.CITY
C3, COUNT(*) M1 FROM ARUP.RESERVATIONS, ARUP.HOTELS WHERE
RUP.HOTELS.HOTEL_ID
= ARUP.RESERVATIONS.HOTEL_ID GROUP BY
RUP.RESERVATIONS.CUST_NAME, ARUP.RESERVATIONS.RESV_ID,
ARUP.HOTELS.CITY;
whenever sqlerror EXIT SQL.SQLCODE
begin
dbms_advisor.mark_recommendation('TASK_117',1,'IMPLEMENTED');
end;
/ 
　　這一檔案包含了所有你需要實施的建議的內容，而不需要你手工去建立一個指令碼。機器DBA又一次替你做了你需要做的工作。
重寫還是報錯
　　現在你可能已經認識到了查詢重寫特性是多麼有用和重要。它能大大降低I/O和處理過程、返回結果更快。
　　還是假定以上的例子，使用者執行一個下面的查詢：
1 SQL> Select city, sum(actual_rate)
2 from hotels h, reservations r, trans t
3 where t.resv_id = r.resv_id
4 and h.hotel_id = r.hotel_id
5 group by city;
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
478 bytes sent via SQL*Net to client
496 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk) 
　　請注意consistent gets的值是6——一個非常低的值。這一結果是基於這個查詢已經基於從3張表建立的2個檢視的查詢重寫。不是從表查詢，而是從MV查詢，一次消耗了更少的如磁碟IO和CPU的資源。
　　但是如果查詢重寫失敗了會怎麼樣呢？可能會以為幾個原因失敗：如果初始化引數query_rewrite_integrity被設定為TRUSTED並且MV的狀態為STALE，查詢就不會被重寫。你可以透過設定會話的引數來模擬這一過程。
SQL> alter session set query_rewrite_enabled = false; 
　　執行這一命令後，查詢計劃顯示是從3張表查詢資料，而不是從MV：
0 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
478 bytes sent via SQL*Net to client
496 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk) 
　　請注意consistent gets的值：從6上升到了16。在真實環境中，這一結果恐怕是無法接受的。因為多出來的資源請求可能無法獲得，而你就必須自己重寫這一查詢了。在那樣的情況下，你就必須確保查詢一定能被重寫。
在Oracle 9i和以下版本中，可能只有一個方法實現：使查詢重寫失效而不是使基礎表的訪問失敗。在10g中透過一個特殊的提示可以提供這樣的機制：REWRITE_OR_ERROR。上面這個查詢就可以這樣寫了：
1 SQL> select /*+ REWRITE_OR_ERROR */ city, sum(actual_rate)
2 from hotels h, reservations r, trans t
3 where t.resv_id = r.resv_id
4 and h.hotel_id = r.hotel_id
5 group by city;
from hotels h, reservations r, trans t
*
ERROR at line 2:
ORA-30393: a query block in the statement did not rewrite 
　　這樣就會產生一個ora-30393的錯誤資訊。這個資訊表示查詢不能透過使用MV來重寫，因此語句失敗。這一錯誤保護可以防止查詢長期執行後系統發生資源缺乏問題。但是還要注意一個潛在問題：如果一個查詢成功了，而不是所有都成功了，這些MV就能被用於查詢的重寫。因此，如果MV_ACTUAL_SALES而不是MV_HOTL_RESV能被使用，查詢將會重寫，錯誤也不會產生。這種情況下，查詢計劃就如以下：
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=6 Bytes=156)
1 0 SORT (GROUP BY) (Cost=11 Card=6 Bytes=156)
2 1 HASH JOIN (Cost=10 Card=80 Bytes=2080)
3 2 MERGE JOIN (Cost=6 Card=80 Bytes=1520)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'HOTELS' (TABLE) (Cost=2 Card=8 Bytes=104)
5 4 INDEX (FULL SCAN) OF 'PK_HOTELS' (INDEX (UNIQUE)) (Cost=1 Card=8)
6 3 SORT (JOIN) (Cost=4 Card=80 Bytes=480)
7 6 TABLE ACCESS (FULL) OF 'RESERVATIONS' (TABLE) (Cost=3 Card=80 Bytes=480)
8 2 MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_ACTUAL_SALES' (MAT_VIEW REWRITE) (Cost=3 Card=80 Bytes=560) 
　　這一查詢使用了MV_ACTUAL_SALES而不是MV_HOTEL_RESV，這樣，表HOTELS和RESERVATIONS就能夠訪問。這種情況下，特別使對後面兩種表會做全表掃描的情況下，將會消耗更多的資源——在你建立MV和設計查詢語句時要特別注意。
　　儘管你已經透過資源管理器（Resource Manager）控制資源使用，使用這一提示能防止在資源管理器被呼叫前查詢被執行。資源管理器基於最佳化器的統計資料來降低資源的消耗，因此統計資料的有無將會影響這一過程。而“重寫還是報錯”這一特性將會不管有誤統計資料都會阻值表的訪問。
　　更佳的查詢計劃
　　在前面的例子中，請注意在查詢計劃中有這樣一行：
MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_ACTUAL_SALES' (MAT_VIEW REWRITE) 
　　這個訪問MAT_VIEW REWRITE的方法是新出現的。它表示已經訪問了MV，而不是表或者段。這就可以讓你在從名字上無法區分時知道是在使用表還是MV。
　　總結
　　在10g中，由於增加了新的Tuning Advisor，它能向你提供很多關於MV設計方面的建議而無需透過猜測方式進行。這使得管理MV容易多了。我特別細化能將建議生成一個完整的指令碼使實施更加迅速，能節省很多時間這一特性。強制重寫或取消查詢這一特性在決策支援系統中非常有用。因為這樣的系統不允許一個沒有被重寫的查詢在資料庫內瘋狂執行。

ZT oracle10g新特性——物化檢視

相關文章