我們繼續來介紹可以影響Oracle Optimizer工作的若干引數。

4、最佳化器引數2——cursor_sharing

Cursor_sharing是Oracle最佳化器的另一個重要引數，可能也是我們日常討論較多的一個引數。最開始的cursor_sharing提出，是建立在Oracle希望可以“一蹴而就”的解決hard parse，提高library cache中SQL執行計劃重用機率的“美好願望”下。在相當長的時間裡，很多DBA和開發人員都將這個引數視為“金手指”，認為只要開啟了這個引數，硬解析比例就會降低，效能瓶頸就會消除。

但是無論是從理論上，還是實際使用效果上，cursor_sharing都沒有實現當時的目標。

筆者在之前的Blog中，對cursor_sharing引數的含義、取值和效果乃至評價都有比較具體的闡述。有興趣的讀者可以參見下面列表系列：

《淺談cursor_sharing取值對SQL共享的影響》

http://space.itpub.net/17203031/viewspace-705196

http://space.itpub.net/17203031/viewspace-705289

SQL> show parameter cursor_shar

NAME TYPE VALUE

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

cursor_sharing string EXACT

簡單的說，cursor_sharing引數對使用繫結變數的SQL語句是沒有效果和影響的。如果該引數設定為非EXACT，那些沒有使用繫結變數的SQL語句的where後面取值都會被預設的替換，並且進行一定程度的執行計劃共享。

但是，無論是cursor_sharing取值為familiar還是force，都存在其在遊標共享問題的不合理性。特別是其對所有SQL執行計劃處理的“一刀切”策略，是很有問題的。

在使用過程中，cursor_sharing也有各種問題，所以很快的大家就放棄了這種引數最佳化的思路。轉回到書寫繫結變數和避免bind peeking等方案上來。

進入11g之後，Adaptive Cursor Sharing技術的提出，意味著Oracle重新思考解決繫結變數的bind peeking問題。Cursor_sharing有傳言在下一個版本中一些取值會去除。

筆者認為：cursor_sharing是一種不成熟的嘗試。在一些特定場合下，可能存在使用的價值。但是作為資料架構師和開發人員，還是老老實實的寫繫結變數穩妥。運維DBA在面對沒有繫結變數的系統時候，也要慎用該引數。

5、最佳化引數3——db_file_multiblock_read_count

這個引數的作用，同Oracle進行物理IO的方式有關。Oracle進行的IO分為Logical IO和Physical IO兩種。不嚴格的說：進行物理IO的次數比例越高，系統效能瓶頸問題出現的多。

但是進行IO也是有不同之處的。進行一次IO的資料塊讀取量不同，可能會影響到Optimizer特別是CBO的成本計算。

在Oracle進行FTS（Full Table Scan）和Index Fast-Full Scan的時候，進行讀取的資料塊都是連續方式訪問的。因為作為資料段Data Segment和索引段Index Segment，都是由一系列內部連續資料塊組成的分割槽extent序列構成的。在進行這樣的操作時，獲取的資料塊大都是相鄰的。

如果我們進行一次物理IO時候，能夠多獲取到一些資料塊，那麼進行物理IO的絕對次數就會變少。這樣相應的成本就會降低。

SQL> show parameter db_file_mu

NAME TYPE VALUE

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

db_file_multiblock_read_count integer 16

SQL> select value, display_value from v$parameter where name='db_file_multiblock_read_count';

VALUE DISPLAY_VA

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

16 16

預設情況下，筆者實驗環境上的該引數為16，表示一次進行讀取的時候最多讀取到16個資料塊。

我們首先回顧一下在預設情況下，進行FTS操作的成本計算。

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 | | 43272 | 3887K| 158 (3)| 00:00:02 |

|* 1 | TABLE ACCESS FULL| T | 43272 | 3887K| 158 (3)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

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

SQL> explain plan for select * from t where object_id between 10 and 50000;

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | 44865 | 4030K| 159 (4)| 00:00:02 |

|* 1 | TABLE ACCESS FULL| T | 44865 | 4030K| 159 (4)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

1 - filter("OBJECT_ID"<=50000 AND "OBJECT_ID">=10)

注意，兩個進行FTS的執行計劃，Oracle最佳化器“估算出”的成本值為158和159。如果我們調整db_file_multiblock_read_count引數，觀察一下兩個SQL的執行計劃變化。

SQL> alter session set db_file_multiblock_read_count=300;

Session altered

SQL> select value, display_value from v$parameter where name='db_file_multiblock_read_count';

VALUE DISPLAY_VA

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

128 128

我們嘗試將這個引數設定為300，但是最後還是調整為最大值128。這個引數雖然可以設定，但是最後還要受到伺服器體系結構和物理配置的影響。

此時兩個SQL的執行計劃如下。

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 | | 43300 | 3890K| 126 (4)| 00:00:02 |

|* 1 | TABLE ACCESS FULL| T | 43300 | 3890K| 126 (4)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

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

SQL> explain plan for select * from t where object_id between 10 and 50000;

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | 44849 | 4029K| 127 (4)| 00:00:02 |

|* 1 | TABLE ACCESS FULL| T | 44849 | 4029K| 127 (4)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

1 - filter("OBJECT_ID"<=50000 AND "OBJECT_ID">=10)

13 rows selected

當我們調整該引數之後，進行FTS操作計算出的成本變小。說明在計算執行計劃的過程中，如果db_file_multiblock_read_count引數設定變大，成本公式中成本值就會變小。

可以看出，該引數設定的較大，可以讓FTS操作成本估算的結果變小。進而，最終的生成執行計劃更傾向於FTS操作。

6、最佳化引數4——optimizer_mode

optimizer_mode也是Oracle早期使用的一個引數，主要是針對SQL執行計劃生成的目標導向。預設情況下，該引數取值為all_rows，表明執行計劃的生成是以最小成本消耗和吞吐量為標準。在all_rows的取值情況下，我們通常是對所有的結果集合進行一次性的處理操作。

與all_rows相對應的optimizer_mode引數是first_rows系列取值。該取值要求最佳化器以快速響應fast response作為執行計劃的生成依據。執行SQL的時候，Oracle會爭取將結果集合先返回給前端。

在實際使用中，單表操作情況下不同的optimizer_mode取值在執行計劃上沒有什麼差異。

我們首先準備實驗資料環境。

SQL> create table t_obj as select * from dba_objects;

Table created

SQL> create table t_tables as select * from dba_tables;

Table created

SQL> create index idx_t_obj_cmp on t_obj(owner,object_name);

Index created

SQL> create index idx_t_tables_cmp on t_tables(owner,table_name);

Index created

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

PL/SQL procedure successfully completed

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

PL/SQL procedure successfully completed

當進行單表操作的時候，first_rows與all_rows取值差異不是很大。

--設定optimizer_mode引數為all_rows

SQL> alter session set optimizer_mode='all_rows';

Session altered

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

VALUE

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

ALL_ROWS

SQL> explain plan for select * from t_obj;

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 172510092

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

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

| 0 | SELECT STATEMENT | | 50387 | 4576K| 157 (3)| 00:00:02 |

| 1 | TABLE ACCESS FULL| T_OBJ | 50387 | 4576K| 157 (3)| 00:00:02 |

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

8 rows selected

--設定引數取值為optimizer_mode為first_rows

SQL> alter session set optimizer_mode='first_rows';

Session altered

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

VALUE

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

FIRST_ROWS

SQL> explain plan for select * from t_obj;

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 172510092

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

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

| 0 | SELECT STATEMENT | | 50387 | 4576K| 157 (3)| 00:00:02 |

| 1 | TABLE ACCESS FULL| T_OBJ | 50387 | 4576K| 157 (3)| 00:00:02 |

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

8 rows selected

單表操作下，執行計劃是沒有什麼差異的。但是，在進行表連線操作的情況下，執行計劃有很大差異。

當引數為all_rows的時候，一般連線Oracle都儘量使用hash join連線方式，不使用index連線鍵。

SQL> alter session set optimizer_mode='all_rows';

Session altered

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

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 2309375240

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

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

| 0 | SELECT STATEMENT | | 1606 | 467K| 171 (4)| 00:00:03 |

|* 1 | HASH JOIN | | 1606 | 467K| 171 (4)| 00:00:03 |

| 2 | TABLE ACCESS FULL| T_TABLES | 1606 | 321K| 12 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| T_OBJ | 50387 | 4576K| 157 (3)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

1 - access("A"."OWNER"="B"."OWNER" AND

"A"."TABLE_NAME"="B"."OBJECT_NAME")

16 rows selected

雖然在連線鍵上有索引，Oracle使用hash join連線方式。Hash Join方式往往會忽視索引路徑。

如果我們將引數設定為first_rows，也就是儘量將先獲取的結果返回。Oracle會更加傾向與巢狀迴圈Nested Loop和索引連線Index Join。

SQL> alter session set optimizer_mode='first_rows';

Session altered

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

VALUE

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

FIRST_ROWS

SQL> explain plan for select * from t_tables a, t_obj b where a.owner=b.owner and a.table_name=b.object_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"."TABLE_NAME"="B"."OBJECT_NAME")

16 rows selected

根據原始的設計，all_rows取值適合於批作業、計算、選擇等常規業務場景。而first_rows主要適合在Web/Windows頁面分頁顯示場景。

在我們使用SQL的時候，預設還是將這個引數設定為all_rows，這樣適應性更好，執行計劃獲取更好。如果一些個別需要要求first_rows，可以使用hint進行特殊設定。

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

相關文章