Oracle Optimizer CBO RBO

http://blog.csdn.net/tianlesoftware/article/details/5824886

 

 

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

CBO 與 RBO

http://blog.csdn.net/tianlesoftware/archive/2010/07/11/5709784.aspx

 

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 選擇執行計劃的一個優先順序列表

        

 

由於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/tianlesoftware/archive/2010/04/08/5458896.aspx

 

 

在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 /*+ first_rows(10) */ name from table;

                      Select /*+ all_rows */ 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.

 

 

 

 

FIRST_ROWS（n） 模式說明

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

這種需求在一些網站或者BBS的分頁上經常看到，比如每次只顯示查詢資訊的前20條或者BBS上的前20個帖子， 這時候設定FIRST_ROWS（20）就非常合適，最佳化器並不需要同時將所有符合條件的結果返回，使用者也不需要。這時，CBO將考慮用一種最快的返回前20條記錄的執行計劃，這種執行計劃對於SQL的整體執行時間也不不是最快的，但是在返回前20條記錄的處理上，確實最快的。

如：

         Select /*+ first_rows(10) */b.x,b.y from

                   (

                   Select /*+ first_rows(10) */ a.*, rownum rnum from

(

         Select /*+ first_rows(20) */ * 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行，而不管所有的結果返回的時長，可能最後一條要很長時間才能獲得。