（轉）Oracle為什麼不使用我的索引?

為什麼Oracle不使用我的索引?
by
標題的這個問題可能是在Metalink論壇與Usenet新聞組出現的最頻繁的問題了.這篇文章使用一個測試用例(可以在你自己的系統來重現的)來演示基於成本的最佳化器的基本工作原理.在看完這篇文章之後,當再次遇到這個令人討厭的問題時,你應該就可以自信的解答了.
由於在安裝Oracle的時候存在大量的選項,因此當某人執行一條你口授的指令碼時,通常很難精確的預測即將出現什麼結果. 當時我想要嘗試一下,希望你的資料庫選擇了一個相對普通的安裝選項,並且最常用的關鍵的引數是取得預設值. 這個例子是在Oracle 8.1.7下建立並測試的,引數db_block_size被設定成最常用的值(8k),引數db_file_multiblock_read_count也設定了一個很常用的值(8).在Oracle 9.2下跑圖-1中的這個指令碼(建立了一組表,在表上新增索引並分些表與索引),結果可能出現部分差異.

create table t1 as
select 	trunc((rownum-1)/15)	n1,  trunc((rownum-1)/15)	n2, rpad('x', 215)		v1
from all_objects
where rownum <= 3000;
create table t2 as
select
    mod(rownum,200) n1,
    mod(rownum,200) n2,
    rpad('x',215) v1
    from all_objects
    where rownum <= 3000;
create index t1_i1 on t1(N1);
create index t2_i1 on t2(n1);
analyze table t1 compute
statistics;
analyze table t2 compute statistics;

圖 1: 測試的資料集
在你準備好資料之後,你可能認為這兩組資料是一樣的,尤其是,在兩個資料集中欄位N1有的值範圍相同(從0-199),並且每個值都出現了15次.你可能會這樣檢查資料:

select n1, count(*)
from t1
group by n1;

查詢表T2也會證實你的上述觀點.
如果你接著執行下面的查詢語句:

select * from t1 where n1 = 45;
select * from t2 where n1 = 45;

你將發現每個查詢語句都返回了15條記錄.然而如果你執行

set autotrace traceonly explain

語句,你將發現這兩個查詢語句擁有兩種不同的執行路徑.針對表T1的查詢使用了索引,而針對表T2的查詢則做了一個全表掃描.
因此,在擁有完全相同的資料的情況下,同一個查詢語句戲劇性地出現了兩個不同的執行路徑.

索引到底怎麼了?

注意: 如果你曾經聽到如下的關於使用索引的”魔法”準則,例如,”在資料少於23%/10%/2%(隨機取一個數字)的時候,Oracle將使用索引,”那麼,此時你將懷疑他們的準確性.例如,在這個例子中,Oracle對於一個在3000記錄中取15條的查詢使用了全表掃描,僅僅0.5%的資料.
要調查諸如此例的問題,只有一個手段(我經常首先嚐試使用的):新增一些索引以使得Oracle選擇我們認為它應該選擇的執行路徑,並檢查這能否給我們部分提示.
在這個例子中,新增一個簡單的提示:
/*+ index(t2, t2_i1) */
就足夠讓Oracle從選擇全表掃描切換到選擇使用索引訪問.圖-2展示了這三種路徑的成本(簡化為C=nnn).

select * from t1 where n1 = 45;
EXECUTION PLAN
--------------
TABLE ACCESS BY INDEX ROWID OF T1 (C=2)
  INDEX(RANGE SCAN) OF T1_I1 (C=1)

select * from t2 where n1 = 45;
EXECUTION PLAN
--------------
TABLE ACCESS FULL OF T2 (C=15)

select /*+ index(t2 t2_i1) */
    *
from t1
where n1 = 45;
EXECUTION PLAN
--------------
TABLE ACCESS BY INDEX ROWID OF T2 (C=16)
  INDEX(RANGE SCAN) OF T2_I1 (C=1)

圖 2: 不同的查詢以及它們的成本
因此,為什麼在T2相關的查詢中Oracle沒有選擇索引作為預設的執行路徑?很簡單,正如執行計劃所展示,選擇執行全表掃描的成本要低於使用索引的成本.

為什麼使用全表掃描成本更低?

當然,這僅僅是在重複問題.為什麼全表掃描的成本會低於使用索引的成本呢?
透過深入調查這個問題,你將揭開了基於成本的最佳化器的關鍵機制(也是致命的錯誤假設).
讓我們透過執行下面這個查詢來開始我們的考查:

select
     table_name,
     blevel,
	   avg_data_blocks_per_key,
	   avg_leaf_blocks_per_key,
	   clustering_factor
from	user_indexes;

下表是對應的輸出結果:

請特別關注”data block per key”的值. 如果你執行一個完全基於這個索引的鍵值的等值校驗的查詢語句,這個值就是Oracle認為必須訪問的不同資料塊的數量.
因此,我們的查詢語句的成本是從哪兒來得呢?就Oracle而言,如果輸入的鍵值為45,當從表T1獲取資料時,我們就可以訪問一個索引葉子塊以及一個表塊(兩個塊),因此成本為2.
如果對錶T2執行同樣的查詢,我們就必須訪問一個索引葉子塊以及15個表塊(總共16個塊),因此成本為16.
很明顯,根據這種觀點,表T1的索引比表T2的索引更加理想.不過,這就帶來了兩個未解決的問題:
全表掃描的成本來自何處,為什麼兩張表之間的avg_data_blocks_per_key的數值差別如此大?
第二個問題的答案很簡單.回頭再看看錶T1的定義,它使用trunc()函式來生成N1的值,使用”rownum -1″除以15並截成整數.

     Trunc(675/15) = 45
     Trunc(676/15) = 45
           …
     Trunc(689/15) = 45

所有值為45的記錄實際上都是一條接著一條連續(可能所有的都能夠儲存到一個資料塊中)的出現的.
表T2使用mod()函式來生成N1的值,使用對rownum取200的模的方式.

      mod(45,200) = 45
      mod(245,200) = 45
            …
      mod(2845,200) = 45

值為45的記錄要隔200才會在表中出現一次(可能導致每一個相關資料塊中都不會超過1條記錄).
透過對錶的分析,Oracle可以得到我們表中的資料分佈的完美的描述.從而最佳化器才確切的明白,對於我們的查詢,Oracle將需要訪問多少個資料塊,在這個簡單的例子中,這個查詢的成本就是需要訪問的資料塊的數量.

但是為什麼是全表掃描呢?

我們看到,對於同樣的執行路徑,對錶T2進行索引訪問的成本要遠遠高於對錶T1的索引訪問成本,但是為什麼會選擇使用全表掃描呢?
這個問題將讓我們發現Oracle做的兩個過於簡單甚至不恰當的假設.
第一個假設是,每個塊訪問都視為一次物理磁碟讀,第二個假設是,多塊讀的速度與單塊讀一樣.
因此,這些假設將對我們的這個實驗產生什麼影響?
如果使用下面的查詢語句查詢user_tables表:

select
      table_name,
      blocks
from user_tables;

你將發現這兩張表每個都是佔用96個資料塊.
在文章開頭,我說過這個測試用例執行在一個db_file_multiblock_read_count的值為8的版本為8的Oracle系統系統上.
粗略地講,Oracle認為它可以透過12(96/8)次磁碟讀請求來讀出所有的96個資料塊.
由於透過索引訪問表需要16個塊(等於物理讀)請求, 從Oracle的可悲的受騙的視角看的話,選擇全表掃描顯得更清晰也更快捷.
瞧!如果你要訪問的資料適當地散佈在表上,即使只有很小比例的資料也會選擇使用全表掃描,在資料塊非常多(也就是表很大)而返回的記錄數很少的時候,這個問題還會被放大.

校正

實際上,可能你已經發現,我計算出來的掃描讀次數為12,而執行計劃中報告的成本是15.一種輕微的簡化版本認為表掃描(或者索引快速全掃描)的成本為
‘number of blocks’ /
db_file_multiblock_read_count.
Oracle使用一種”調整後”的多塊讀的值來做這種計算(然而,在掃描開始之後,它仍然盡力使用這個真實的請求值來掃描).
為了方面查詢,下表對比了幾個真實值與調整後的值:

你還將發現,當你為這個引數提供一個不切實際的很大的值後,Oracle可以為你提供保護來避免發生錯誤.
順便提一下,Oracle 9中有一點小小的改變,表掃描的成本還會做進一步的調整(對相除之後的結果加1),這意味著Oracle 9中的表掃描的成本會比Oracle 8中大一點點,從而索引會變得更可能被使用一點點.

修正

我們已經看到,最佳化器有兩個內建的假設,而這兩個假設又不是很合理.

• 一次單塊讀的成本與一次多塊讀的成本一樣-(實際上不大可能,特別是執行在沒有順序的檔案系統上的時候)
• 一次塊訪問就是一次物理磁碟讀-(那麼Buffer Cache是幹什麼吃的?)

從Oracle 8.1剛釋出開始,就有多個引數可供我們以一種相當切合實際的方式來修正這些假設.
Tim Gorman的文章為這些引數提供了一個切實的描述,下面是簡單的描述:

• Optimizer_index_cost_adj的值範圍為1到10000,預設為100.實際上,這個引數描述的是,相對於一次多塊讀來講,一次單塊讀有多麼便宜.如果它的值為30,也就是告訴Oracle一次單塊讀的成本是一次多塊讀的成本的30%. 從而Oracle就會因此這個引數的值很小而更多的傾向於選擇使用索引訪問.
• Optimizer_index_caching的值範圍為0到100,預設為0.這個引數告訴Oracle,假定索引塊將在Buffer Cache中存在百分比.在這個例子中,將這個值設定為接近100的值將助長使用索引而不是表掃描.

關於這些引數的真正美好的事情是,可以將他們設定成”符合實際”的值.
將optimizer_index_caching設定成”buffer cache hit ratio”範圍內的一個值(你需要自己決定具體是按照default pool,keep pool還是這兩個的某種組合來得到這個數值).
Optimizer_index_cost_adj的值的設定要更加複雜一點.檢查v$system_event檢視中等待事件”db file scattered read”(多塊讀取)與”db file sequential read”(單塊讀取)的有代表性的等待時間.用後者(單塊讀的等待時間)除以前者(多塊讀的等待時間)並乘以100.

改進

不要忘了,這兩個引數可能需要一天(周)的不同時段進行調整以反映終端使用者的工作負載.僅僅取得一組數字,就一直使用下去,是不可行的.
很高興,在Oracle 9中,情況得到了改善.你可以收集系統統計資訊,通常就包含以下四個統計資料:

• 單塊讀的平均讀取時間
• 多塊讀的平均讀取時間
• 實際發生的多塊讀的平均讀取塊數
• CPU的理論可用速度

要詳細介紹這個特性足夠配得上一整篇文章,但是這裡我特別強調一點,前三個統計值使得Oracle可以明白對多塊讀的真實成本(相對單塊讀來講).實際上,CPU速度使得Oracle可以得出不適宜的訪問機制的CPU成本,比如,讀取一個資料塊中的每條記錄以找出特定的資料值,以及與此相似的行為.
當你將系統升級到Oracle 9時,你首先需要檢查的事情就是是否正確使用系統統計資訊.單單這個特性就可能大大降低你嘗試”最佳化”的糟糕的SQL的時間.
順便提一下,儘管系統統計資訊帶來了驚人的效果,這兩個最佳化器調整引數仍然有效,雖然使用它們的確切地公式在Oracle 8與Oracle 9之間發生了變化.

主題的變種

當然,我選擇了一個非常特殊的例子,一個單列非唯一索引上的等值查詢,並且表中沒有空值,這種情況非常容易處理.(我甚至都沒有提及索引的blevel與clustering_factor.)Oracle還有多個不同的方法來處理更加一般的例子.
考慮如下這些我為了方便而忽視的情況:

• 多列索引
• 使用多列索引中的部分列
• 範圍掃描
• 唯一索引
• 由非唯一索引代表的唯一約束
• 索引跳躍掃描
• 只查詢索引的語句
• 點陣圖索引
• 空值的影響

這個列表還可以不停地列下去.並沒有一個簡單的公式來告訴你Oracle是如何計算它的成本,只存在一個通用準則,透過它你可以瞭解這個方法的梗概,以及一組可以應用到不同情形下的不同計算公式.
不管怎樣,本文的目的是讓你知道有這個通用準則,以及最佳化器策略中內嵌的兩個基本假設.我希望,這篇文章可以幫助你更加深入的理解那些最佳化器做出來的眾所周知的怪事.