Oracle最佳化器的兩種最佳化方式

Oracle的最佳化器有兩種最佳化方式：

基於規則的最佳化方式：Rule-Based Optimization（RBO）

基於成本或者統計資訊的最佳化方式（Cost-Based Optimization：CBO）

RBO方式：最佳化器在分析SQL語句時，所遵循的是Oracle內部預定的一些規則。比如我們常見的，當一個where子句中的一列有索引時去走索引。

CBO方式：CBO是在ORACLE7 引入，但到ORACLE8i 中才成熟。ORACLE 已經聲明在ORACLE9i之後的版本中，RBO將不再支援。它是看語句的代價（Cost），這裡的代價主要指Cpu和記憶體。CPU Costing的計算方式現在預設為CPU+I/O兩者之和。可通過DBMS_XPLAN.DISPLAY_CURSOR觀察更為詳細的執行計畫。最佳化器在判斷是否用這種方式時，主要參照的是表及索引的統計資訊。統計資訊給出表的大小、有少行、每行的長度等資訊。這些統計資訊起初在庫內是沒有的，是做analyze後才出現的，很多的時侯到期統計資訊會令最佳化器做出一個錯誤的執行計畫，因些應及時更新這些資訊。按理，CBO應該自動收集，實際卻不然，有時候在CBO情況下，還必須定期對大表進行分析。

注意：走索引不一定就是優的，比如一個表只有兩行資料，一次IO就可以完成全表的檢索，而此時走索引時則需要兩次IO,這時全表掃描（full table scan）是最好。

Oracle使用Optimizer_mode參數來控制最佳化器的偏好，9i常用的幾個參數有：first_rows,all_rows,first_rows_N，rule,choose等。而10g少了rule和choose。

Rule：基於規則的方式。

Choolse：指的是當一個表或或索引有統計資訊，則走CBO的方式，如果表或索引沒統計資訊，表又不是特別的小，而且相應的列有索引時，那麼就走索引，走RBO的方式。

First Rows：它與Choose方式是類似的，所不同的是當一個表有統計資訊時，它將是以最快的方式返回查詢的最先的幾行，從總體上減少了回應時間。

本欄目更多精彩內容：http://www.bianceng.cn/database/Oracle/

All Rows: 10g中的預設值，也就是我們所說的Cost的方式，當一個表有統計資訊時，它將以最快的方式返回表的所有的行，從總體上提高查詢的輸送量。沒有統計資訊則走RBO的方式。

設定最佳化器模式：

（1）Instance層級我們可以通過在initSID.ora檔案中設定

OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS

SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE

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

optimizer_mode                       string      ALL_ROWS

（2） Sessions層級通過ALTER SESSION SET OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS來設定。

（3）語句層級用Hint（/*+ … */）來設定

OPTIMIZER_INDEX_COST_ADJ參數

參數OPTIMIZER_INDEX_COST_ADJ可以理解為Oracle執行多塊（MultiBlock）I/O（比如全表掃描）的代價與執行單塊（Single-block）I/O代價的相對比例。OPTIMIZER_INDEX_COST_ADJ通過指明索引I/O代價與掃描全表I/O代價的相對比值來影響CBO的行為，取值越小，CBO越傾向於使用索引，取值越大，越傾向於全表掃描。而預設值100，指明預設下，二者的代價是相等。

官方文檔（Reference）中對這個參數描述如下：

OPTIMIZER_INDEX_COST_ADJ

Property

Description

Parameter type

Integer

Default value

100

Modifiable

ALTER SESSION, ALTER SYSTEM

Range of values

1 to 10000

OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly—that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.

The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal.

Note:

The adjustment does not apply to user-defined cost functions for domain indexes.

出自： http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/initparams160.htm#REFRN10143

在CBO下寫SQL語句的注意事項：

1、RBO自ORACLE 6版以來被採用，有著一套嚴格的使用規則，只要你按照它去寫SQL語句，無論資料表中的內容怎樣，也不會影響到你的“執行計畫”，也就是說對資料不“敏感”；CBO計算各種可能“執行計畫”的“代價”，即cost，從中選用cost最低的方案，作為實際運行方案。各“執行計畫”的cost的計算根據，依賴於資料表中資料的統計分布，ORACLE資料庫本身對該統計分布並不清楚，必須要分析表和相關的索引（使用ANALYZE 命令），才能搜集到CBO所需的資料。

2、使用CBO 時，編寫SQL語句時，不必考慮"FROM" 子句後面的表或視圖的順序和"WHERE" 子句後面的條件順序；ORACLE自7版以來採用的許多新技術都是基於CBO的，如星型串連排列查詢，雜湊串連查詢，函數索引，和並行查詢等。

3、一般而言，CBO所選擇的“執行計畫”都不會比RBO的“執行計畫”差，而且相對而言，CBO對程式員的要求沒有RBO那麼苛刻，節省了程式員為了從多個可能的“執行計畫”中選擇一個最優的方案而花費的調試時間，但在某些場合下也會存在問題。較典型的問題有：有時，表明明建有索引，但查詢過程顯然沒有用到相關的索引，導致查詢過程耗時漫長，佔用資源巨大，這時就需要仔細分析執行計畫，找出原因。例如，可以看串連順序是否允許使用相關索引。假設表emp的deptno列上有索引，表dept的列deptno上無索引，WHERE語句有emp.deptno=dept.deptno條件。在做NL串連時，emp做為外表，先被訪問，由於串連機制原因，外表的資料訪問方式是全表掃描，emp.deptno上的索引顯然是用不上，最多在其上做索引全掃描或索引快速全掃描。