共計 8840 個字符，預計需要花費 23 分鐘才能閱讀完成。

這篇文章主要介紹了 ORACLE 優化器 RBO 與 CBO 有什么用，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓丸趣 TV 小編帶著大家一起了解一下。

RBO 和 CBO 的基本概念

Oracle 數據庫中的優化器又叫查詢優化器（Query
Optimizer）。它是 SQL 分析和執行的優化工具，它負責生成、制定 SQL 的執行計劃。Oracle 的優化器有兩種，基于規則的優化器（RBO）與基于代價的優化器（CBO)

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

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

RBO 自 ORACLE 6 以來被采用，一直沿用至 ORACLE 9i. ORACLE 10g 開始，ORACLE 已經徹底丟棄了 RBO，它有著一套嚴格的使用規則，只要你按照它去寫 SQL 語句，無論數據表中的內容怎樣，也不會影響到你的“執行計劃”，也就是說 RBO 對數據不“敏感”；它根據 ORACLE 指定的優先順序規則，對指定的表進行執行計劃的選擇。比如在規則中，索引的優先級大于全表掃描;RBO 是根據可用的訪問路徑以及訪問路徑等級來選擇執行計劃，在 RBO 中，SQL 的寫法往往會影響執行計劃，它要求開發人員非常了解 RBO 的各項細則，菜鳥寫出來的 SQL 腳本性能可能非常差。隨著 RBO 的被遺棄，漸漸不為人所知。也許只有老一輩的 DBA 對其了解得比較深入。關于 RBO 的訪問路徑，官方文檔做了詳細介紹：

RBO Path 1: Single Row by Rowid

RBO Path 2: Single Row by Cluster Join

RBO Path 3: Single Row by Hash Cluster Key
with Unique or Primary Key

RBO Path 4: Single Row by Unique or
Primary Key

RBO Path 5: Clustered Join

RBO Path 6: Hash Cluster Key

RBO Path 7: Indexed Cluster Key

RBO Path 8: Composite Index

RBO Path 9: Single-Column Indexes

RBO Path 10: Bounded Range Search on
Indexed Columns

RBO Path 11: Unbounded Range Search on
Indexed Columns

RBO Path 12: Sort Merge Join

RBO Path 13: MAX or MIN of Indexed Column

RBO Path 14: ORDER BY on Indexed Column

RBO Path 15: Full Table Scan

CBO 是一種比 RBO 更加合理、可靠的優化器，它是從 ORACLE 8 中開始引入，但到 ORACLE 9i 中才逐漸成熟，在 ORACLE 10g 中完全取代 RBO，CBO 是計算各種可能“執行計劃”的“代價”，即 COST，從中選用 COST 最低的執行方案，作為實際運行方案。它依賴數據庫對象的統計信息，統計信息的準確與否會影響 CBO 做出最優的選擇。如果對一次執行 SQL 時發現涉及對象（表、索引等）沒有被分析、統計過，那么 ORACLE 會采用一種叫做動態采樣的技術，動態的收集表和索引上的一些數據信息。

關于 RBO 與 CBO，我有個形象的比喻：大數據時代到來以前，做生意或許憑借多年累計下來的經驗（RBO）就能夠很好的做出決策，跟隨市場變化。但是大數據時代，如果做生意還是靠以前憑經驗做決策，而不是靠大數據、數據分析、數據挖掘做決策，那么就有可能做出錯誤的決策。這也就是越來越多的公司對 BI、數據挖掘越來越重視的緣故，像電商、游戲、電信等行業都已經大規模的應用，以前在一家游戲公司數據庫部門做 BI 分析，挖掘潛在消費用戶簡直無所不及。至今映像頗深。

CBO 與 RBO 的優劣

CBO 優于 RBO 是因為 RBO 是一種呆板、過時的優化器，它只認規則，對數據不敏感。畢竟規則是死的，數據是變化的，這樣生成的執行計劃往往是不可靠的，不是最優的，CBO 由于 RBO 可以從很多方面體現。下面請看一個例子，此案例來自于《讓 Oracle 跑得更快》。

SQL create table test as select 1 id ,object_name from dba_objects;

Table created.

SQL create index idx_test on test(id);

Index created.

SQL update test set id=100 where rownum =1;

1 row updated.

SQL select id, count(1) from test group by id;

  ID  COUNT(1)

———- ———-

  100  1

  1  50314

從上面可以看出，該測試表的數據分布極其不均衡,ID=100 的記錄只有一條，而 ID= 1 的記錄有 50314 條。我們先看看 RBO 下兩條 SQL 的執行計劃.

SQL select /*+ rule */ * from test where id =100;

Execution Plan

———————————————————-

Plan hash value: 2473784974

————————————————

| Id  | Operation  | Name  |

————————————————

|  0 | SELECT STATEMENT  |  |

|  1 |  TABLE ACCESS BY INDEX ROWID| TEST  |

|*  2 |  INDEX RANGE SCAN  | IDX_TEST |

————————————————

Predicate Information (identified by operation id):

—————————————————

  2 – access(ID =100)

Note

—–

  – rule based optimizer used
(consider using cbo)

Statistics

———————————————————-

  1  recursive calls

  0  db block gets

  3  consistent gets

  0  physical reads

  0  redo size

  588  bytes sent via SQL*Net to client

  469  bytes received via SQL*Net from client

  2  SQL*Net roundtrips to/from client

  0  sorts (memory)

  0  sorts (disk)

  1  rows processed

SQL

SQL select /*+ rule */ * from test where id=1;

50314 rows selected.

Execution Plan

———————————————————-

Plan hash value: 2473784974

————————————————

| Id  | Operation  | Name  |

————————————————

|  0 | SELECT STATEMENT  |  |

|  1 |  TABLE ACCESS BY INDEX ROWID| TEST  |

|*  2 |  INDEX RANGE SCAN  | IDX_TEST |

————————————————

Predicate Information (identified by operation id):

—————————————————

  2 – access(ID =1)

Note

—–

  – rule based optimizer used
(consider using cbo)

Statistics

———————————————————-

  1  recursive calls

  0  db block gets

  7012  consistent gets

  97  physical reads

  0  redo size

  2243353  bytes sent via SQL*Net to client

  37363  bytes received via SQL*Net from client

  3356  SQL*Net roundtrips to/from client

  0  sorts (memory)

  0  sorts (disk)

  50314  rows processed

從執行計劃可以看出，RBO 的執行計劃讓人有點失望，對于 ID=1，幾乎所有的數據全部符合謂詞條件，走索引只能增加額外的開銷（因為 ORACLE 首先要訪問索引數據塊，在索引上找到了對應的鍵值，然后按照鍵值上的 ROWID 再去訪問表中相應數據），既然我們幾乎要訪問所有表中的數據，那么全表掃描自然是最優的選擇。而 RBO 選擇了錯誤的執行計劃。可以對比一下 CBO 下 SQL 的執行計劃，顯然它對數據敏感，執行計劃及時的根據數據量做了調整, 當查詢條件為 1 時，它走全表掃描；當查詢條件為 100 時，它走區間索引掃描。如下所示：

SQL select * from test where id=1;

50314 rows selected.

Execution Plan

———————————————————-

Plan hash value: 1357081020

————————————————————————–

| Id  | Operation  | Name | Rows  | Bytes | Cost (%CPU)| Time  |

————————————————————————–

|  0 | SELECT STATEMENT  |   | 49075 | 
3786K|  52  (2)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TEST | 49075 |  3786K| 
52  (2)| 00:00:01 |

————————————————————————–

Predicate Information (identified by operation id):

—————————————————

  1 –
filter(ID =1)

Note

—–

  – dynamic
sampling used for this statement

Statistics

———————————————————-

  32  recursive calls

  0  db block gets

   3644 
consistent gets

  0  physical reads

  0  redo size

  1689175  bytes sent via SQL*Net to client

  37363  bytes received via SQL*Net from client

  3356  SQL*Net roundtrips to/from client

  0  sorts (memory)

   0 
sorts (disk)

  50314  rows processed

SQL select * from test where id =100;

Execution Plan

———————————————————-

Plan hash value: 2473784974

—————————————————————————————-

| Id  | Operation  | Name  | Rows 
| Bytes | Cost (%CPU)| Time  |

—————————————————————————————-

|  0 | SELECT STATEMENT  |  | 
1 |  79 |  2 
(0)| 00:00:01 |

|  1 |  TABLE ACCESS BY INDEX ROWID| TEST  |  1 | 
79 |  2  (0)| 00:00:01 |

|*  2 |  INDEX RANGE SCAN  | IDX_TEST
|  1 |  | 
1  (0)| 00:00:01 |

—————————————————————————————-

Predicate Information (identified by operation id):

—————————————————

  2 – access(ID =100)

Note

—–

  – dynamic sampling used for this
statement

Statistics

———————————————————-

  9  recursive calls

  0  db block gets

  73  consistent gets

  0  physical reads

  0  redo size

  588  bytes sent via SQL*Net to client

  469  bytes received via SQL*Net from client

  2  SQL*Net roundtrips to/from client

  0  sorts (memory)

  0  sorts (disk)

  1  rows processed

SQL

僅此一項就可以看出為什么 ORACLE 極力推薦使用 CBO，從 ORACLE
10g 開始不支持 RBO 的緣故。所謂長江后浪推前浪，前浪死在沙灘上。

CBO 知識點的總結

CBO 優化器根據 SQL 語句生成一組可能被使用的執行計劃，估算出每個執行計劃的代價，并調用計劃生成器（Plan Generator）生成執行計劃，比較執行計劃的代價，最終選擇選擇一個代價最小的執行計劃。查詢優化器由查詢轉換器（Query Transform）、代價估算器（Estimator）和計劃生成器（Plan Generator）組成。

CBO 優化器組件

CBO 由以下組件構成：

·  查詢轉化器（Query Transformer）

查詢轉換器的作用就是等價改變查詢語句的形式，以便產生更好的執行計劃。它決定是否重寫用戶的查詢（包括視圖合并、謂詞推進、非嵌套子查詢 / 子查詢反嵌套、物化視圖重寫），以生成更好的查詢計劃。

The input to the query transformer is a parsed query, which is represented
by a set of

query blocks. The query blocks are nested or interrelated
to each other. The form of the

query determines how the query blocks are interrelated to each other. The
main

objective of the query transformer is to determine if it
is advantageous to change the

form of the query so that it enables generation of a better query plan.
Several different

query transformation techniques are employed by the query
transformer, including:

■ View Merging

■ Predicate Pushing

■ Subquery Unnesting

■ Query Rewrite with Materialized Views

Any combination of these transformations can be applied to a given query.

· 代價評估器（Estimator）

評估器通過復雜的算法結合來統計信息的三個值來評估各個執行計劃的總體成本：選擇性（Selectivity）、基數（Cardinality）、成本（Cost）

計劃生成器會考慮可能的訪問路徑（Access Path）、關聯方法和關聯順序，生成不同的執行計劃，讓查詢優化器從這些計劃中選擇出執行代價最小的一個計劃。

· 計劃生成器（Plan Generator）

計劃生成器就是生成大量的執行計劃，然后選擇其總體代價或總體成本最低的一個執行計劃。

由于不同的訪問路徑、連接方式和連接順序可以組合，雖然以不同的方式訪問和處理數據，但是可以產生同樣的結果

下圖是我自己為了加深理解，用工具畫的圖

查看 ORACLE 優化器

SQL show parameter optimizer_mode;

NAME 
TYPE  VALUE

————————— ———– —————–

optimizer_mode 
string  ALL_ROWS

修改 ORACLE 優化器

ORACLE
10g 優化器可以從系統級別、會話級別、語句級別三種方式修改優化器模式，非常方便靈活。

其中 optimizer_mode 可以選擇的值有：first_rows_n,all_rows.  其中 first_rows_n 又有 first_rows_1000, first_rows_100, first_rows_10, first_rows_1

在 Oracle
9i 中，優化器模式可以選擇 first_rows_n,all_rows, choose, rule  等模式：

Rule：  基于規則的方式。

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

If OPTIMIZER_MODE=CHOOSE, if statistics do not exist, and if you do not
add hints to SQL statements, then SQL statements use the RBO. You can use the
RBO to access both relational data and object types. If
OPTIMIZER_MODE=FIRST_ROWS, FIRST_ROWS_n, or ALL_ROWS and no statistics exist,
then the CBO uses default statistics. Migrate existing applications to use the
cost-based approach.

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

All
Rows: 10g 中的默認值，也就是我們所說的 Cost 的方式，當一個表有統計信息時，它將以最快的方式返回表的所有的行，從總體上提高查詢的吞吐

雖然 Oracle
10g 中不再支持 RBO，Oracle 10g 官方文檔關于 optimizer_mode 參數的只有 first_rows 和 all_rows. 但是依然可以設置 optimizer_mode 為 rule 或 choose，估計是 ORACLE 為了過渡或向下兼容考慮。如下所示。

系統級別

SQL alter system set optimizer_mode=rule scope=both;

System altered.

SQL show parameter optimizer_mode

NAME 
TYPE  VALUE

——————————– ———– ———————–

optimizer_mode 
string  RULE

 

會話級別

會話級別修改優化器模式，只對當前會話有效，其它會話依然使用系統優化器模式。

SQL
alter session set optimizer_mode=first_rows_100;

Session
altered.

語句級別

語句級別通過使用提示 hints 來實現。

SQL select /*+ rule */ * from dba_objects
where rownum = 10;

感謝你能夠認真閱讀完這篇文章，希望丸趣 TV 小編分享的“ORACLE 優化器 RBO 與 CBO 有什么用”這篇文章對大家有幫助，同時也希望大家多多支持丸趣 TV，關注丸趣 TV 行業資訊頻道，更多相關知識等著你來學習!