oracle 10G 物化檢視新特性(測試效果不理想)

物化檢視

利用強制查詢重寫和新的強大的調整顧問程式 — 它們使您不再需要憑猜測進行工作 — 的引入，在 10g 中管理物化檢視變得更加容易

物化檢視 (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';

您如何才能知道已經為這個物化檢視建立了其正常工作所必需的所有物件？在 Oracle 資料庫 10g 之前，這是用 DBMS_MVIEW 程式包中的 EXPLAIN_MVIEW 和 EXPLAIN_REWRITE 過程來判斷的。這些過程（在 10g 中仍然提供）非常簡要地說明一種特定的功能 — 如快速重新整理功能或查詢重寫功能 — 可能用於上述的物化檢視，但不提供如何實現這些功能的建議。相反，需要對每一個物化檢視的結構進行目視檢查，這是非常不實際的。

在 10g 中，新的 DBMS_ADVISOR 程式包中的一個名為 TUNE_MVIEW 的過程使得這項工作變得非常容易：您利用 IN 引數來呼叫程式包，這構造了物化檢視建立指令碼的全部內容。該過程建立一個顧問程式任務 (Advisor Task)，它擁有一個特定的名稱，僅利用 OUT 引數就能夠把這個名稱傳回給您。

下面是一個例子。因為第一個引數是一個 OUT 引數，所以您需要在 SQL*Plus 中定義一個變數來儲存它。

SQL> -- 首先定義一個變數來儲存 OUT 引數
SQL> var adv_name varchar2(20)
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;

現在您可以在該變數中找出顧問程式的名稱。

SQL> print adv_name

ADV_NAME
-----------------------
TASK_117

接下來，透過查詢一個新的 DBA_TUNE_MVIEW 來獲取由這個顧問程式提供的建議。務必在執行該命令之前執行 SET LONG 999999，因為該檢視中的列語句是一個 CLOB，預設情況下只顯示 80 個字元。

select script_type, statement 
from   dba_tune_mview 
where  task_name = 'TASK_117' 
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 語句。

在實施的最後一個步驟中，顧問程式建議改變建立物化檢視的方式。注意我們的例子中的不同之處：將一個 count(*) 新增到了物化檢視中。因為我們將這個物化檢視定義為可快速重新整理的，所以必須有 count(*)，以便顧問程式糾正遺漏。

TUNE_MVIEW 過程不僅在建議方面超越了在 EXPLAIN_MVIEW 和 EXPLAIN_REWRITE 中提供的功能，還為建立相同的物化檢視指出了更容易和更高效的途徑。有時，顧問程式可以實際推薦多個物化檢視，以使查詢更加高效。

您可能會問，如果任何一個經驗豐富的 DBA 都能夠找出 MV 建立指令碼中缺了什麼，然後自己糾正它，那這還有什麼用？嗯，顧問程式正是用來完成這項工作的：它是一位經驗豐富、高度自覺的自動資料庫管理員，它可以生成 能與人的建議相媲美的建議，但有一個非常重要的不同之處：它免費工作，並且不會要求休假或加薪。這一好處使高階 DBA 解放出來，將日常的工作交給較低階的 DBA，從而允許他們將其專業技能應用到更具有戰略意義的目標上。

您還可以將顧問程式的名稱作為值傳遞給 TUNE_MVIEW 過程中的引數，這將使用該名稱而非系統生成的名稱生成一個的顧問程式。

更容易的實施

既然您可以看到建議，那麼您可能想實施它們。一種方式是選擇列 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, 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;

whenever sqlerror EXIT SQL.SQLCODE

begin
dbms_advisor.mark_recommendation('TASK_117',1,'IMPLEMENTED');
end;
/

這個檔案包含了您實施建議所需的一切，從而為您省去了相當大的手動建立檔案的麻煩。這個自動資料庫管理員又一次能夠為您完成工作。

重寫或退出！

至此，您一定意識到了查詢重寫特性有多重要和多有用。它顯著地減少了 I/O 和處理，並能夠更快地返回結果。

讓我們基於上述例子假定一種情況。使用者執行以下查詢：

Select city, sum(actual_rate)
from hotels h, reservations r, trans t
where t.resv_id = r.resv_id
and h.hotel_id = r.hotel_id
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 — 一個非常低的值。這個結果基於的事實是，重寫了查詢來使用在三個表上建立的兩個物化檢視。選擇不是從表中進行的，而是從物化檢視中進行，從而消耗了更少的資源（如 I/O 和 CPU）。

但如果查詢重寫失敗了，那該怎麼辦？它失敗的原因可能有以下幾種：如果初始化引數 query_rewrite_integrity 的值被設為 TRUSTED，且 MV 的狀態是 STALE，那麼將不會重寫該查詢。您可以透過在查詢之前在會話中設定這個值來模擬這個過程。

alter session set query_rewrite_enabled = false;

在這條命令之後，說明計劃 (EXPLAIN PLAN) 顯示是從所有三個表中而不是從 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。在實際情況下，這個結果可能無法接受，因為無法提供所需的額外資源，因此您可能想自己重寫查詢。在這種情況下，您可以確保如果而且只有在查詢被重寫的情況下，才允許進行查詢。

在 Oracle9i 資料庫和更低版本中，決策是單向的：您可以禁用查詢重寫，但不能禁用基礎表訪問。不過 Oracle 資料庫 10g 提供了一種機制 — 透過一個特殊的提示 REWRITE_OR_ERROR 來實現這一目的。上述查詢將利用該提示寫為：

select /*+ REWRITE_OR_ERROR */ city, sum(actual_rate)
from hotels h, reservations r, trans t
where t.resv_id = r.resv_id
and h.hotel_id = r.hotel_id
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_HOTEL_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 時您將注意到這種情況。

雖然您可以始終利用資源管理器來控制資源使用情況，但使用該提示將防止執行查詢，即使在呼叫資源管理器之前。資源管理器根據最佳化器統計資料估計所需的資源，因此是否存在足夠準確的統計資料將影響這個過程。不過，重寫或錯誤特性將停止表訪問，而不管統計資料如何。

說明計劃更好地進行說明

在上一個例子中，請注意說明計劃輸出中的行：

MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_ACTUAL_SALES' (MAT_VIEW REWRITE) 

這種訪問方法 —MAT_VIEW REWRITE— 是新增的；它顯示正在訪問 MV，而非表或段。該過程立即告訴您表或 MV 是否被使用 — 即使名稱沒有表明段的本質。

結論

在 10g 中，透過引入強大的新調整顧問程式 — 它們能夠告訴您許多有關 MV 的設計的資訊，從而使您不再需要憑猜測進行工作，管理 MV 變得更加容易。我尤其喜歡能夠生成一個完整的指令碼的調整建議，這種指令碼可以快速實施，從而顯著地節省時間和精力。強制重寫或退出查詢的能力在決策支援系統 中會非常有幫助 — 在這種系統中必須保留資源，並且未重寫的查詢將不允許在資料庫內隨意執行。

有關在 10g 中管理物化檢視的更多資訊，請參考《Oracle 資料庫資料倉儲指南 10g 第 1 版 (10.1)》中的。[@more@]說的挺好，但做測試，不是那麼回事，和實際手工寫的差別很大，也有可能試的方法有問題