在前面的文章中我討論到存儲概要並且描述了一種通過濫用系統來生成你所需要存儲概要的方法我同時也指出在Oracle 中使用這種方法存在一些風險因為存儲在數據庫中的細節信息已經變得非常復雜在接下來的文章中我將介紹一種合法的操作存儲概要的方法這種方法可以應用在Oracle 與Oracle 中這篇文章的細節都是基於實驗得出的實驗環境是Oracle 與Oracle 的默認安裝環境
回顧
當你知道如何通過給一段DML語句添加提示就可以讓它運行的快很多但是你卻沒有訪問源代碼並將提示放到適當位置的途徑 你會怎麼做?
在上一篇文章中我展示了你可以如何用存儲概要(也被稱為執行計劃穩定性)來驅使數據庫引擎為你做這種工作
一個存儲概要由兩個組件組成(寬泛地講)一個你希望控制的SQL語句一組每當Oracle發現這條SQL被優化都將在它上面應用的提示這兩個組件都被保存在一個被稱為outln的數據庫schema中
我們可以使用一組如圖中類似的查詢語句來檢查保存在其中的SQL語句以及附著在這條SQL語句上的提示
select name used sql_text
from user_outlines
where category = DEFAULT
;
select stage node hint
from user_outline_hints
where name = {one of the names}
;
Figure Examining stored outlines
在前面的文章中我介紹了這樣一種想法來欺騙系統 使用合法的方法創建一個存儲概要 接著使用一個文本相似的但已經添加過提示的語句來創建一個存儲概要最後使用一組SQL語句來交換這兩個存儲概要的實際結果來修復存儲概要
當時我曾提到這種方法對Oracle 來講或許是安全的但是由於在新版本中引入的變化 在Oracle 中可能會導致問題
這篇文章將對這些變化進行考查 介紹一種合法的方法來得到你想要的一組存儲到outln中的提示用來解決你的那些問題語句
相關變化
如果你登錄到outln schema(在Oracle 中它默認是鎖住的)查看可用的表清單你將發現Oracle 比Oracle 多出來一張表 這些表為:
ol$ SQL語句
ol$hints 提示表
ol$nodes 查詢塊
第三張表是一張新表被用來將提示列表與這條SQL語句(一份內部重寫的版本)的多個不同查詢塊你還將發現提示列表(ol$hints)也被加強了其中還包括文本長度與偏移量的細節信息
圖為這三張表的詳細描述用星號標注了Oracle 中出現的新字段
ol$
OL_NAME VARCHAR()
SQL_TEXT LONG
TEXTLEN NUMBER
SIGNATURE RAW()
HASH_VALUE NUMBER
HASH_VALUE NUMBER ***
CATEGORY VARCHAR()
VERSION VARCHAR()
CREATOR VARCHAR()
TIMESTAMP DATE
FLAGS NUMBER
HINTCOUNT NUMBER
SPARE NUMBER ***
SPARE VARCHAR() ***
Ol$hints
OL_NAME VARCHAR()
HINT# NUMBER
CATEGORY VARCHAR()
HINT_TYPE NUMBER
HINT_TEXT VARCHAR()
STAGE# NUMBER
NODE# NUMBER
TABLE_NAME VARCHAR()
TABLE_TIN NUMBER
TABLE_POS NUMBER
REF_ID NUMBER ***
USER_TABLE_NAME VARCHAR() ***
COST FLOAT() ***
CARDINALITY FLOAT() ***
BYTES FLOAT() ***
HINT_TEXTOFF NUMBER ***
HINT_TEXTLEN NUMBER ***
JOIN_PRED VARCHAR() ***
SPARE NUMBER ***
SPARE NUMBER ***
ol$nodes (completely new in )
OL_NAME VARCHAR()
CATEGORY VARCHAR()
NODE_ID NUMBER
PARENT_ID NUMBER
NODE_TYPE NUMBER
NODE_TEXTLEN NUMBER
NODE_TEXTOFF NUMBER
Figure The outln tables
你可能很快會注意到多處細節有大量信息被基於這些表的視圖排除在外了視圖user_outline_hints的視圖定義完全沒有改變盡管表ol$hints上新增加了個字段實際上這個視圖在Oracle 的時候就極度不足因為它遺漏了相當有用的hint#字段
你還會注意到Oracle 現在有兩個hash_value字段如果你在Oracle 與Oracle 中對同樣的SQL語句創建存儲概要你將發現它們擁有同樣的hash_value但是Oracle 中對應的hash_value可能完全不同
你可以也會發現Oracle 中的signature(簽名)字段的值與Oracle 中的值是不同的 這是由於Oracle這兩個版本之間策略上的最主要的調整就是為了提高存儲概要的重復利用在Oracle 中只有在你的SQL語句與存儲的SQL語句完全匹配(包含空格符/大小寫以及換行符)的時候才可以使用到Oracle 之後這個限制放寬了只要在去除掉重復的空字符並且將文本都轉換成同樣的大小寫之後SQL語句能夠匹配就可以使用存儲概要了例如下面的兩條SQL語句將使用同一個存儲概要
select * from t where id = ;
SELECT *
FROM T
WHERE ID = ;
策略上的這個調整導致了第一次創建這個執行計劃的SQL語句的簽名的調整;如果你的數據庫從Oracle 升級到Oracle 就必須更新存儲概要或者必須確認它們不再被使用(事實上別名為dbms_outln包outln_pkg包含一個特別的存儲過程update_signatures來處理這個問題
不過關於Oracle 中這些表的最意義重大的事情卻是對查詢語句中涉及到的文本與對象的極度詳細描述創建圖中顯示的例子並在繼續閱讀之前詳細查看ol$hints表中的內容
drop table t;
create table t
nologging
as
select
rownum id
rownum n
object_name
rpad(x) padding
from
all_objects
where
rownum <=
;
alter table t
add constraint t_pk primary key (id);
create index t_i on t(n);
analyze table t compute statistics;
create or replace outline demo_ on
select * from t
where id =
and n =
;
Figure Sample code
這個例子立足於一個簡單的小表包含兩組相近的列其中一個列為逐漸(從而也創建了索引)另外包含一個簡單的非唯一索引我們為一個典型的查詢創建一個存儲概要來查看我們可以如何對待它
如果針對由這個例子創建的存儲概要demo_運行圖中的示例查詢我們將發現這個查詢將附帶個提示
STAGE NODE HINT
NO_EXPAND
ORDERED
NO_FACT(T)
INDEX(T T_PK)
NOREWRITE
NOREWRITE
不出意外其中的第四行顯示我們將使用主鍵索引來訪問這張表如果我們實際上想要Oracle使用這個非唯一索引T_I訪問表我們該對存儲概要做什麼呢?理論上講我們可以調整這個存儲概要以使得
INDEX(T T_PK)
變成
INDEX(T T_I)
新特性
我們可以做的第一件事是查看包dbms_outln_edit這個包在Oracle 中引入正如它的名字提示的那樣它的目標是編輯存儲概要這看上去令人充滿希望
然而查看包的方法列表檢查文檔手冊我們發現這個包只包含如下幾個編輯相關的方法
CREATE_EDIT_TABLES
DROP_EDIT_TABLES
CHANGE_JOIN_POS
前兩個方法允許我們創建或刪除outln用戶擁有的表的本地拷貝第三個方法允許我們交換一個存儲概要計劃中的表連接順序 哪怕僅僅是幫助我們修改一個簡單的提示的方法也是沒有的目前這個包看上去實際上一無是處但是它們注定會越來越完善
當然B方案就是去侵入它了!如果我們登錄到outln用戶並自己診察ol$hints表(也就是支撐視圖user_outline_hints的表)的內容我們可以嘗試下面的這個更新操作:
update ol$hints
set
hint_text = INDEX(T T_I)
where
ol_name = demo_
and hint# =
;
登錄回到我們的測試Schema清空共享池並且打開存儲概要:
connect test_user/test
alter system flush shared_pool;
alter session set use_stored_outlines = true;
實際上我們將發現侵入的存儲概要確實如你所願了但是這是一個讓人不爽的解決方案
因為我們一直會給一個關於更改數據字典表的嚴厲的警告
舊方法()
接著我們的目標就是尋找一種迂回但又看似無害的方法來改變存儲概要表的內容並且不需要直接的侵入存儲概要表
從前(在Oracle 以前)我們有多種實現辦法它們都是基於這樣一個事實存儲概要的效果僅僅取決於進來的SQL語句的文本而完全不關心對對象類型或者對象的所有者
將表替換成添加過提示的視圖是一種有效的方法(我相信這種方法最初是由Tom Kyte在它的《Expert One on One: Oracle》這本書中介紹的)
連接到另外一個擁有表T的訪問權限的Schema按照下面的定義創建一個添加過提示的視圖視圖與表的名稱保持一致
Create or replace view t as
Select /*+ index(tt_i) */
*
from test_usert;
一旦視圖創建完成就在這個schema下使用下面的這個命令重編譯這個已存在的存儲概要
alter outline demo_ rebuild;
注意:必須擁有權限alter any outline才可以執行這個命令
如果登錄回到原來的schema清空緩存(flush shared pool)並且啟用存儲概要我們將會發現原來的查詢語句現在如願以償的使用上了索引T_I
INDEX(T T_I)
這樣為什麼可行?因為存儲概要並不屬於任何一個schema 當我們在另外一個schema中重編譯這個稱為demo_的存儲概要的時候名稱T應用到了一個本地的包含提示的視圖上了因此Oracle將這個提示包裝進了真實的執行計劃中從而也進入了這個存儲概要通過查看視圖user_outline_hints將會發現關鍵的那一行已經變成了
INDEX(T T_I)
很不幸我們還將注意到它現在包含行如下形式的提示:
NOREWRITE
NOREWRITE
NOREWRITE
而原來我們只有兩行:
NOREWRITE
NOREWRITE
我們引入了一個新的提示也就是Stage Node 我不敢說我確切的知道這是什麼意思但是它一定與這樣一個事實有關為了在另外一個Schema解析優化這個查詢Oracle執行了一個額外的步驟來將視圖引用轉換成基礎表的引用
雖然目前這不會導致存儲概要無法正確使用(或者如同它在這個簡單的例子中這樣)誰又能說Oracle在將來的版本又會有多挑剔呢
舊方法()
因為視圖引入了一個可能在將來版本變成錯誤的異常我們不得不更加挑剔 讓我們試試下面的這種方法:
Create a new schema
Create table T in that schema
Create ONLY the index T_I
Rebuild the outline in that schema
如果比較存儲概要重建前後user_outline_hints的詳細內容(必須重新登錄到原來的Schema來做這件事)我們將發現除了我們想要改變的那一行它們是完全一樣的重新登錄回原來的Schema通過清空共享池以及打開存儲概要做一個常規檢查我們將會發現修改後的存儲概要已經被使用了
然而還有一個潛在的威脅不過這一次更加隱蔽再回去看圖中出現在Oracle 中的新字段的定義你認為字段user_table_name中保存的值將會是什麼啊?它應該是有限制的表名稱例如:
{User_name}{table_name}
在我們的例子中這將告訴Oracle表T實際上是一個屬於新的Schema的表而不是原來的Schema下面的表即使Oracle確實在使用這個存儲概要這個表裡的信息也充分說明Oracle是在錯誤的對象上面應用這個存儲概要
另外它現在現在有效但是為什麼有這個信息在這兒呢可能是為了將來的版本增強做准備呢
可靠的賭注
看來要生成存儲概要而又不面臨將來的風險就只有一種方法了那就是盡可能的真實
在這個示例中你需要刪除主鍵索引生成執行計劃然後替換掉主鍵
當然你可能不想在生產環境做這件事即使你在生產環境做了存儲概要也有可能選擇走全表掃描(而不是走你想要的那個索引)
底線是你必須至少在另一個數據庫中有一個這個Schema的空閒拷貝接著需要非常小心的操作這個拷貝以得到需要的存儲概要一旦得到這個存儲概要你就可以從一個數據庫導出它並將其導入另外一個數據庫
例如:在這個空閒的數據庫上刪除主鍵以避免PK唯一掃描就是可行的如果Oracle並沒有自動的采用另外一個索引你可以對系統說各種謊言諸如:
將optimizer_mode改成first_rows_
構造數據使得列N上的數據是唯一的(不過不要將其改成唯一索引這樣生成出來的存儲概要將是unique scan而不是range scan了)
使用dbms_stats來使這個索引獲得一個難以置信的clustering_factor
調整參數optimiser_index_caching來告訴系統這個索引已經完全被緩存
調整optimiser_index_cost_adj來告訴系統多塊讀要比單塊讀要慢倍
使用dbms_stats修改aux_stats$表來達到上一條同樣的宣稱效果並且添加這樣一個事實一次多塊讀的典型大小為個塊
重建這個索引以包含where從句中的所有字段
給定存儲概要表中的內容假使表的所有者不變對象類型不變以及不改變索引的唯一度幾乎任何事情都可以做 如果你可以構造一個數據集與環境來生成一份與生產系統沒有內部不一致的存儲概要那麼你就可以以任何方式來欺騙系統
結論
相對於Oracle 來講在Oracle 中進入存儲概要的信息變更更加精細了之前可以非常容易也很明顯無風險的調整存儲概要的方法現在還仍然可以工作但是Oracle 中收集的巨量的附加信息表明之前的那種方法現在可能會給將來留下隱患
雖然Oracle 中引入了一個編輯存儲概要的包但它當前還只是局限在交換表的連接順序除了使用第二套系統來調整索引(通過改變環境參數以及人造的統計信息)外 看似不存在安全的干預存儲概要的方法
From:http://tw.wingwit.com/Article/program/Oracle/201311/17707.html
