目錄

一、定位慢 SQL

（一）開(kāi)啟跟蹤日志記錄

1.跟蹤日志記錄配置

（二）通過(guò)系統(tǒng)視圖查看

1.SQL 記錄配置

2.查詢(xún)方式

二、SQL分析方法

（一）執(zhí)行計(jì)劃

1.概述

2.查看執(zhí)行計(jì)劃

（二）常見(jiàn)操作符解讀

1.NSET：結(jié)果集收集

2.PRJT：投影

3.SLCT：選擇

4.AAGR：簡(jiǎn)單聚集

5.FAGR：快速聚集

6.HAGR：HASH 分組聚集

7.SAGR：流分組聚集

8.BLKUP：二次掃描 (回表)

9.CSCN：全表掃描

10.SSEK、CSEK、SSCN：索引掃描

11.NEST LOOP：嵌套循環(huán)連接

12.HASH JOIN：哈希連接

13.MERGE JOIN：歸并排序連接

（三）ET 工具

1.功能的開(kāi)啟/關(guān)閉

（四）dbms_sqltune 工具

（五）存儲(chǔ)過(guò)程調(diào)試

1.圖形化界面工具進(jìn)行調(diào)試

2.DMDBG 進(jìn)行調(diào)試

三、SQL 語(yǔ)句優(yōu)化

?（一）索引

（二）SQL 語(yǔ)句改寫(xiě)

1.優(yōu)化 GROUP BY

2.用 UNION ALL 替換 UNION

3.用 EXISTS 替換 DISTINCT

4.多使用 COMMIT

?5.用WHERE子句替換HAVING子句

6.用TRUNCATE替換DELETE

7.用 EXISTS 替換 IN、用 NOT EXISTS 替換 NOT IN

8.半連接優(yōu)化

9.反連接優(yōu)化

（三）表設(shè)計(jì)優(yōu)化

1.表類(lèi)型選擇

2.水平分區(qū)表

3.全局臨時(shí)表

4.hint優(yōu)化sql

---

一、定位慢 SQL

定位執(zhí)行效率低的 SQL 語(yǔ)句是 SQL 優(yōu)化的第一步。待優(yōu)化的 SQL 可大致分為兩類(lèi)：SQL執(zhí)行時(shí)間在十幾秒到數(shù)十秒之間，但執(zhí)行頻率不高，此類(lèi) SQL 對(duì)數(shù)據(jù)庫(kù)整體性能影響并不大，可以放到最后進(jìn)行優(yōu)化。

SQL 單獨(dú)執(zhí)行時(shí)間可能很快，在幾百毫秒到幾秒之間，但執(zhí)行頻率非常高，甚至達(dá)到每秒上百次，高并發(fā)下執(zhí)行效率降低，很可能導(dǎo)致系統(tǒng)癱瘓，此類(lèi) SQL 是優(yōu)化的首要對(duì)象。

（一）開(kāi)啟跟蹤日志記錄

跟蹤日志文件是一個(gè)純文本文件，以”dmsql_實(shí)例名_日期_時(shí)間命名.log”，默認(rèn)生成在 DM 安裝目錄的 log 子目錄下。跟蹤日志內(nèi)容包含系統(tǒng)各會(huì)話(huà)執(zhí)行的 SQL 語(yǔ)句、參數(shù)信息、錯(cuò)誤信息、執(zhí)行時(shí)間等。跟蹤日志主要用于分析錯(cuò)誤和分析性能問(wèn)題，基于跟蹤日志可以對(duì)系統(tǒng)運(yùn)行狀態(tài)進(jìn)行分析。

1.跟蹤日志記錄配置

（1）配置 dm.ini 文件，設(shè)置 SVR_LOG = 1 以啟用 sqllog.ini 配置，該參數(shù)為動(dòng)態(tài)參數(shù)，可通過(guò)調(diào)用數(shù)據(jù)庫(kù)函數(shù)直接修改，如下所示：

SP_SET_PARA_VALUE(1,'SVR_LOG',1);

（2）配置數(shù)據(jù)文件目錄下的 sqllog.ini 文件。

[dmdba@localhost DAMENG]$ cat sqllog.ini

BUF_TOTAL_SIZE ?= 10240 ?#SQLs Log Buffer Total Size(K)(1024~1024000)

BUF_SIZE ???????= 1024 ??#SQLs Log Buffer Size(K)(50~409600)

BUF_KEEP_CNT ???= 6 ?????#SQLs Log buffer keeped count(1~100)

[SLOG_ALL]

????FILE_PATH ??????= ../log

????PART_STOR ??????= 0

????SWITCH_MODE ????= 1

????SWITCH_LIMIT ???= 100000

????ASYNC_FLUSH ????= 0

????FILE_NUM ???????= 200

????ITEMS ??????????= 0

????SQL_TRACE_MASK ?= 2:3:23:24:25

????MIN_EXEC_TIME ??= 0

????USER_MODE ??????= 0

????USERS ??????????=

注意

為避免記錄 SQL log 對(duì)服務(wù)器產(chǎn)生較大的影響，可以配置異步日志刷新（參數(shù) ASYNC_FLUSH 設(shè)置為 1）。

（3）如果對(duì) sqllog.ini 進(jìn)行了修改，可通過(guò)調(diào)用以下函數(shù)即時(shí)生效，無(wú)需重啟數(shù)據(jù)庫(kù)，如下所示：

SP_REFRESH_SVR_LOG_CONFIG();

（二）通過(guò)系統(tǒng)視圖查看

DM 數(shù)據(jù)庫(kù)提供系統(tǒng)動(dòng)態(tài)視圖，可自動(dòng)記錄執(zhí)行時(shí)間超過(guò)設(shè)定閾值的 SQL 語(yǔ)句。

1.SQL 記錄配置

當(dāng) INI 參數(shù) ENABLE_MONITOR=1、MONITOR_TIME=1 打開(kāi)時(shí)，顯示系統(tǒng)最近 1000 條執(zhí)行時(shí)間超過(guò)預(yù)定值的 SQL 語(yǔ)句，默認(rèn)預(yù)定值為 1000 毫秒。

以上兩個(gè)參數(shù)可通過(guò) SP_SET_PARA_VALUE 系統(tǒng)函數(shù)修改，通過(guò) SF_GET_PARA_VALUE 系統(tǒng)函數(shù)查看當(dāng)前值。

--修改參數(shù)值

SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1);

SP_SET_PARA_VALUE(1,'MONITOR_TIME',1);

--查看參數(shù)值

select SF_GET_PARA_VALUE(1,'ENABLE_MONITOR');

select SF_GET_PARA_VALUE(1,'MONITOR_TIME');

注意

兩個(gè)參數(shù)均為動(dòng)態(tài)參數(shù)，可直接調(diào)用系統(tǒng)函數(shù)進(jìn)行修改，無(wú)須重啟數(shù)據(jù)庫(kù)實(shí)例服務(wù)；
通過(guò) SP_SET_PARA_VALUE 方式修改的參數(shù)值僅對(duì)當(dāng)前會(huì)話(huà)以及新建會(huì)話(huà)生效，對(duì)其它已建立會(huì)話(huà)不生效。

2.查詢(xún)方式

• 查詢(xún)當(dāng)前正在執(zhí)行的會(huì)話(huà)信息。

SELECT * FROM (SELECT 'SP_CLOSE_SESSION('||SESS_ID||');' AS CLOSE_SESSION,

???????DATEDIFF(SS,LAST_SEND_TIME,SYSDATE) sql_exectime,

???????TRX_ID,

???????CLNT_IP,

???????B.IO_WAIT_TIME AS IO_WAIT_TIME,

???????SF_GET_SESSION_SQL(SESS_ID) FULLSQL,

???????A.SQL_TEXT

?FROM V$SESSIONS a,V$SQL_STAT B WHERE STATE IN ('ACTIVE','WAIT')

?AND A.SESS_ID = B.SESSID

?)

SQL_TEXT 列記錄的是部分 SQL 語(yǔ)句；FULLSQL 列存儲(chǔ)了完整的執(zhí)行 SQL 語(yǔ)句。

• 查詢(xún)超過(guò)執(zhí)行時(shí)間閾值的 SQL 語(yǔ)句。

可通過(guò)查詢(xún) V$LONG_EXEC_SQLS 系統(tǒng)視圖獲取結(jié)果：

SELECT * FROM V$LONG_EXEC_SQLS;

查詢(xún)結(jié)果字段詳細(xì)信息介紹如下表所示：

列名	說(shuō)明
SESS_ID	會(huì)話(huà) ID，會(huì)話(huà)唯一標(biāo)識(shí)
SQL_ID	語(yǔ)句 ID，語(yǔ)句唯一標(biāo)識(shí)
SQL_TEXT	SQL 文本
EXEC_TIME	執(zhí)行時(shí)間（毫秒）
FINISH_TIME	執(zhí)行結(jié)束時(shí)間
N_RUNS	執(zhí)行次數(shù)
SEQNO	編號(hào)
TRX_ID	事務(wù)號(hào)

二、SQL分析方法

（一）執(zhí)行計(jì)劃

1.概述

簡(jiǎn)單來(lái)說(shuō)，執(zhí)行計(jì)劃就是一條 SQL 語(yǔ)句在數(shù)據(jù)庫(kù)中的執(zhí)行過(guò)程或訪問(wèn)路徑的描述。SQL 語(yǔ)言是種功能強(qiáng)大且非過(guò)程性的編程語(yǔ)言，比如以下這條 SQL 語(yǔ)句：

SELECT * FROM T1, T2 WHERE T1.ID = T2.ID AND T1.ID = 6;

開(kāi)發(fā)人員只關(guān)心 SQL 語(yǔ)句能否返回 T1 與 T2 表的關(guān)聯(lián)查詢(xún)結(jié)果，不需要指定該 SQL 如何執(zhí)行，也就是說(shuō)不關(guān)心該 SQL 是先訪問(wèn) T1 表還是先訪問(wèn) T2 表。對(duì)于 SQL 來(lái)說(shuō)，兩種訪問(wèn)方式就是兩個(gè)執(zhí)行計(jì)劃，查詢(xún)優(yōu)化器 (CBO) 將根據(jù)代價(jià)也就是開(kāi)銷(xiāo)來(lái)選擇最優(yōu)的執(zhí)行計(jì)劃。以如下 SQL 語(yǔ)句執(zhí)行計(jì)劃為例：

SELECT * FROM SYSOBJECTS;

1 ??#NSET2: [0, 1282, 396]

2 ????#PRJT2: [0, 1282, 396]; exp_num(17), is_atom(FALSE)

3 ??????#CSCN2: [0, 1282, 396]; SYSINDEXSYSOBJECTS(SYSOBJECTS as SYSOBJECTS)

執(zhí)行計(jì)劃的每行即為一個(gè)計(jì)劃節(jié)點(diǎn)，主要包含三部分信息。

• 第一部分 NEST2、PRJT2、CSCN2 為操作符及數(shù)據(jù)庫(kù)具體執(zhí)行了什么操作。
• 第二部分的三元組為該計(jì)劃節(jié)點(diǎn)的執(zhí)行代價(jià)，具體含義為[代價(jià)，記錄行數(shù)，字節(jié)數(shù)]。
• 第三部分為操作符的補(bǔ)充信息。

例如：第三個(gè)計(jì)劃節(jié)點(diǎn)表示操作符是 CSCN2（即全表掃描），代價(jià)估算是 0 ms，掃描的記錄行數(shù)是 1282 行，輸出字節(jié)數(shù)是 396 個(gè)。

各計(jì)劃節(jié)點(diǎn)的執(zhí)行順序?yàn)?#xff1a;縮進(jìn)越多的越先執(zhí)行，同樣縮進(jìn)的上面的先執(zhí)行，下面的后執(zhí)行，上下的優(yōu)先級(jí)高于內(nèi)外?？s進(jìn)最深的，最先執(zhí)行；縮進(jìn)深度相同的，先上后下?？谠E：最右最上先執(zhí)行。

#CSCN2: [1, 2, 12]; INDEX33555496(TEST)

?操作符，[代價(jià)，行數(shù)，字節(jié)數(shù)] 描述

2.查看執(zhí)行計(jì)劃

達(dá)夢(mèng)數(shù)據(jù)庫(kù)可通過(guò)兩種方式查看執(zhí)行計(jì)劃。

方式一：通過(guò) DM 數(shù)據(jù)庫(kù)配套管理工具查看。

方式二：使用 explain 命令查看。

以下對(duì)兩種查看方式進(jìn)行介紹。

（1）管理工具查看執(zhí)行計(jì)劃

（2）在 DM 配套管理工具中，選中待查看執(zhí)行計(jì)劃的 SQL 語(yǔ)句，點(diǎn)擊工具欄中的按鈕，或使用快捷鍵 F9，即可查看執(zhí)行計(jì)劃。

（3）使用 explain 命令查看執(zhí)行計(jì)劃

（4）在待查看執(zhí)行計(jì)劃的 SQL 語(yǔ)句前加 explain 執(zhí)行 SQL 語(yǔ)句即可查看預(yù)估的執(zhí)行計(jì)劃：

explain select * from sysobjects;

--執(zhí)行計(jì)劃

1 ??#NSET2: [1, 986, 396]

2 ????#PRJT2: [1, 986, 396]; exp_num(17), is_atom(FALSE)

3 ??????#CSCN2: [1, 986, 396]; SYSINDEXSYSOBJECTS(SYSOBJECTS as SYSOBJECTS)

（5）使用 disql 命令行查真實(shí)執(zhí)行計(jì)劃

SQL> set autotrace traceonly

SQL> select * from sysobjects;

2348 rows got

1 ??#NSET2: [1, 2348->2348, 397]

2 ????#PRJT2: [1, 2348->2348, 397]; exp_num(17), is_atom(FALSE)

3 ??????#CSCN2: [1, 2348->2348, 397]; SYSINDEXSYSOBJECTS(SYSOBJECTS)

Statistics-----------------------------------------------------------------

????????0 ??????????data pages changed

????????0 ??????????undo pages changed

????????67 ?????????logical reads

????????0 ??????????physical reads

????????0 ??????????redo size

????????364191 ?????bytes sent to client

????????155 ????????bytes received from client

????????2 ??????????roundtrips to/from client

????????0 ??????????sorts (memory)

????????0 ??????????sorts (disk)

????????2348 ???????rows processed

????????0 ??????????io wait time(ms)

????????2 ??????????exec time(ms)

重點(diǎn)關(guān)注 logical reads（邏輯讀）和 physical reads（物理讀）相應(yīng)的指標(biāo)值，并結(jié)合 rows processed 返回處理行數(shù)多少來(lái)分析。如果返回行數(shù)少（并且 bytes sent to client 總量不大），應(yīng)盡可能減少 IO 開(kāi)銷(xiāo)，讓執(zhí)行計(jì)劃選擇正確的索引路徑。

Sort(disk) 一般因排序（ hash join 發(fā)生歸并、order by、group by 場(chǎng)景）區(qū)內(nèi)存不足，如果數(shù)據(jù)庫(kù)服務(wù)器物理內(nèi)存充足，可以適當(dāng)上調(diào)排序區(qū)內(nèi)存，盡量避免操作刷盤(pán)，否則會(huì)影響執(zhí)行性能。

（二）常見(jiàn)操作符解讀

下面通過(guò)幾個(gè)例子來(lái)介紹一些常見(jiàn)操作符。準(zhǔn)備測(cè)試表及數(shù)據(jù)如下：

DROP TABLE T1;

DROP TABLE T2;

CREATE TABLE T1(C1 INT ,C2 CHAR(1),C3 VARCHAR(10) ,C4 VARCHAR(10) );

CREATE TABLE T2(C1 INT ,C2 CHAR(1),C3 VARCHAR(10) ,C4 VARCHAR(10) );

INSERT INTO T1

SELECT LEVEL C1,CHR(65+MOD(LEVEL,57)) C2,'TEST',NULL FROM DUAL

CONNECT BY LEVEL<=10000;

INSERT INTO T2

SELECT LEVEL C1,CHR(65+MOD(LEVEL,57)) C2,'TEST',NULL ?FROM DUAL

CONNECT BY LEVEL<=10000;

CREATE INDEX IDX_C1_T1 ON T1(C1);

SP_INDEX_STAT_INIT(USER,'IDX_C1_T1');

1.NSET：結(jié)果集收集

EXPLAIN SELECT * FROM T1;

1 ??#NSET2: [1, 10000, 156]

2 ????#PRJT2: [1, 10000, 156]; exp_num(5), is_atom(FALSE)

3 ??????#CSCN2: [1, 10000, 156]; INDEX33556710(T1)

NSET 是用于結(jié)果集收集的操作符，一般是查詢(xún)計(jì)劃的頂層節(jié)點(diǎn)，優(yōu)化工作中無(wú)需對(duì)該操作符過(guò)多關(guān)注，一般沒(méi)有優(yōu)化空間。

2.PRJT：投影

EXPLAIN SELECT * FROM T1;

1 ??#NSET2: [1, 10000, 156]

2 ????#PRJT2: [1, 10000, 156]; exp_num(5), is_atom(FALSE)

3 ??????#CSCN2: [1, 10000, 156]; INDEX33556710(T1)

PRJT 是關(guān)系的【投影】 (project) 運(yùn)算，用于選擇表達(dá)式項(xiàng)的計(jì)算。廣泛用于查詢(xún)，排序，函數(shù)索引創(chuàng)建等。優(yōu)化工作中無(wú)需對(duì)該操作符過(guò)多關(guān)注，一般沒(méi)有優(yōu)化空間。

3.SLCT：選擇

EXPLAIN SELECT * FROM T1 WHERE C2='TEST';

1 ??#NSET2: [1, 250, 156]

2 ????#PRJT2: [1, 250, 156]; exp_num(5), is_atom(FALSE)

3 ??????#SLCT2: [1, 250, 156]; T1.C2 = TEST

4 ????????#CSCN2: [1, 10000, 156]; INDEX33556717(T1)

SLCT 是關(guān)系的【選擇】運(yùn)算，用于查詢(xún)條件的過(guò)濾?？杀容^返回結(jié)果集與代價(jià)估算中是否接近，如相差較大可考慮收集統(tǒng)計(jì)信息。若該過(guò)濾條件過(guò)濾性較好，可考慮在條件列增加索引。

4.AAGR：簡(jiǎn)單聚集

EXPLAIN SELECT COUNT(*) FROM T1 WHERE C1 = 10;

1 ??#NSET2: [0, 1, 4]

2 ????#PRJT2: [0, 1, 4]; exp_num(1), is_atom(FALSE)

3 ??????#AAGR2: [0, 1, 4]; grp_num(0), sfun_num(1)

4 ????????#SSEK2: [0, 1, 4]; scan_type(ASC), IDX_C1_T1(T1), scan_range[10,10]

AAGR 用于沒(méi)有 GROUP BY 的 COUNT、SUM、AGE、MAX、MIN 等聚集函數(shù)的計(jì)算。

5.FAGR：快速聚集

EXPLAIN ?SELECT MAX(C1) FROM T1;

1 ??#NSET2: [1, 1, 0]

2 ????#PRJT2: [1, 1, 0]; exp_num(1), is_atom(FALSE)

3 ??????#FAGR2: [1, 1, 0]; sfun_num(1)

FAGR 用于沒(méi)有過(guò)濾條件時(shí)，從表或索引快速獲取 MAX、MIN、COUNT 值。

6.HAGR：HASH 分組聚集

EXPLAIN SELECT COUNT(*) FROM T1 GROUP BY C2;

1 ??#NSET2: [1, 100, 48]

2 ????#PRJT2: [1, 100, 48]; exp_num(1), is_atom(FALSE)

3 ??????#HAGR2: [1, 100, 48]; grp_num(1), sfun_num(1)

4 ????????#CSCN2: [1, 10000, 48]; INDEX33556717(T1)

HAGR 用于分組列沒(méi)有索引只能走全表掃描的分組聚集，該示例中 C2 列沒(méi)有創(chuàng)建索引。

7.SAGR：流分組聚集

EXPLAIN SELECT COUNT(*) FROM T1 GROUP BY C1;

1 ??#NSET2: [1, 100, 4]

2 ????#PRJT2: [1, 100, 4]; exp_num(1), is_atom(FALSE)

3 ??????#SAGR2: [1, 100, 4]; grp_num(1), sfun_num(1)

4 ????????#SSCN: [1, 10000, 4]; IDX_C1_T1(T1)

SAGR 用于分組列是有序的情況下，可以使用流分組聚集，C1 列上已經(jīng)創(chuàng)建了索引，SAGR2 性能優(yōu)于 HAGR2。

8.BLKUP：二次掃描 (回表)

EXPLAIN SELECT * FROM T1 WHERE C1=10;

1 ??#NSET2: [0, 1, 156]

2 ????#PRJT2: [0, 1, 156]; exp_num(5), is_atom(FALSE)

3 ??????#BLKUP2: [0, 1, 156]; IDX_C1_T1(T1)

4 ????????#SSEK2: [0, 1, 156]; scan_type(ASC), IDX_C1_T1(T1), scan_range[10,10]

BLKUP 先使用二級(jí)索引索引定位 rowid，再根據(jù)表的主鍵、聚集索引、rowid 等信息獲取數(shù)據(jù)行中其它列。

9.CSCN：全表掃描

EXPLAIN SELECT * FROM T1;

1 ??#NSET2: [1, 10000, 156]

2 ????#PRJT2: [1, 10000, 156]; exp_num(5), is_atom(FALSE)

3 ??????#CSCN2: [1, 10000, 156]; INDEX33556710(T1)

CSCN2 是 CLUSTER INDEX SCAN 的縮寫(xiě)即通過(guò)聚集索引掃描全表，全表掃描是最簡(jiǎn)單的查詢(xún)，如果沒(méi)有選擇謂詞，或者沒(méi)有索引可以利用，則系統(tǒng)一般只能做全表掃描。全表掃描 I/O 開(kāi)銷(xiāo)較大，在一個(gè)高并發(fā)的系統(tǒng)中應(yīng)盡量避免全表掃描。

10.SSEK、CSEK、SSCN：索引掃描

-- 創(chuàng)建所需索引

CREATE CLUSTER INDEX IDX_C1_T2 ?ON T2(C1);

CREATE ?INDEX IDX_C1_C2_T1 ?ON T1(C1,C2);

SSEK

EXPLAIN SELECT * FROM T1 WHERE C1=10;

1 ??#NSET2: [0, 1, 156]

2 ????#PRJT2: [0, 1, 156]; exp_num(5), is_atom(FALSE)

3 ??????#BLKUP2: [0, 1, 156]; IDX_C1_T1(T1)

4 ????????#SSEK2: [0, 1, 156]; scan_type(ASC), IDX_C1_T1(T1), scan_range[10,10]

SSEK2 是二級(jí)索引掃描即先掃描索引，再通過(guò)主鍵、聚集索引、rowid 等信息去掃描表。

CSEK

EXPLAIN SELECT * FROM T2 WHERE C1=10;

1 ??#NSET2: [0, 250, 156]

2 ????#PRJT2: [0, 250, 156]; exp_num(5), is_atom(FALSE)

3 ??????#CSEK2: [0, 250, 156]; scan_type(ASC), IDX_C1_T2(T2), scan_range[10,10]

CSEK2 是聚集索引掃描只需要掃描索引，不需要掃描表，即無(wú)需 BLKUP 操作，如果 BLKUP 開(kāi)銷(xiāo)較大時(shí)，可考慮創(chuàng)建聚集索引。

SSCN

EXPLAIN SELECT C1,C2 FROM T1;

1 ??#NSET2: [1, 10000, 60]

2 ????#PRJT2: [1, 10000, 60]; exp_num(3), is_atom(FALSE)

3 ??????#SSCN: [1, 10000, 60]; IDX_C1_C2_T1(T1)

SSCN 是索引全掃描，不需要掃描表。

11.NEST LOOP：嵌套循環(huán)連接

嵌套循環(huán)連接是最基礎(chǔ)的連接方式，將一張表（驅(qū)動(dòng)表）的每一個(gè)值與另一張表（被驅(qū)動(dòng)表）的所有值拼接，形成一個(gè)大結(jié)果集，再?gòu)拇蠼Y(jié)果集中過(guò)濾出滿(mǎn)足條件的行。驅(qū)動(dòng)表的行數(shù)就是循環(huán)的次數(shù)，將在很大程度上影響執(zhí)行效率。

連接列是否有索引，都可以走 NEST LOOP，但沒(méi)有索引，執(zhí)行效率會(huì)很差，語(yǔ)句如下所示：

select /*+use_nl(t1,t2)*/* from t1 inner join t2 on t1.c1=t2.c1 where t1.c2='A';

1 ??#NSET2: [17862, 24725, 296]

2 ????#PRJT2: [17862, 24725, 296]; exp_num(8), is_atom(FALSE)

3 ??????#SLCT2: [17862, 24725, 296]; T1.C1 = T2.C1

4 ????????#NEST LOOP INNER JOIN2: [17862, 24725, 296];

5 ??????????#SLCT2: [1, 250, 148]; T1.C2 = 'A'

6 ????????????#CSCN2: [1, 10000, 148]; INDEX33555594(T1)

7 ??????????#CSCN2: [1, 10000, 148]; INDEX33555595(T2)

可針對(duì) T1 和 T2 的連接列創(chuàng)建索引，并收集統(tǒng)計(jì)信息，語(yǔ)句如下所示：

CREATE INDEX IDX_T1_C2 ?ON T1(C2);CREATE INDEX IDX_T2_C1 ?ON T2(C1);

DBMS_STATS.GATHER_INDEX_STATS(USER,'IDX_T1_C2');

DBMS_STATS.GATHER_INDEX_STATS(USER,'IDX_T2_C1');

再次查看執(zhí)行計(jì)劃可看出效率明顯改善，代價(jià)有顯著下降，語(yǔ)句如下所示：

select /*+use_nl(t1,t2)*/* from t1 inner join t2 on t1.c1=t2.c1 where t1.c2='A';

1 ??#NSET2: [9805, 17151, 296]

2 ????#PRJT2: [9805, 17151, 296]; exp_num(8), is_atom(FALSE)

3 ??????#SLCT2: [9805, 17151, 296]; T1.C1 = T2.C1

4 ????????#NEST LOOP INNER JOIN2: [9805, 17151, 296];

5 ??????????#BLKUP2: [1, 175, 148]; IDX_T1_C2(T1)

6 ????????????#SSEK2: [1, 175, 148]; scan_type(ASC), IDX_T1_C2(T1), scan_range['A','A']

7 ??????????#CSCN2: [1, 10000, 148]; INDEX33555585(T2)

適用場(chǎng)景：

驅(qū)動(dòng)表有很好的過(guò)濾條件

表連接條件能使用索引

結(jié)果集比較小

12.HASH JOIN：哈希連接

哈希連接是在沒(méi)有索引或索引無(wú)法使用情況下大多數(shù)連接的處理方式。哈希連接使用關(guān)聯(lián)列去重后結(jié)果集較小的表做成 HASH 表，另一張表的連接列在 HASH 后向 HASH 表進(jìn)行匹配，這種情況下匹配速度極快，主要開(kāi)銷(xiāo)在于對(duì)連接表的全表掃描以及 HASH 運(yùn)算。

select * from t1 inner join t2 on t1.c1=t2.c1 where t1.c2='A';

1 ??#NSET2: [4, 24502, 296]

2 ????#PRJT2: [4, 24502, 296]; exp_num(8), is_atom(FALSE)

3 ??????#HASH2 INNER JOIN: [4, 24502, 296]; ?KEY_NUM(1); KEY(T1.C1=T2.C1) KEY_NULL_EQU(0)

4 ????????#SLCT2: [1, 250, 148]; T1.C2 = 'A'

5 ??????????#CSCN2: [1, 10000, 148]; INDEX33555599(T1)

6 ????????#CSCN2: [1, 10000, 148]; INDEX33555600(T2)

哈希連接比較消耗內(nèi)存如果系統(tǒng)有很多這種連接時(shí)，需調(diào)整以下 3 個(gè)參數(shù)：

參數(shù)名	說(shuō)明
HJ_BUF_GLOBAL_SIZE	HASH 連接操作符的數(shù)據(jù)總緩存大小 ()>=HJ_BUF_SIZE)，系統(tǒng)級(jí)參數(shù)，以兆為單位。有效值范圍（10~500000）
HJ_BUF_SIZE	單個(gè)哈希連接操作符的數(shù)據(jù)總緩存大小，以兆為單位。有效值范圍（2~100000）
HJ_BLK_SIZE	哈希連接操作符每次分配緩存( BLK )大小，以兆為單位，必須小于 HJ_BUF_SIZE。有效值范圍（1~50）

13.MERGE JOIN：歸并排序連接

歸并排序連接需要兩張表的連接列都有索引，對(duì)兩張表掃描索引后按照索引順序進(jìn)行歸并。

-- 對(duì)連接列創(chuàng)建索引CREATE INDEX IDX_T1_C1C2 ON T1(C1,C2);

select /*+use_merge(t1 t2)*/t1.c1,t2.c1 from t1 inner join t2 on t1.c1=t2.c1 where t2.c2='b';

1 ??#NSET2: [13, 24725, 56]

2 ????#PRJT2: [13, 24725, 56]; exp_num(2), is_atom(FALSE)

3 ??????#SLCT2: [13, 24725, 56]; T2.C2 = 'b'

4 ????????#MERGE INNER JOIN3: [13, 24725, 56]; KEY_NUM(1); KEY(COL_0 = COL_0) KEY_NULL_EQU(0)

5 ??????????#SSCN: [1, 10000, 4]; IDX_C1_T1(T1)

6 ??????????#BLKUP2: [1, 10000, 52]; IDX_T2_C1(T2)

7 ????????????#SSCN: [1, 10000, 52]; IDX_T2_C1(T2)

（三）ET 工具

ET 工具是 DM 數(shù)據(jù)庫(kù)自帶的 SQL 性能分析工具，能夠統(tǒng)計(jì) SQL 語(yǔ)句執(zhí)行過(guò)程中每個(gè)操作符的實(shí)際開(kāi)銷(xiāo)，為 SQL 優(yōu)化提供依據(jù)以及指導(dǎo)。

1.功能的開(kāi)啟/關(guān)閉

ET 功能默認(rèn)關(guān)閉，可通過(guò)配置 INI 參數(shù)中的 ENABLE_MONITOR=1、MONITOR_SQL_EXEC=1 開(kāi)啟該功能。

--兩個(gè)參數(shù)均為動(dòng)態(tài)參數(shù)，可直接調(diào)用系統(tǒng)函數(shù)進(jìn)行修改

SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1);

SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',1);

--會(huì)話(huà)級(jí)別修改只在當(dāng)前會(huì)話(huà)生效

SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC',1);

--關(guān)閉 ET

SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',0);

SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',0);

注意

ET 功能的開(kāi)啟將對(duì)數(shù)據(jù)庫(kù)整體性能造成一定影響，優(yōu)化工作結(jié)束后盡量關(guān)閉該功能以提升數(shù)據(jù)庫(kù)整體運(yùn)行效率；DM數(shù)據(jù)庫(kù)新版本的開(kāi)啟ET功能時(shí)需要設(shè)置：ENABLE_MONITOR = 1（默認(rèn)打開(kāi)）MONITOR_TIME = 1（默認(rèn)打開(kāi)）MONITOR_SQL_EXEC = 1（設(shè)置成1）。

（四）dbms_sqltune 工具

DBMS_SQLTUNE 包提供一系列實(shí)時(shí) SQL 監(jiān)控的方法。當(dāng) SQL 監(jiān)控功能開(kāi)啟后，DBMS_SQLTUNE 包可以實(shí)時(shí)監(jiān)控 SQL 執(zhí)行過(guò)程中的信息，包括：執(zhí)行時(shí)間、執(zhí)行代價(jià)、執(zhí)行用戶(hù)、統(tǒng)計(jì)信息等情況。

使用前提：建議會(huì)話(huà)級(jí)開(kāi)啟參數(shù) MONITOR_SQL_EXEC=1，而 MONITOR_SQL_EXEC 在達(dá)夢(mèng)數(shù)據(jù)庫(kù)中一般默認(rèn)是 1，無(wú)需調(diào)整。

ALTER SESSION SET 'MONITOR_SQL_EXEC' = 1;

<執(zhí)行待優(yōu)化SQL>

select DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_EXEC_ID=>1213701) from dual;

dbms_sqltune 系統(tǒng)包相比 ET 功能更強(qiáng)大，能夠獲取 IO 操作量，查看真實(shí)執(zhí)行計(jì)劃，每個(gè)操作符消耗占比和相應(yīng)的花費(fèi)時(shí)間，還能看出每個(gè)操作符執(zhí)行的次數(shù)，非常便于了解執(zhí)行計(jì)劃中瓶頸位置。

dbms_sqltune 功能遠(yuǎn)不止定位執(zhí)行計(jì)劃瓶頸，還擁有調(diào)優(yōu)助手功能（建議性提示建某索引和收集某統(tǒng)計(jì)信息）。

（五）存儲(chǔ)過(guò)程調(diào)試

在 DM 數(shù)據(jù)庫(kù)中，我們可以利用 DM PL/SQL 進(jìn)行存儲(chǔ)過(guò)程及函數(shù)的編寫(xiě)。有時(shí)，我們編寫(xiě)的程序塊中可能會(huì)有 BUG 導(dǎo)致編譯失敗，這種情況下就需要用到相關(guān)工具來(lái)進(jìn)行調(diào)試。本章節(jié)主要介紹兩種存儲(chǔ)過(guò)程的調(diào)試方法。

1.圖形化界面工具進(jìn)行調(diào)試

在有圖形化界面的情況下，我們可以利用管理工具來(lái)進(jìn)行匿名塊的調(diào)試。

（1）使用匿名塊調(diào)試

開(kāi)啟系統(tǒng)包 DEBUG SYSTEM?！竟ぞ甙?>DEBUG SYSTEM-> 右鍵->【啟用】；

點(diǎn)擊上方的【調(diào)試】按鈕進(jìn)入調(diào)試。

（2）創(chuàng)建測(cè)試存儲(chǔ)過(guò)程，并進(jìn)行調(diào)試

創(chuàng)建測(cè)試存儲(chǔ)過(guò)程。

--當(dāng)i到3的時(shí)候就會(huì)報(bào)除0錯(cuò)誤DECLARE

--變量

aa int;BEGIN

--調(diào)試語(yǔ)句

for i in 1..3

loop

aa = 1 / (3-i);

print aa;

end loop;

END

進(jìn)入調(diào)試，點(diǎn)擊【進(jìn)入】按鈕，會(huì)依照代碼邏輯進(jìn)行分步調(diào)試，顯示區(qū)域會(huì)顯示當(dāng)前變量執(zhí)行結(jié)果。若遇到報(bào)錯(cuò)會(huì)終止調(diào)試，并顯示報(bào)錯(cuò)內(nèi)容，可根據(jù)報(bào)錯(cuò)內(nèi)容進(jìn)行代碼修改。

2.DMDBG 進(jìn)行調(diào)試

當(dāng)我們沒(méi)有圖形界面，或者通過(guò)遠(yuǎn)程連接服務(wù)器的時(shí)候，我們也可以利用 DM 提供的命令行調(diào)試工具 dmdbg ，來(lái)完成同樣的任務(wù)。

（1）登錄 dmdbg。dmdbg 與 disql 同級(jí)目錄，都在 dmdbms/bin 目錄下，登錄方式也與 disql 類(lèi)似：

cd /dmdbms/bin

./dmdbg SYSDBA/SYSDBA@LOCALHOST:5236

--可以使用 help 命令查看參數(shù)

DEG> help

（2）引用存儲(chǔ)過(guò)程。在 disql 中創(chuàng)建如下測(cè)試存儲(chǔ)過(guò)程，然后在 DBG 中把?call TEST_DMDBG;?放到 SQL 內(nèi)執(zhí)行，如下所示：

--注意調(diào)試前確保已經(jīng)開(kāi)啟系統(tǒng)包 DEBUG SYSTEM--登錄disql，在disql中創(chuàng)建如下測(cè)試存儲(chǔ)過(guò)程

create or replace procedure TEST_DEBUGas

aa int;begin

for i in 1..3

loop

aa = 1 / (3-i);

print aa;

end loop;end;

--登錄dmdbg，把 call TEST_DMDBG; 放到 SQL 內(nèi)執(zhí)行

DBG> sql call TEST_DMDBG;

（3）添加斷點(diǎn)。從頭開(kāi)始調(diào)試，將斷點(diǎn)放到最開(kāi)始的位置，可以在需要的行數(shù)打上斷點(diǎn)，如下所示：

DBG> B 0

Breakpoint 1 at @dbg_main, line: 1@{call TEST_DMDBG;}

（4）開(kāi)始調(diào)試。

DBG> r

Breakpoint 1, ?line: 1@{call TEST_DMDBG;}

（5）進(jìn)入循環(huán)里調(diào)試。

DBG> s

SYSDBA.TEST_DMDBG line: 5 ??????@{ ??for i in 1..3}

DBG> s

SYSDBA.TEST_DMDBG line: 7 ??????@{ ????aa = 1 / (3-i);}

（6）查看當(dāng)前的堆棧。

DBG> bt

\#0 ?????SYSDBA.TEST_DMDBG() ????line: 7@{ ????aa = 1 / (3-i);}

\#1 ?????@dbg_main ??????line: 1@{call TEST_DMDBG;}

（7）查看當(dāng)前變量的數(shù)值。

--可以通過(guò) P 變量名的方式輸出打印

DBG> p aa

$3 = 0--當(dāng) i 到3的時(shí)候就會(huì)報(bào)除0錯(cuò)誤

DBG> s

[TEST_DMDBG] 除0錯(cuò)誤.error code=-6103--報(bào)錯(cuò)停止

三、SQL 語(yǔ)句優(yōu)化

關(guān)于查詢(xún)語(yǔ)句，有以下幾點(diǎn)特征：

• 返回?cái)?shù)據(jù)越多，語(yǔ)句執(zhí)行時(shí)間越長(zhǎng)；
• 分頁(yè)是一個(gè)優(yōu)化重點(diǎn)，order by 排序大小由結(jié)果集大小決定，過(guò)大會(huì)在臨時(shí)表空間排序，性能降低；
• 一些語(yǔ)句會(huì)隱式排序，比如 uinon group by；
• buffer 過(guò)小，數(shù)據(jù)頁(yè)頻繁的換入換出。

關(guān)于優(yōu)化 sql 語(yǔ)句：

• 通過(guò)各種手段減少 sql 執(zhí)行過(guò)程中的 IO 代價(jià)，內(nèi)存中的計(jì)算，臨時(shí)表使用等；
• 表與表之間的關(guān)系，即關(guān)聯(lián)條件之間的數(shù)據(jù)對(duì)應(yīng)關(guān)系；
• 表數(shù)據(jù)量大小，對(duì)于 OLTP 是否滿(mǎn)足小表驅(qū)動(dòng)大表；
• sql 返回結(jié)果集多少，如果返回結(jié)果集少，sql 優(yōu)化余地較大；
• 合理利用索引（組合索引）的特點(diǎn)，雖然維護(hù)索引也需要代價(jià)，但是對(duì)于查詢(xún)來(lái)說(shuō)，很多時(shí)候效果立竿見(jiàn)影。

關(guān)于訪問(wèn)和連接方法：

• 全表掃描訪問(wèn)；
• 索引掃描訪問(wèn)；
• 嵌套循環(huán)連接；
• 哈希連接；
• 歸并連接；
• 半連接、反連接；
• n 張表至少有 n-1 次連接。

?（一）索引

索引是一種特殊的數(shù)據(jù)庫(kù)結(jié)構(gòu)，由數(shù)據(jù)表中的一列或多列組合而成，可以用來(lái)快速查詢(xún)數(shù)據(jù)表中有某一特定值的記錄。

索引結(jié)構(gòu)：最常見(jiàn)的索引結(jié)構(gòu)為 B*樹(shù)索引

最頂層的為根節(jié)點(diǎn)，最底層的為葉子節(jié)點(diǎn)，中間層為內(nèi)節(jié)點(diǎn)。實(shí)際使用當(dāng)中一般不止 3 層（取決于數(shù)據(jù)量大小），除根節(jié)點(diǎn)以及葉子節(jié)點(diǎn)以外僅為內(nèi)節(jié)點(diǎn)。對(duì)于一個(gè) m 階（本例中 m=2）的 B*樹(shù)存儲(chǔ)結(jié)構(gòu)有以下幾個(gè)特點(diǎn)：

• 每個(gè)結(jié)點(diǎn)最多有 m 個(gè)子結(jié)點(diǎn)。
• 除了根結(jié)點(diǎn)和葉子結(jié)點(diǎn)外，每個(gè)結(jié)點(diǎn)最少有 m/2（向上取整）個(gè)子結(jié)點(diǎn)。
• 如果根結(jié)點(diǎn)不是葉子結(jié)點(diǎn)，那根結(jié)點(diǎn)至少包含兩個(gè)子結(jié)點(diǎn)。
• 所有的葉子結(jié)點(diǎn)都位于同一層。
• 每個(gè)結(jié)點(diǎn)都包含 k 個(gè)元素，這里 m/2 ≤ k < m，這里 m/2 向下取整。
• 每個(gè)節(jié)點(diǎn)中的元素從小到大排列。
• 每個(gè)元素左結(jié)點(diǎn)的值都小于或等于該元素，右結(jié)點(diǎn)的值都大于或等于該元素。
• 所有的非葉子節(jié)點(diǎn)只存儲(chǔ)關(guān)鍵字信息。
• 所有的葉子結(jié)點(diǎn)中包含了全部元素的信息。
• 所有葉子節(jié)點(diǎn)之間都有一個(gè)鏈指針。

可以看出在該存儲(chǔ)結(jié)構(gòu)中查找特定數(shù)據(jù)的算法復(fù)雜度為 O(log2N)，查找速度僅與樹(shù)高度有關(guān)。
????對(duì)于聚集索引葉子節(jié)點(diǎn)存儲(chǔ)的元素是數(shù)據(jù)塊即為整行數(shù)據(jù)，對(duì)于非聚集索引葉子節(jié)點(diǎn)存儲(chǔ)的元素是索引字段的所對(duì)應(yīng)的聚集索引的值或 rowid，如果需要獲取其它字段信息需要根據(jù)聚集索引的值或 rowid 回表 (BLKUP) 進(jìn)行查詢(xún)。

索引適用范圍：

在以下場(chǎng)景下可考慮創(chuàng)建索引：

• 僅當(dāng)要通過(guò)索引訪問(wèn)表中很少的一部分行（1%~20%）。
• 索引可覆蓋查詢(xún)所需的所有列，不需額外去訪問(wèn)表。

注意

對(duì)于一個(gè)表來(lái)說(shuō)索引并非越多越好，過(guò)多的索引將影響該表的 DML 效率。

存在下列情況將導(dǎo)致無(wú)法使用索引：

組合索引中，條件列中沒(méi)有組合索引的首列。

• 條件列帶有函數(shù)或計(jì)算。
• 索引排序是按照字段值進(jìn)行排序的，字段值通過(guò)函數(shù)或計(jì)算后的值索引無(wú)法獲取。
• 索引過(guò)濾性能不好時(shí)。

建立索引的原則：

• 建立唯一索引。唯一索引能夠更快速地幫助我們進(jìn)行數(shù)據(jù)定位；
• 為經(jīng)常需要進(jìn)行查詢(xún)操作的字段建立索引；
• 對(duì)經(jīng)常需要進(jìn)行排序、分組以及聯(lián)合操作的字段建立索引；
• 在建立索引的時(shí)候，要考慮索引的最左匹配原則（在使用 SQL 語(yǔ)句時(shí)，如果 where 部分的條件不符合最左匹配原則，可能導(dǎo)致索引失效，或者不能完全發(fā)揮建立的索引的功效）；
• 不要建立過(guò)多的索引。因?yàn)樗饕旧頃?huì)占用存儲(chǔ)空間；
• 如果建立的單個(gè)索引查詢(xún)數(shù)據(jù)很多，查詢(xún)得到的數(shù)據(jù)的區(qū)分度不大，則考慮建立合適的聯(lián)合索引；
• 盡量考慮字段值長(zhǎng)度較短的字段建立索引，如果字段值太長(zhǎng)，會(huì)降低索引的效率。

（二）SQL 語(yǔ)句改寫(xiě)

DM 數(shù)據(jù)庫(kù)針對(duì) SQL 語(yǔ)句有以下常見(jiàn)幾種改寫(xiě)方法：

1.優(yōu)化 GROUP BY

提高 GROUP BY 語(yǔ)句的效率，可以在 GROUP BY 之前過(guò)濾掉不需要的內(nèi)容。

--優(yōu)化前

SELECT JOB,AVG(AGE) FROM TEMP

GROUP BY JOB HAVING JOB = 'STUDENT' OR JOB = 'MANAGER';

--優(yōu)化后

SELECT JOB,AVG(AGE) FROM TEMP

WHERE JOB = 'STUDENT' OR JOB = 'MANAGER' GROUP BY JOB;

2.用 UNION ALL 替換 UNION

當(dāng) SQL 語(yǔ)句需要 UNION 兩個(gè)查詢(xún)結(jié)果集合時(shí)，這兩個(gè)結(jié)果集合會(huì)以 UNION ALL 的方式被合并，在輸出最終結(jié)果前進(jìn)行排序。用 UNION ALL 替代 UNION, 這樣排序就不必要了，效率就會(huì)因此得到提高。

注意

UNION 將對(duì)結(jié)果集合排序，這個(gè)操作會(huì)使用到 SORT_AREA_SIZE 這塊內(nèi)存，對(duì)于這塊內(nèi)存的優(yōu)化也很重要；UNION ALL 將重復(fù)輸出兩個(gè)結(jié)果集合中相同記錄，要從業(yè)務(wù)需求判斷使用 UNION ALL 的可行性。

--優(yōu)化前

SELECT USER_ID,BILL_ID FROM USER_TAB1 WHERE AGE = '20'

UNION

SELECT USER_ID,BILL_ID FROM USER_TAB2 WHERE AGE = '20';

?--優(yōu)化后

SELECT USER_ID,BILL_ID FROM USER_TAB1 WHERE AGE = '20'

UNION ALL

SELECT USER_ID,BILL_ID FROM USER_TAB2 WHERE AGE = '20';

3.用 EXISTS 替換 DISTINCT

當(dāng) SQL 包含一對(duì)多表查詢(xún)時(shí)，避免在 SELECT 子句中使用 DISTINCT，一般用 EXISTS 替換 DISTINCT 查詢(xún)更為迅速。

--優(yōu)化前

SELECT DISTINCT USER_ID,BILL_ID FROM USER_TAB1 D,USER_TAB2 E

WHERE D.USER_ID= E.USER_ID;

--優(yōu)化后

SELECT USER_ID,BILL_ID FROM USER_TAB1 D WHERE EXISTS(SELECT 1 FROM USER_TAB2 E WHERE E.USER_ID= D.USER_ID);

4.多使用 COMMIT

可以在程序中盡量多使用COMMIT，這樣程序的性能得到提高，需求也會(huì)因?yàn)镃OMMIT所釋放的資源而減少。
COMMIT 所釋放的資源:

• 回滾段上用于恢復(fù)數(shù)據(jù)的信息；
• 被程序語(yǔ)句獲得的鎖；
• redo log buffer 中的空間；
• 為管理上述 3 種資源中的內(nèi)部花銷(xiāo)。

?5.用WHERE子句替換HAVING子句

避免使用 HAVING 子句，HAVING 只會(huì)在檢索出所有記錄之后才對(duì)結(jié)果集進(jìn)行過(guò)濾，這個(gè)處理需要排序、總計(jì)等操作，可以通過(guò) WHERE 子句限制記錄的數(shù)目。on、where、having 三個(gè)都可以加條件子句，其中，on 是最先執(zhí)行，where 次之，having 最后。

• on 是先把不符合條件的記錄過(guò)濾后才進(jìn)行統(tǒng)計(jì)，在兩個(gè)表聯(lián)接時(shí)才用 on；
• 在單表查詢(xún)統(tǒng)計(jì)的情況下，如果要過(guò)濾的條件沒(méi)有涉及到要計(jì)算字段，where 和 having 結(jié)果是一樣的，但 where 比 having 快；
• 如果涉及到計(jì)算字段，where 的作用時(shí)間是在計(jì)算之前完成，而 having 是在計(jì)算后才起作用，兩者的結(jié)果會(huì)不同；
• 在多表聯(lián)接查詢(xún)時(shí)，on 比 where 更早起作用。首先會(huì)根據(jù)各個(gè)表之間的關(guān)聯(lián)條件，把多個(gè)表合成一個(gè)臨時(shí)表后，由 where 進(jìn)行過(guò)濾再計(jì)算，計(jì)算完再由 having 進(jìn)行過(guò)濾。

6.用TRUNCATE替換DELETE

當(dāng)刪除表中的記錄時(shí)，在通常情況下, 回滾段用來(lái)存放可以被恢復(fù)的信息。如果沒(méi)有 COMMIT 事務(wù)，會(huì)將數(shù)據(jù)恢復(fù)到執(zhí)行刪除命令之前的狀況；而當(dāng)運(yùn)用 TRUNCATE 時(shí)，回滾段不再存放任何可被恢復(fù)的信息。當(dāng)命令運(yùn)行后，數(shù)據(jù)不能被恢復(fù)。因此很少的資源被調(diào)用，執(zhí)行時(shí)間也會(huì)很短。

注意

TRUNCATE 只在刪除全表適用，TRUNCATE 是 DDL 不是 DML。

7.用 EXISTS 替換 IN、用 NOT EXISTS 替換 NOT IN

在基于基礎(chǔ)表的查詢(xún)中可能會(huì)需要對(duì)另一個(gè)表進(jìn)行聯(lián)接。在這種情況下, 使用 EXISTS (或 NOT EXISTS )通常將提高查詢(xún)的效率。在子查詢(xún)中，NOT IN 子句將執(zhí)行一個(gè)內(nèi)部的排序和合并。無(wú)論在哪種情況下，NOT IN 都是最低效的(要對(duì)子查詢(xún)中的表執(zhí)行一個(gè)全表遍歷)，所以盡量將 NOT IN 改寫(xiě)成外連接( Outer Joins )或 NOT EXISTS。

--優(yōu)化前

SELECT A.* FROM TEMP(基礎(chǔ)表) A WHERE AGE > 0

AND A.ID IN(SELECT ID FROM TEMP1 WHERE NAME ='TOM');

--優(yōu)化后

SELECT A.* FROM TEMP(基礎(chǔ)表) A WHERE AGE > 0

AND EXISTS(SELECT 1 FROM TEMP1 WHERE A.ID= ID AND NAME='TOM');

8.半連接優(yōu)化

半連接也是子查詢(xún)的一種，查詢(xún)只返回主表數(shù)據(jù)，子查詢(xún)作為條件過(guò)濾使用。exists 關(guān)注是否有返回行，取決于關(guān)聯(lián)列，in 關(guān)注是否存在過(guò)濾數(shù)據(jù)，在半連接改寫(xiě)中理解這點(diǎn)很重要。

優(yōu)化改寫(xiě)：半連接改 join 。

--改寫(xiě)前，已下兩種寫(xiě)法特征就是執(zhí)行計(jì)劃出現(xiàn) semi 關(guān)鍵字

--寫(xiě)法一：

select EMPNO, ENAME, JOB, MGR, HIREDATE

??from emp2

?where deptno in (select deptno from dept2)

--寫(xiě)法二：

?select EMPNO, ENAME, JOB, MGR, HIREDATE

??from emp2

?where exists (select deptno from dept2 where dept2.deptno = emp2.deptno)

--改寫(xiě)優(yōu)化--當(dāng)子查詢(xún)中部門(mén)表中部門(mén)編號(hào)不存在重復(fù)改寫(xiě)如下：

????select emp2.EMPNO,

???????????emp2.ENAME,

???????????emp2.JOB,

???????????emp2.MGR,

???????????emp2.HIREDATE

??????from emp2inner join dept2

????????on dept2.deptno = emp2.deptno

--若存在數(shù)據(jù)重復(fù)先根據(jù)關(guān)聯(lián)列去重再關(guān)聯(lián)select dept2.*

??from (select distinct deptno from emp2) emp2

?inner join dept2on dept2.deptno = emp2.deptno

9.反連接優(yōu)化

同半連接一樣，查詢(xún)也只返回主表數(shù)據(jù)，通過(guò) not in 和 not exists 過(guò)濾，再改寫(xiě)的過(guò)程中特別要注意反連接 not in 對(duì)空值敏感。

--ept2 deptno 列不存在空值時(shí)，以下兩種寫(xiě)法等價(jià)，當(dāng) not in 存在空時(shí)，無(wú)數(shù)據(jù)行返回，因此 not exists 改寫(xiě) not in 需要加上 not is null

select * from emp2 where deptno not in (select deptno from dept2);

select * from emp2 e where not exists (select * from dept2 d where d.deptno = e.deptno)

--not in、 not exists 改寫(xiě) left join

select * from emp2 E ?where deptno not in (select deptno from dept2 D)

--反連接驅(qū)動(dòng)是 E 表，被驅(qū)動(dòng)是 D 表，所以改寫(xiě) left join ,not in 表示不在此范圍，即 emp2 有的部門(mén)編號(hào)，dept2 沒(méi)有

--左連接會(huì)將右表沒(méi)有的內(nèi)容用 NULL 表示，所以關(guān)聯(lián)后取 d.deptno is null 過(guò)濾select e.*

??from emp2 e

??left join dept2 d

????on d.deptno = e.deptno

?where d.deptno is null

（三）表設(shè)計(jì)優(yōu)化

表設(shè)計(jì)優(yōu)化可以從三個(gè)方面入手：選擇合適的表類(lèi)型、設(shè)置分區(qū)表、設(shè)置全局臨時(shí)表。

1.表類(lèi)型選擇

達(dá)夢(mèng)數(shù)據(jù)庫(kù)提供了三種表類(lèi)型：行存儲(chǔ)表、列存儲(chǔ)表（HUGE）和堆表。運(yùn)維人員可根據(jù)實(shí)際需求選擇合適的表類(lèi)型。

表類(lèi)型	描述	主要特征	適用場(chǎng)景
行存儲(chǔ)表	行存儲(chǔ)是以記錄為單位進(jìn)行存儲(chǔ)的，數(shù)據(jù)頁(yè)面中存儲(chǔ)的是完整的若干條記錄	1．按行存儲(chǔ) 2．每個(gè)表都創(chuàng)建一個(gè) B 樹(shù)，并在葉子上存放數(shù)據(jù)	適用于高并發(fā) OLTP 場(chǎng)景。
列存儲(chǔ)表（HUGE）	列存儲(chǔ)是以列為單位進(jìn)行存儲(chǔ)的，每一個(gè)列的所有行數(shù)據(jù)都存儲(chǔ)在一起，而且一個(gè)指定的頁(yè)面中存儲(chǔ)的都是某一個(gè)列的連續(xù)數(shù)據(jù)。	1．按列存儲(chǔ) 2．非事務(wù)型 HUGE 表：LOG NONE、LOG LAST、LOG ALL3．事務(wù)型 HUGE 表	適用于海量數(shù)據(jù)分析場(chǎng)景
堆表	堆表是指采用了物理 ROWID 形式的表，即使用文件號(hào)、頁(yè)號(hào)和頁(yè)內(nèi)偏移而得到 ROWID 值，這樣就不需要存儲(chǔ) ROWID 值，可以節(jié)省空間	1．數(shù)據(jù)頁(yè)都是通過(guò)鏈表形式存儲(chǔ) 2．可設(shè)置并發(fā)分支	并發(fā)插入性能較高

2.水平分區(qū)表

分區(qū)類(lèi)型

• 范圍(range)水平分區(qū)：對(duì)表中的某些列上值的范圍進(jìn)行分區(qū)，根據(jù)某個(gè)值的范圍，決定將該數(shù)據(jù)存儲(chǔ)在哪個(gè)分區(qū)上；
• 哈希（hash）水平分區(qū)：通過(guò)指定分區(qū)編號(hào)來(lái)均勻分布數(shù)據(jù)的一種分區(qū)類(lèi)型，通過(guò)在 I/O 設(shè)備上進(jìn)行散列分區(qū)，使得這些分區(qū)大小基本一致；
• 列表（list）水平分區(qū)：通過(guò)指定表中的某個(gè)列的離散值集，來(lái)確定應(yīng)當(dāng)存儲(chǔ)在一起的數(shù)據(jù)。例如，可以對(duì)表上的 status 列的值在('A'，'H'，'O')放在一個(gè)分區(qū)，值在('B'，'I'，'P')放在另一個(gè)分區(qū)，以此類(lèi)推；
• 多級(jí)分區(qū)表：按上述三種分區(qū)方法進(jìn)行任意組合，將表進(jìn)行多次分區(qū)，稱(chēng)為多級(jí)分區(qū)表。

分區(qū)優(yōu)勢(shì)

• 減少訪問(wèn)數(shù)據(jù)
• 操作靈活：可以操作分區(qū) truncate、分區(qū) drop、分區(qū) add、分區(qū) exchange

舉例說(shuō)明

select *? from range_part_tab

where deal_date >= TO_DATE('2019-08-04','YYYY-MM-DD')

and deal_date <= TO_DATE('2019-08-07','YYYY-MM-DD');

執(zhí)行計(jì)劃：

1 #NSET2:[24,18750,158]

2 ?#PRJT2:[24,18750,158];exp_num(6),is_atom(FALSE)

3 ??#PARALLEL:[24,18750,158];scan_type(GE_LE),key_num(0,1,1)

4 ???#SLCT2:[24,18750,158];[(RANGE_PART_TAB.DEAL_DATE >= var2 AND RANGE_PART_TAB.DEAL_DATE <= var4)]

5 ?????#CSCN2:[73,500000,158];INDEX33555933(RANGE_PART_TAB)

--#PARALLEL：控制水平分區(qū)子表的掃描

• 對(duì)主表和所有子表都收集統(tǒng)計(jì)信息
• 對(duì)索引收集統(tǒng)計(jì)信息

注意

如果 SQL 中有可利用的索引，普通表也可能比分區(qū)表性能高。

3.全局臨時(shí)表

當(dāng)處理復(fù)雜的查詢(xún)或事務(wù)時(shí)，由于在數(shù)據(jù)寫(xiě)入永久表之前需要暫時(shí)存儲(chǔ)一些行信息或需要保存查詢(xún)的中間結(jié)果，可能需要一些表來(lái)臨時(shí)存儲(chǔ)這些數(shù)據(jù)。DM 允許創(chuàng)建臨時(shí)表來(lái)保存會(huì)話(huà)甚至事務(wù)中的數(shù)據(jù)。在會(huì)話(huà)或事務(wù)結(jié)束時(shí)，這些表上的數(shù)據(jù)將會(huì)被自動(dòng)清除。

全局臨時(shí)表類(lèi)型

• 事務(wù)級(jí)-ON COMMIT DELETE ROWS
• 會(huì)話(huà)級(jí)-ON COMMIT PRESERVE ROWS

全局臨時(shí)表優(yōu)勢(shì)

• 不同 session 數(shù)據(jù)獨(dú)立
• 自動(dòng)清理

舉例說(shuō)明

第一步：原始語(yǔ)句如下：

--T_1 視圖（與 oracle 的 dblink 全表查詢(xún)）

--T_1 視圖的結(jié)構(gòu)為

--(INIT_DATE int , BRANCH_NO int , FUND_ACCOUNT int , BUSINESS_FLAG int , remark varchar(32))

--T_2 表

--T_2 表的結(jié)構(gòu)為

--(BRANCH_NO int,FUND_ACCOUNT int , prodta_no int,v_config_4662 varchar(32))

select a.init_date as?oc_date,a.BRANCH_NO,a.FUND_ACCOUNT,a.BUSINESS_FLAG,a.remark,b.BRANCH_NO,b.FUND_ACCOUNT,b.prodta_no

from T_1 a,T_2 b

where init_date = 20181120

AND a.BRANCH_NO = b.BRANCH_NO

AND a.FUND_ACCOUNT = b.FUND_ACCOUNT

and instr(v_config_4662, ',' || b.prodta_no || ',')>0

and a.BUSINESS_FLAG in (2629,2630)

and nvl(a.remark,' ')not like '%實(shí)時(shí)TA%';

第二步：創(chuàng)建臨時(shí)表 T1_20181122，將 T_1 視圖中部分?jǐn)?shù)據(jù)插入臨時(shí)表中。

CREATE GLOBAL TEMPORARY TABLE "T1_20181122"

(init_date int, ?BRANCH_NO int, FUND_ACCOUNT int,BUSINESS_FLAG int,remark varchar(32));

--插入dblink獲取的數(shù)據(jù)到臨時(shí)表

insert into T1_20181122

select *

from T_1 a

where init_date = 20181120

and a.BUSINESS_FLAG in (2629,2630)

and nvl(a.remark,' ')not like '%實(shí)時(shí)TA%';

第三步：語(yǔ)句改寫(xiě)。

select a.init_date as oc_date,a.BRANCH_NO,a.FUND_ACCOUNT,a.BUSINESS_FLAG,a.remark,b.BRANCH_NO,b.FUND_ACCOUNT,b.prodta_no

from T1_20181122 a, T_2 b

where a.BRANCH_NO = b.BRANCH_NO

AND a.FUND_ACCOUNT = b.FUND_ACCOUNT

and instr(v_config_4662, ',' || b.prodta_no || ',')>0;

執(zhí)行計(jì)劃：50 分鐘 >>1 分鐘。

--原語(yǔ)句執(zhí)行計(jì)劃

1 #NSET2:[11,1,1644]

1. #PRJT2:[11,1,1644];exp_num(41),is_atom(FALSE)

3 #HASH2 INNER JOIN:[11,1,1644];KEY_NUM(2);

4 #SLCT2:[0,1,270];exp11>0

5 #CSCN2:[0,1,270];INDEX33560908(T_HSOTCPRODCASHACCT as B)

6 #HASH RIGHT SEMI JOIN2:[10,380,1374];n_keys(1)

7 #CONST VALUE LIST:[0,2,30];row_num(2),col_num(1),

8 #SLCT2:[10,380,1374];(A.INIT_DATE = var4 AND NOT(exp11 LIKE '%實(shí)時(shí)TA%'))

9 #PRJT2:[10,1000,1374];exp_num(13),is_atom(FALSE)

10 #REMOTE SCAN:[0,0,0] HIS_FUNDJOUR@HS08HIS

--改寫(xiě)后執(zhí)行計(jì)劃

1 ??#NSET2: [1, 1, 124]

2 ????#PRJT2: [1, 1, 124]; exp_num(8), is_atom(FALSE)

3 ??????#HASH2 INNER JOIN: [1, 1, 124]; ?KEY_NUM(2); KEY(B.BRANCH_NO=A.BRANCH_NO AND B.FUND_ACCOUNT=A.FUND_ACCOUNT) KEY_NULL_EQU(0, 0)

4 ????????#SLCT2: [1, 1, 60]; exp11 > 0

5 ??????????#CSCN2: [1, 1, 60]; INDEX33555476(T_2 as B)

6 ????????#CSCN2: [1, 1, 64]; INDEX33555478(T1_20181122 as A)

4.hint優(yōu)化sql

當(dāng)統(tǒng)計(jì)信息已收集，且索引也按照需求建立，sql 執(zhí)行效率仍然不符合預(yù)期，可以考慮添加 hint 方式來(lái)進(jìn)行優(yōu)化。

--例如：多個(gè)單表查詢(xún)通過(guò) union 連接后組成一個(gè)視圖，然后視圖通過(guò) where 過(guò)濾，過(guò)濾條件在每個(gè)單表中過(guò)濾性較好，且存在索引，通過(guò)視圖過(guò)濾無(wú)法使用索引，執(zhí)行時(shí)間55s。select * from dms.view_da_base where ahdm = '00005fe8-b171-4292-b776-b53c3fd65923';

使用 hint 修改參數(shù) VIEW_FILTER_MERGING 的值對(duì)視圖條件進(jìn)行優(yōu)化，當(dāng)參數(shù)值取 1 時(shí)表示盡可能地進(jìn)行視圖條件合并。

select /*+ VIEW_FILTER_MERGING(1) */ * from dms.view_da_base where ahdm = '00005fe8-b171-4292-b776-b53c3fd65923';

當(dāng)系統(tǒng)在運(yùn)行過(guò)程中，出現(xiàn)慢 sql 需要緊急進(jìn)行優(yōu)化處理時(shí)，可以在數(shù)據(jù)庫(kù)端通過(guò) SF_INJECT_HINT 函數(shù)將 hint 與 sql 進(jìn)行綁定對(duì)慢 SQL 進(jìn)行優(yōu)化。

--設(shè)置 INI 參數(shù) ENABLE_INJECT_HINT 為 1

SP_SET_PARA_VALUE(1,'ENABLE_INJECT_HINT',1); ??

?--計(jì)劃添加 hint 后的 SQL

select ???/*+ VIEW_FILTER_MERGING(1) */ ?* from dms.view_da_base where ahdm = '00005fe8-b171-4292-b776-b53c3fd65923';

---模糊匹配 sql 進(jìn)行 hint 綁定

?SF_INJECT_HINT('select * from dms.view_da_base where ???,', 'VIEW_FILTER_MERGING(1)', 'VIEW_HINT', 'to testfunction of injecting hint', TRUE, TRUE);

注意

此優(yōu)化方式不推薦作為常規(guī)優(yōu)化方法使用，特定場(chǎng)景優(yōu)化或應(yīng)急處理時(shí)使用。

達(dá)夢(mèng)數(shù)據(jù)庫(kù) - 新一代大型通用關(guān)系型數(shù)據(jù)庫(kù) | 達(dá)夢(mèng)在線服務(wù)平臺(tái)