最後我們介紹幾個可能影響到Optimizer的系統引數。

7、最佳化引數5——optimizer_index_caching

引數optimizer_index_caching的含義在於一個估算值，就是告訴最佳化器當進行索引路徑檢索的時候，特別是nested loop連線時索引探知（index probe）的成本。該引數的取值範圍是0-100，描述使用索引的時候，索引在buffer cache中的比例。這個引數的選取會影響到nested loop和in-list迴圈的成本估算值。

預設情況下，Oracle這個引數取值為0。

SQL> show parameter optimizer_index_cach

NAME TYPE VALUE

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

optimizer_index_caching integer 0

如果這個取值設定為100，就表示在使用索引路徑的時候，所檢索的索引塊在buffer cache中都已經快取住。注意，這個引數只是一個估計值，是我們進行控制成本公式使用的。真正在執行的時候，索引塊在buffer cache中的比例是不能預知的！！

顯而易見，在控制這個引數的前提下，Oracle在計算索引路徑執行計劃的時候，成本值就自然有所降低。進而，最後CBO選擇出的執行計劃就更加傾向於索引路徑和Nested Loop連線方式。

我們透過下面簡單的實驗去驗證。我們首先看看非連線條件下的索引路徑成本計算，選取上面引數實驗t_obj和t_tables作為實驗環境。

--補充索引構建

SQL> create index idx_t_obj_owner on t_obj(owner);

Index created

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

PL/SQL procedure successfully completed

分別在optimizer_index_caching兩個極端情況下進行試驗。

--在取值為0的時候進行試驗；

SQL> alter session set optimizer_index_caching=0;

Session altered

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

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 2947685431

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

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

| 0 | SELECT STATEMENT | | 25 | 2325 | 2

| 1 | TABLE ACCESS BY INDEX ROWID| T_OBJ | 25 | 2325 | 2

|* 2 | INDEX RANGE SCAN | IDX_T_OBJ_OWNER | 25 | | 1

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

Predicate Information (identified by operation id):

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

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

14 rows selected

--修改引數

SQL> alter session set optimizer_index_caching=100;

Session altered

SQL> select value from v$parameter where name='optimizer_index_caching';

VALUE

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

100

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

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 2947685431

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

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

| 0 | SELECT STATEMENT | | 25 | 2325 | 2

| 1 | TABLE ACCESS BY INDEX ROWID| T_OBJ | 25 | 2325 | 2

|* 2 | INDEX RANGE SCAN | IDX_T_OBJ_OWNER | 25 | | 1

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

Predicate Information (identified by operation id):

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

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

14 rows selected

雖然都是進行索引路徑，但是對最後成本值影響不大。究其原因，可能是筆者選擇的選擇率過小的原因。

下面實驗nested loop路徑成本情況。

SQL> alter session set optimizer_index_caching=0;

Session altered

SQL> explain plan for select /*+ use_nl(a, b)*/* from t_obj a, t_tables b where a.owner=b.owner and a.object_name=b.table_name;

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 2368859676

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

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

| 0 | SELECT STATEMENT | | 1606 | 467K| 3229 (1

| 1 | TABLE ACCESS BY INDEX ROWID| T_OBJ | 1 | 93 | 2 (0

| 2 | NESTED LOOPS | | 1606 | 467K| 3229 (1

| 3 | TABLE ACCESS FULL | T_TABLES | 1606 | 321K| 12 (0

|* 4 | INDEX RANGE SCAN | IDX_T_OBJ_CMP | 1 | | 1 (0

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

Predicate Information (identified by operation id):

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

4 - access("A"."OWNER"="B"."OWNER" AND "A"."OBJECT_NAME"="B"."TABLE_NAME")

16 rows selected

當進行強制的nested loop路徑選擇之後，我們在index_caching引數為0的時候，成本為3229，主要消耗在進行nested loop連線路徑上。那麼，當我們修改為100時，執行計劃如何變化呢？

SQL> alter session set optimizer_index_caching=100;

Session altered

SQL> explain plan for select /*+ use_nl(a, b)*/* from t_obj a, t_tables b where a.owner=b.owner and a.object_name=b.table_name;

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 709597304

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

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

| 0 | SELECT STATEMENT | | 1606 | 467K| 789

| 1 | TABLE ACCESS BY INDEX ROWID| T_TABLES | 1 | 205 | 1

| 2 | NESTED LOOPS | | 1606 | 467K| 789

| 3 | TABLE ACCESS FULL | T_OBJ | 50387 | 4576K| 157

|* 4 | INDEX RANGE SCAN | IDX_T_TABLES_CMP | 1 | | 0

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

Predicate Information (identified by operation id):

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

4 - access("A"."OWNER"="B"."OWNER" AND "A"."OBJECT_NAME"="B"."TABLE_NAME")

16 rows selected

效果非常明顯，成本值只有789。說明該引數在成本路徑計算中的作用和分量。但是，再次強調：引數只是控制和影響CBO進行決策的手段，它們只能影響到最後執行計劃的決策選擇，真正的執行時間還要看實際的環境和資料情況而定。

8、最佳化引數6——optimizer_index_cost_adj

該引數也是用來調整索引路徑成本的。簡單的說，optimizer_index_cost_adj引數就是索引路徑偏好的“縮放燈”。引數取值範圍為0-10000，預設為100。

SQL> show parameter optimizer_index_cost

NAME TYPE VALUE

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

optimizer_index_cost_adj integer 100

當Oracle依據統計量（資料表、索引物件）計算出index access路徑成本之後，會根據這個引數進行一定比例的放大和縮小。例如，如果該引數設定為10，表示會對索引路徑執行計劃的成本進行10%的縮小。

應用該引數，我們可以控制索引路徑的偏好度，放大或者縮小CBO成本計算公式中索引路徑成本的計算值。進而控制實際選擇出的執行計劃。

下面我們根據實驗來體會效果，我們同樣適用兩個SQL語句在三個引數取值下的情況進行實驗。

首先，我們選擇預設的optimizer_index_cost_adj取值下的情況。

SQL> select value from v$parameter where name='optimizer_index_cost_adj';

VALUE

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

100

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

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 172510092

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

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

| 0 | SELECT STATEMENT | | 22351 | 2029K| 156 (2)| 00:00:02 |

|* 1 | TABLE ACCESS FULL| T_OBJ | 22351 | 2029K| 156 (2)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

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

13 rows selected

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

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 2947685431

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

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

| 0 | SELECT STATEMENT | | 25 | 2325 | 2

| 1 | TABLE ACCESS BY INDEX ROWID| T_OBJ | 25 | 2325 | 2

|* 2 | INDEX RANGE SCAN | IDX_T_OBJ_OWNER | 25 | | 1

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

Predicate Information (identified by operation id):

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

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

14 rows selected

下面，我們縮小引數值到10，也就是縮小索引路徑成本到原來的10%。

SQL> alter session set optimizer_index_cost_adj=10;

Session altered

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

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 2947685431

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

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

| 0 | SELECT STATEMENT | | 22351 | 2029K| 65

| 1 | TABLE ACCESS BY INDEX ROWID| T_OBJ | 22351 | 2029K| 65

|* 2 | INDEX RANGE SCAN | IDX_T_OBJ_OWNER | 22351 | | 5

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

Predicate Information (identified by operation id):

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

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

14 rows selected

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

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 2947685431

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

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

| 0 | SELECT STATEMENT | | 25 | 2325 | 1

| 1 | TABLE ACCESS BY INDEX ROWID| T_OBJ | 25 | 2325 | 1

|* 2 | INDEX RANGE SCAN | IDX_T_OBJ_OWNER | 25 | | 1

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

Predicate Information (identified by operation id):

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

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

14 rows selected

我們看到了實驗現象：對owner=’SYS’而言，當不進行索引路徑成本縮放的時候索引路徑的成本是要高於全表掃描FTS的，所以CBO選擇了FTS作為路徑計算。但是，當我們將引數設定為10，也就是進行10%比例的縮放時，索引路徑的成本就下降到了65，小於FTS的156成本值。結果CBO在這種情況下選擇了索引路徑。

當owner=’SCOTT’語句中，成本也有一定程度的降低。因為取值本身就很小了，所以減小效果不明顯。

下面我們將引數調節到另一個極端，引數放大到10000。

SQL> alter session set optimizer_index_cost_adj=10000;

Session altered

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

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 172510092

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

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

| 0 | SELECT STATEMENT | | 22351 | 2029K| 156 (2)| 00:00:02 |

|* 1 | TABLE ACCESS FULL| T_OBJ | 22351 | 2029K| 156 (2)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

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

13 rows selected

--強制的索引路徑；

SQL> explain plan for select /*+ index(t_obj IDX_T_OBJ_OWNER)*/* from t_obj where wner='SYS';

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 2947685431

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

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

| 0 | SELECT STATEMENT | | 22351 | 2029K| 64906

| 1 | TABLE ACCESS BY INDEX ROWID| T_OBJ | 22351 | 2029K| 64906

|* 2 | INDEX RANGE SCAN | IDX_T_OBJ_OWNER | 22351 | | 5283

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

Predicate Information (identified by operation id):

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

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

14 rows selected

上面的實驗中，我們將索引路徑的成本放大了100倍。原有的owner=’SYS’語句重新選擇了FTS路徑。如果我們強迫它去走索引路徑，結果成本計算出的64906，恰恰是之前650成本值的近100倍。

那麼，owner=’SCOTT’結果情況如何呢？

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

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 172510092

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

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

| 0 | SELECT STATEMENT | | 25 | 2325 | 156 (2)| 00:00:02 |

|* 1 | TABLE ACCESS FULL| T_OBJ | 25 | 2325 | 156 (2)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

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

13 rows selected

owner=’SCOTT’選擇率極低，但是在放大100倍的效應下，CBO也放棄了索引路徑，選擇了FTS作為最終執行計劃。

9、結論

Oracle Optimizer本質上，就是一系列計算公式比較。我們提供資料物件的統計量，外加適當的引數作為引導，最後可以幫助CBO去生成更好的執行計劃。

聊聊Oracle Optimizer相關的幾個引數（下）

相關文章