Oracle Optimizer CBO RBO

之前整理的一篇有關CBO和RBO文章：

Oracle CBO 與 RBO

http://blog.csdn.net/xujinyang/article/details/6882068

Oracle 資料庫中優化器（Optimizer）是SQL分析和執行的優化工具，它負責指定SQL的執行計劃，也就是它負責保證SQL執行的效率最高，比如優化器決定Oracle 以什麼樣的方式來通路資料，是全表掃描（Full Table Scan），索引範圍掃描（Index Range Scan）還是全索引快速掃描（INDEX Fast Full Scan：INDEX_FFS）;對于表關聯查詢，它負責确定表之間以一種什麼方式來關聯，比如HASH_JOHN還是NESTED LOOPS 或者MERGE JOIN。 這些因素直接決定SQL的執行效率，是以優化器是SQL 執行的核心，它做出的執行計劃好壞，直接決定着SQL的執行效率。

         Oracle 的優化器有兩種：

                   RBO(Rule-Based Optimization): 基于規則的優化器

                   CBO(Cost-Based Optimization): 基于代價的優化器

         從Oracle 10g開始，RBO 已經被棄用，但是我們依然可以通過Hint 方式來使用它。

一．        RBO 基于規則的優化器

在8i之前，Oracle 使用的是一種叫作RBO（Rule Based Optimizer）的優化器，它的執行機制非常簡單，就是在優化器裡面嵌入若幹種規則，執行的SQL語句符合哪種規則（RANK），則按照規則(RANK)制定出相應的執行計劃，比如說表上有個索引，如果謂詞上有索引的列存在，則Oracle 會選擇索引，否則選擇全表掃描；又比如，兩個表關聯的時候，按照表在SQL中的位置來決定哪個是驅動表，哪個是被驅動表。

RBO 選擇執行計劃的一個優先級清單

Rank	Access Path
1	Single row by ROWID
2	Single row by cluster join
3	Single row by hash cluster key with unique or primary key
4	Single row by unique or primary key
5	Cluster Join
6	Hash cluster key
7	Indexed cluster key
8	Composite index
9	Single-column index
10	Bounded range search on indexed columns
11	Unbounded range search on indexed columns
12	Sort-merge join
13	MAX OR MIN of indexed column
14	ORDER by on indexed column
15	Full table scan

由于RBO 隻是簡單的去比對Rank，是以它的執行計劃有時并不是最佳的。 比如我們有一張資料分布非常不均勻的表。 90%的資料内容是一樣的，并且在這個字段上有索引。 如果我們的SQL 謂詞裡有這個字段，那麼RBO 就會選擇走索引。 這就會增加額外的開銷。 因為Oracle 要先通路索引資料塊，在索引上找到相應的鍵值，然後按照鍵值上的rowid 在去通路表中的相應資料。 在這種情況下，我們選擇全表掃描是最優的，但是RBO 不會這麼選擇。

二．        CBO 基于成本的優化器

從8i開始，Oracle 引入了CBO（Cost Based Optimizer），它的思路是讓Oracle 擷取所有執行計劃相關的資訊，通過對這些資訊做計算分析，最後得出一個代價最小的執行計劃作為最終的執行計劃。

CBO是一種比RBO 更理性化的優化器。從10g開始，Oracle 已經徹底丢棄了RBO。 即使在表，索引沒有被分析的時候，Oracle依然會使用CBO。此時，Oracle 會使用一種叫做動态采樣的技術，在分析SQL的時候，動态的收集表，索引上的一些資料塊，使用這些資料塊的資訊及字典表中關于這些對象的資訊來計算出執行計劃的代價，進而挑出最優的執行計劃。

當表沒有做分析的時候，Oracle 會使用動态采樣來收集統計資訊，這個動作隻有在SQL執行的第一次，即硬分析階段使用，後續的軟分析将不在使用動态采樣，直接使用第一次SQL 硬分析時生成的執行計劃。

Oracle SQL的硬解析和軟解析

http://blog.csdn.net/xujinyang/article/details/6829604

在Oracle 10g中，CBO 可選的運作模式有2種：

（1）       FIRST_ROWS(n)

（2）       ALL_ROWS  -- 10g中的預設值

檢視CBO 模式：

SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE

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

optimizer_mode                       string      ALL_ROWS

修改CBO 模式的三種方法：

（1）       SQL 語句：

Sessions級别：

         SQL> alter session set optimizer_mode=all_rows;

         （2） 修改pfile 參數：

OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS

（3）       語句級别用Hint（）來設定

Select name from table;

                      Select name from table;  

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

FIRST_ROWS（n） 模式說明

         當CBO 的優化模式設定為FIRST_ROWS（n）時，Oracle 在執行SQL時，優先考慮将結果集中的前n條記錄以最快的速度回報回來，而其他的結果并不需要同時傳回。

這種需求在一些網站或者BBS的分頁上經常看到，比如每次隻顯示查詢資訊的前20條或者BBS上的前20個文章， 這時候設定FIRST_ROWS（20）就非常合适，優化器并不需要同僚将所有符合條件的結果傳回，使用者也不需要。這時，CBO将考慮用一種最快的傳回前20條記錄的執行計劃，這種執行計劃對于SQL的整體執行時間也不不是最快的，但是在傳回前20條記錄的處理上，确實最快的。

如：

         Select b.x,b.y from

                   (

                   Select a.*, rownum rnum from

(

         Select * from t order by x

) a

Where rownum < 20

) b where rnum >=10;

在這個分頁例子中，每次從結果集中取10條記錄，記錄按照x字段排序。

注意： 排序使用的字段x 必須建立有索引，否則CBO 會忽略FIRST_ROWS(n)，而使用ALL_ROWS.

ALL_ROWS 模式說明

         當CBO 模式設定為ALL_ROWS時，Oracle 會用最快的速度将SQL執行完畢，将結果集全部傳回，它和FIRST_ROWS（n）的差別在于，ALL_ROWS強調以最快的速度将SQL執行完畢，并将所有的結果集回報回來，而FIRST_ROWS（n）則側重于傳回前n條記錄的執行時間。

         ALL_ROWS在OLAP 系統中使用得比較多，它用最快的速度獲得SQL執行的最後一條記錄，而不是前N條記錄。 和FIRST_ROWS（n）正好相反。 ALL_ROWS 強調SQL整體的執行效率，而FIRST_ROWS（n）強調用最快的速度傳回前N行，而不管所有的結果傳回的時長，可能最後一條要很長時間才能獲得。

整理自《讓Oracle 跑的更快》

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