數(shù)據(jù)庫(kù)執(zhí)行SQL都會(huì)先進(jìn)行語(yǔ)義解析，然后將SQL分成一步一步可執(zhí)行的計(jì)劃，然后逐步執(zhí)行。通過(guò)分析執(zhí)行計(jì)劃，我們可以清晰的看到數(shù)據(jù)庫(kù)執(zhí)行的操作，這對(duì)于數(shù)據(jù)庫(kù)SQL的優(yōu)化具有重大意義。

1. 執(zhí)行計(jì)劃

用戶成功連接數(shù)據(jù)庫(kù)之后，用戶和數(shù)據(jù)庫(kù)成功建立起了會(huì)話。此后，用戶每通過(guò)會(huì)話發(fā)出一條SQL語(yǔ)句，數(shù)據(jù)庫(kù)系統(tǒng)都會(huì)對(duì)其進(jìn)行一系列檢查、分析、處理。

同時(shí)優(yōu)化器會(huì)對(duì)SQL進(jìn)行一些優(yōu)化，并選擇出一個(gè)它覺(jué)得最優(yōu)的執(zhí)行計(jì)劃，然后再去執(zhí)行這些操作。由于SQL不同的寫法會(huì)影響優(yōu)化器為之生成和選定的執(zhí)行計(jì)劃。所以我們就可以通過(guò)改寫SQL語(yǔ)句來(lái)改變其執(zhí)行計(jì)劃，從而提升SQL語(yǔ)句性能。

2. 系統(tǒng)統(tǒng)計(jì)數(shù)據(jù)

系統(tǒng)統(tǒng)計(jì)數(shù)據(jù)反映了數(shù)據(jù)庫(kù)系統(tǒng)的處理能力，會(huì)對(duì)執(zhí)行計(jì)劃中左右操作成本（其實(shí)就是性能消耗）計(jì)算產(chǎn)生重要影響。系統(tǒng)統(tǒng)計(jì)數(shù)據(jù)主要包括轉(zhuǎn)速、單塊讀消耗時(shí)間、多塊讀消耗時(shí)間、多塊讀平均每次讀取的數(shù)據(jù)塊等。

系統(tǒng)統(tǒng)計(jì)數(shù)據(jù)會(huì)影響優(yōu)化器計(jì)算分析SQL語(yǔ)句執(zhí)行計(jì)劃的成本所選擇的算法，也會(huì)影響SQL語(yǔ)句生成和選擇的執(zhí)行計(jì)劃。

3. 對(duì)象統(tǒng)計(jì)數(shù)據(jù)

優(yōu)化器對(duì)SQL進(jìn)行解析的時(shí)候，會(huì)根據(jù)系統(tǒng)統(tǒng)計(jì)數(shù)據(jù)和對(duì)象統(tǒng)計(jì)數(shù)據(jù)等信息，計(jì)算成本，最后選出最低成本的執(zhí)行計(jì)劃。由于系統(tǒng)統(tǒng)計(jì)數(shù)據(jù)認(rèn)為很難干涉，所以對(duì)象統(tǒng)計(jì)數(shù)據(jù)對(duì)于SQL執(zhí)行計(jì)劃來(lái)說(shuō)影響更大。

對(duì)象統(tǒng)計(jì)數(shù)據(jù)主要包括三個(gè)部分：表（分區(qū)及子分區(qū)）相關(guān)統(tǒng)計(jì)數(shù)據(jù)、索引相關(guān)統(tǒng)計(jì)數(shù)據(jù)和字段相關(guān)統(tǒng)計(jì)數(shù)據(jù)。所以收集這些信息則可以進(jìn)行對(duì)象統(tǒng)計(jì)數(shù)據(jù)的分析，從而進(jìn)行SQL優(yōu)化。

4. 獲取執(zhí)行計(jì)劃

獲取執(zhí)行計(jì)劃有多種方法，下面分別介紹一下。

4.1 通過(guò)各種GUI工具獲得執(zhí)行計(jì)劃

通過(guò)各種GUI可以獲取到執(zhí)行計(jì)劃，其優(yōu)點(diǎn)是操作簡(jiǎn)單，靈活；獲取的信息也比較多。

下面是通過(guò)Sql Developer中的工具直接獲取到的執(zhí)行計(jì)劃示例

4.2 autotrace功能

autotrace功能是Oracle公司的產(chǎn)品，其功能強(qiáng)大、使用靈活，因而應(yīng)用廣泛。

4.2.1使用方法介紹

set autot off  關(guān)閉autotrace功能
set autot on 開啟autotrace功能,輸出SQL語(yǔ)句的查詢結(jié)果，執(zhí)行計(jì)劃以及相關(guān)的性能統(tǒng)計(jì)數(shù)據(jù)
set autot on expl 開啟autotrace功能，輸出SQL語(yǔ)句的查詢結(jié)果，執(zhí)行計(jì)劃，不輸出性能統(tǒng)計(jì)數(shù)據(jù)
set autot on stat 開啟autotrace功能，輸出SQL語(yǔ)句的查詢結(jié)果以及相關(guān)性能數(shù)據(jù)，不輸出執(zhí)行計(jì)劃
set autot trace 開啟autotrace功能，只輸出SQL語(yǔ)句的執(zhí)行計(jì)劃以及性能數(shù)據(jù)，不輸出查詢結(jié)果
set autot trace expl 開啟autotrace功能，只輸出SQL的執(zhí)行計(jì)劃，不輸出查詢結(jié)果及性能數(shù)據(jù)
set autot trace stat 開啟autotrace功能，只輸出SQL的性能統(tǒng)計(jì)數(shù)據(jù)，不輸出執(zhí)行計(jì)劃以及查詢結(jié)果

如下示例：

set autotrace on
select * from emp join DEPT on emp.DEPTNO = DEPT.DEPTNO
where DNAME = 'SALES';

圖中輸出了執(zhí)行計(jì)劃以及性能數(shù)據(jù).

4.3 使用DBMS_XPLAN包

DBMS_XPLAN是Oracel數(shù)據(jù)庫(kù)的內(nèi)置包，該包提供了多個(gè)函數(shù)，通過(guò)這些函數(shù)，用戶可以比較容易的獲取執(zhí)行計(jì)劃等數(shù)據(jù)。

4.3.1 DISPLAY方法

DBMS_XPLAN.DISPLAY(
 table_name in varchar2 default 'PLAN_TABLE',
    statement_id in varchar2 default null,
    format in varchar2 default 'TYPICAL',
    filter_preds in varchar2 default null);

以上是DISPLAY的語(yǔ)法，默認(rèn)執(zhí)行計(jì)劃存儲(chǔ)表為PLAN_TABLE，如果要查詢此表需要有SELECT的權(quán)限。

其中的參數(shù)含義如下：

• table_name ：存儲(chǔ)執(zhí)行計(jì)劃的表名。
• statement_id ：SQL語(yǔ)句的ID ，可以使用set statement_id 來(lái)指定其ID。如果為null，則表示獲取最近被解釋的SQL的執(zhí)行計(jì)劃。
• format ：執(zhí)行計(jì)劃的具體輸出級(jí)別 其值有
  • 'BASIC' ：基本輸出，經(jīng)輸出執(zhí)行計(jì)劃中每個(gè)節(jié)點(diǎn)),
  • 'TYPICAL' ：典型格式輸出，默認(rèn)格式。該格式輸出每個(gè)節(jié)點(diǎn)的ID、操作名、節(jié)點(diǎn)的數(shù)據(jù)行、字節(jié)數(shù)、優(yōu)化成本等。
  • 'SERIAL' ：串行執(zhí)行格式，輸出與典型格式類似。
  • 'ALL' ：完全格式， 最高用戶級(jí)別的輸出格式，除了輸出典型格式的內(nèi)容，還會(huì)輸出投影以及別名的相關(guān)信息。

示例如下：

explain plan for 
select * from emp join DEPT on emp.DEPTNO = DEPT.DEPTNO
where DNAME = 'SALES';
select * from table(dbms_xplan.display())

為了更好的控制執(zhí)行計(jì)劃的輸出格式，如下的關(guān)鍵字可以添加到標(biāo)準(zhǔn)格式后面，用來(lái)自定義輸出格式以及信息。

• ROWS 輸出優(yōu)化器估算出的數(shù)據(jù)行數(shù)
• BYTES 輸出優(yōu)化器估算出的字節(jié)數(shù)
• COST 輸出優(yōu)化器估算出的成本
• PARTITION 輸出分區(qū)裁剪相關(guān)信息
• PREDICATE 輸出謂詞部分相關(guān)信息
• PARALLEL 輸出并行操作(PX)相關(guān)信息
• PROJECTION 輸出字段映射部分相關(guān)信息
• ALIAS 輸出查詢塊/對(duì)象 別名相關(guān)信息
• REMOTE 輸出分布式查詢相關(guān)信息
• NOTE 輸出執(zhí)行計(jì)劃的提醒部分相關(guān)信息

示例如下：

explain plan for 
select * from emp join DEPT on emp.DEPTNO = DEPT.DEPTNO
where DNAME = 'SALES';
select * from table(dbms_xplan.display());
select * from table(dbms_xplan.display(null,null,'BASIC ROWS BYTES'));
select * from table(dbms_xplan.display(null,null,'ALL -PROJECTION -NOTE'));
select * from table(dbms_xplan.display(null,null,'ALL PROJECTION NOTE'));

4.3.2 DISPLAY_CURSOR方法

語(yǔ)法如下

DBMS_XPLAN.DISPLAY_CURSOR(
 sql_id in varchar2 default null,--默認(rèn)獲取會(huì)話最后一個(gè)游標(biāo)處的執(zhí)行計(jì)劃
    child_number in number default null,--游標(biāo)的子號(hào)
    format in varchar2 default 'TYPICAL' --輸出級(jí)別，與之前介紹相同
);

此函數(shù)可以獲取內(nèi)存游標(biāo)緩存處的執(zhí)行計(jì)劃和統(tǒng)計(jì)信息。

示例如下：

alter session set statistics_level = all;
select * from emp join DEPT on emp.DEPTNO = DEPT.DEPTNO
where DNAME = 'SALES';
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

執(zhí)行結(jié)果：

以下函數(shù)使用較少，所以僅介紹其語(yǔ)法及功能。

4.3.3 DISPLAY_AWR

語(yǔ)法如下

DBMS_XPLAN.DISPLAY_AWR(
    sql_id IN varchar2
 plan_hash_value in number default null,
    db_id in number default null,
    format in varchar2 default 'TYPICAL');

DISPLAY_AWR函數(shù)獲取存儲(chǔ)在AWR歷史庫(kù)中SQL語(yǔ)句的執(zhí)行計(jì)劃相關(guān)信息。

4.3.4 DISPLAY_PLAN

語(yǔ)法如下

DBMS_XPLAN.DISPLAY_PLAN(
    table_name in varchar2 default 'PLAN_TABLE',
    statement_id in varchar2 default null,
    format in varchar2 default 'TYPICAL',
    filter_preds in varchar2 default null,
    type in varchar2 default null --輸出類型，其值為'TEXT','ACTIVE','HTML','XML'
);

該函數(shù)可獲取執(zhí)行計(jì)劃存儲(chǔ)表的內(nèi)容。可顯示CLOB類型信息，包括執(zhí)行計(jì)劃以及相關(guān)統(tǒng)計(jì)信息。

4.3.5 DISPLAY_SQL_PLAN_BASELINE

語(yǔ)法如下

DISPLAY_XPLAN.DISPLAU_SQL_PLAN_BASELINE(
    sql_handle in varchar2 := null,
    plan_name in varchar2 := null,
    format in varchar2 := 'TYPICAL')
return dbms_xpaln_type_table;

此函數(shù)和獲取存儲(chǔ)在系統(tǒng)視圖中SQL語(yǔ)句計(jì)劃基線的執(zhí)行計(jì)劃相關(guān)的信息。

4.3.6 DISPLAY_SQLSET

DBMS_XPLAN.DISPLAY_SQLSET(
 sqlset_name in varchar2,
    sql_id in varchar2,
    plan_hash_value in number := null,
    format in varchar2 := 'TYPICAL',
    sqlset_owner in varchar2 := null
)
return DBMS_XPLAN_TYPE_TABLE PIPELINED;

此函數(shù)獲取存儲(chǔ)在SQL調(diào)優(yōu)集中SQL語(yǔ)句的執(zhí)行計(jì)劃以及相關(guān)信息。

4.4 查詢PLAN_TABLE獲取執(zhí)行計(jì)劃

我們可以通過(guò)編寫的SQL語(yǔ)句來(lái)查詢執(zhí)行計(jì)劃。即直接查詢執(zhí)行計(jì)劃存儲(chǔ)表（默認(rèn)為PLAN_TABLE）

explain plan SET STATEMENT_ID = 'TEST1' for 
select * from emp join DEPT on emp.DEPTNO = DEPT.DEPTNO
where DNAME = 'SALES';

SELECT  ID, PARENT_ID ,OPERATION ,OBJECT_NAME NAME , BYTES ,IO_COST ,CPU_COST
FROM PLAN_TABLE WHERE STATEMENT_ID = 'TEST1' ORDER BY ID ;

或者使用如下SQL查詢

SELECT  ID, PARENT_ID ,
    LPAD(' ', LEVEL-1)||OPERATION||' '||OPTIONS||' '||OBJECT_NAME NAME
FROM PLAN_TABLE 
CONNECT BY prior id = parent_id 
    and prior statement_id = statement_id 
start with id = 0 
    and statement_id = 'TEST1'
 ORDER BY ID ;

結(jié)果如下

4.5 跟蹤計(jì)劃

通過(guò)對(duì)SQL語(yǔ)句進(jìn)行跟蹤，從而獲取相關(guān)執(zhí)行計(jì)劃等。

主要方法有SQL_TRACE 和OPTIMIZER_TRACE ，前者會(huì)在跟蹤文件里輸出執(zhí)行計(jì)劃及性能統(tǒng)計(jì)等相關(guān)數(shù)據(jù)。OPTIMIZER_TRACE 在跟蹤文件里記錄優(yōu)化器分析、選擇執(zhí)行計(jì)劃的過(guò)程。