Oracle Database 10 g : 為 DBA 提供的最佳前 20 位的特性（十二） 作者 Arup Nanda 來源： OTN 第 12 周
物化視圖 利用強(qiáng)制查詢重寫和新的強(qiáng)大的調(diào)整顧問程序 — 它們使您不再需要憑猜測進(jìn)行工作 — 的引入，在 10 g 中治理物化視圖變得更加輕易 物化視圖 (MV) — 也稱為快照 — 一段時間來已經(jīng)廣泛使用。 MV 在一個段中存儲查詢結(jié)果，并且能夠在提交查詢時將結(jié)果返回給用戶，從而不再需要重新執(zhí)行查詢 — 在查詢要執(zhí)行幾次時（這在數(shù)據(jù)倉庫環(huán)境中非經(jīng)常見），這是一個很大的好處。物化視圖可以利用一個快速刷新機(jī)制從基礎(chǔ)表中全部或增量刷新。 假定您已經(jīng)定義了一個物化視圖，如下： 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'; 您如何才能知道已經(jīng)為這個物化視圖創(chuàng)建了其正常工作所必需的所有對象？在 Oracle 數(shù)據(jù)庫 10 g 之前，這是用 DBMS_MVIEW 程序包中的 EXPLAIN_MVIEW 和 EXPLAIN_REWRITE 過程來判定的。這些過程（在 10 g 中仍然提供）非常簡要地說明一種特定的功能 — 如快速刷新功能或查詢重寫功能 — 可能用于上述的物化視圖，但不提供如何實(shí)現(xiàn)這些功能的建議。相反，需要對每一個物化視圖的結(jié)構(gòu)進(jìn)行目視檢查，這是非常不實(shí)際的。 在 10 g 中，新的 DBMS_ADVISOR 程序包中的一個名為 TUNE_MVIEW 的過程使得這項(xiàng)工作變得非常輕易：您利用 IN 參數(shù)來調(diào)用程序包，這構(gòu)造了物化視圖創(chuàng)建腳本的全部內(nèi)容。該過程創(chuàng)建一個顧問程序任務(wù) (Advisor Task) ，它擁有一個特定的名稱，僅利用 OUT 參數(shù)就能夠把這個名稱傳回給您。 下面是一個例子。因?yàn)榈谝粋€參數(shù)是一個 OUT 參數(shù)，所以您需要在 SQL*Plus 中定義一個變量來保存它。 SQL> -- 首先定義一個變量來保存 OUT 參數(shù) 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; 現(xiàn)在您可以在該變量中找出顧問程序的名稱。 SQL> PRint adv_name ADV_NAME ----------------------- TASK_117
接下來，通過查詢一個新的 DBA_TUNE_MVIEW 來獲取由這個顧問程序提供的建議。務(wù)必在運(yùn)行該命令之前執(zhí)行 SET LONG 999999 ，因?yàn)樵撘晥D中的列語句是一個 CLOB ，默認(rèn)情況下只顯示 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 列顯示建議的性質(zhì)。大多數(shù)行將要執(zhí)行，因此名稱為 IMPLEMENTATION 。假如接受，則需按照由 ACTION_ID 列指出的特定順序執(zhí)行建議的操作。 假如您仔細(xì)查看這些自動生成的建議，那么您將注重到它們與您自己通過目視分析生成的建議是類似的。這些建議合乎邏輯；快速刷新的存在需要在擁有適當(dāng)子句（如那些包含新值的子句）的基礎(chǔ)表上有一個 MATERIALIZED VIEW LOG 。 STATEMENT 列甚至提供了實(shí)施這些建議的確切 SQL 語句。 在實(shí)施的最后一個步驟中，顧問程序建議改變創(chuàng)建物化視圖的方式。注重我們的例子中的不同之處：將一個 count(*) 添加到了物化視圖中。因?yàn)槲覀儗⑦@個物化視圖定義為可快速刷新的，所以必須有 count(*) ，以便顧問程序糾正遺漏。 TUNE_MVIEW 過程不僅在建議方面超越了在 EXPLAIN_MVIEW 和 EXPLAIN_REWRITE 中提供的功能，還為創(chuàng)建相同的物化視圖指出了更輕易和更高效的途徑。有時，顧問程序可以實(shí)際推薦多個物化視圖，以使查詢更加高效。
您可能會問，假如任何一個經(jīng)驗(yàn)豐富的 DBA 都能夠找出 MV 創(chuàng)建腳本中缺了什么，然后自己糾正它，那這還有什么用？嗯，顧問程序正是用來完成這項(xiàng)工作的：它是一位經(jīng)驗(yàn)豐富、高度自覺的自動數(shù)據(jù)庫治理員，它可以生成能與人的建議相媲美的建議，但有一個非常重要的不同之處：它免費(fèi)工作，并且不會要求休假或加薪。這一好處使高級 DBA 解放出來，將日常的工作交給較低級的 DBA ，從而答應(yīng)他們將其專業(yè)技能應(yīng)用到更具有戰(zhàn)略意義的目標(biāo)上。 您還可以將顧問程序的名稱作為值傳遞給 TUNE_MVIEW 過程中的參數(shù)，這將使用該名稱而非系統(tǒng)生成的名稱生成一個的顧問程序。 更輕易的實(shí)施 既然您可以看到建議，那么您可能想實(shí)施它們。一種方式是選擇列 STATEMENT ，假脫機(jī)到一個文件，然后執(zhí)行該腳本文件。一種更輕易的替代方法是調(diào)用附帶的封裝過程： begin dbms_advisor.create_file ( dbms_advisor.get_task_script ('TASK_117'), 'MVTUNE_OUTDIR', 'mvtune_script.sql' ); end; / 該過程調(diào)用假定您已經(jīng)定義了一個目錄對象，例如： create Directory mvtune_outdir as '/home/oracle/mvtune_outdir'; 對 dbms_advisor 的調(diào)用將在 /home/oracle/mvtune_outdir 目錄中創(chuàng)建一個名為 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; / 這個文件包含了您實(shí)施建議所需的一切，從而為您省去了相當(dāng)大的手動創(chuàng)建文件的麻煩。這個自動數(shù)據(jù)庫治理員又一次能夠?yàn)槟瓿晒ぷ鳌?重寫或退出！ 至此，您一定意識到了查詢重寫特性有多重要和多有用。它顯著地減少了 I/O 和處理，并能夠更快地返回結(jié)果。 讓我們基于上述例子假定一種情況。用戶執(zhí)行以下查詢： 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; 執(zhí)行狀態(tài)顯示以下內(nèi)容： 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 — 一個非常低的值。這個結(jié)果基于的事實(shí)是，重寫了查詢來使用在三個表上創(chuàng)建的兩個物化視圖。選擇不是從表中進(jìn)行的，而是從物化視圖中進(jìn)行，從而消耗了更少的資源（如 I/O 和 CPU ）。 但假如查詢重寫失敗了，那該怎么辦？它失敗的原因可能有以下幾種：假如初始化參數(shù) query_rewrite_integrity 的值被設(shè)為 TRUSTED ，且 MV 的狀態(tài)是 STALE ，那么將不會重寫該查詢。您可以通過在查詢之前在會話中設(shè)定這個值來模擬這個過程。 alter session set query_rewrite_enabled = false;
在這條命令之后，說明計劃 (EXPLAIN PLAN) 顯示是從所有三個表中而不是從 MV 中作出的選擇。執(zhí)行狀態(tài)現(xiàn)在顯示： 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 。在實(shí)際情況下，這個結(jié)果可能無法接受，因?yàn)闊o法提供所需的額外資源，因此您可能想自己重寫查詢。在這種情況下，您可以確保假如而且只有在查詢被重寫的情況下，才答應(yīng)進(jìn)行查詢。 在 Oracle9 i 數(shù)據(jù)庫和更低版本中，決策是單向的：您可以禁用查詢重寫，但不能禁用基礎(chǔ)表訪問。不過 Oracle 數(shù)據(jù)庫 10 g 提供了一種機(jī)制 — 通過一個非凡的提示 REWRITE_OR_ERROR 來實(shí)現(xiàn)這一目的。上述查詢將利用該提示寫為： 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; 注重現(xiàn)在的錯誤消息。 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 ；因此，語句失敗。這種防出錯功能將潛在地防止運(yùn)行時間很長的查詢獨(dú)占系統(tǒng)資源。不過，請注重一個潛在的陷阱：假如 MV 之一（而不是全部）可用于重寫查詢，那么查詢將成功。因此假如能夠使用 MV_ACTUAL_SALES 但不能使用 MV_HOTEL_RESV ，那么查詢將被重寫，錯誤將不會出現(xiàn)。在這種情況下，執(zhí)行計劃將看起來像這樣： 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 表被訪問。這種方法（非凡是后一個表的全表掃描），無疑將使用更多的資源 — 在設(shè)計查詢和 MV 時您將注重到這種情況。
雖然您可以始終利用資源治理器來控制資源使用情況，但使用該提示將防止執(zhí)行查詢，即使在調(diào)用資源治理器之前。資源治理器根據(jù)優(yōu)化器統(tǒng)計數(shù)據(jù)估計所需的資源，因此是否存在足夠準(zhǔn)確的統(tǒng)計數(shù)據(jù)將影響這個過程。不過，重寫或錯誤特性將停止表訪問，而不管統(tǒng)計數(shù)據(jù)如何。 說明計劃更好地進(jìn)行說明 在上一個例子中，請注重說明計劃輸出中的行： MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_ACTUAL_SALES' (MAT_VIEW REWRITE) 這種訪問方法 — MAT_VIEW REWRITE — 是新增的；它顯示正在訪問 MV ，而非表或段。該過程立即告訴您表或 MV 是否被使用 — 即使名稱沒有表明段的本質(zhì)。 結(jié)論 在 10 g 中，通過引入強(qiáng)大的新調(diào)整顧問程序 — 它們能夠告訴您許多有關(guān) MV 的設(shè)計的信息，從而使您不再需要憑猜測進(jìn)行工作，治理 MV 變得更加輕易。我尤其喜歡能夠生成一個完整的腳本的調(diào)整建議，這種腳本可以快速實(shí)施，從而顯著地節(jié)省時間和精力。強(qiáng)制重寫或退出查詢的能力在決策支持系統(tǒng)中會非常有幫助 — 在這種系統(tǒng)中必須保留資源，并且未重寫的查詢將不答應(yīng)在數(shù)據(jù)庫內(nèi)隨意運(yùn)行。 有關(guān)在 10 g 中治理物化視圖的更多信息，請參考《 Oracle 數(shù)據(jù)庫數(shù)據(jù)倉庫指南 10 g 第 1 版 (10.1) 》中的 第 8 章 。
