優化器

eric0435發表於2012-10-14
優化
Oracle 的優化器（Optimizer）實際上是資料庫環境的引數設定。可以在INITsid.ORA 檔案內的
OPTIMZER_MODE=RULE 或OPTIMZER_MODE=COST 或OPTIMZER_MODE=CHOOSE 來
設定優化目標。使用者也可以在會話和查詢方式下更改優化器的預設操作模式。
如果OPTIMZER_MODE=RULE，則啟用基於規則的優化器(RBO)。基於規則的優化器按照一
系列的語法規則來推測可能執行路徑和比較可替換的執行路徑。
如果OPTIMZER_MODE=COST，則啟用基於成本的優化器(CBO)。它使用ANALYZE 語句來
生成資料庫物件的統計資料。這些統計資料包括表的行數、平均長度及索引中不同的關鍵字
數等。基於這些統計資料，成本優化器可以計算出可獲得的執行路徑的成本。並選擇具有最
小的成本執行路徑。在CBO 模式下，需要經常執行ANALYZE 命令來確保資料的準確性。
如果OPTIMZER_MODE=CHOOSE，則在表被分析的情況下啟用基於成本的優化器。但當一
個查詢分析的表是未被ANALYZE 分析統計過的時侯，CBO 優化器就決定進行全表掃描操
作。所以為了減少可能的全表掃描，應該儘量避免使用OPTIMZER_MODE=CHOOSE 選項。
下面介紹在程式設計序中常用到的語句優化。
討論SQL 處理技術、優化方法以及優化程式如何執行SQL 語句。包括：
 SQL 處理體系結構
 EXPLAIN PLAN
 優化程式的定義
 選擇優化程式的方法和目標
 基於開銷的優化程式(CBO=Cost_Base Optimizer)
 CBO 引數
 可擴充套件的優化程式
 基於規則的優花程式(RBO=Rule_Base Optimizer)
 優化程式操作概述
   優化連線（Join）
 優化使用公共子表示式的語句
 表達及條件的評價
 轉換及優化的語句

SQL 處理體系結構主要有：
 解析程式
 優化程式
 行源產生程式
 SQL 執行

解析程式執行下面兩種功能：
 語法分析：檢查SQL 語句的語法正確性
 語義分析：例如檢查當前資料庫物件和相應的物件屬性，並判斷其準確性。

優化器是SQL 處理引擎的核心，Oracle 伺服器支援兩種優化方法：基於規則的優化和基
於開銷的優化程式

行源程式產生器(Row Source generator)從優化程式接受優化規則，並輸出SQL 語句的執
行規則。執行規則由行源的集合所組成，它呈現出樹型結構。行源程式是一種迭代的控制結
構，它以迭代的方式對執行集合進行處理，並且每次只處理一次

SQL 執行（Execution）是操作執行規則時的元件，它與SQL 語句緊密相連，並且可以給出查
詢的結果。

通過使用EXPLAIN PLAN 語句，使用者可以對SQL 語句優化程式所選擇的執行規劃進行
檢查。只要發出EXPLAIN PLAN 語句，就可以對輸出表進行查詢，下面是輸出表的資料描述
先前所檢查的語句：
ID   OPERATION                                 OPTION                     OBJECT_NAME
----- ----------------------------------------- ------------------------- -----------------------
0 SELECT STATEMENT
1 FILTER
2 NESTED LOOPS
3 TABLE ACCESS                                     FULL                         EMP
4 TABLE ACCESS                                     BY ROWID                DEPT
5 INDEX                                                  UNIQUNE SCAN        PK_DEPTNO
6 TABLE ACCESS                                     FULL                         SALGRADE

預設方式下，基於代價（CBO）是最好的方法。它使用最少的資源就能對所有行進行處
理。Oracle也可以使用時間響應來優化語句。如： SQL語句並行執行。優化器可以選擇最小共
用資源消費開支時間。它可以由引數OPTIMIZER_PERCENT_PARALLEL來指定並行執行的優
化器的個數。
執行計劃由優化器根據目標來產生。最大吞吐量優化類似於寧願不要索引的全表掃描的結果
或者相當於不要巢狀迴圈的分類合併連線。然而最佳響應時間的優化有點類似於索引掃描或
巢狀迴圈連線。
例如你可以在任意的巢狀迴圈操作中或分類合併操作中使用連線語句。當巢狀迴圈操作快速
返回第一行時，這些分類合併語句也可以快速返回整個結果。如果你的目標是改善吞吐量。
那麼優化器就有點象選擇分類合併連線操作。如果你的目標是選擇改善響應時間，則優化器
更象選擇一個巢狀連線操作。
綜合上面的描述，選擇優化器主要是基 於應用的需要：
 對於批處理應用，如報表輸出應用，需要採用吞吐量優化。因為吞吐量對於批處理來
說更重要。
 對於互動式應用，如Oracle Forms 應用或SQL*PLUS查詢，需要採用最佳時間響應優
化。因為互動式使用者等著看到第一行的資料。
 對於用ROWNUM來限制查詢結果的查詢，優化首先要考慮的響應時間。因為它要求
的是得到最快的結果。
當選擇最佳路徑和目標時，優化器受到下面因素的影響：
　?OPTIMIZER_MODE 初始引數
　?資料字典的統計資料
　? ALTER SESSION 語句的OPTIMIZER_GOAL 引數
　? 在提示中改變目標

OPTIMIZER_MODE 初始引數
可以用OPTIMIZER_MODE來建立優化路徑的預設值,它可以取下面的值：
choose 表示優化器在基於代價和基於規則兩種之間進行選擇。如果資料字典有訪問表的至少
一行的統計資料，則優化器使用的基於代價和最佳吞吐量方法。如果訪問表沒有統計資料，
則優化使用基於規則的路徑。預設為基於規則的路徑。
All_rows 對整個SQL語句，優化器使用基於代價的路徑。使用最小資源返回整個行。
FIRST_ROWS 對整個SQL語句，優化器使用基於代價的路徑。使用最小資源返回第一行。
RULE 對整個SQL語句，優化器使用基於規則的路徑。
如果優化使用基於代價的路徑，而訪問的表沒有統計資料時，優化器就使用該表的一些近似
值來代替。

資料字典中的統計資料
Oracle 為CBO 儲存有列、表、簇 、索引及分割槽的統計資料。可以使用ANALYZE 語句或
COMPUTE STATISTICS 子句和DBMS_STATS 包來得到詳細的統計結果。為了給優化提供最
新的資料，你應該經常使用ANALYZE 語句對錶進行統計

ALTER SESSION 語句的OPTIMIZER_GOAL 引數
ALTER SESSION 語句中帶OPTIMIZER_GOAL 引數可以越過初始化路徑和
OPTIMIZER_MODE 引數所建立的目標。這個引數的結果影響到SQL 語句的優化。但它對會
話中所使用SQL 語句的遞迴不起作用。OPTIMIZER_GOAL 可以有下面的值：
choose 表示優化器在基於代價和基於規則兩種之間進行選擇。如果資料字典有訪問表的至少
一行的統計資料，則優化器使用的基於代價和最佳吞吐量方法。如果訪問表沒有統計資料，
則優化使用基於規則的路徑。預設為基於規則的路徑。
All_rows 對整個SQL語句，優化器使用基於代價的路徑。使用最小資源返回整個行。
FIRST_ROWS 對整個SQL語句，優化器使用基於代價的路徑。使用最小資源返回第一行。
RULE 對整個SQL語句，優化器使用基於規則的路徑。

關於提示的改變目標
可以在單個的SQL 語句中，使用FIRST_ROWS, ALL_ROWS, CHOOSE, 或 RULE 可以替代由
OPTIMIZER_MODE 初始引數和ALTER SESSION 的OPTIMIZER_GOAL 引數所設定的效果。
在預設下，系統使用的是基於最佳吞吐量的代價的優化。但可以用下面方法來改變CBO 的目
標：
 在會話中為CBO改變所有的SQL語句目標，使用：
ALTER SESSION SET OPTIMIZER_MODE 語句帶 ALL_ROWS 或FIRST_ROWS子句。
 為單個SQL語句指定CBO的目標。使用 ALL_ROWS 或 FIRST_ROWS 提示。如：
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;


基於代價優化器（CBO）
CBO在考慮訪問路徑和模式的統計資料（表或索引）來確定哪種執行計劃最有效。同時CBO
也考慮提示語句中哪種是最優的建議。CBO 考慮下面步驟：
1. 優化器在它的有效路徑和提示中為SQL語句產生一組潛在的計劃。
2. 優化器根據資料字典的統計估計每個計劃的代價，並儲存表、索引和分割槽的特徵。
代價（cost）是預期資源要求的估計值。優化器計算每一種訪問方法的代價並基於估計計算機
資源（包括I/O和記憶體）的次序。從而得到執行該語句的需求。
較大的代價需要較多時間來執行，而較小的計劃需要較少的時間來執行。當使用並行時，資
源不直接與消失的時間有關。
3. 優化器比較這些代價然後選擇較小的代價。
維護CBO的有效性，必須收集有關的統計並使它們準確才行。可以用下面方法來收集統計數
據：
 用ANALYZE 語句；
 使用DBMS_STATS 包。

CBO 結構調整
CBO包括下面部件：
 查詢轉換器
 評價器
 規劃產生程式

CBO 需求
要採用基於CBO的優化，要求下面的任何一種特徵：
 分割槽的表
 索引構造的表
 逆鍵字索引
 基於函式的索引
   在SELECT語句中的SAMPLE子句
 並行執行和並行DML
 星形轉換
 星形連線
 可執行的優化器
 查詢重寫(實體化檢視)
 進展儀表(Progress meter)
 HASH 連線
 點陣圖索引

使用CBO
要在語句中使用CBO，就要收集表的統計資料，並用下面方法來使CBO有效：
 確認OPTIMIZER_MODE 初始引數設定為 CHOOSE.
 在會話中啟動CBO ，要用 ALTER SESSION SET OPTIMIZER_MODE 語句並帶
ALL_ROWS 或FIRST_ROWS 子句。
 在一個單獨的SQL語句中使用CBO，要用提示而不用RULE。
由CBO根據表的大小來產生規劃，如果使用直方圖情況下，還要產生的潛在的資料分佈。當
通過具有少量資料的CBO來進行應用程式的原型測試時，不要認為實際資料庫選擇的規劃與
原型資料庫所選擇的規劃就一定一致。

CBO 訪問路徑
明確地描述執行計劃如何從資料庫返回資料是優化器的最重要選擇之一。對於任何表的任意
行，該表的行的定位和返回都有許多的訪問路徑。優化器可從中選擇一個。下面是Oracle訪問
資料的基本方法：
全表掃描
全表掃描是從表中返回所有的行。執行全表掃描，Oracle從該表讀所有的行。檢查每一行，看
它是否滿足where 子句的要求。實際上Oracle是讀表中相繼的資料塊。所以它可以採用多塊
讀。
樣本表掃描
Oracle在Oracle8i Release 8.1.6 版本之後，提供了在select 語句後加 SAMPLE BLOCK 來對錶進
行部分的掃描查詢。這種查詢要求是隻能對單表進行，不能在連線查詢上使用樣本掃描。也
不能在遠端進行樣本掃描。當然，如果希望在遠端上進行樣本掃描的話，可以先用CREATE
TABLE AS SELECT 語句將遠端表複製到本地，然後在使用 SAMPLE BLOCK SAMPLE
BLOCK 語句。

例：掃描emp表的1%的內容，則發出：
select count(*)*100 from emp sample block(1);
或
SELECT * FROM emp SAMPLE BLOCK (1);
用Rowid訪問表
用Oracle的rowid 也能訪問到表的記錄。每個行的rowid 能確定資料檔案、行所在資料塊及該行
所在的塊位置。可以用rowid 來快速定位到一個單行。
簇(Cluster)掃描
對於以索引簇形式存放的表，簇掃描能夠從中獲得具有相同簇鍵(cluster key)值的行。在索引
化的簇中，所有具有相同簇鍵值的行都被儲存進相同的資料庫塊中。為了執行簇掃描，Oracle
首先通過掃描簇的索引，並從 中得到所選擇的rowid 值，然後再基於該rowid 對所有選擇行進
行定位。

基於規則(RBO)的優化程式
儘管Oracle 支援基於RBO(Rule-Based Optimzer)的優化程式，但建議大多數系統還是採用
基於CBO 的優化。因為CBO 支援DSS 的某些新增強的功能，所以對於象資料倉儲應用系統
也要採用CBO 方法。
如果滿足：
１） 設定 OPTIMIZER_MODE=CHOOSE;
２） 資料字典中沒有統計資訊；
３） 未給SQL 語句加提示。
則可以採用RBO。
如果是：
１） OPTIMIZER_MODE=FIRST_ROWS 或ALL_ROWS ;
２） 不存在可用的統計資訊；
則CBO 使用預設值

RBO 訪問路徑
如果使用基於RBO，則在執行規則中，訪問路徑的等級具有啟發性，即如果存在多種可執行
的SQL 語句，則RBO 選擇等級較低的操作，因為等級低的語句執行速度快。下面是RBO 訪
問路徑及等級：
 路徑1：根據rowid 訪問行
 路徑2：通過cluster 聯結訪問行
 路徑3：根據唯一性主鍵或主鍵的雜湊簇訪問單行
 路徑4：根據唯一鍵或主鍵訪問單行
 路徑5：cluster 連線
 路徑6：雜湊cluster 鍵
 路徑7：索引化cluster 鍵
 路徑8：複合索引
 路徑9：單列索引
 路徑10：索引化有界搜尋
 路徑11：索引化無界搜尋
 路徑12：合併排序聯結
 路徑13：帶有MAX 或MIN 的列
 路徑14：帶ORDER BY 的查詢
 路徑15：全表掃描


優化器操作
Oracle 接收到一條SQL 語句後，首先進行句法分析，檢查文字並對該查詢產生可執行的方案
（plan）。這個可執行的方案有EXPLAIN PLAN 來處理，方案確定後語句就被存放在SQL 共
享區內。而Oracle 的優化器是用來確當對特定語句的最佳執行路徑。Oracle 的優化器可以有
兩種型別：即基於規則和基於代價。下面是優化程式能進行優化的SQL 語句。

可優化的SQL 語句
 簡單的SQL 語句，即只設計單個表的insert,update,select
 簡單的查詢
 等式連線
 非等式連線
 外連線
 笛卡爾乘積
 複合語句
 組合查詢
 訪問檢視
 分散式語句

優化程式操作
優化程式自動簡化ＳＱＬ語句中的某些常用結構，如果結果簡化執行的話，這些語句變
得非常簡單，如2000/10 簡化為 200 。也可能變複雜，如將帶 OR 的運算語句轉換為兩個復
合的子查詢。對於前者可以隨時進行，但後者則取決於where 子句的列上是否有索引以及選
擇哪種優化方法。
此外，還有一些其它的轉換，包括：
 化簡算術表示式
 將IN 轉化為一系列的OR 條件
 將一個BETWEEN … AND 轉換為一對比較表示式
 將 OR 轉換為複合查詢
 檢視的定義合併到條件語句中
 將一個複雜語句轉換成連線條件語句

基於規則或基於代價優化方法
Oracle 按照若干準則對每個語句進行優化，包括：
 引用的物件有一個並行度
 語句中的提示
 OPTIMZER_GOAL 的會話設定
 初始化引數OPTIMZER_MODE 的值
 被引用的物件的統計結果

通過優化程式的分析，決定執行是採用基於規則或基於代價方法。考察優化的首要因素是看
語句是否存在一次全表掃描和是否有並行（是否包括PARALLEL 選項）。如果都有的話，就
使用基於代價的優化來建立一個包含並行的執行計劃。
基於規則的方法不支援並行處理語句的執行計劃。如果沒有rule 語句的提示，Oracle 都採用基
於代價方法進行優化

優化連線
Oracle 提供了4 種連線操作：
 巢狀迴圈連線（nested loop）
 合併排序連線（merge join）
 哈西連線(Hash join)
 簇連線（cluster join）

巢狀連線
巢狀迴圈連線（NESTED LOOPS）操作是將兩個資料來源連線起來。通常是在連線時系統提供
了可用的索引。實際在處理過程中，Oracle 先處理第1 行，接著下一行（不是等到整個處理完
畢才返回第1 行）。在處理巢狀連線時，Oracle 優化器首先為連線選擇一個驅動表 ，可能對驅
動表進行全表掃描。對驅動表的每行進行索引檢查，以便了解在表之間是否存在匹配。如果
有一個匹配存在，則通過NESTED LOOPS 操作並將該記錄返回給使用者。
例如：
SQL> explain plan for
select a.deptno,a.dname,b.empno,b.ename,b.sal
from dept a, emp b where a.deptno=b.deptno and a.deptno=20;
Explained.
SQL> select operation,options,object_name from plan_table;
OPERATION OPTIONS OBJECT_NAME
------------------ ------------------ ------------
SELECT STATEMENT
MERGE JOIN
TABLE ACCESS BY INDEX ROWID DEPT
INDEX UNIQUE SCAN PK_DEPT
FILTER
TABLE ACCESS FULL EMP
6 rows selected.
SQL> explain plan for
select a.deptno,a.dname,b.empno,b.ename,b.sal
from dept a, emp b where a.deptno=b.deptno and b.empno=7369;
Explained.
SQL> select operation,options,object_name from plan_table;
OPERATION OPTIONS OBJECT_NAME
------------------ ------------------ ------------
SELECT STATEMENT
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID EMP
INDEX UNIQUE SCAN PK_EMP
TABLE ACCESS BY INDEX ROWID DEPT
INDEX UNIQUE SCAN PK_DEPT
6 rows selected.
在看為等價連線使用索引的例子：
SQL> select dept_id, id
2 from s_emp, s_dept
3 where s_emp.dept_id = s_dept.id;
這樣Oracle 優化器使用巢狀迴圈連線替代排序合併連線。巢狀迴圈連線不需要任何的排序操
作，所以操作步驟為：
1 執行S_EMP 表全表掃描
2.每個返回行都使用DEPT_ID值，以在PK_ID索引上進行唯一掃描
3.使用從索引中返回的ROWID來定位S_DEPT表的相應行.
4. 為S_DEPT組合從S_EMP返回的每個行。
合併連線
排序合併連線用於從兩個獨立的資料來源進行連線。HASH 連線可能執行比排序合併連線更好。
但另一方面，排序連線可能在下面情況下執行比HASH 連線更好。
 行源資料已經做過排序；
 一個排序也沒有做。
然而，如果一個排序操作涉及到一個較慢的方法（如一個索引掃描對應全表掃描），則採用排
序合併可能會失策。
當連線條件是在兩個表之間使用不等式（如、>=）時，排序合併連線很有用。排序
合併連線執行比對大量資料進行巢狀迴圈連線更好(不要用HASH 連線，除非有等價條件)。
在合併連線裡，沒有驅動表概念，該連線主要考慮兩步：
１． 排序連線操作:連線鍵的輸入端都是被排序過；
２． 合併連線操作：排序列表要合併在一起。
例子：帶有非等價的排序連線
SELECT SUM(l.), l2.
FROM order_items l, order_items l2
WHERE l. < l2.
AND l.order_id <> l2.order_id
GROUP BY l2., l2.line_item_id ；
Plan
--------------------------------------------------
SELECT STATEMENT
SORT GROUP BY
MERGE JOIN
SORT JOIN
TABLE ACCESS FULL ORDER_ITEMS
FILTER
SORT JOIN
TABLE ACCESS FULL ORDER_ITEMS

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

相關文章