Advisor
在g將查詢重寫並且引進了新的強大的調優建議者使管理物化視圖變得容易多了
物化視圖(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 rhotel_id = hhotel_id;
你如何知道使這個MV正常工作的所有必須對象都已經被創建呢？在g之前這一檢測是通過包DBMS_MVIEW的存儲過程EXPLAIN_MVIEW和EXPLAIN_REWRITE實現的這些存儲過程在g還存在它們的功能很簡單——檢測MV是否具備快速刷新能力和查詢重新能力但它們並不提供如何使這些能力有效的建議相反要求對於每個MV的結構都做檢查是不切實際的
在g中有一個新的包DBMS_ADVISOR它有一個存儲過程TUNE_MVIEW使這項工作變得非常容易你可以在調用這個包時輸入一個輸入參數參數內容為創建MV的整個腳本這個存儲過程創建了一個建議者任務(Advisor Task)它的名字會通常存儲過程唯一的輸出參數返回給用戶
這有一個例子由於第一個參數是一個輸出參數所以你必須定義一個變量
SQL> first define a variable to hold the OUT parameter
SQL> var adv_name varchar()
SQL> begin
dbms_advisortune_mview
(
:adv_name
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 rhotel_id = hhotel_id);
* end;
Now you can find out the name of the Advisor from the variable
SQL> print adv_name
ADV_NAME
TASK_
接下來
可以通過一個新視圖DBA_TUNE_MVIEW從Advisor那獲取到所提供的建議
在執行查詢前記得先執行設置SET LONG
因為這個視圖中的這個字段是一個CLOB類型
而默認知會顯示
個字符
SQL> select script_type
statement
from dba_tune_mview
where task_name =
TASK_
order by script_type
action_id;
CRIPT_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 C
ARUP
RESERVATIONS
RESV_ID
C
ARUP
HOTELS
CITY C
COUNT(*) M
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_advisorcreate_file (
dbms_advisorget_task_script (TASK_)
MVTUNE_OUTDIR
mvtune_scriptsql
);
end;
/
這一存儲過程是假定你已經定義了一個目錄對象的情況下調用的如
create directory mvtune_outdir as /home/oracle/mvtune_outdir;
調用包dbms_advisor的這個存儲過程會在目錄/home/oracle/mvtune_outdir中生成一個名叫mvtune_scriptsql的腳本文件如果查看文件它有如下內容
Rem SQL Access Advisor: Version Production
Rem
Rem Username: ARUP
Rem Task: TASK_
Rem Execution date:
Rem
set feedback
set linesize
set trimspool on
set tab off
set pagesize
whenever sqlerror CONTINUE
CREATE MATERIALIZED VIEW LOG ON
ARUPHOTELS
WITH ROWID SEQUENCE(HOTEL_IDCITY)
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON
ARUPHOTELS
ADD ROWID SEQUENCE(HOTEL_IDCITY)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON
ARUPRESERVATIONS
WITH ROWID SEQUENCE(RESV_IDHOTEL_IDCUST_NAME)
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON
ARUPRESERVATIONS
ADD ROWID SEQUENCE(RESV_IDHOTEL_IDCUST_NAME)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW ARUPMV_HOTEL_RESV
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT ARUPRESERVATIONSCUST_NAME C
RUPRESERVATIONSRESV_ID C ARUPHOTELSCITY
C COUNT(*) M FROM ARUPRESERVATIONS ARUPHOTELS WHERE
RUPHOTELSHOTEL_ID
= ARUPRESERVATIONSHOTEL_ID GROUP BY
RUPRESERVATIONSCUST_NAME ARUPRESERVATIONSRESV_ID
ARUPHOTELSCITY;
whenever sqlerror EXIT SQLSQLCODE
begin
dbms_advisormark_recommendation(TASK_IMPLEMENTED);
end;
/
這一文件包含了所有你需要實施的建議的內容而不需要你手工去創建一個腳本機器DBA又一次替你做了你需要做的工作
重寫還是報錯
現在你可能已經認識到了查詢重寫特性是多麼有用和重要它能大大降低I/O和處理過程返回結果更快
還是假定以上的例子用戶執行一個下面的查詢
SQL> Select city sum(actual_rate)
from hotels h reservations r trans t
where tresv_id = rresv_id
and hhotel_id = rhotel_id
group by city;
recursive calls
db block gets
consistent gets
physical reads
redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
請注意consistent gets的值是——一個非常低的值這一結果是基於這個查詢已經基於從張表創建的個視圖的查詢重寫不是從表查詢而是從MV查詢一次消耗了更少的如磁盤IO和CPU的資源
但是如果查詢重寫失敗了會怎麼樣呢？可能會以為幾個原因失敗如果初始化參數query_rewrite_integrity被設置為TRUSTED並且MV的狀態為STALE查詢就不會被重寫你可以通過設置會話的參數來模擬這一過程
SQL> alter session set query_rewrite_enabled = false;
執行這一命令後查詢計劃顯示是從張表查詢數據而不是從MV
recursive calls
db block gets
consistent gets
physical reads
redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
請注意consistent gets的值從上升到了在真實環境中這一結果恐怕是無法接受的因為多出來的資源請求可能無法獲得而你就必須自己重寫這一查詢了在那樣的情況下你就必須確保查詢一定能被重寫
在Oracle i和以下版本中可能只有一個方法實現使查詢重寫失效而不是使基礎表的訪問失敗在g中通過一個特殊的提示可以提供這樣的機制REWRITE_OR_ERROR上面這個查詢就可以這樣寫了
SQL> select /*+ REWRITE_OR_ERROR */ city sum(actual_rate)
from hotels h reservations r trans t
where tresv_id = rresv_id
and hhotel_id = rhotel_id
group by city;
from hotels h reservations r trans t
*
ERROR at line :
ORA: a query block in the statement did not rewrite
這樣就會產生一個ora的錯誤信息這個信息表示查詢不能通過使用MV來重寫因此語句失敗這一錯誤保護可以防止查詢長期運行後系統發生資源缺乏問題但是還要注意一個潛在問題如果一個查詢成功了而不是所有都成功了這些MV就能被用於查詢的重寫因此如果MV_ACTUAL_SALES而不是MV_HOTL_RESV能被使用查詢將會重寫錯誤也不會產生這種情況下查詢計劃就如以下
Execution Plan
SELECT STATEMENT Optimizer=ALL_ROWS (Cost= Card= Bytes=)
SORT (GROUP BY) (Cost= Card= Bytes=)
HASH JOIN (Cost= Card= Bytes=)
MERGE JOIN (Cost= Card= Bytes=)
TABLE ACCESS (BY INDEX ROWID) OF HOTELS (TABLE) (Cost= Card= Bytes=)
INDEX (FULL SCAN) OF PK_HOTELS (INDEX (UNIQUE)) (Cost= Card=)
SORT (JOIN) (Cost= Card= Bytes=)
TABLE ACCESS (FULL) OF RESERVATIONS (TABLE) (Cost= Card= Bytes=)
MAT_VIEW REWRITE ACCESS (FULL) OF MV_ACTUAL_SALES (MAT_VIEW REWRITE) (Cost= Card= Bytes=)
這一查詢使用了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
總結
在g中由於增加了新的Tuning Advisor它能向你提供很多關於MV設計方面的建議而無需通過猜測方式進行這使得管理MV容易多了我特別細化能將建議生成一個完整的腳本使實施更加迅速能節省很多時間這一特性強制重寫或取消查詢這一特性在決策支持系統中非常有用因為這樣的系統不允許一個沒有被重寫的查詢在數據庫內瘋狂執行
From:http://tw.wingwit.com/Article/program/Oracle/201311/17088.html
