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

創(chuàng)新互聯(lián) - 成都服務(wù)器托管，四川服務(wù)器租用，成都服務(wù)器租用，四川網(wǎng)通托管,綿陽服務(wù)器托管，德陽服務(wù)器托管，遂寧服務(wù)器托管，綿陽服務(wù)器托管，四川云主機(jī)，成都云主機(jī)，西南云主機(jī)，成都服務(wù)器托管，西南服務(wù)器托管，四川/成都大帶寬，大帶寬服務(wù)器，四川老牌ＩＤＣ服務(wù)商

  Oracle優(yōu)化器的優(yōu)化方法有兩種：

• CBO 基于成本的優(yōu)化法則

• RBO 基于規(guī)則的優(yōu)化法則

  初始化參數(shù)optimizer_mode控制著優(yōu)化器優(yōu)化的行為

SQL> show parameter optimizer_mode

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

optimizer_mode有如下五個(gè)取值

• CHOOSE 使用CBO還是RBO，基于統(tǒng)計(jì)信息是否存在，如果有統(tǒng)計(jì)系統(tǒng)則使用CBO，否則使用RBO。

• ALL_ROWS 基于CBO，采用盡快返回所有結(jié)果的一種最優(yōu)執(zhí)行計(jì)劃。

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

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

• RULE 采用基于CBO的優(yōu)化法則。

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

我們看看優(yōu)化器對查詢的影響

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")

修改優(yōu)化器模式后

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有如下三個(gè)組件構(gòu)成

• Query Transformer

• Estimator

• Plan Generator

一、Query Transformer

  輸入部分是已經(jīng)被解析器解析過的sql。

查詢轉(zhuǎn)換包括如下技術(shù)：

View Merging（視圖合并）

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

假設(shè)有這樣一個(gè)視圖

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

進(jìn)行如下查詢

SELECT employee_id
FROM   employees_50_vw 
WHERE  employee_id > 150;

優(yōu)化器轉(zhuǎn)換后，查詢變?yōu)?/p>

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

Predicate Pushing（謂詞推進(jìn)）

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

假設(shè)有如下視圖

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 );

我們發(fā)出這樣的查詢

SELECT last_name
FROM   all_employees_vw
WHERE  department_id = 50;

轉(zhuǎn)換后

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（子查詢解嵌套）

最典型的就是子查詢轉(zhuǎn)變?yōu)楸磉B接了，它和視圖合并的主要區(qū)別就在于它的子查詢位于where子句，由轉(zhuǎn)換器進(jìn)行解嵌套的檢測。

假設(shè)有這樣的一個(gè)查詢

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

查詢轉(zhuǎn)換后

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

Query Rewrite with Materialized Views

假設(shè)建立一個(gè)物化視圖

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;

執(zhí)行如下查詢

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;

查詢轉(zhuǎn)換后

SELECT calendar_month, dollars
FROM   cal_month_sales_mv;

二、Estimator

Estimator決定了一個(gè)給定的執(zhí)行計(jì)劃的總成本。估計(jì)量生成三種不同類型的措施,以實(shí)現(xiàn)這一目標(biāo):

• Selectivity

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

• Cardinality

   基數(shù)就是行集中行的數(shù)量?；鶖?shù)分為： 

   基礎(chǔ)基數(shù)（Base cardinality）：就是基表中的行數(shù)?；A(chǔ)基數(shù)在表分析期間獲得。如果表沒有可用的統(tǒng)計(jì)信息，則評估器利用表中區(qū)（extents）的數(shù)量來估算基礎(chǔ)基數(shù)。 

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

   連接基數(shù)（Join cardinality）：就是兩個(gè)行集在連接之后產(chǎn)生的行數(shù)。連接就是由兩個(gè)行集產(chǎn)生的笛卡爾積，再由連接謂詞過濾結(jié)果。因此，連接基數(shù)是兩個(gè)行集基數(shù)與連接謂詞選擇性的乘積。 

   Distinct基數(shù)（Distinct cardinality）：就是一個(gè)行集的字段distinct之后的行數(shù)。一個(gè)

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

   Group基數(shù)（Group cardinality）：就是一個(gè)行集在應(yīng)用GROUP BY之后產(chǎn)生行的數(shù)量。Group基數(shù)依賴于每個(gè)組中字段的distinct基數(shù)和行集的行數(shù)。

• Cost

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

三、Plan Generator

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