国产数据库-ET分析SQL

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

前提

数据库状态正常
数据库置有表数据

一、配置ET功能

达梦数据库ET功能默认是关闭，需设置INI参ENABLE_MONITOR=1、 MONITOR_TIME=1 和MONITOR_SQL_EXEC=1。具体配置方法参考以下操作方法。

SQL> SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1); 
SQL> SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',1); 
SQL> SP_SET_PARA_VALUE(1,'MONITOR_TIME',1); 

二、ET分析SQL

ET 的语法定义为：ET(ID_IN INT)，ID_IN指SQL语句的执行ID

SQL> SELECT  ID,XM,ZJH,ZL,DJSJ,XYSJ  FROM HG.BASE_VIP_INFO  WHERE XM = 'W1';
行号     ID   XM ZJH                ZL           DJSJ                     XYSJ                      
---------- ---- -- ------------------ ------------ ------------------------ --------------------------
1          1    W1 606801996755787000 randm_values 22-06-09 19:28:01.000000 2022-06-09 19:28:01.557447
2          414  W1 263732620498041000 randm_values 22-06-09 19:28:01.000000 2022-06-09 19:28:01.557447
3          797  W1 298935271473106000 randm_values 22-06-09 19:28:01.000000 2022-06-09 19:28:01.557447
4          1599 W1 738035957765782000 randm_values 22-06-09 19:28:01.000000 2022-06-09 19:28:01.557447
5          2657 W1 912679790711347000 randm_values 22-06-09 19:28:01.000000 2022-06-09 19:28:01.557447
6          3256 W1 338929752138876000 randm_values 22-06-09 19:28:01.000000 2022-06-09 19:28:01.557447
7          3538 W1 751730203466364000 abcdefg      22-06-09 19:28:01.000000 2022-06-09 19:28:01.557447
8          4128 W1 834787471515493000 randm_values 22-06-09 19:28:01.000000 2022-06-09 19:28:01.557447
9          7440 W1 231366661205593000 abcdefg      22-06-09 19:28:01.000000 2022-06-09 19:28:01.557447
10         7869 W1 748155955853013000 randm_values 22-06-09 19:28:01.000000 2022-06-09 19:28:01.557447
11         8335 W1 284711726188991000 abcdefg      22-06-09 19:28:01.000000 2022-06-09 19:28:01.557447
..........
行号     ID    XM ZJH                ZL           DJSJ                     XYSJ                      
---------- ----- -- ------------------ ------------ ------------------------ --------------------------
23         28289 W1 320066046025635000 abcdefg      22-06-09 19:28:01.000000 2022-06-09 19:28:01.557447
24         30033 W1 104649754336406000 randm_values 22-06-09 19:28:01.000000 2022-06-09 19:28:01.557447
25         30306 W1 742519278099071000 randm_values 22-06-09 19:28:01.000000 2022-06-09 19:28:01.557447
26         31876 W1 633206016446094000 randm_values 22-06-09 19:28:01.000000 2022-06-09 19:28:01.557447
27         35703 W1 811909453622955000 abcdefg      22-06-09 19:28:01.000000 2022-06-09 19:28:01.557447
28         35754 W1 668149861911382000 abcdefg      22-06-09 19:28:01.000000 2022-06-09 19:28:01.557447
29         36585 W1 679933454459502000 abcdefg      22-06-09 19:28:01.000000 2022-06-09 19:28:01.557447
30         38415 W1 159017253089239000 randm_values 22-06-09 19:28:01.000000 2022-06-09 19:28:01.557447
31         38567 W1 517747395261073000 abcdefg      22-06-09 19:28:01.000000 2022-06-09 19:28:01.557447
32         39185 W1 463078259054142000 randm_values 22-06-09 19:28:01.000000 2022-06-09 19:28:01.557447
33         39590 W1 176038299629483000 randm_values 22-06-09 19:28:01.000000 2022-06-09 19:28:01.557447

33 rows got
已用时间: 1.060(毫秒). 执行号:5.
SQL> ET(5)
2   ;

行号     OP    TIME(US)             PERCENT RANK                 SEQ         N_ENTER     HASH_USED_CELLS      HASH_CONFLICT       
---------- ----- -------------------- ------- -------------------- ----------- ----------- -------------------- --------------------
1          DLCK  25                   0.08%   5                    0           5           0                    0
2          PRJT2 93                   0.3%    4                    2           270         0                    0
3          SLCT2 1795                 5.74%   3                    3           270         0                    0
4          NSET2 13533                43.28%  2                    1           139         0                    0
5          CSCN2 15821                50.6%   1                    4           135         0                    0

已用时间: 466.669(毫秒). 执行号:4.

ET分析SQL可看出每个操作符使用的时间

三、SQL获取操作符执行时间

未开启ET功能可通过SQL语句来获取查询SQL每个操作步骤具体执行的时间

--? SQL执行号
SELECT N.NAME, TIME_USED, N_ENTER FROM V$SQL_NODE_NAME N, V$SQL_NODE_HISTORY H WHERE N.TYPE$ = H.TYPE$ AND EXEC_ID = ？

四、图形界面使用ET

1、执行SQL

2、调出ET

总结

以上为达梦ET工具使用内容，本文仅仅简单介绍了ET的使用，更多信息请参考https://eco.dameng.com/