Oracle优化器介绍
Oracle優化器介紹
本文講述了Oracle優化器的概念、工作原理和使用方法，兼顧了Oracle8i、9i以及最新的10g三個版本。理解本文將有助于您更好的更有效的進行SQL優化工作。
RBO優化器
RBO是一種基于規則的優化器，隨著CBO優化器的逐步發展和完善，在最新的10g版本中Oracle已經徹底廢除了RBO。正在使用Oracle8i或9i的人們或多或少的都會碰到RBO，因此在詳細介紹CBO之前，我們有必要簡單回顧一下古老的RBO優化器。
在RBO中Oracle根據可用的訪問路徑和訪問路徑的等級來選擇執行計劃，等級越高的訪問路徑通常運行SQL越慢，如果一個語句有多個路徑可走，Oracle總是選擇等級較低的訪問路徑。
RBO訪問路徑
1級：用Rowid定位單行
當WHERE子句中直接嵌入Rowid時，RBO走此路徑。Oracle不推薦直接引用Rowid，Rowid可能會由于版本的改變而變化，行遷移、行鏈接、EXP/IMP也會使Rowid發生變化。
2級：用Cluster Join定位單行
兩個表做等值連接，一方的連接字段是Cluster Key，且WHERE中存在可以保證該語句僅返回一行記錄的條件時，RBO走此路徑。
3級：用帶用唯一約束或做主鍵的Hash Cluster Key定位單行
4級：用唯一約束的字段或做主鍵的字段來定位單行
5級：Cluster Join
6級：使用Hash Cluster Key
7級：使用索引Cluster Key
8級：使用復合索引
9級：使用單字段索引
10級：用索引進行有界限范圍的查找
如，column >[=] expr AND column <[=] expr或column BETWEEN expr AND expr
或column LIKE ‘c%’
11級：用索引字段進行無界限的查找
如，WHERE column >[=] expr 或 WHERE column <[=] expr
12級：排序合并連接
13級：對索引字段使用MAX或MIN函數
14級：ORDER BY索引字段
15級：全表掃描
如果可以使用索引RBO會盡可能的去用索引而不是全表掃描，但是在下列一些情況RBO只能使用全表掃描：
如果column1和column2是同一個表的字段，含有條件column1 < column2或column1 > column2或column1 <= column2或column1 >= column2，RBO會用全表掃描。
如果使用column IS NULL或column IS NOT NULL或column NOT IN或column != expr或column LIKE ‘%ABC’時，不論column有無索引，RBO都使用全表掃描。
如果expr = expr2，expr表達式作用了一個字段上，無論該字段有無索引，RBO都會全表掃描。
NOT EXISTS子查詢以及在視圖中使用ROWNUM也會造成RBO進行全表掃描。
以上就是RBO的全部可用訪問路徑。RBO優化器死板的根據規則來選擇執行計劃顯然不夠靈活，在RBO中也無法使用物化視圖等Oracle提供的新特性，在Oracle8i時CBO已經基本成熟，因此Oracle強烈建議改用CBO優化器。下文將全面介紹CBO優化器。
CBO優化器結構
CBO是基于成本的優化器，它根據可用的訪問路徑、對象的統計信息、嵌入的Hints來選擇一個成本最低的執行計劃。
CBO主要包含以下組件：
- l 查詢轉換器（Query Transformer）
- l 評估器（Estimator）
- l 計劃生成器（Plan Generator）
如下圖所示：
查詢轉換器
查詢語句的形式會影響所產生的執行計劃，查詢轉換器的作用就是改變查詢語句的形式以產生較好的執行計劃。
從Oracle 8i開始就有四種轉換技術：視圖合并（View Merging）、謂詞推進（Predicate Pushing）、非嵌套子查詢（Subquery Unnesting）和物化視圖的查詢重寫（Query Rewrite with Materialized Views）。
視圖合并：如果SQL語句中含有視圖，經分析后會把視圖放在獨立的“視圖查詢塊”中，每個視圖會產生一個視圖子計劃，當為整個語句產生執行計劃時，視圖子計劃會被直接拿來使用而不會照顧到語句的整體性，這樣就很容易導致不良執行計劃的生成。視圖合并就是為了去掉“視圖查詢塊”，將視圖合并到一個整體的查詢塊中，這樣就不會有視圖子計劃產生，執行計劃的優良性得到提升。
謂詞推進：不是所有的視圖都能夠被合并，對于那些不能被合并的視圖Oracle會將相應的謂詞推進到視圖查詢塊中，這些謂詞通常是可索引的或者是過濾性較強的。
非嵌套子查詢：子查詢和視圖一樣也是被放于獨立查詢塊中的，查詢轉換器會將絕大多數子查詢轉換為連接從而合并為同一查詢塊，少量不能被轉換為連接的子查詢，會將它們的子計劃安照一個高效的方式排列。
物化視圖的查詢重寫：當query_rewrite_enabled=true時，查詢轉換器尋找與該查詢語句相關聯的物化視圖，并用物化視圖改寫該查詢語句。
關于“窺視”（Peeking）:
在Oracle9i中為查詢轉換器增加了一個功能，就是當用戶使用綁定變量時，查詢轉換器可以“偷窺”綁定變量的實際值。
我們知道使用綁定變量雖然可以有效的減少“硬分析”，但它帶來的負面影響是優化器無法根據實際的數據分布來優化SQL，很有可能本可以走索引的SQL卻做了全表掃描。“窺視”正是為了解決這個問題，但是它并沒有徹底的解決，Oracle只允許第一次調用時進行“窺視”，接下來的調用即使綁定變量的值發生了變化，也仍然是使用第一次生成的執行計劃，這就造成了一個錯誤的執行計劃會被多次使用，10g中的“窺視”也是如此。
評估器
評估器通過計算三個值來評估計劃的總體成本：選擇性（Selectivity）、基數（Cardinality）、成本（Cost）。
選擇性：是一個大于0小于1的數，0表示沒有記錄被選定，1表示所有記錄都被選定。統計信息和直方圖關系到選擇性值的準確性。如：name=’Davis’，如果不存在統計信息評估器將根據所用的謂詞來指定一個缺省的選擇性值，此時評估器會始終認為等式謂詞的選擇性比不等式謂詞小；如果存在統計信息而不存在直方圖，此時選擇性值為1/count(distinct name)；如果存在統計信息也存在直方圖，選擇性值則為count（name）where name=’Davis’ / count（name）where name is not null。
基數：通常表中的行數稱為“基礎基數”（Base cardinality）；當用WHERE中的條件過濾后剩下的行數稱為“有效基數”（Effective cardinality）；連接操作之后產生的結果集行數稱為“連接基數”（Join cardinality）；一個字段DISTINCT之后的行數稱為“DISTINCT基數”；“GROUP基數”（Group cardinality）比較特殊，它與基礎基數和DISTINCT基數有關，例如：group by colx則GROUP基數就等于基礎基數，但是group by colx,coly的GROUP基數則大于max ( distinct cardinality of colx , distinct cardinality of coly )且小于min ( (distinct cardinality of colx * distinct cardinality of coly) , base cardinality)。
成本：就是度量資源消耗的單位。可以理解為執行表掃描、索引掃描、連接、排序等操作所消耗I/O、CPU、內存的數量。
計劃生成器
計劃生成器的作用就是生成大量的執行計劃，然后選擇其中總體成本最低的一個。
由于不同的訪問路徑、連接方式和連接順序可以任意組合，雖然以不同的方式訪問和處理數據，但是可以產生同樣的結果，因此一個SQL可能存在大量不同的執行計劃。但實際上計劃生成器很少會試驗所有的可能存在的執行計劃，如果它發現當前執行計劃的成本已經很低了，它將停止試驗，相反當前計劃的成本如果很高，它將繼續試驗其他執行計劃，因此如果能使計劃生成器一開始就找到成本很低的執行計劃，則會大量減少所消耗的時間，這也正是我們為什么用HINTS來優化SQL的原因之一。
優化器模式及優化目標
除了上述的CBO優化器外，Oracle還有一種基于規則的RBO優化器，在8i以后Oracle就不再發展RBO了，有很多新特性在RBO中也不被支持，在最新的10g中RBO已被徹底廢除。在10g前RBO與CBO共存，用戶可以通過設置初始化參數OPTIMIZER_MODE來決定到底使用哪個優化器，也可以用ALTER SESSION來改變當前SESSION中OPTIMIZER_MODE的值。除此之外在SQL中嵌入HINTS可以指定具體某個SQL使用哪個優化器。
CBO雖然是基于成本的優化器，但仍然允許以“時間”或者說“響應速度”為優化目標，通過設置OPTIMIZER_MODE或者對具體語句嵌入HINTS都可以指定優化目標。
OPTIMIZER_MODE選項如下：
- l ALL_ROWS
- l FIRST_ROWS_n
- l FIRST_ROWS
- l CHOOSE
- l RULE
CHOOSE
僅在9i及之前版本中被支持，10g已經廢除。8i及9i中為默認值。
這個值表示SQL語句既可以使用RBO優化器也可以使用CBO優化器，而決定該SQL到底使用哪個優化器的唯一因素是，所訪問的對象是否存在統計信息。如果所訪問的全部對象都存在統計信息，則使用CBO優化器優化SQL；如果只有部分對象存在統計信息，也仍然使用CBO優化器優化SQL，優化器會為不存在統計信息對象依據一些內在信息（如分配給該對象的數據塊）來生成統計信息，只是這樣生成的統計信息可能不準確，而導致產生不理想的執行計劃；如果全部對象都無統計信息，則使用RBO來優化該SQL語句。
RULE
僅在9i及之前版本中被支持，10g已經廢除。
不論是否存在統計信息，都將使用RBO優化器來優化SQL。
ALL_ROWS
在10g中為默認值。
不論是否存在統計信息，都使用CBO優化器，且把CBO的優化目標設定為“最小的成本”。
FIRST_ROWS
CBO盡可能快速的返回結果集的前面少數行記錄。
不論是否存在統計信息，都使用CBO優化器，FIRST_ROWS導致CBO使用“試探法”來產生執行計劃，這種方式其成本可能會稍大一些。
FIRST_ROWS_n
不論是否存在統計信息，都使用CBO優化器，并以最快的速度返回前n行記錄，n可以是1,10,100,1000。
影響優化器模式及目標的HINTS：
- l RULE：意義同OPTIMIZER_MODE=RULE區別在于HINTS作用在語句級，10g中該HINTS已被廢棄。
- l CHOOSE：意義同OPTIMIZER_MODE=CHOOSE，10g中已被廢棄。
- l FIRST_ROWS：意義同OPTIMIZER_MODE=FIRST_ROWS，10g中已被廢棄。
- l ALL_ROWS：意義同OPTIMIZER_MODE=ALL_ROWS。
- l FIRST_ROWS(n)：意義同OPTIMIZER_MODE=FIRST_ROWS_n。
- l CPU_COSTING：啟用CPU成本計算，也就是在總成本中考慮CPU的成本，缺省是啟用的。該HINTS是10g中新增加的。
- l NO_CPU_COSTING：關閉CPU成本計算，也就是在總成本中不考慮CPU的成本，只計算I/O的成本。該HINTS也是10g中新增加的。
訪問路徑
訪問路徑就是從數據庫里檢索數據的方式。優化器首先檢查WHERE子句和FROM子句的條件，確定有哪些訪問路徑是可用的。然后優化器使用這些訪問路徑或各訪問路徑的聯合，產生一組可能存在的執行計劃，再通過索引、字段、表的統計信息評估每個計劃的成本，最后優化器選擇成本最低的執行計劃所對應的訪問路徑。
如果SQL語句的FROM子句無SAMPLE或SAMPLE BLOCK，優化器在選擇訪問路徑的時候會優先考慮語句中的HINTS。
優化器可用的訪問路徑如下：
- l 全表掃描（Full Table Scans）
- l Rowid掃描（Rowid Scans）
- l 索引掃描（Index Scans）
- l 簇掃描（Cluster Scans）
- l 散列掃描（Hash Scans）
- l 表取樣掃描（Sample Table Scans）
全表掃描
全表掃描將讀取HWM之下的所有數據塊，所有行都要經WHERE子句過濾看是否滿足條件。當Oracle執行全表掃描時會按順序讀取每個塊且只讀一次，如果能夠一次讀取多個塊，可以有效的提高效率，初始化參數DB_FILE_MULTIBLOCK_READ_COUNT用來設置在一次I/O中可以讀取多少個數據塊。 通常我們認為應該避免全表掃描，但是在檢索大量數據時全表掃描優于索引掃描，這正是因為全表掃描可以在一次I/O中讀卻多個塊，從而減少了I/O的次數。在使用全表掃描的同時也可以使用并行來提高掃描的速度。
CBO優化器何時會選擇全表掃描：
1） 無合適的索引。
2） 檢索表中絕大多數的數據。
3） 表非常小。比如，表中的塊小于DB_FILE_MULTIBLOCK_READ_COUNT，只需一次I/O。如果這樣的表被頻繁使用應該alter table table_name storage(buffer_pool keep)。
4） 高并行度。如果在表級設置了較高的并行度，如alter table table_name parallel(degree 10)，通常會使CBO選擇全表掃描。通常建議在語句級用HINTS來實現并行，如/*+full(table_name) parallel(table_name degree)*/。
5） 太舊的統計數據。如果表沒有進行過分析或很久沒有再次分析，CBO可能會錯誤的認為表含有及少的數據塊。
6） 在語句中嵌入了全表掃描的HINTS。
Rowid掃描
Rowid表示行在數據塊中的具體位置，Rowid是查找具體行的最快方式。可以在WHERE子句中寫入Rowid，但是不推薦這么做。通常都是通過索引來獲得Rowid，但如果被檢索的行都包含在索引中時，直接訪問索引就能得到所需的數據則不會使用Rowid。
索引掃描
索引不僅包含被索引的字段值，還包含行的位置標識Rowid，如果語句只檢索索引字段，Oracle將直接從索引中讀取而不需要通過Rowid去訪問表，如果語句通過索引檢索其他字段值，則Oracle通過索引獲得Rowid從而迅速找到具體的行。
索引掃描類型：
1） 唯一索引掃描（Index Unique Scans）
2） 索引范圍掃描（Index Range Scans）
3） 索引降序范圍掃描（Index Range Scans Descending）
4） 跳躍式索引掃描（Index Skip Scans）
5） 全索引掃描（Full Index Scans）
6） 快速全索引掃描（Fast Full Index Scans）
7） 索引連接（Index Joins）
在解釋上述索引掃描類型之前，首先要明確一個問題——Oracle對I/O的評估是針對“塊”的而不是“行”。優化器在決定是使用全表掃描還是索引掃描時，看的是所涉及塊占全表的比例而不是檢索的行占表的比例。當然如果一個塊中只包含一個行數據，那么訪問塊和行是等同的，但是通常情況下都是一個塊中含有多個行的數據，因此如果檢索的行都聚集在少數塊中則會大大降低I/O。
例如：一個有9行數據的表占據三個數據塊，在STATUS字段上有一個非唯一索引，該字段共有三類不同的值分別是1、2、3。
第一種情況：
???????????????? Block 1?????? Block 2??????? Block 3 ???????????????? -------?????? -------??????? -------- ???????????????? 1? 1? 1?????? 2? 2? 2??????? 3? 3? 3??索引字段（STATUS）相同的值都聚集在表的同一個物理塊中，這種情況下獲取STATUS=1的數據只需讀取表的一個物理塊，即一次I/O。
第二種情況：
???????????????? Block 1?????? Block 2??????? Block 3 ???????????????? -------?????? -------??????? -------- ???????????????? 1? 2? 3?????? 1? 2? 3??????? 1? 2? 3索引字段（STATUS）相同的值被分散在表中三個物理塊，這時要得到STATUS=1的數據則要讀取表的三個物理塊，即三次I/O才能獲得。
1）唯一索引掃描
在利用一個主鍵字段或含有唯一約束的字段選擇一行記錄時，通常發生唯一索引掃描。
2）索引范圍掃描
索引范圍掃描返回的數據返照索引字段值升序排列，值相同的按Rowid升序排列。如果在語句中使用了ORDER BY ASC子句，而且排序字段是索引字段時Oracle不會對ORDER BY再次排序。
例如：
SQL> select * from t;
?????? COLX????????????? COLY
--------------- ---------------
???????? 1?????????????????? 3
???????? 1?????????????????? 2
???????? 1?????????????????? 1
???????? 1?????????????????? 0
SQL> create index ind_t on t(coly);
SQL> set autotrace on
SQL> select * from t where coly>0;
????? COLX??????????????? COLY
--------------- ---------------
???????? 1??????????????????? 1
???????? 1??????????????????? 2
???????? 1??????????????????? 3
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T'
2 1 INDEX (RANGE SCAN) OF 'IND_T' (NON-UNIQUE)
沒有使用ORDER BY結果集已經是按COLY升序排列。
SQL> set autotrace traceonly
SQL> select * from t where coly>0 order by coly;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T'
2 1 INDEX (RANGE SCAN) OF 'IND_T' (NON-UNIQUE)
可以看到執行計劃中無SORT 步驟，說明Oracle忽略了ORDER BY子句。
優化器在下列情況會使用索引范圍掃描：
→ COL1 = :b1
→ COL1 > :b1
→ COL1 < :b1
→ COL1 LIKE ‘ABC%’會做索引范圍掃描，而COL1 LIKE ‘%ABC’則不會。
→ 對于復合索引，通常只有復合索引的第一個字段包含在AND條件之中時才會使用復合索引。
3）索引降序范圍掃描
如果在order by中指定了索引是降序排列的，或者使用了INDEX_DESC提示，優化器會使用索引降序范圍掃描。
例如：
SQL> select /*+index_desc(t ind_t)*/colx,coly from t where coly<3;
???? COLX??????????????? COLY
--------------- ---------------
?????? 1????????????????????? 2
?????? 1????????????????????? 1
?????? 1????????????????????? 0
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=4 Bytes=104)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=4 Bytes=104)
2 1 INDEX (RANGE SCAN DESCENDING) OF 'IND_T' (NON-UNIQUE) (Cost=2 Card=1)
4）跳躍式索引掃描
跳躍式索引掃描是用來提高復合索引效率的，通常當復合索引的第一個索引字段不在語句中指定時是無法使用復合索引的，此時如果復合索引的第一個索引字段DISTINCT值非常小，而復合索引的其他索引字段DISTINCT值非常大時，可以使用跳躍式索引掃描來跳過該復合索引的第一個索引字段。跳躍式掃描會使復合索引在邏輯上分裂成N個較小的索引，N值等于復合索引的第一個索引字段的DISTINCT值。
例如：
SQL> select* from employees;
? SEX???????? EMPLOYEE_ID?????????? ADDRESS
------ -------------------- --------------------
?? F?????????????? 98???????????????????????? ABC
?? F?????????????? 100?????????????????????? ABC
?? F?????????????? 102?????????????????????? ABC
?? F?????????????? 104?????????????????????? ABC
?? M????????????? 101?????????????????????? ABC
?? M????????????? 103?????????????????????? ABC
?? M????????????? 105?????????????????????? ABC
SQL> create index ind_sex_empid on employees(sex,employee_id);
SQL>set autotrace traceonly
SQL>select/*+index_ss(employees ind_sex_empid)*/* from employees where employee_id=101;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=11)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES' (Cost=3 Card=1 Bytes=11)
2 1 INDEX (SKIP SCAN) OF 'IND_SEX_EMPID' (NON-UNIQUE) (Cost=2 Card=1)
索引IND_SEX_EMPID結構如下：
5）全索引掃描
當查詢涉及的字段都包含在索引中，如果WHERE子句中謂詞非第一個索引字段，或無WHERE子句但是被索引字段中至少有一個非空屬性時，通常會做全索引掃描。全索引掃描結果集按索引字段排序。
6）快速全索引掃描
當查詢涉及的字段都包含在索引中，且被索引字段中至少有一個非空屬性時，可以使用INDEX_FFS(table_name index_name)來使語句做快速全索引掃描。快速全索引掃描不同于全索引掃描，它使用多塊讀取的方式來讀全部索引塊，而且可以使用并行讀取。快速全索引掃描的結果集不會排序。位圖索引不能使用快速全索引掃描。
7）索引連接
索引連接是幾個索引的散列連接。如果查詢的字段上都存在索引，可以使用索引連接來避免訪問表。
例如：
SQL> select* from t;
??????? COL1?????????????? COL2????????????? COL3????????????? COL4
--------------- --------------- --------------- ---------------
???????? 1???????????????????? 2??????????????????? 3?????????????????? 4
???????? 1???????????????????? 1??????????????????? 3?????????????????? 4
???????? 1???????????????????? 1??????????????????? 5?????????????????? 4
SQL> create index ind_col1 on t(col1);
索引已創建。
SQL> create index ind_col2 on t(col2);
索引已創建。
SQL> create index ind_col3 on t(col3);
索引已創建。
不使用索引連接：
SQL> set autotrace traceonly
SQL> select col1,col2,col3 from t where col2>0;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T'
2 1 INDEX (RANGE SCAN) OF 'IND_COL2' (NON-UNIQUE)
使用索引連接：
SQL> select /*+index_join(t ind_col1 ind_col2 ind_col3)*/col1,col2,col3 from t where col2>0;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=70 Card=4 Bytes=156)
1 0 VIEW OF 'index$_join$_001' (Cost=70 Card=4 Bytes=156)
2 1 HASH JOIN
3 2 HASH JOIN
4 3 INDEX (RANGE SCAN) OF 'IND_COL2' (NON-UNIQUE) (Cost=3 Card=4 Bytes=156)
5 3 INDEX (FAST FULL SCAN) OF 'IND_COL1' (NON-UNIQUE) (Cost=3 Card=4 Bytes=156)
6 2 INDEX (FAST FULL SCAN) OF 'IND_COL3' (NON-UNIQUE) (Cost=3 Card=4 Bytes=156)
簇掃描
在被索引的簇中，有著相同簇鍵值的行存儲在同一數據塊中。執行簇掃描時，首先通過掃描簇索引獲得被檢索行的Rowid，然后使用Rowid來定位具體的行。
散列掃描
散列掃描就是在一個散列簇中定位數據行。在一個散列簇中，具有相同散列值的行存儲在相同的數據塊中。在執行散列掃描時，首先通過一個散列函數來獲得散列值，然后用散列值在數據塊中定位具體行。
表取樣掃描
當FROM子句后帶有SAMPLE或SAMPLE BLOCK時，會執行表取樣掃描來隨機檢索表中的數據。如：select* from t sample block (1);
處理連接
影響一個連接語句執行計劃的四個重要因素是：訪問路徑、連接方式、連接順序和成本評估。
優化器如何處理連接順序
優化器首先確定連接的表中是否包含其結果只有一行記錄的表，如果存在這樣的表，優化器在對連接表排序時會把這樣的表放在最前端。如果是個外連接，含有（+）操作符的表一定排在不含（+）的表的后面。同理被轉換成ANTI-JOIN或SEMI-JOIN的子查詢，子查詢的表一定排在外部表的后面，但是HASH-ANTI-JOIN和HASH-SEMI-JOIN在一定情況下可以違反此順序。通常可以用ORDERED來指定連接順序，但是ORDERED所指定的順序如果違反了外連接的順序，則ORDERED將被忽略。
優化器如何評估成本
嵌套循環連接，在于外表返回的每一行都要在內表中進行匹配的成本，成本計算如下：
cost of nested loop = access cost of outer + (number of rows from outer * access cost of inner)
排序合并連接，在于把兩個大表讀入內存并進行排序的成本，成本計算如下：
cost of merge join = access cost of A + access cost of B + (sort cost of A + sort cost of B)
散列連接，在于將小表讀入內存分成若干散列表，然后由大表對每個散列表都進行一次匹配的成本，成本計算如下：
cost of hash join = access cost of smaller + (access cost of bigger * number of hash partitions of smaller)
以上成本計算公式不是絕對的，優化器對成本的評估還會受到其他因素的影響，比如：內存排序區過小會增加排序合并連接的成本，由于此種情況下的排序消耗了過多的CPU和I/O。多塊讀取會降低排序合并連接的成本，如果內表的連接字段存在索引也會降低嵌套循環連接的成本。
優化器可用的連接方式
1） 嵌套循環連接：適用于外表有效基數較小，內表連接字段含有索引，且查詢整體返回結果集不太大（小于1萬行）的情況下。HINTS：use_nl
2） 散列連接：適用于查詢整體返回大量結果集，且有較小的連接表可以放入內存作為散列表的情況下。適用散列連接要注意HASH_AREA_SIZE要足夠大，可以容下散列表。如果散列表無法完全放入內存，要設置較大的臨時段，從而盡量提高I/O性能。HINTS：use_hash
3） 排序合并連接：適用于查詢整體返回大量結果集，兩個大表做連接，且表已經排過序的情況下。當兩個表已經排過序時，使用排序合并連接的性能可能會優于散列連接。HASH_AREA_SIZE和SORT_AREA_SIZE設置過小，可能會導致優化器避開散列連接而選擇排序合并連接。HINTS：use_merge
4） 迪卡爾積連接：當兩個表沒有任何連接條件時會使用此連接方式。
一些影響優化器的初始化參數
OPTIMIZER_FEATURES_ENABLE：每個版本的Oracle優化器特性都不相同，特別是做了版本升級以后一定要修改這個參數才可以使用僅被該版本支持的優化器特性。可以賦予它的值如：9.2.0、9.0.2、9.0.1、8.1.7、8.1.6等。
CURSOR_SHARING：這個參數會將SQL語句中的直接量用變量來替換，存在大批直接量的OLTP系統可以考慮啟用這個參數。但是要注意，綁定變量雖然可以使大量的SQL重用，減少分析時間，但是執行計劃可能會不理想。通常OLTP系統適用于綁定變量，OLTP系統特點是，SQL運行頻繁且時間相對較短，SQL的分析時間比重較大。如果在DSS系統中，SQL運行時間長，相比之下分析時間微不足道，好的執行計劃才是最重要的，因此DSS系統不建議使用這個參數。
HASH_AREA_SIZE：這是散列表的存放區域，如果使用散列連接這個參數值不能太小，否則對散列連接性能影響很大。如果是9i建議啟動工作區自動管理，然后設置PGA_AGGREGATE_TARGET。
SORT_AREA_SIZE：內存排序區的大小，如果排序時內存區不夠會寫入磁盤。9i同樣建議啟動工作區自動管理，然后設置PGA_AGGREGATE_TARGET。
HASH_JOIN_ENABLED：只有啟用這個參數，CBO在考慮連接方式的時候才會考慮散列連接。
OPTIMIZER_INDEX_CACHING：這個參數表示被緩存的索引塊所占的百分比，可選值的范圍是0-100。這個值會影響嵌套循環連接，如果這個值設得較高，CBO將更傾向使用嵌套循環。
OPTIMIZER_INDEX_COST_ADJ：優化器利用這個參數（是個百分比）把索引掃描的成本轉換為等價的全表掃描的成本，然后與全表掃描的成本進行比較。缺省值100，表示索引掃描成本與全表掃描成本等價。可選值范圍是0-10000。
OPTIMIZER_MAX_PERMUTATIONS：這個初始參數用來設定優化器最多考慮多少種連接順序，優化器不斷的產生可能的表的連接的排列，直到排列數達到參數optimizer_max_permutations為止。一旦優化器停止產生新的排列，它將會從中選擇出成本最小的排列。
DB_FILE_MULTIBLOCK_READ_COUNT：這個參數表示在全表掃描或索引快速全掃描時一次I/O讀的連續數據塊數量（block#連續，且一次I/O不能超過extent）。
OPTIMIZER_MODE：優化器模式。值為：RULE、CHOOSE、ALL_ROWS、FIRST_ROWS_n、FIRST_ROWS。
PARTITION_VIEW_ENABLED：如果設置為TRUE, 該優化器將跳過分區視圖中未被請求的分區，該參數還能更改基于成本的優化程序從基礎表統計信息計算分區視圖統計信息的方式。
QUERY_REWRITE_ENABLE：如果設置為TRUE，優化器將利用可用的物化視圖來重寫SQL。
總結
以上是生活随笔為你收集整理的Oracle优化器介绍的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 七夕怕被偷窥？摄像头探测器来帮您！
- 下一篇： DIALOGBOXPARAMA