SQL效能的度量 - CBO最佳化方式

一條查詢語句的處理包括語法分析、語句最佳化和查詢執行三個階段。在語句最佳化階段，Oracle預設使用基於成本的最佳化器（CBO，cost based optimizer）來選擇最好的執行計劃，這個最好的標準就是消耗的系統資源如CPU以及I/O資源最少。

基於CBO的最佳化器有三種最佳化方式來滿足不同的查詢需求，即all_rows、first_rows_n、first_rows。

all_rows：Oracle的預設方式，最佳化目標是實現查詢的最大吞吐量。

first_rows_n：最佳化輸出查詢的前n行資料，滿足快速響應的查詢需求。

first_rows：first_rows方式的老版本，儘快輸出查詢的前幾行資料。

Oracle提供在不同級別上的最佳化，即例項級、會話級和語句級。查詢當前資料庫的CBO最佳化方式

show parameter optimizer_mode;

NAME                                 TYPE        VALUE

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

optimizer_mode                       string      ALL_ROWS

在例項級設定CBO最佳化方式

alter system set optimizer_mode = first_rows_10;

在會話級設定CBO最佳化方式

alter session set optimizer_mode = all_rows;

在語句級設定CBO最佳化方式，需要使用hint提示

select /*+first_rows_10*/ ename, sal, mgr from scott.emp;

最佳化器的工作過程包括根據統計資料進行SQL轉換、根據資源消耗情況選擇訪問路徑、根據統計資料選擇連線方法以及確定連線次序幾個步驟，最後選擇一個最好的執行計劃。