Oracle Database 10 g : 為 DBA 提供的最佳前 20 位的特性（十二） 作者 Arup Nanda 來源： OTN 第 12 周
物化視圖 利用強制查詢重寫和新的強大的調整顧問程序 — 它們使您不再需要憑猜測進行工作 — 的引入，在 10 g 中治理物化視圖變得更加輕易 物化視圖 (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 數據庫 10 g 之前，這是用 DBMS_MVIEW 程序包中的 EXPLAIN_MVIEW 和 EXPLAIN_REWRITE 過程來判定的。這些過程（在 10 g 中仍然提供）非常簡要地說明一種特定的功能 — 如快速刷新功能或查詢重寫功能 — 可能用于上述的物化視圖，但不提供如何實現這些功能的建議。相反，需要對每一個物化視圖的結構進行目視檢查，這是非常不實際的。 在 10 g 中，新的 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 。在實際情況下，這個結果可能無法接受，因為無法提供所需的額外資源，因此您可能想自己重寫查詢。在這種情況下，您可以確保假如而且只有在查詢被重寫的情況下，才答應進行查詢。 在 Oracle9 i 數據庫和更低版本中，決策是單向的：您可以禁用查詢重寫，但不能禁用基礎表訪問。不過 Oracle 數據庫 10 g 提供了一種機制 — 通過一個非凡的提示 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 是否被使用 — 即使名稱沒有表明段的本質。 結論 在 10 g 中，通過引入強大的新調整顧問程序 — 它們能夠告訴您許多有關 MV 的設計的信息，從而使您不再需要憑猜測進行工作，治理 MV 變得更加輕易。我尤其喜歡能夠生成一個完整的腳本的調整建議，這種腳本可以快速實施，從而顯著地節省時間和精力。強制重寫或退出查詢的能力在決策支持系統中會非常有幫助 — 在這種系統中必須保留資源，并且未重寫的查詢將不答應在數據庫內隨意運行。 有關在 10 g 中治理物化視圖的更多信息，請參考《 Oracle 數據庫數據倉庫指南 10 g 第 1 版 (10.1) 》中的 第 8 章 。
