3. Oracle Optimizer

影響 SQL 執行的速度取決於 SQL 的執行計畫 (Execution plan) ，而 Oracle 則是依照內部的優化器 (Optimizer) 來訂定其執行計畫。 Oracle 優化器大體上可分為基於規則 (RBO,Rule-Based Optimizer) 、 基於成本(CBO,Cost-Based Optimizer) 以及選擇性 (Choose) 三大類。

Oracl e對於 SQL 執行的存取途徑 (access path) 有一套既定的規則，基於規則的優化器會根據這些規則來決定 SQL 最佳的存取途徑；基於成本的優化器則是會考慮到各項因子，例如 CPU、I/O…等，利用這些因子來計算 SQL 依不同的存取途徑所花的成本，並選擇最低成本的途徑做為此 SQL 最佳的執行方式；選擇性 (Choose) 優化器則是會以物件的統計值 (Statistic) 為基礎，若物件有統計值，則選擇基於成本的優化器來執行，反之若物件無統計值，則選擇基於規則的優化器來執行。

在 Oracle 7 之前的版本，主要採用基於規則的優化器， Oracle 8i、9i 的版本主要採用的是 Choose 優化器，而基於成本的優化器在 Oracle 10g 之後便成為主流了。我們可透過設定 optimizer_mode 參數來變更所使用的優化器類別。

Oracle Optimizer 的功能說穿了就是在決定 SQL 語法訪問 Table 的方式而已，只是 RBO 、 CBO 用來決定訪問的方式不同。 Oracle訪問Table的方式可分為全表掃描與ROWID掃描。

全表掃描採用的是一次讀取多個 data block ，依序的訪問 Table 中的每筆資料，而每次讀取 data block 的數量則是由參數 db_file_multiblock_read_count 所決定。

ROWID 指的是資料實際存放在 data block 上的物理位址， Table 中每筆資料都會由 object number + file number + block number + row number 構成一組rowid ，為64進位制，以 A~Z 、 a~z 、 0~9 、 + 號， / 號共64個字符所組成，例如某筆資料的ROWID為 AAAMAkAAEAAAAF/AAA ，第 1~6 位 AAAMAk 表示這筆資料屬於哪個 table 的 object number ；第 7~9 位 AAE 表示此筆資料存放於哪個 data file 上的 file number ；第 10~15 位 AAAAF/ 表示為 block number ，最後 AAA 三位則是為 row number 。 Oracle的索引 (Index) 實現了數據資料與數據物理位址之間的聯繫，也因此我們透過索引定位到數據實體的物理位址，由此很快的就可以得到我們想要的資料了。

• 基於規則的優化器 (RBO)

Oracle 依照訪問 Table 的方式訂定出一套排名，例如 Oracle 認為使用 ROWID 搜尋是最有效率的，因此將 ROWID 搜尋的方法排名在第一位，使用 Full Table Scan 是最沒有效率的，因此將 Full Table Scan 排名在最後一位。在使用基於規則的優化器下， Oracle 在解析 SQL 語句的時候會找出所有可能的執行方式，然後選擇排名 (Rank) 最小來當作最佳的執行方式。

例如一段SQL如下:

select First_name||' '||Last_name from employees where department_id = 20     and  employees_id between 100 and 200 order by First_name;

已知 employees 這個 table 於 department_id 欄位上有索引EMP_DEPARTMENT_IX ，同樣在 employee_id 欄位上也有索引 EMP_EMP_ID_PK ，在使用 RBO 的情況下， Oracle 會選擇何種方式來執行呢 ? 首先評估各種可能的執行方式以及其Rank :

• Full Table Scan 🡪 Rank 15

• Single-column index (使用EMP_DEPARTMENT_IX) 🡪 Rank 9

• Bounded range Search on indexed columns (使用EMP_EMP_ID_PK) 🡪 Rank 10

在基於規則的優化器下， Oracle 會選擇最小的 Rank 做為其執行的方式，因此最終會使用 EMP_DEPARTMENT_IX 這個index來做為執行的方式。

• 基於成本的優化器 (CBO)

Oracle基於成本的優化器主要是使用統計資料 (Statistic) 來估算各個執行計畫所需的成本，並選擇成本最小做為最佳的執行計畫。這些統計資料存放在資料庫的資料字典裡 (Data Dictionary) ，例如資料庫在估算一個table資料量的大小時，不是使用 "select count(*)" 這個指令去查詢，而是去尋找 USER_TABLES 裡面的 NUM_ROWS 這個欄位所記錄的比數，若 USER_TABLES 所記錄的筆數與實際上使用 count 所估算出來的筆數差距甚遠時，便有可能導致 Oracle 錯估執行計畫的成本而選擇了一個較差的執行計畫。我們可以使用 DBMS_STATS 這個系統 package 來蒐集統計信息，而在 Oracle 9i之前的版本也可以使用 analyze 這個指令來蒐集統計信息。

在設定基於成本的優化器時，總共有 ALL_Rows 、 First_Rows_N ， First_Rows 三種模式可供選擇。 ALL_Rows 表示優化器會選擇能夠在最短的時間內返回所有資料行的執行計畫； First_Rows_N 其中的N可以是 1、10、100、1000 或任何整數，這告訴優化器數據總量的哪些部分應該最先被獲取，優化器將會選擇能最快返回 N 筆資料的執行計畫； First_Rows 則是優化器會選擇能最快返回第一筆資料的執行計畫。

最簡單推算執行計畫 Cost 的方式就是去計算截取資料所需讀取 Data Block 的次數。若使用全表掃描 (Full Table Scan) ，其 Cost = (Table 總 block 數量 /  db_file_multiblock_read_count)；若使用 ROWID 掃描 (Index Scan)，其 Cost = (查詢資料筆數占總資料量的比例  *  Cluster_Factor)， Cluster_Factor 指的是使用索引完整掃描資料所需讀取的 block 數量。例如一個查詢如下 :

SELECT * FROM employees WHERE department_id = 50;

已知 department_id 欄位上有索引 EMP_DEPARTMENT_IX ，我們可由USER_TABLES 得知 employees 的 NUM_ROWS = 107，Blocks = 5 :

由 USER_INDEXES 得知 EMP_DEPARTMENT_IX 的 Clustering_Factor = 9 :

由參數檔可知 db_file_multiblock_read_count = 8 :

department_id = 50的資料筆數為 45 筆:

我們來計算看看最佳的執行計畫為何:

• Full Table Scan : Cost = 5/8 = 0.625。

• 使用EMP_DEPARTMENT_IX : Cost = (45/107 * 9) = 3.785。

由上述分析可知使用 Full Table Scan 所需的 Cost 最小，因此這個查詢的執行計畫，會使用 Full Table Scan 。

然而這種簡易的計算方式使用的是 IO cost-based ，上述範例只是用來幫助我們了解 CBO 的機制而已，實際上 Oracle 為了能夠更精準找到最佳的執行計畫， CBO 計算上考量的數據非常多，主要使用的是 CPU Model 來計算，公式如下 :

Cost = ( #SRds * sreadtim + #MRds * mredtim + #CPUCycles / cpuspeed) / sreadtim

#SRDs : 單 Block 讀取數量 (number of single block reads)

#MRDs : 多 Block 讀取數量 (number of multi block reads)

#CPUCycles : CPU 速度 (number of CPU Cycles)

sreadtim  : 單 Block 的讀取時間 (single block read time)

mreadtim  : 多 Block 的讀取時間 (multi block read time)

cpuspeed  : CPU 每秒速率 (CPU cycles per second)

這些數據可以由 sys.aux_stats$ 獲得，並且可以使用 dbms_stats.gather_system_stats 來蒐集這些數據。實際 CBO 的 Cost = IO Cost + CPU Cost ，計算上頗為複雜，這邊就不再做深入的探討。

了解優化器的運作方式及原理對於我們做 SQL Tuning 是有幫助的。