Oracle CBO 與 RBO
http://blog.csdn.net/tianlesoftware/article/details/5709784

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

基於規則的最佳化方式：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情況下，還必須定期對大表進行分析。這個可以用定時的Job來實現，具體參考blog：

Oracle 10g Statistic資料統計

http://blog.csdn.net/tianlesoftware/archive/2009/10/15/4668723.aspx

 

注意：走索引不一定就是優的，比如一個表只有兩行資料，一次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方式是類似的，所不同的是當一個表有統計資訊時，它將是以最快的方式返回查詢的最先的幾行，從總體上減少了響應時間。

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（/*+ ... */）來設定

常見Oracle HINT的用法

http://blog.csdn.net/tianlesoftware/archive/2009/12/13/4969702.aspx

 

 

 

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.

出自： 

 

 

 

在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上的索引顯然是用不上，最多在其上做索引全掃描或索引快速全掃描。

4、如果一個語句使用 RBO的執行計劃確實比CBO 好，則可以透過加 " rule" 提示，強制使用RBO。

5、使用CBO 時，SQL語句 "FROM" 子句後面的表，必須全部使用ANALYZE 命令分析過，如果"FROM" 子句後面的是檢視，則此檢視的基礎表，也必須全部使用ANALYZE 命令分析過；否則，ORACLE 會在執行此SQL語句之前，自動進行ANALYZE 命令分析，這會極大導致SQL語句執行極其緩慢。

6、使用CBO 時，SQL語句 "FROM" 子句後面的表的個數不宜太多，因為CBO在選擇表連線順序時，會對"FROM" 子句後面的表進行階乘運算，選擇最好的一個連線順序。假如"FROM" 子句後有6個表，則其可選擇的連線順序就是6*5*4*3*2*1 = 720 種，CBO 選擇其中一種，而如果"FROM" 子句後有12個表，則其可選擇的連線順序就是12*11*10*9*8*7*6*5*4*3*2*1= 479001600 種，可以想象從中選擇一種，會消耗多少CPU 時間？如果實在是要訪問很多表，則最好使用 ORDER 提示，強制使用"FROM" 子句表固定的訪問順序。

7、使用CBO 時，SQL語句中不能引用系統資料字典表或檢視，因為系統資料字典表都未被分析過，可能導致極差的“執行計劃”。但是不要擅自對資料字典表做分析，否則可能導致死鎖，或系統效能嚴重下降。

8、使用CBO 時，要注意看採用了哪種型別的表連線方式。ORACLE的共有Sort Merge Join（SMJ）、Hash Join（HJ）和Nested Loop Join（NL）。CBO有時會偏重於SMJ 和 HJ，但在OLTP 系統中，NL 一般會更好，因為它高效的使用了索引。在兩張表連線，且內表的目標列上建有索引時，只有Nested Loop才能有效地利用到該索引。SMJ即使相關列上建有索引，最多隻能因索引的存在，避免資料排序過程。HJ由於須做HASH運算，索引的存在對資料查詢速度幾乎沒有影響。

9、使用CBO 時，必須保證為表和相關的索引蒐集足夠的統計資料。對資料經常有增、刪、改的表最好定期對錶和索引進行分析，可用SQL語句“analyze table xxx compute statistics for all indexes;"ORACLE掌握了充分反映實際的統計資料，才有可能做出正確的選擇。

10、使用CBO 時，要注意被索引的欄位的值的資料分佈，會影響SQL語句的執行計劃。例如：表emp，共有一百萬行資料，但其中的emp.deptno列，資料只有4種不同的值，如10、20、30、40。雖然emp資料行有很多，ORACLE預設認定表中列的值是在所有資料行均勻分佈的，也就是說每種deptno值各有25萬資料行與之對應。假設SQL搜尋條件DEPTNO=10，利用deptno列上的索引進行資料搜尋效率，往往不比全表掃描的高，ORACLE理所當然對索引“視而不見”，認為該索引的選擇性不高。

我們考慮另一種情況，如果一百萬資料行實際不是在4種deptno值間平均分配，其中有99萬行對應著值10，5000行對應值20，3000行對應值30，2000行對應值40。在這種資料分佈圖案中對除值為10外的其它deptno值搜尋時，毫無疑問，如果索引能被應用，那麼效率會高出很多。我們可以採用對該索引列進行單獨分析，或用analyze語句對該列建立直方圖，對該列蒐集足夠的統計資料，使ORACLE在搜尋選擇性較高的值能用上索引。