【摘】 oracle優化器

一 ORACLE優化器概述

ORACLE優化器是用於生成SQL語句訪問資料庫時使用的執行計劃的。ORACLE優化器通過使用ORACLE蒐集的關於資料庫物件的統計資料來生成SQL語句的訪問計劃（使用什麼物件）並執行計劃（使用何種操作）

二 ORACLE優化器的分類

ORACLE優化器分為三類：

1） RULE優化器：基於規則的優化器相對比較簡單，通過檢查資料庫的可用路徑並將這些路徑與路徑表進行比較，從而確定SQL語句的執行計劃。RULE優化的過程中不需要任何表或索引的統計資訊，而且也會忽略任何表或索引的統計資訊

2） COST優化器：基於開銷的優化器相對比較複雜，通過使用資料庫的結構和資料等資訊來選擇最優的執行計劃。優化的過程中需要相關表和索引的統計資訊。

3） CHOOSE優化器：在存在相關統計資訊的情況下采用基於開銷的優化器，在不存在相關統計資訊的情況下才用基於規則的優化器。這是8I中預設的優化器工作方式

三 查詢當前優化器的工作方式

查詢當前優化器的工作方式比較簡單的方法有以下兩種：

1） 檢視ORACLE資料庫的初始化檔案，看看初始化引數optimizer_mode的取值

2） SQL> select name,value from v$parameter where name = 'optimizer_mode';

NAME VALUE

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

optimizer_mode CHOOSE

四 修改優化器的工作模式

可以使用下面的兩種方法來選擇優化器模式（基於開銷的模式和基於規則的模式）：

1） 初始化引數optimizer_mode

2） alter session 命令的 optimizer_goal引數

選擇優化器模式時所能使用的選項

l choose：這個選項允許優化器根據特定表或索引的統計資料的可用性選擇優化模式。如果sql語句中涉及到的表中有一個表有統計資料，那麼優化器將選擇基於開銷的優化方法；否則如果所有表都沒有統計資料，那麼優化器將選擇基於規則的優化模式

l rule：這個選項將導致優化器總是使用基於規則的優化方法，而不管系統中是否存在被訪問表的統計資料

l all_rows：這個選項將導致優化器對所有的sql語句都使用基於開銷的優化方法，即使對於系統中不存在可用統計資料的表，情況也是如此。這個選項的目的是使用最少的系統資源獲得最大的吞吐量，力求完成sql語句的總花費為最少

l first_rows_n：這個選項將導致優化器對所有的sql語句都使用基於開銷的優化方法，即使對於系統中不存在可用統計資料的表，情況也是如此。這個選項的目的是獲得最快的響應時間，力求最快返回結果中的前n條記錄

五 基於規則的優化器

因為我們現在的測試環境和所有的醫保中心以及定點醫療機構都是採用預設的choose優化器選項，而且所有的表和索引等也沒有相關的統計資訊，所以實際上oracle資料庫都是執行在rule的優化器模式下。下面詳細的介紹一下基於規則的優化器模式，對基於開銷的優化器僅做簡單的介紹。

在基於規則的和基於開銷的優化器中，基於規則的優化器相對來說比較簡單。在基於規則的優化方法中，優化器通過檢查資料庫的可用路徑並將這些路徑與路徑表進行比較，從而確定sql語句的執行計劃。下面的表中包含了與各種不同訪問路徑相關的開銷

級別
 訪問路徑
 
1
 通過rowid訪問單行資料
 
2
 通過簇連線訪問單行資料
 
3
 通過帶惟一鍵或主鍵的雜湊簇訪問單行資料
 
4
 通過惟一鍵或主鍵訪問單行資料
 
5
 簇連線
 
6
 雜湊簇鍵
 
7
 索引簇鍵
 
8
 複合鍵
 
9
 單欄位索引
 
10
 在索引欄位上有邊界的範圍內搜尋資料
 
11
 在索引欄位上無邊界的範圍內搜尋資料
 
12
 排序合併連線
 
13
 搜尋索引欄位的最大或最小值
 
14
 對索引欄位使用order by操作
 
15
 全表掃描
 

舉個最簡單的例子來說明基於規則的優化器如果選擇執行路徑

建立測試用表

SQL> drop table test;

表已丟棄。

SQL> create table test

2 (id varchar2(3),

3 name varchar2(10))

4 tablespace test;

表已建立。

SQL> insert into test values ('001','tom');

已建立 1 行。

SQL> commit;

提交完成。

沒有建立的索引的情況下，該sql執行的唯一方式就是全表掃描（即使全表掃描被規則優化器認為是最慢的一種方式）

SQL> set autotrace on

SQL> select * from test where id='001';

ID NAME

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

001 tom

Execution Plan

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

0 SELECT STATEMENT ptimizer=CHOOSE

1 0 TABLE ACCESS (FULL) OF 'TEST'

在test的id欄位上建立索引後，上面這條sql語句就存在兩種執行方案，一是全表掃描（15），另一種就是單欄位索引（9）。Oracle根據前面的規則認為後者的開銷比較小，所以優化器選擇通過索引的掃描。

SQL> create index idx_test on test(id);

索引已建立。

SQL> select * from test where id='001';

ID NAME

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

001 tom

Execution Plan

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

0 SELECT STATEMENT ptimizer=CHOOSE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'

2 1 INDEX (RANGE SCAN) OF 'IDX_TEST' (NON-UNIQUE)

上面的這個例子說明了基於規則的優化器的工作方式。基於規則的優化器原理比較簡單，可以給系統提供比較好的執行計劃的穩定性。

六 基於開銷的優化器

基於開銷的優化器需要表或索引上的統計資訊，他根據這些統計資訊來為sql選擇一條他認為是開銷最小的執行路徑。由於資料庫裡面的資料是一直在變化的，所以統計資訊也應該及時的更新，這樣才可以為基於開銷提供準確的資訊，為sql提供更加優化的方案。

SQL> alter session set OPTIMIZER_MODE=all_rows;

會話已更改。

SQL> analyze table test compute statistics;

表已分析。

SQL> select * from test where id='001';

ID NAME

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

001 tom

Execution Plan

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

0 SELECT STATEMENT ptimizer=ALL_ROWS (Cost=1 Card=1 Bytes=6)

1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=1 Card=1 Bytes=6)

雖然test表上存在索引，但是由於表中僅有一條記錄，所以全表掃描的開銷應該比通過索引來查詢記錄要小的多。因此基於開銷的優化器選擇了全表掃描。從上面的兩個例子的對比來看只要有了統計資訊，基於開銷的優化器比基於規則的優化器要聰明的多。

七 總結

一般來說8i以下的資料庫使用基於規則的優化器比較合適，因為這些版本里面的基於開銷的優化器並不完善，為sql語句提供的執行路徑並不總是最優的；而8i以及以上的版本，基於開銷的優化器已經比較完善了，可以很好的代替基於規則的優化器來工作了。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-590905/，如需轉載，請註明出處，否則將追究法律責任。