Oracle 學習之性能優化（八）優化器

　  我們知道，在sql語句解析的過程中，有一個過程叫優化。Oracle中有一個叫優化器的組件，專門來處理sql的優化。在考慮查詢條件和對象引用的許多相關因素后，優化器能確定出執行SQL語句最有效的方式來。對于任何SQL語句，優化器優化的結果,可以極大地影響執行時間。

  Oracle優化器的優化方法有兩種：

• CBO 基于成本的優化法則

• RBO 基于規則的優化法則
  

  初始化參數optimizer_mode控制著優化器優化的行為

SQL> show parameter optimizer_mode

NAME				     TYPE			       VALUE
------------------------------------ --------------------------------- ------------------------------
optimizer_mode			     string			       ALL_ROWS

optimizer_mode有如下五個取值

• CHOOSE 使用CBO還是RBO，基于統計信息是否存在，如果有統計系統則使用CBO，否則使用RBO。

• ALL_ROWS 基于CBO，采用盡快返回所有結果的一種最優執行計劃。

• FIRST_ROWS_n 基于CBO，盡快的返回前n行數據，n的取值為1,10,100,1000

• FIRST_ROWS 基于CBO和試探法相結合的方法，查找一種可以最快返回前面少數行的方法；這個參數主要用于向 后兼容。

• RULE 采用基于CBO的優化法則。

Oracle 11g的版本只有中間三個參數有效，并且不推薦使用FIRST_ROWS .

我們看看優化器對查詢的影響

SQL> alter system set optimizer_mode=all_rows;

System altered.

SQL> conn scott/tiger
Connected.
SQL> set autot traceonly exp
SQL> select * from emp,dept where emp.deptno=dept.deptno;

Execution Plan
----------------------------------------------------------
Plan hash value: 844388907

----------------------------------------------------------------------------------------
| Id  | Operation		     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |	       |    14 |   812 |     6	(17)| 00:00:01 |
|   1 |  MERGE JOIN		     |	       |    14 |   812 |     6	(17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2	 (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN	     | PK_DEPT |     4 |       |     1	 (0)| 00:00:01 |
|*  4 |   SORT JOIN		     |	       |    14 |   532 |     4	(25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL	     | EMP     |    14 |   532 |     3	 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
       filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

修改優化器模式后

SQL> alter session set optimizer_mode=first_rows_1;

Session altered.

SQL> select * from emp,dept where emp.deptno=dept.deptno;

Execution Plan
----------------------------------------------------------
Plan hash value: 3625962092

----------------------------------------------------------------------------------------
| Id  | Operation		     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |	       |     1 |    58 |     3	 (0)| 00:00:01 |
|   1 |  NESTED LOOPS		     |	       |       |       |	    |	       |
|   2 |   NESTED LOOPS		     |	       |     1 |    58 |     3	 (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL	     | EMP     |     1 |    38 |     2	 (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN	     | PK_DEPT |     1 |       |     0	 (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1	 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

CBO有如下三個組件構成

• Query Transformer

• Estimator

• Plan Generator

一、Query Transformer

  輸入部分是已經被解析器解析過的sql。

查詢轉換包括如下技術：

View Merging（視圖合并）

  查詢涉及到的每個視圖被parser展開，并且分離成一個一個的查詢塊。查詢塊本質上代表了視圖的定義。將其與查詢剩余部分合并成一個總的執行計劃，轉換后的語句基本上不包含視圖了。我們來舉例說明

假設有這樣一個視圖

CREATE VIEW employees_50_vw AS
  SELECT employee_id, last_name, job_id, salary, commission_pct, department_id
  FROM   employees
  WHERE  department_id = 50;

進行如下查詢

SELECT employee_id
FROM   employees_50_vw 
WHERE  employee_id > 150;

優化器轉換后，查詢變為

SELECT employee_id
FROM   employees
WHERE  department_id = 50 
AND    employee_id > 150;

Predicate Pushing（謂詞推進）

  將謂詞從內部查詢塊推進到一個不可合并的查詢塊中，這樣可以使得謂詞條件更早的被選擇，更早的過濾掉不需要的數據行，提高效率，同樣可以使用這種方式允許某些索引的使用。

假設有如下視圖

CREATE VIEW all_employees_vw AS
  ( SELECT employee_id, last_name, job_id, commission_pct, department_id
    FROM   employees )
  UNION
  ( SELECT employee_id, last_name, job_id, commission_pct, department_id
    FROM   contract_workers );

我們發出這樣的查詢

SELECT last_name
FROM   all_employees_vw
WHERE  department_id = 50;

轉換后

SELECT last_name
FROM   ( SELECT employee_id, last_name, job_id, commission_pct, department_id
         FROM   employees
         WHERE  department_id=50
         UNION
         SELECT employee_id, last_name, job_id, commission_pct, department_id
         FROM   contract_workers
         WHERE  department_id=50 );

Subquery Unnesting（子查詢解嵌套）

最典型的就是子查詢轉變為表連接了，它和視圖合并的主要區別就在于它的子查詢位于where子句，由轉換器進行解嵌套的檢測。

假設有這樣的一個查詢

SELECT * 
FROM   sales
WHERE  cust_id IN ( SELECT cust_id FROM customers );

查詢轉換后

SELECT sales.* 
FROM   sales, customers
WHERE  sales.cust_id = customers.cust_id;

Query Rewrite with Materialized Views

假設建立一個物化視圖

CREATE MATERIALIZED VIEW cal_month_sales_mv
  ENABLE QUERY REWRITE 
AS
  SELECT t.calendar_month_desc, SUM(s.amount_sold) AS dollars
  FROM   sales s, times t 
  WHERE  s.time_id = t.time_id
  GROUP BY t.calendar_month_desc;

執行如下查詢

SELECT t.calendar_month_desc, SUM(s.amount_sold)
FROM   sales s, times t
WHERE  s.time_id = t.time_id
GROUP BY t.calendar_month_desc;

查詢轉換后

SELECT calendar_month, dollars
FROM   cal_month_sales_mv;

二、Estimator

Estimator決定了一個給定的執行計劃的總成本。估計量生成三種不同類型的措施,以實現這一目標:

• Selectivity

  這里的第一個度量值——選擇性，表示sql命中的行數與行集的比值。所謂行集可以是表、視圖，或者是一個連接或GROUP BY操作的中間結果。選擇性與查詢中的謂詞有關，比如last_name=’Smith’，或者一個聯合謂詞last_name=’Smith’ and job_type=’Clerk’。一個謂詞充當著一個過濾器的角色，在行集中過濾了一定量的行，謂詞的選擇性是一個比值，它表示一個行集經過謂詞的過濾后剩下的行占原有行集的比例。其值在0.0和1.0之間，0.0表示在行集中沒有行被選擇；1.0表示行集中的所有行都被選擇了。如果沒有可用的統計信息，評估器為選擇性賦予一個內部的缺省值，這個內部缺省值隨著謂詞的不同而不同。例如：等式謂詞（last_name=’Smith’）的內部缺省值低于范圍謂詞（last_name>’Smith’），評估器會假定等式謂詞返回的行數小于范圍謂詞。當存在可用的統計信息，評估器將使用統計信息來估算選擇性。例如：對于一個等式謂詞（last_name=’Smith’），選擇性的值是distinct last_name的倒數即：（1/count(distinct last_name)）。但是如果在last_name字段上存在直方圖（histogram），則選擇性值為：coun（last_name）where last_name=’Smith’ / count（last_name）where last_name is not null。可見在數據傾斜的字段上應用直方圖能夠幫助CBO進行準確的選擇性評估

• Cardinality

   基數就是行集中行的數量。基數分為： 

   基礎基數（Base cardinality）：就是基表中的行數。基礎基數在表分析期間獲得。如果表沒有可用的統計信息，則評估器利用表中區（extents）的數量來估算基礎基數。 

   有效基數（Effective cardinality）：就是從基表中選擇的行數。有效基數與具體的謂詞和字段有關。有效基數是根據基礎基數和作用于該表的所有謂詞的選擇性得出的，如果沒有謂詞作用于該表，則有效基數就等于基礎基數。 

   連接基數（Join cardinality）：就是兩個行集在連接之后產生的行數。連接就是由兩個行集產生的笛卡爾積，再由連接謂詞過濾結果。因此，連接基數是兩個行集基數與連接謂詞選擇性的乘積。 

   Distinct基數（Distinct cardinality）：就是一個行集的字段distinct之后的行數。一個

行集的distinct基數是基于字段中的數據的。例如：一個擁有100行的行集，如果一個字段distinct之后還剩下20行，則distinct基數就為20。 

   Group基數（Group cardinality）：就是一個行集在應用GROUP BY之后產生行的數量。Group基數依賴于每個組中字段的distinct基數和行集的行數。

• Cost

   成本是用來描述工作單元或資源使用的。CBO是用磁盤I/O、CPU和內存的使用情況來作為工作單元的，因此CBO使用的成本可以描述為，在一次操作的執行過程中所用的磁盤I/O數量以及CPU和內存的總使用量。

三、Plan Generator

  主要功能是把給定的query生成各種可能的計劃，并且挑出成本最低的一個。