wordpress tag 別名北京優(yōu)化seo公司
目錄
一、定位慢 SQL
（一）開啟跟蹤日志記錄
1.跟蹤日志記錄配置
（二）通過系統(tǒng)視圖查看
1.SQL 記錄配置
2.查詢方式
二、SQL分析方法
（一）執(zhí)行計劃
1.概述
2.查看執(zhí)行計劃
（二）常見操作符解讀
1.NSET：結(jié)果集收集
2.PRJT：投影
3.SLCT：選擇
4.AAGR：簡單聚集
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.功能的開啟/關閉
（四）dbms_sqltune 工具
（五）存儲過程調(diào)試
1.圖形化界面工具進行調(diào)試
2.DMDBG 進行調(diào)試
三、SQL 語句優(yōu)化
?（一）索引
（二）SQL 語句改寫
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)化
（三）表設計優(yōu)化
1.表類型選擇
2.水平分區(qū)表
3.全局臨時表
4.hint優(yōu)化sql
一、定位慢 SQL
定位執(zhí)行效率低的 SQL 語句是 SQL 優(yōu)化的第一步。待優(yōu)化的 SQL 可大致分為兩類：SQL執(zhí)行時間在十幾秒到數(shù)十秒之間，但執(zhí)行頻率不高，此類 SQL 對數(shù)據(jù)庫整體性能影響并不大，可以放到最后進行優(yōu)化。
SQL 單獨執(zhí)行時間可能很快，在幾百毫秒到幾秒之間，但執(zhí)行頻率非常高，甚至達到每秒上百次，高并發(fā)下執(zhí)行效率降低，很可能導致系統(tǒng)癱瘓，此類 SQL 是優(yōu)化的首要對象。
（一）開啟跟蹤日志記錄
跟蹤日志文件是一個純文本文件，以”dmsql_實例名_日期_時間命名.log”，默認生成在 DM 安裝目錄的 log 子目錄下。跟蹤日志內(nèi)容包含系統(tǒng)各會話執(zhí)行的 SQL 語句、參數(shù)信息、錯誤信息、執(zhí)行時間等。跟蹤日志主要用于分析錯誤和分析性能問題，基于跟蹤日志可以對系統(tǒng)運行狀態(tài)進行分析。
1.跟蹤日志記錄配置
（1）配置 dm.ini 文件，設置 SVR_LOG = 1 以啟用 sqllog.ini 配置，該參數(shù)為動態(tài)參數(shù)，可通過調(diào)用數(shù)據(jù)庫函數(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 對服務器產(chǎn)生較大的影響，可以配置異步日志刷新（參數(shù) ASYNC_FLUSH 設置為 1）。
（3）如果對 sqllog.ini 進行了修改，可通過調(diào)用以下函數(shù)即時生效，無需重啟數(shù)據(jù)庫，如下所示：
SP_REFRESH_SVR_LOG_CONFIG();
（二）通過系統(tǒng)視圖查看
DM 數(shù)據(jù)庫提供系統(tǒng)動態(tài)視圖，可自動記錄執(zhí)行時間超過設定閾值的 SQL 語句。
1.SQL 記錄配置
當 INI 參數(shù) ENABLE_MONITOR=1、MONITOR_TIME=1 打開時，顯示系統(tǒng)最近 1000 條執(zhí)行時間超過預定值的 SQL 語句，默認預定值為 1000 毫秒。
以上兩個參數(shù)可通過 SP_SET_PARA_VALUE 系統(tǒng)函數(shù)修改，通過 SF_GET_PARA_VALUE 系統(tǒng)函數(shù)查看當前值。
--修改參數(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');
注意
兩個參數(shù)均為動態(tài)參數(shù)，可直接調(diào)用系統(tǒng)函數(shù)進行修改，無須重啟數(shù)據(jù)庫實例服務；
通過 SP_SET_PARA_VALUE 方式修改的參數(shù)值僅對當前會話以及新建會話生效，對其它已建立會話不生效。
2.查詢方式
- 查詢當前正在執(zhí)行的會話信息。
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 語句；FULLSQL 列存儲了完整的執(zhí)行 SQL 語句。
- 查詢超過執(zhí)行時間閾值的 SQL 語句。
可通過查詢 V$LONG_EXEC_SQLS 系統(tǒng)視圖獲取結(jié)果：
SELECT * FROM V$LONG_EXEC_SQLS;
查詢結(jié)果字段詳細信息介紹如下表所示：
| 列名 | 說明 |
| SESS_ID | 會話 ID，會話唯一標識 |
| SQL_ID | 語句 ID，語句唯一標識 |
| SQL_TEXT | SQL 文本 |
| EXEC_TIME | 執(zhí)行時間（毫秒） |
| FINISH_TIME | 執(zhí)行結(jié)束時間 |
| N_RUNS | 執(zhí)行次數(shù) |
| SEQNO | 編號 |
| TRX_ID | 事務號 |
二、SQL分析方法
（一）執(zhí)行計劃
1.概述
簡單來說，執(zhí)行計劃就是一條 SQL 語句在數(shù)據(jù)庫中的執(zhí)行過程或訪問路徑的描述。SQL 語言是種功能強大且非過程性的編程語言，比如以下這條 SQL 語句：
SELECT * FROM T1, T2 WHERE T1.ID = T2.ID AND T1.ID = 6;
開發(fā)人員只關心 SQL 語句能否返回 T1 與 T2 表的關聯(lián)查詢結(jié)果，不需要指定該 SQL 如何執(zhí)行，也就是說不關心該 SQL 是先訪問 T1 表還是先訪問 T2 表。對于 SQL 來說，兩種訪問方式就是兩個執(zhí)行計劃，查詢優(yōu)化器 (CBO) 將根據(jù)代價也就是開銷來選擇最優(yōu)的執(zhí)行計劃。以如下 SQL 語句執(zhí)行計劃為例：
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í)行計劃的每行即為一個計劃節(jié)點，主要包含三部分信息。
- 第一部分 NEST2、PRJT2、CSCN2 為操作符及數(shù)據(jù)庫具體執(zhí)行了什么操作。
- 第二部分的三元組為該計劃節(jié)點的執(zhí)行代價，具體含義為[代價，記錄行數(shù)，字節(jié)數(shù)]。
- 第三部分為操作符的補充信息。
例如：第三個計劃節(jié)點表示操作符是 CSCN2（即全表掃描），代價估算是 0 ms，掃描的記錄行數(shù)是 1282 行，輸出字節(jié)數(shù)是 396 個。
各計劃節(jié)點的執(zhí)行順序為：縮進越多的越先執(zhí)行，同樣縮進的上面的先執(zhí)行，下面的后執(zhí)行，上下的優(yōu)先級高于內(nèi)外?？s進最深的，最先執(zhí)行；縮進深度相同的，先上后下?？谠E：最右最上先執(zhí)行。
#CSCN2: [1, 2, 12]; INDEX33555496(TEST)
?操作符，[代價，行數(shù)，字節(jié)數(shù)] 描述
2.查看執(zhí)行計劃
達夢數(shù)據(jù)庫可通過兩種方式查看執(zhí)行計劃。
方式一：通過 DM 數(shù)據(jù)庫配套管理工具查看。
方式二：使用 explain 命令查看。
以下對兩種查看方式進行介紹。
（1）管理工具查看執(zhí)行計劃
（2）在 DM 配套管理工具中，選中待查看執(zhí)行計劃的 SQL 語句，點擊工具欄中的按鈕，或使用快捷鍵 F9，即可查看執(zhí)行計劃。
（3）使用 explain 命令查看執(zhí)行計劃
（4）在待查看執(zhí)行計劃的 SQL 語句前加 explain 執(zhí)行 SQL 語句即可查看預估的執(zhí)行計劃：
explain select * from sysobjects;
--執(zhí)行計劃
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 命令行查真實執(zhí)行計劃
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)
重點關注 logical reads（邏輯讀）和 physical reads（物理讀）相應的指標值，并結(jié)合 rows processed 返回處理行數(shù)多少來分析。如果返回行數(shù)少（并且 bytes sent to client 總量不大），應盡可能減少 IO 開銷，讓執(zhí)行計劃選擇正確的索引路徑。
Sort(disk) 一般因排序（ hash join 發(fā)生歸并、order by、group by 場景）區(qū)內(nèi)存不足，如果數(shù)據(jù)庫服務器物理內(nèi)存充足，可以適當上調(diào)排序區(qū)內(nèi)存，盡量避免操作刷盤，否則會影響執(zhí)行性能。
（二）常見操作符解讀
下面通過幾個例子來介紹一些常見操作符。準備測試表及數(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é)果集收集的操作符，一般是查詢計劃的頂層節(jié)點，優(yōu)化工作中無需對該操作符過多關注，一般沒有優(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 是關系的【投影】 (project) 運算，用于選擇表達式項的計算。廣泛用于查詢，排序，函數(shù)索引創(chuàng)建等。優(yōu)化工作中無需對該操作符過多關注，一般沒有優(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 是關系的【選擇】運算，用于查詢條件的過濾?？杀容^返回結(jié)果集與代價估算中是否接近，如相差較大可考慮收集統(tǒng)計信息。若該過濾條件過濾性較好，可考慮在條件列增加索引。
4.AAGR：簡單聚集
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 用于沒有 GROUP BY 的 COUNT、SUM、AGE、MAX、MIN 等聚集函數(shù)的計算。
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 用于沒有過濾條件時，從表或索引快速獲取 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 用于分組列沒有索引只能走全表掃描的分組聚集，該示例中 C2 列沒有創(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 先使用二級索引索引定位 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 的縮寫即通過聚集索引掃描全表，全表掃描是最簡單的查詢，如果沒有選擇謂詞，或者沒有索引可以利用，則系統(tǒng)一般只能做全表掃描。全表掃描 I/O 開銷較大，在一個高并發(fā)的系統(tǒ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 是二級索引掃描即先掃描索引，再通過主鍵、聚集索引、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 是聚集索引掃描只需要掃描索引，不需要掃描表，即無需 BLKUP 操作，如果 BLKUP 開銷較大時，可考慮創(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)連接是最基礎的連接方式，將一張表（驅(qū)動表）的每一個值與另一張表（被驅(qū)動表）的所有值拼接，形成一個大結(jié)果集，再從大結(jié)果集中過濾出滿足條件的行。驅(qū)動表的行數(shù)就是循環(huán)的次數(shù)，將在很大程度上影響執(zhí)行效率。
連接列是否有索引，都可以走 NEST LOOP，但沒有索引，執(zhí)行效率會很差，語句如下所示：
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)
可針對 T1 和 T2 的連接列創(chuàng)建索引，并收集統(tǒng)計信息，語句如下所示：
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í)行計劃可看出效率明顯改善，代價有顯著下降，語句如下所示：
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)
適用場景：
驅(qū)動表有很好的過濾條件
表連接條件能使用索引
結(jié)果集比較小
12.HASH JOIN：哈希連接
哈希連接是在沒有索引或索引無法使用情況下大多數(shù)連接的處理方式。哈希連接使用關聯(lián)列去重后結(jié)果集較小的表做成 HASH 表，另一張表的連接列在 HASH 后向 HASH 表進行匹配，這種情況下匹配速度極快，主要開銷在于對連接表的全表掃描以及 HASH 運算。
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)有很多這種連接時，需調(diào)整以下 3 個參數(shù)：
| 參數(shù)名 | 說明 |
| HJ_BUF_GLOBAL_SIZE | HASH 連接操作符的數(shù)據(jù)總緩存大小 ()>=HJ_BUF_SIZE)，系統(tǒng)級參數(shù)，以兆為單位。有效值范圍（10~500000） |
| HJ_BUF_SIZE | 單個哈希連接操作符的數(shù)據(jù)總緩存大小，以兆為單位。有效值范圍（2~100000） |
| HJ_BLK_SIZE | 哈希連接操作符每次分配緩存( BLK )大小，以兆為單位，必須小于 HJ_BUF_SIZE。有效值范圍（1~50） |
13.MERGE JOIN：歸并排序連接
歸并排序連接需要兩張表的連接列都有索引，對兩張表掃描索引后按照索引順序進行歸并。
-- 對連接列創(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ù)庫自帶的 SQL 性能分析工具，能夠統(tǒng)計 SQL 語句執(zhí)行過程中每個操作符的實際開銷，為 SQL 優(yōu)化提供依據(jù)以及指導。
1.功能的開啟/關閉
ET 功能默認關閉，可通過配置 INI 參數(shù)中的 ENABLE_MONITOR=1、MONITOR_SQL_EXEC=1 開啟該功能。
--兩個參數(shù)均為動態(tài)參數(shù)，可直接調(diào)用系統(tǒng)函數(shù)進行修改
SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1);
SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',1);
--會話級別修改只在當前會話生效
SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC',1);
--關閉 ET
SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',0);
SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',0);
注意
ET 功能的開啟將對數(shù)據(jù)庫整體性能造成一定影響，優(yōu)化工作結(jié)束后盡量關閉該功能以提升數(shù)據(jù)庫整體運行效率；DM數(shù)據(jù)庫新版本的開啟ET功能時需要設置：ENABLE_MONITOR = 1（默認打開）MONITOR_TIME = 1（默認打開）MONITOR_SQL_EXEC = 1（設置成1）。
（四）dbms_sqltune 工具
DBMS_SQLTUNE 包提供一系列實時 SQL 監(jiān)控的方法。當 SQL 監(jiān)控功能開啟后，DBMS_SQLTUNE 包可以實時監(jiān)控 SQL 執(zhí)行過程中的信息，包括：執(zhí)行時間、執(zhí)行代價、執(zhí)行用戶、統(tǒng)計信息等情況。
使用前提：建議會話級開啟參數(shù) MONITOR_SQL_EXEC=1，而 MONITOR_SQL_EXEC 在達夢數(shù)據(jù)庫中一般默認是 1，無需調(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 功能更強大，能夠獲取 IO 操作量，查看真實執(zhí)行計劃，每個操作符消耗占比和相應的花費時間，還能看出每個操作符執(zhí)行的次數(shù)，非常便于了解執(zhí)行計劃中瓶頸位置。
dbms_sqltune 功能遠不止定位執(zhí)行計劃瓶頸，還擁有調(diào)優(yōu)助手功能（建議性提示建某索引和收集某統(tǒng)計信息）。
（五）存儲過程調(diào)試
在 DM 數(shù)據(jù)庫中，我們可以利用 DM PL/SQL 進行存儲過程及函數(shù)的編寫。有時，我們編寫的程序塊中可能會有 BUG 導致編譯失敗，這種情況下就需要用到相關工具來進行調(diào)試。本章節(jié)主要介紹兩種存儲過程的調(diào)試方法。
1.圖形化界面工具進行調(diào)試
在有圖形化界面的情況下，我們可以利用管理工具來進行匿名塊的調(diào)試。
（1）使用匿名塊調(diào)試
開啟系統(tǒng)包 DEBUG SYSTEM?！竟ぞ甙?>DEBUG SYSTEM-> 右鍵->【啟用】；
點擊上方的【調(diào)試】按鈕進入調(diào)試。
（2）創(chuàng)建測試存儲過程，并進行調(diào)試
創(chuàng)建測試存儲過程。
--當i到3的時候就會報除0錯誤DECLARE
--變量
aa int;BEGIN
--調(diào)試語句
for i in 1..3
loop
aa = 1 / (3-i);
print aa;
end loop;
END
進入調(diào)試，點擊【進入】按鈕，會依照代碼邏輯進行分步調(diào)試，顯示區(qū)域會顯示當前變量執(zhí)行結(jié)果。若遇到報錯會終止調(diào)試，并顯示報錯內(nèi)容，可根據(jù)報錯內(nèi)容進行代碼修改。
2.DMDBG 進行調(diào)試
當我們沒有圖形界面，或者通過遠程連接服務器的時候，我們也可以利用 DM 提供的命令行調(diào)試工具 dmdbg ，來完成同樣的任務。
（1）登錄 dmdbg。dmdbg 與 disql 同級目錄，都在 dmdbms/bin 目錄下，登錄方式也與 disql 類似：
cd /dmdbms/bin
./dmdbg SYSDBA/SYSDBA@LOCALHOST:5236
--可以使用 help 命令查看參數(shù)
DEG> help
（2）引用存儲過程。在 disql 中創(chuàng)建如下測試存儲過程，然后在 DBG 中把?call TEST_DMDBG;?放到 SQL 內(nèi)執(zhí)行，如下所示：
--注意調(diào)試前確保已經(jīng)開啟系統(tǒng)包 DEBUG SYSTEM--登錄disql，在disql中創(chuàng)建如下測試存儲過程
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ào)試，將斷點放到最開始的位置，可以在需要的行數(shù)打上斷點，如下所示：
DBG> B 0
Breakpoint 1 at @dbg_main, line: 1@{call TEST_DMDBG;}
（4）開始調(diào)試。
DBG> r
Breakpoint 1, ?line: 1@{call TEST_DMDBG;}
（5）進入循環(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）查看當前的堆棧。
DBG> bt
\#0 ?????SYSDBA.TEST_DMDBG() ????line: 7@{ ????aa = 1 / (3-i);}
\#1 ?????@dbg_main ??????line: 1@{call TEST_DMDBG;}
（7）查看當前變量的數(shù)值。
--可以通過 P 變量名的方式輸出打印
DBG> p aa
$3 = 0--當 i 到3的時候就會報除0錯誤
DBG> s
[TEST_DMDBG] 除0錯誤.error code=-6103--報錯停止
三、SQL 語句優(yōu)化
關于查詢語句，有以下幾點特征：
- 返回數(shù)據(jù)越多，語句執(zhí)行時間越長；
- 分頁是一個優(yōu)化重點，order by 排序大小由結(jié)果集大小決定，過大會在臨時表空間排序，性能降低；
- 一些語句會隱式排序，比如 uinon group by；
- buffer 過小，數(shù)據(jù)頁頻繁的換入換出。
關于優(yōu)化 sql 語句：
- 通過各種手段減少 sql 執(zhí)行過程中的 IO 代價，內(nèi)存中的計算，臨時表使用等；
- 表與表之間的關系，即關聯(lián)條件之間的數(shù)據(jù)對應關系；
- 表數(shù)據(jù)量大小，對于 OLTP 是否滿足小表驅(qū)動大表；
- sql 返回結(jié)果集多少，如果返回結(jié)果集少，sql 優(yōu)化余地較大；
- 合理利用索引（組合索引）的特點，雖然維護索引也需要代價，但是對于查詢來說，很多時候效果立竿見影。
關于訪問和連接方法：
- 全表掃描訪問；
- 索引掃描訪問；
- 嵌套循環(huán)連接；
- 哈希連接；
- 歸并連接；
- 半連接、反連接；
- n 張表至少有 n-1 次連接。
?（一）索引
索引是一種特殊的數(shù)據(jù)庫結(jié)構(gòu)，由數(shù)據(jù)表中的一列或多列組合而成，可以用來快速查詢數(shù)據(jù)表中有某一特定值的記錄。
索引結(jié)構(gòu)：最常見的索引結(jié)構(gòu)為 B*樹索引
最頂層的為根節(jié)點，最底層的為葉子節(jié)點，中間層為內(nèi)節(jié)點。實際使用當中一般不止 3 層（取決于數(shù)據(jù)量大小），除根節(jié)點以及葉子節(jié)點以外僅為內(nèi)節(jié)點。對于一個 m 階（本例中 m=2）的 B*樹存儲結(jié)構(gòu)有以下幾個特點：
- 每個結(jié)點最多有 m 個子結(jié)點。
- 除了根結(jié)點和葉子結(jié)點外，每個結(jié)點最少有 m/2（向上取整）個子結(jié)點。
- 如果根結(jié)點不是葉子結(jié)點，那根結(jié)點至少包含兩個子結(jié)點。
- 所有的葉子結(jié)點都位于同一層。
- 每個結(jié)點都包含 k 個元素，這里 m/2 ≤ k < m，這里 m/2 向下取整。
- 每個節(jié)點中的元素從小到大排列。
- 每個元素左結(jié)點的值都小于或等于該元素，右結(jié)點的值都大于或等于該元素。
- 所有的非葉子節(jié)點只存儲關鍵字信息。
- 所有的葉子結(jié)點中包含了全部元素的信息。
- 所有葉子節(jié)點之間都有一個鏈指針。
可以看出在該存儲結(jié)構(gòu)中查找特定數(shù)據(jù)的算法復雜度為 O(log2N)，查找速度僅與樹高度有關。
????對于聚集索引葉子節(jié)點存儲的元素是數(shù)據(jù)塊即為整行數(shù)據(jù)，對于非聚集索引葉子節(jié)點存儲的元素是索引字段的所對應的聚集索引的值或 rowid，如果需要獲取其它字段信息需要根據(jù)聚集索引的值或 rowid 回表 (BLKUP) 進行查詢。
索引適用范圍：
在以下場景下可考慮創(chuàng)建索引：
- 僅當要通過索引訪問表中很少的一部分行（1%~20%）。
- 索引可覆蓋查詢所需的所有列，不需額外去訪問表。
注意
對于一個表來說索引并非越多越好，過多的索引將影響該表的 DML 效率。
存在下列情況將導致無法使用索引：
組合索引中，條件列中沒有組合索引的首列。
- 條件列帶有函數(shù)或計算。
- 索引排序是按照字段值進行排序的，字段值通過函數(shù)或計算后的值索引無法獲取。
- 索引過濾性能不好時。
建立索引的原則：
- 建立唯一索引。唯一索引能夠更快速地幫助我們進行數(shù)據(jù)定位；
- 為經(jīng)常需要進行查詢操作的字段建立索引；
- 對經(jīng)常需要進行排序、分組以及聯(lián)合操作的字段建立索引；
- 在建立索引的時候，要考慮索引的最左匹配原則（在使用 SQL 語句時，如果 where 部分的條件不符合最左匹配原則，可能導致索引失效，或者不能完全發(fā)揮建立的索引的功效）；
- 不要建立過多的索引。因為索引本身會占用存儲空間；
- 如果建立的單個索引查詢數(shù)據(jù)很多，查詢得到的數(shù)據(jù)的區(qū)分度不大，則考慮建立合適的聯(lián)合索引；
- 盡量考慮字段值長度較短的字段建立索引，如果字段值太長，會降低索引的效率。
（二）SQL 語句改寫
DM 數(shù)據(jù)庫針對 SQL 語句有以下常見幾種改寫方法：
1.優(yōu)化 GROUP BY
提高 GROUP BY 語句的效率，可以在 GROUP BY 之前過濾掉不需要的內(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
當 SQL 語句需要 UNION 兩個查詢結(jié)果集合時，這兩個結(jié)果集合會以 UNION ALL 的方式被合并，在輸出最終結(jié)果前進行排序。用 UNION ALL 替代 UNION, 這樣排序就不必要了，效率就會因此得到提高。
注意
UNION 將對結(jié)果集合排序，這個操作會使用到 SORT_AREA_SIZE 這塊內(nèi)存，對于這塊內(nèi)存的優(yōu)化也很重要；UNION ALL 將重復輸出兩個結(jié)果集合中相同記錄，要從業(yè)務需求判斷使用 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
當 SQL 包含一對多表查詢時，避免在 SELECT 子句中使用 DISTINCT，一般用 EXISTS 替換 DISTINCT 查詢更為迅速。
--優(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，這樣程序的性能得到提高，需求也會因為COMMIT所釋放的資源而減少。
COMMIT 所釋放的資源:
- 回滾段上用于恢復數(shù)據(jù)的信息；
- 被程序語句獲得的鎖；
- redo log buffer 中的空間；
- 為管理上述 3 種資源中的內(nèi)部花銷。
?5.用WHERE子句替換HAVING子句
避免使用 HAVING 子句，HAVING 只會在檢索出所有記錄之后才對結(jié)果集進行過濾，這個處理需要排序、總計等操作，可以通過 WHERE 子句限制記錄的數(shù)目。on、where、having 三個都可以加條件子句，其中，on 是最先執(zhí)行，where 次之，having 最后。
- on 是先把不符合條件的記錄過濾后才進行統(tǒng)計，在兩個表聯(lián)接時才用 on；
- 在單表查詢統(tǒng)計的情況下，如果要過濾的條件沒有涉及到要計算字段，where 和 having 結(jié)果是一樣的，但 where 比 having 快；
- 如果涉及到計算字段，where 的作用時間是在計算之前完成，而 having 是在計算后才起作用，兩者的結(jié)果會不同；
- 在多表聯(lián)接查詢時，on 比 where 更早起作用。首先會根據(jù)各個表之間的關聯(lián)條件，把多個表合成一個臨時表后，由 where 進行過濾再計算，計算完再由 having 進行過濾。
6.用TRUNCATE替換DELETE
當刪除表中的記錄時，在通常情況下, 回滾段用來存放可以被恢復的信息。如果沒有 COMMIT 事務，會將數(shù)據(jù)恢復到執(zhí)行刪除命令之前的狀況；而當運用 TRUNCATE 時，回滾段不再存放任何可被恢復的信息。當命令運行后，數(shù)據(jù)不能被恢復。因此很少的資源被調(diào)用，執(zhí)行時間也會很短。
注意
TRUNCATE 只在刪除全表適用，TRUNCATE 是 DDL 不是 DML。
7.用 EXISTS 替換 IN、用 NOT EXISTS 替換 NOT IN
在基于基礎表的查詢中可能會需要對另一個表進行聯(lián)接。在這種情況下, 使用 EXISTS (或 NOT EXISTS )通常將提高查詢的效率。在子查詢中，NOT IN 子句將執(zhí)行一個內(nèi)部的排序和合并。無論在哪種情況下，NOT IN 都是最低效的(要對子查詢中的表執(zhí)行一個全表遍歷)，所以盡量將 NOT IN 改寫成外連接( Outer Joins )或 NOT EXISTS。
--優(yōu)化前
SELECT A.* FROM TEMP(基礎表) A WHERE AGE > 0
AND A.ID IN(SELECT ID FROM TEMP1 WHERE NAME ='TOM');
--優(yōu)化后
SELECT A.* FROM TEMP(基礎表) A WHERE AGE > 0
AND EXISTS(SELECT 1 FROM TEMP1 WHERE A.ID= ID AND NAME='TOM');
8.半連接優(yōu)化
半連接也是子查詢的一種，查詢只返回主表數(shù)據(jù)，子查詢作為條件過濾使用。exists 關注是否有返回行，取決于關聯(lián)列，in 關注是否存在過濾數(shù)據(jù)，在半連接改寫中理解這點很重要。
優(yōu)化改寫：半連接改 join 。
--改寫前，已下兩種寫法特征就是執(zhí)行計劃出現(xiàn) semi 關鍵字
--寫法一：
select EMPNO, ENAME, JOB, MGR, HIREDATE
??from emp2
?where deptno in (select deptno from dept2)
--寫法二：
?select EMPNO, ENAME, JOB, MGR, HIREDATE
??from emp2
?where exists (select deptno from dept2 where dept2.deptno = emp2.deptno)
--改寫優(yōu)化--當子查詢中部門表中部門編號不存在重復改寫如下：
????select emp2.EMPNO,
???????????emp2.ENAME,
???????????emp2.JOB,
???????????emp2.MGR,
???????????emp2.HIREDATE
??????from emp2inner join dept2
????????on dept2.deptno = emp2.deptno
--若存在數(shù)據(jù)重復先根據(jù)關聯(lián)列去重再關聯(lián)select dept2.*
??from (select distinct deptno from emp2) emp2
?inner join dept2on dept2.deptno = emp2.deptno
9.反連接優(yōu)化
同半連接一樣，查詢也只返回主表數(shù)據(jù)，通過 not in 和 not exists 過濾，再改寫的過程中特別要注意反連接 not in 對空值敏感。
--ept2 deptno 列不存在空值時，以下兩種寫法等價，當 not in 存在空時，無數(shù)據(jù)行返回，因此 not exists 改寫 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 改寫 left join
select * from emp2 E ?where deptno not in (select deptno from dept2 D)
--反連接驅(qū)動是 E 表，被驅(qū)動是 D 表，所以改寫 left join ,not in 表示不在此范圍，即 emp2 有的部門編號，dept2 沒有
--左連接會將右表沒有的內(nèi)容用 NULL 表示，所以關聯(lián)后取 d.deptno is null 過濾select e.*
??from emp2 e
??left join dept2 d
????on d.deptno = e.deptno
?where d.deptno is null
（三）表設計優(yōu)化
表設計優(yōu)化可以從三個方面入手：選擇合適的表類型、設置分區(qū)表、設置全局臨時表。
1.表類型選擇
達夢數(shù)據(jù)庫提供了三種表類型：行存儲表、列存儲表（HUGE）和堆表。運維人員可根據(jù)實際需求選擇合適的表類型。
| 表類型 | 描述 | 主要特征 | 適用場景 |
| 行存儲表 | 行存儲是以記錄為單位進行存儲的，數(shù)據(jù)頁面中存儲的是完整的若干條記錄 | 1．按行存儲 2．每個表都創(chuàng)建一個 B 樹，并在葉子上存放數(shù)據(jù) | 適用于高并發(fā) OLTP 場景。 |
| 列存儲表（HUGE） | 列存儲是以列為單位進行存儲的，每一個列的所有行數(shù)據(jù)都存儲在一起，而且一個指定的頁面中存儲的都是某一個列的連續(xù)數(shù)據(jù)。 | 1．按列存儲 2．非事務型 HUGE 表：LOG NONE、LOG LAST、LOG ALL3．事務型 HUGE 表 | 適用于海量數(shù)據(jù)分析場景 |
| 堆表 | 堆表是指采用了物理 ROWID 形式的表，即使用文件號、頁號和頁內(nèi)偏移而得到 ROWID 值，這樣就不需要存儲 ROWID 值，可以節(jié)省空間 | 1．數(shù)據(jù)頁都是通過鏈表形式存儲 2．可設置并發(fā)分支 | 并發(fā)插入性能較高 |
2.水平分區(qū)表
分區(qū)類型
- 范圍(range)水平分區(qū)：對表中的某些列上值的范圍進行分區(qū)，根據(jù)某個值的范圍，決定將該數(shù)據(jù)存儲在哪個分區(qū)上；
- 哈希（hash）水平分區(qū)：通過指定分區(qū)編號來均勻分布數(shù)據(jù)的一種分區(qū)類型，通過在 I/O 設備上進行散列分區(qū)，使得這些分區(qū)大小基本一致；
- 列表（list）水平分區(qū)：通過指定表中的某個列的離散值集，來確定應當存儲在一起的數(shù)據(jù)。例如，可以對表上的 status 列的值在('A'，'H'，'O')放在一個分區(qū)，值在('B'，'I'，'P')放在另一個分區(qū)，以此類推；
- 多級分區(qū)表：按上述三種分區(qū)方法進行任意組合，將表進行多次分區(qū)，稱為多級分區(qū)表。
分區(qū)優(yōu)勢
- 減少訪問數(shù)據(jù)
- 操作靈活：可以操作分區(qū) truncate、分區(qū) drop、分區(qū) add、分區(qū) exchange
舉例說明
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í)行計劃：
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ū)子表的掃描
- 對主表和所有子表都收集統(tǒng)計信息
- 對索引收集統(tǒng)計信息
注意
如果 SQL 中有可利用的索引，普通表也可能比分區(qū)表性能高。
3.全局臨時表
當處理復雜的查詢或事務時，由于在數(shù)據(jù)寫入永久表之前需要暫時存儲一些行信息或需要保存查詢的中間結(jié)果，可能需要一些表來臨時存儲這些數(shù)據(jù)。DM 允許創(chuàng)建臨時表來保存會話甚至事務中的數(shù)據(jù)。在會話或事務結(jié)束時，這些表上的數(shù)據(jù)將會被自動清除。
全局臨時表類型
- 事務級-ON COMMIT DELETE ROWS
- 會話級-ON COMMIT PRESERVE ROWS
全局臨時表優(yōu)勢
- 不同 session 數(shù)據(jù)獨立
- 自動清理
舉例說明
第一步：原始語句如下：
--T_1 視圖（與 oracle 的 dblink 全表查詢）
--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 '%實時TA%';
第二步：創(chuàng)建臨時表 T1_20181122，將 T_1 視圖中部分數(shù)據(jù)插入臨時表中。
CREATE GLOBAL TEMPORARY TABLE "T1_20181122"
(init_date int, ?BRANCH_NO int, FUND_ACCOUNT int,BUSINESS_FLAG int,remark varchar(32));
--插入dblink獲取的數(shù)據(jù)到臨時表
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 '%實時TA%';
第三步：語句改寫。
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í)行計劃：50 分鐘 >>1 分鐘。
--原語句執(zhí)行計劃
1 #NSET2:[11,1,1644]
- #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 '%實時TA%'))
9 #PRJT2:[10,1000,1374];exp_num(13),is_atom(FALSE)
10 #REMOTE SCAN:[0,0,0] HIS_FUNDJOUR@HS08HIS
--改寫后執(zhí)行計劃
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
當統(tǒng)計信息已收集，且索引也按照需求建立，sql 執(zhí)行效率仍然不符合預期，可以考慮添加 hint 方式來進行優(yōu)化。
--例如：多個單表查詢通過 union 連接后組成一個視圖，然后視圖通過 where 過濾，過濾條件在每個單表中過濾性較好，且存在索引，通過視圖過濾無法使用索引，執(zhí)行時間55s。select * from dms.view_da_base where ahdm = '00005fe8-b171-4292-b776-b53c3fd65923';
使用 hint 修改參數(shù) VIEW_FILTER_MERGING 的值對視圖條件進行優(yōu)化，當參數(shù)值取 1 時表示盡可能地進行視圖條件合并。
select /*+ VIEW_FILTER_MERGING(1) */ * from dms.view_da_base where ahdm = '00005fe8-b171-4292-b776-b53c3fd65923';
當系統(tǒng)在運行過程中，出現(xiàn)慢 sql 需要緊急進行優(yōu)化處理時，可以在數(shù)據(jù)庫端通過 SF_INJECT_HINT 函數(shù)將 hint 與 sql 進行綁定對慢 SQL 進行優(yōu)化。
--設置 INI 參數(shù) ENABLE_INJECT_HINT 為 1
SP_SET_PARA_VALUE(1,'ENABLE_INJECT_HINT',1); ??
?--計劃添加 hint 后的 SQL
select ???/*+ VIEW_FILTER_MERGING(1) */ ?* from dms.view_da_base where ahdm = '00005fe8-b171-4292-b776-b53c3fd65923';
---模糊匹配 sql 進行 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)化方法使用，特定場景優(yōu)化或應急處理時使用。
達夢數(shù)據(jù)庫 - 新一代大型通用關系型數(shù)據(jù)庫 | 達夢在線服務平臺