淺談optimizer_mode優化器模式

Oracle query optimizer（查詢優化器）是我們接觸最多的一個資料庫元件。查詢優化器最主要的工作就是接受輸入的SQL以及各種環境引數、配置引數，生成合適的SQL執行計劃（Execution Plan）。

Query Optimizer一共經歷了兩個歷史階段：RBO和CBO。RBO時代，Oracle執行計劃是通過一系列固化的規則進行執行計劃生成。而CBO時代，則是利用系統統計量進行各種執行路徑試算，獲取相對相對成本最低的執行計劃。

進入Oracle 10g之後，Query Optimizer就已經將CBO作為預設優化器，並且Oracle官方不再支援RBO服務。但是，通過優化器引數optimizer_mode，我們可以控制Oracle優化器生成不同模式下的執行計劃。本篇通過實驗，來驗證Oracle 10g下，不同optimizer_mode的取值效應，以及和統計量關係。

1、實驗環境準備

我們選擇Oracle 10gR2作為基礎實驗環境。

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 – Production

在Oracle 10gR2下，引數optimizer_mode取值為ALL_ROWS。

SQL> show parameter optimizer_mode

NAME TYPE VALUE

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

optimizer_mode string ALL_ROWS

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

NAME VALUE

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

optimizer_mode ALL_ROWS

資料表構建如下：

SQL> create table t as select * from dba_objects;

Table created

SQL> create index idx_t_owner on t(owner);

Index created

2、ALL_ROWS——CBO優化器應用

進入Oracle 10g之後，ALL_ROWS成為Optimizer_Mode的預設引數取值。ALL_ROWS模式下，查詢優化器就會採用完全的CBO機制，藉助資料表、索引的統計量，可以最大限度的獲取到成本最低的執行計劃。ALL_ROWS模式的優化器目標就是生成成本cost最低的執行計劃。

當資料表上沒有統計量資訊是，如果優化器模式選擇ALL_ROWS，Oracle如何工作呢？

//刻意清除掉統計量；

SQL> exec dbms_stats.delete_table_stats(user,'T',cascade_parts => true,cascade_columns => true);

PL/SQL procedure successfully completed

之後生成執行計劃：

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

Explained

SQL> select * from table(dbms_xplan.display(null,null,'advanced'));

PLAN_TABLE_OUTPUT

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

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | 34611 | 5982K| 157 (3)| 00:00:02 |

|* 1 | TABLE ACCESS FULL| T | 34611 | 5982K| 157 (3)| 00:00:02 |

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

Outline Data

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

（篇幅原因，有省略。。。。。。）

/*+

BEGIN_OUTLINE_DATA

FULL(@"SEL$1" "T"@"SEL$1")

OUTLINE_LEAF(@"SEL$1")

ALL_ROWS

OPTIMIZER_FEATURES_ENABLE('10.2.0.1')

IGNORE_OPTIM_EMBEDDED_HINTS

END_OUTLINE_DATA

*/

Note

-----

- dynamic sampling used for this statement

46 rows selected

在沒有統計量的情況下，Oracle還是生成了ALL_ROWS模式下的執行計劃。值得關注的是結尾處的“- dynamic sampling used for this statement”。此時，Oracle優化器應用了動態統計量收集技術（Dynamic Sampling）。

動態統計量收集是Oracle CBO優化器的一種特性。優化器生成執行計劃是依據成本cost公式計算出的，如果相關資料表沒有收集過統計量，又要使用CBO的機制，就會引起動態取樣。

動態取樣（dynamic sampling）就是在生成執行計劃是，以一個很小的採用率現進行統計量收集。由於取樣率低，取樣過程快但是不精確，而且取樣結果不會進入到資料字典中。

如果我們進行統計量收集之後，ALL_ROWS就可以生成標準的CBO執行計劃。

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

PL/SQL procedure successfully completed

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

Explained

SQL> select * from table(dbms_xplan.display(null,null,'advanced'));

PLAN_TABLE_OUTPUT

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

Plan hash value: 1516787156

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

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

| 0 | SELECT STATEMENT | | 34 | 3128 | 2 (0)|

| 1 | TABLE ACCESS BY INDEX ROWID| T | 34 | 3128 | 2 (0)|

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

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

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

INDEX(@"SEL$1" "T"@"SEL$1" ("T"."OWNER"))

OUTLINE_LEAF(@"SEL$1")

ALL_ROWS

OPTIMIZER_FEATURES_ENABLE('10.2.0.1')

IGNORE_OPTIM_EMBEDDED_HINTS

END_OUTLINE_DATA

*/

Predicate Information (identified by operation id):

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

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

44 rows selected

2、RULE——RBO的應用

在CBO時代，雖然已經不提供明確的RBO優化器支援，但是RBO相關程式碼還是駐留在Query Optimizer中的。我們對optimizer_mode引數設定為RULE，就可以指示優化器生成RBO執行計劃。

設定模式引數。

SQL> alter session set optimizer_mode='RULE';

Session altered

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

NAME VALUE

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

optimizer_mode RULE

在optimizer_mode下，我們生成執行計劃如下：

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

Explained

SQL> select * from table(dbms_xplan.display(null,null,'advanced'));

PLAN_TABLE_OUTPUT

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

Plan hash value: 1516787156

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

| Id | Operation | Name |

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

| 0 | SELECT STATEMENT | |

| 1 | TABLE ACCESS BY INDEX ROWID| T |

|* 2 | INDEX RANGE SCAN | IDX_T_OWNER |

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

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

INDEX(@"SEL$1" "T"@"SEL$1" ("T"."OWNER"))

OUTLINE_LEAF(@"SEL$1")

RBO_OUTLINE

OPTIMIZER_FEATURES_ENABLE('10.2.0.1')

IGNORE_OPTIM_EMBEDDED_HINTS

END_OUTLINE_DATA

*/

Predicate Information (identified by operation id):

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

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

Note

-----

- rule based optimizer used (consider using cbo)

49 rows selected

注意幾個細節：在CBO環境下，由於owner=’SYS’條件進行全表掃描操作成本小於索引路徑。於是CBO選擇了全表掃描執行計劃。

但是在RBO時代，索引路徑規則是高於全表掃描的。所以執行計劃直接確定為索引路徑。Query Optimizer也注意到當前使用的優化器模式不合適，所以顯示的提示出“- rule based optimizer used (consider using cbo)”，告知使用者當前使用的是RBO，建議使用CBO。

3、CHOOSE——兩個時代的過渡

在Oracle 9i一個時期，optimizer_mode的預設引數取值為CHOOSE。那個時期是query optimizer從RBO向CBO轉換的時代，Oracle選擇了漸變式的轉換。由於那時沒有專門的夜間統計量收集作業，很多時候資料表是沒有統計量的。加之動態抽樣技術的不成熟，所以CBO的應用存在一些障礙。在這個時期，Oracle提出了CHOOSE作為優化器預設模式。

CHOOSE是介於自動選擇切換RBO和CBO。如果SQL涉及的資料表中有一個有統計量，那麼該SQL就是用CBO優化器。否則就是用RBO。動態取樣技術是在CBO優化器前提下的技術方案。

SQL> alter session set optimizer_mode='CHOOSE';

Session altered

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

NAME VALUE

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

optimizer_mode CHOOSE

首先實驗沒有統計量的情況。

//刪除統計量；

SQL> exec dbms_stats.delete_table_stats(user,'T',cascade_columns => true,cascade_indexes => true);

PL/SQL procedure successfully completed

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

Explained

SQL> select * from table(dbms_xplan.display(null,null,'advanced'));

PLAN_TABLE_OUTPUT

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

Plan hash value: 1516787156

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

| Id | Operation | Name |

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

| 0 | SELECT STATEMENT | |

| 1 | TABLE ACCESS BY INDEX ROWID| T |

|* 2 | INDEX RANGE SCAN | IDX_T_OWNER |

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

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

INDEX(@"SEL$1" "T"@"SEL$1" ("T"."OWNER"))

OUTLINE_LEAF(@"SEL$1")

RBO_OUTLINE

OPTIMIZER_FEATURES_ENABLE('10.2.0.1')

IGNORE_OPTIM_EMBEDDED_HINTS

END_OUTLINE_DATA

*/

Note

-----

- rule based optimizer used (consider using cbo)

49 rows selected

SQL>

當沒有統計量的時候，Oracle不會如ALL_ROWS的時候進行動態取樣，而是直接使用RBO。

強行收集了統計量之後，如果一個表有統計量，而另一個沒有會如何呢？

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

PL/SQL procedure successfully completed

SQL> create table t_parts as select * from dba_tables;

Table created

SQL> explain plan for select * from t, t_parts where t.object_name=t_parts.table_name and t.owner=t_parts.table_name;

Explained

SQL> select * from table(dbms_xplan.display(null,null,'advanced'));

PLAN_TABLE_OUTPUT

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

Plan hash value: 2202291360

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

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

| 0 | SELECT STATEMENT | | 1598 | 903K| 171 (4)| 00:00:03 |

|* 1 | HASH JOIN | | 1598 | 903K| 171 (4)| 00:00:03 |

| 2 | TABLE ACCESS FULL| T_PARTS | 1598 | 759K| 12 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| T | 50405 | 4528K| 157 (3)| 00:00:02 |

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

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

USE_HASH(@"SEL$1" "T"@"SEL$1")

LEADING(@"SEL$1" "T_PARTS"@"SEL$1" "T"@"SEL$1")

FULL(@"SEL$1" "T"@"SEL$1")

FULL(@"SEL$1" "T_PARTS"@"SEL$1")

OUTLINE_LEAF(@"SEL$1")

OPTIMIZER_FEATURES_ENABLE('10.2.0.1')

IGNORE_OPTIM_EMBEDDED_HINTS

END_OUTLINE_DATA

*/

Note

-----

- dynamic sampling used for this statement

123 rows selected

資料表T有統計量，而T_PARTS沒有統計量。在CHOOSE模式下，就選擇了CBO優化器，進而對T_PARTS進行動態取樣。

4、FIRST_ROWS——追求最快相應的優化模式

最後聊聊FIRST_ROWS系列（包括FIRST_ROWS_N）模式。該模式是一種介於CBO與RBO之間的優化器模式，與CHOOSE的切換方式不同。FIRST_ROWS模式系列與CBO一個顯著的差異就是追求最優執行計劃的標準不同。

ALL_ROWS代表的CBO追求的是生成綜合成本最低的SQL執行計劃。而FIRST_ROWS追求的是執行計劃最快返回結果集合，優先相應前幾條的查詢結果。所以，FIRST_ROWS與CBO的核心價值有一些差異。

FIRST_ROWS有其用途場景，最多的就是在於分頁操作SQL。當結果集合很大的時候，使用者或者應用往往不關心全部集合的情況，而是關注返回前幾條記錄的響應速度。

5、結論

optimizer_mode是Oracle Query Optimizer核心引數，控制優化器工作的取向和目標。從optimizer_mode所支援的各種取值，我們也很容易看到從RBO到CBO的轉換過程。

熟悉optimizer_mode各個取值的含義和用途，對我們控制優化器生成更好的執行計劃至關重要。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17203031/viewspace-705012/，如需轉載，請註明出處，否則將追究法律責任。

淺談optimizer_mode優化器模式

相關文章