數(shù)據(jù)庫SQL的優(yōu)化

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

1. 執(zhí)行計劃

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

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

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

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

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

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

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

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

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

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

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

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

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

4.2 autotrace功能

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

4.2.1使用方法介紹

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

如下示例：

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

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

4.3 使用DBMS_XPLAN包

DBMS_XPLAN是Oracel數(shù)據(jù)庫的內(nèi)置包，該包提供了多個函數(shù)，通過這些函數(shù)，用戶可以比較容易的獲取執(zhí)行計劃等數(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的語法，默認執(zhí)行計劃存儲表為PLAN_TABLE，如果要查詢此表需要有SELECT的權(quán)限。

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

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

• 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 輸出查詢塊/對象 別名相關(guān)信息
• REMOTE 輸出分布式查詢相關(guān)信息
• NOTE 輸出執(zhí)行計劃的提醒部分相關(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方法

語法如下

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

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

示例如下：

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ù)使用較少，所以僅介紹其語法及功能。

4.3.3 DISPLAY_AWR

語法如下

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ù)獲取存儲在AWR歷史庫中SQL語句的執(zhí)行計劃相關(guān)信息。

4.3.4 DISPLAY_PLAN

語法如下

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í)行計劃存儲表的內(nèi)容?？娠@示CLOB類型信息，包括執(zhí)行計劃以及相關(guān)統(tǒng)計信息。

4.3.5 DISPLAY_SQL_PLAN_BASELINE

語法如下

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ù)和獲取存儲在系統(tǒng)視圖中SQL語句計劃基線的執(zhí)行計劃相關(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ù)獲取存儲在SQL調(diào)優(yōu)集中SQL語句的執(zhí)行計劃以及相關(guān)信息。

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

我們可以通過編寫的SQL語句來查詢執(zhí)行計劃。即直接查詢執(zhí)行計劃存儲表（默認為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 跟蹤計劃

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

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