索引是我們進行最佳化的一種重要方式。實際工作中，一個簡單的索引，可能就會大大提升提高關鍵業務作業效率，最終提升使用者滿意度。在CBO時代，DBA和開發人員經常為索引為什麼不出現在執行計劃中而困惑。

問題提出

進入CBO時代之後，Oracle最佳化器變得越來越智慧、靈活，生成的SQL執行計劃越來越高效。這一切都是依賴兩個因素：最佳化器引數公式和強大的統計量收集能力。其中，統計量是我們最經常打交道的部分。

所謂統計量，就是針對當前的資料庫物件實際情況，採用一定的抽樣比例，應用一定的統計方法，生成資料的描述資訊。並且最終將其記錄在資料字典中，供下一次生成執行計劃是呼叫使用。

越精確的統計量，也就意味越優質高效的SQL執行計劃。反過來說，過時、錯誤的統計量，也可能會生成錯誤的執行計劃。下面我們來看這個實驗。

環境準備

先準備資料表T和對應索引。

SQL> create table t as select * from dba_objects;

Table created

SQL> create index idx_t_owner on t(owner);

Index created

//收集統計量

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

正常實驗

此時，我們透過dbms_stats包進行統計量收集。此時，可以說資料表T相關物件的資料字典上統計資訊是正確的。

SQL> explain plan for select * from t where wner='SYS';

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 43153 | 3877K| 160 (3)| 00:00:02 |

|* 1 | TABLE ACCESS FULL| T | 43153 | 3877K| 160 (3)| 00:00:02 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OWNER"='SYS')

13 rows selected

SQL> rollback;

Rollback complete

SQL> alter system flush shared_pool;

System altered

SQL> explain plan for select * from t where wner='SCOTT';

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1516787156

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 54 | 4968 | 2 (0)|

| 1 | TABLE ACCESS BY INDEX ROWID| T | 54 | 4968 | 2 (0)|

|* 2 | INDEX RANGE SCAN | IDX_T_OWNER | 54 | | 1 (0)|

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OWNER"='SCOTT')

14 rows selected

上述實驗證明了兩個有代表性的場景。Owner=’SYS’取值較多、偏移度高，這樣進行全表掃描的成本較低。而owner=’SCOTT’的選擇性好，進行索引路徑成本較低。

統計量失效實驗

我們使用dbms_stats包進行統計量收集。如果我們此時修改更新資料，改變了資料原有的分佈結構和狀態，而不去進行手工的統計量更新。那麼，生成的執行計劃是怎麼樣呢？

//清理shared_pool，避免執行計劃重用；

SQL> alter system flush shared_pool;

System altered

SQL> update t set wner='SCOTT' where wner='SYS';

43037 rows updated

SQL> commit;

Commit complete

此時，owner=’SCOTT’，佔到了超過原有’SYS’的比例。如果正確的執行計劃，應該是進行全表掃描。事實又是如何呢？

SQL> explain plan for select * from t where wner='SCOTT';

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1516787156

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 54 | 4968 | 2 (0)|

| 1 | TABLE ACCESS BY INDEX ROWID| T | 54 | 4968 | 2 (0)|

|* 2 | INDEX RANGE SCAN | IDX_T_OWNER | 54 | | 1 (0)|

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OWNER"='SCOTT')

14 rows selected

在沒有重新收集統計量，使用原有舊的統計量的情況下，Oracle生成了索引路徑執行計劃。當資料發生大面積的更新，修改了原有的資料分佈，統計量是會與實際資料分佈不一致，從而引起執行計劃的生成錯誤。

那麼，修復的方法也很簡單，我們重新收集一下統計量就可以了。

//手工的收集統計量

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

SQL> alter system flush shared_pool;

System altered

SQL> explain plan for select * from t where wner='SCOTT';

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 43405 | 3984K| 160 (3)| 00:00:02 |

|* 1 | TABLE ACCESS FULL| T | 43405 | 3984K| 160 (3)| 00:00:02 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OWNER"='SCOTT')

13 rows selected

此時，我們已經獲取到了本篇的結論：當統計量和實際資料情況不一致，CBO生成的執行計劃的確會有問題，生成的路徑的確可能不是最優的。

進一步的思考

我們瞭解了結構，那麼如何避免這種情況發生呢？

回答這個問題，首先我們需要從Oracle統計量收集的方式談起。在RBO時代，最佳化器工作是不需要統計量的。所以雖然比較教條，但也是不需要擔心統計量過時的問題。在CBO時代，我們不得不考慮統計量收集的方式。

在Oracle10g開始，Oracle開始提供自動化統計量收集的作業。當使用預設安裝方式之後，每天晚上10點到凌晨，Oracle都會執行作業，來進行統計量收集。此時收集的範圍定義為發生資料變數達到一定比例的資料表。Oracle希望使用這個機制來保證儘可能有更新的統計量使用。

這種作業機制可以實現自動的統計量收集，但是也會給其他方面帶來困擾。最大的一方面就是影響到系統夜間作業的執行。我們的應用系統中經常會有一些如大運算、報表、資料彙集等大作業。為了避免和白天的業務視窗時間衝突，我們常常將它們排在夜間完成，沒想到會與Oracle統計量自動收集衝突。這也就是為什麼有DBA抱怨這些大作業夜間做的速度甚至沒有白天快的原因。

上述問題的解決其實也比較容易，就是禁止夜間進行統計量收集（具體方法請查閱網路資源）。手工組織統計量作業排程計劃。這樣可以靈活的避免負載衝突的情況。

下面說說發生統計量與實際資料分佈不一致出現的場景，從網路上各同行反映的資訊看，大都有如下情況：

ü 沒有定期收集統計量的方案，使得統計量過舊；

ü 系統升級移植，因為版本不一致使得匯入的對應統計量錯誤；

ü 陰差陽錯，在夜間剛剛收集完統計量之後呼叫大作業，匯入海量資料直接改變了原有的資料分佈情況。第二天直接生成錯誤的執行計劃；

解決的方法相對容易，發現統計量錯誤的物件，手工收集下就可以了。

最後，筆者想談一下統計量收集的頻度問題。一個資料表，是不是每次都需要收集統計量？是不是隻要發生變化就要收集統計量？筆者認為這不是絕對的。

一張資料表，如果資料分佈是有規律的、取值是有範圍的，那麼一份統計量完全可以解決所有的問題，不需要每時每刻進行收集。

CBO是一次時代的飛躍，但是伴隨飛躍必然存在新問題的引入。意識到問題，正視問題和解決問題，是我們正確對待統計量過時問題的根本出發點。

索引失效系列——統計量過期引起執行計劃錯誤

相關文章