########sample 2   https://www.modb.pro/db/635695

干货攻略】达梦数据库DM8的SQL性能优化思路浅谈系列（二）

们在上一次的分享中已介绍SQL优化的重要性，预估执行计划生成及基础说明和达梦性能分析思路。今天我们接着来聊一下达梦数据库参数调整、跟踪存储过程中的慢SQL思路及辅助性能分析工具介绍和使用以及如何跟踪慢SQL。

达梦数据库DM.INI参数调整思路

达梦数据库的参数灵活性非常高，配置的好，它能达到一个整体很好的性能效果，而且跟产品深度融合，复用标准工艺参数，能很好的降本增效，这是我们每一个DBA都要重视的优化方向。
达梦优化参数分类
（1）相关文件路径设置参数（控制文件，备份文件，系统文件，临时文件等）；（2）实例名（3）内存池以及缓存池（4）线程相关参数（5）查询INI优化类参数（这块跟我们产品特性有比较大关系）；（6）检查点参数（7）IO优化参数（8）数据库设置类参数（9）兼容类参数（比如兼容Oracle等）（10）REDO相关参数（11）事务相关参数（12）监控参数（13）…
达梦数据库参数配置需要考虑的因素：（1）受机器硬件配置影响(CPU,内存根据需要设置)；（2）受需要适配的产品影响（以产品和项目实际使用规模等情况为导向来设置，比如大型ERP产品，基础表比较多，字典缓冲区就要调整到合理值，避免设置过小，频繁被淘汰刷新，又例如看整个产品你的SQL分类，HASH类的SQL过多，则需要HJ_BUF_GLOBAL_SIZE ，HJ_BLK_SIZE合理设置，层次查询过多，则需要设置合理的CNNTB_OPT_FLAG；（3）受目标环境影响（生产环境，测试环境）（4）受适配源端数据库影响等；这里提供一种思路，大量的参数，需要一一去测试验证了解，这就需要非常了解产品的数据库DBA或数据库设计人员，在实践中一一去优化，形成适合自己产品的标准参数工艺。然后复用不断优化，才能深度的和产品融合，这才能真正用好数据库。达梦有700多个参数，每个参数的具体取值，需要根据产品特性来调整的。如下总结的 这些影响数据库性能的参数，其取值，需要根据实际产品特性具体调整，不能照搬。这里的取值只供参考：

跟踪存储过程中的慢SQL思路

当我们拿到一个慢存储过程，而且涉及比较复杂的逻辑时候，我们应该如何入手？（1）先要获取存储过程的传递参数，有时候可以找开发人员获取，进行调试，当开发人员给的参数执行可以调试的存储过程并不慢，那我们应该如何处理呢？

（2）有些存储过程，可能特定的传参才会导致他的缓慢，这个时候，可以获取到存储过程的传参，通过专业SQL分析工具或使用达梦客户端工具进行调试，并分析缓慢的原因。
（3）存储过程一般都是统计运算类的，一般情况下是可以反复调用的，这个时候，我们一定要确定好，不能盲目执行，避免造成数据错乱，当确定可以调试后，我们就用达梦的客户端调试工具进行调试，我们可以适当在存储过程中打print输出时间，调用完成后，看下在哪一段执行过慢，这样有针对性的优化。
（4）对于有些不能调试的存储过程，我们如何处理呢？这里我在跟踪SQL慢方式中，我们先手动改好，把改写好的存储过程，更新到仿真测试环境，把可能影响性能的SQL都改写成动态嵌套SQL_INFO中，这样就可以在日志表中获取慢SQL了。



性能辅助分析工具

达梦丰富的性能优化辅助工具，能帮助DBA更好的定位问题，给我们日常SQL分析带来了很大的帮助，在预估执行计划不准情况下，我们可以利用性能辅助工具，来处理，举个例子，比如我们存储过程的性能问题，则可以通过ET来看大概性能分布情况，这样就更有效的知道什么操作符造成的。为什么要看实时执行计划？其主要原因还是，预估执行计划可能并不准确。

使用如下工具默认都要检查如下三个参数，否则无法使用：参数1，2默认开启：–内存参数DM.INI直接修改，需要重启动生效，默认一般已经开启。

SP_SET_PARA_VALUE(1,‘ENABLE_MONITOR’,1);
SP_SET_PARA_VALUE(1,‘MONITOR_TIME’,1);

参数3会话级别开启：---会话参数，建议遇到问题，当前会话开启即可，不建议参数级别开启；MONITOR_SQL_EXEC为会话级动态参数，可以设置只针对当前会话开启：

SF_SET_SESSION_PARA_VALUE(‘MONITOR_SQL_EXEC’,1);

3.1达梦ET工具使用

在使用ET工具前检查ENABLE_MONITOR，MONITOR_TIME是否开启，默认配置都是开启状态，我们再检查下MONITOR_SQL_EXEC，如果没有开启，在直接在要执行SQL的会话窗口先执行：

SF_SET_SESSION_PARA_VALUE(‘MONITOR_SQL_EXEC’,1);

3.1.1 执行了此参数之后，执行我们要跟踪的SQL，点击执行号，就可以弹出分析了；

3.1.2 ET工具还可以分析存储过程，当存储过程执行比较慢的时候，我们调用后，也可以点击执行号，看他的执行占用备份比,我们通常是看PERCENT百分比，这个通常能代表，哪一个操作符耗时比较长。

用例：

3.1.3在DISQL中也可以使用

操作方法：ET(执行号)；

3.2、v$cachepln中如何获取历史执行计划v$cachepln保存了历史的SQL缓存计划，平常我们在跟踪问题的时候，也可以分析它的历史缓存计划是否是最优，把历史计划生成出来,此功能对标Oracle的V$SQL_PLAN_STATISTICS_ALL只是获取方式有所差异，今天演示下达梦的获取方法：步骤1：我们先查询下v$cachepln，获得我前面执行SQL产生的缓存计划的CACHE_ITEM我们以此测试SQL语句为例，先执行：

select a.dxid from TEST_TAB1 a,TEST_TAB2 b
where a.dxid=b.dxid;

再查询

SELECT * FROM v$cachepln WHERE SQLSTR LIKE ‘%TEST_TAB1%’;–140357593086048

步骤2：再通过调用生成执历史执行计划语句,主要通过步骤1获取得CACHE_ITEM，以及DUMP_FILE指定服务器目录生成历史缓存执行计划信息；

alter session set events ‘immediate trace name plndump level 140357593086048, dump_file ‘’/home/dmdba/140357593086048.log’‘’;

步骤3：登录服务器在/home/dmdba/140357593086048.log 找到此文件，即可获取历史缓存执行计划信息；

打开140357593086048.log文件

如上就是v$cachepln如何获取历史的缓存执行计划，这个可以结合分析当前SQL语句性能问题，判定缓存计划是否有问题，如果历史的执行计划错误的，我们还可以通过SP_CLEAR_PLAN_CACHE(cache_item)来清理；如果要全部清理，也可以直接使用call SP_CLEAR_PLAN_CACHE()；3.3、实际执行计划和统计信息的跟踪

set autotrace <OFF(默认值) | NL | INDEX | ON|TRACE> ;

参数详解：

（1）当SET AUTOTRACE OFF 停止 AUTOTRACE 功能，常规执行语句;（2）当SET AUTOTRACE NL 时，开启 AUTOTRACE 功能，不执行语句，如果执行计划中有嵌套循环操作，那么打印 NL 操作符的内容;（3）当 SET AUTOTRACE INDEX(或者 ON)时，开启 AUTOTRACE 功能，不执行语句，如果有表扫描，那么打印执行计划中表扫描的方式、表名和索引。（4）当 SET AUTOTRACE TRACE 时，开启 AUTOTRACE 功能，执行语句，打印执行计划。
此功能与服务器 EXPLAIN 语句的区别在于，EXPLAIN 只生成执行计划，并不会真正执行SQL 语句，因此产生的执行计划有可能不准。而 TRACE 获得的执行计划，是服务器实际执行的计划 —来自《DM8_DIsql使用手册.pdf》说明手册。
预估执行计划：我们看结果集，实际SQL，关联就20条记录，但是结果集显示是：2619310，这个是明显不准确的，这样的前提下，执行性能也是不高的；

如上预估执行计划不准的情况下，我们需要的是服务器实际的执行计划，所以需要如下

设置SET AUTOTRACE TRACE来获取。

实操如下：当登录DISQL后

SQL> ALTER SESSION SET ‘MONITOR_SQL_EXEC’=1;
DMSQL 过程已成功完成
已用时间: 174.805(毫秒). 执行号:106510701.
SQL> set autotrace TRACE;
SQL> select a.dxid from TEST_TAB1 a,TEST_TAB2 b where a.dxid=b.dxid;
已用时间: 66.393(毫秒). 执行号:112458304.
SQL> select a.dxid from TEST_TAB1 a,TEST_TAB2 b where a.dxid=b.dxid;
行号 DXID
1 11487146
2 279528455
3 279659238
4 280097010
5 283571338
6 280439206
7 285447684
8 290622185
9 285435977
10 285435974
11 272522356
…
20 rows got
1 #NSET2: [2360, 2619310->0, 60]
2 #PRJT2: [2360, 2619310->0, 60]; exp_num(1), is_atom(FALSE)
3 #HASH2 INNER JOIN: [2360, 2619310->0, 60]; KEY_NUM(1);
4 #CSCN2: [1, 20, 30]; INDEX35162081(TEST_TAB2)
5 #CSCN2: [1469, 13228840->11668240, 30]; INDEX35162080(TEST_TAB1)

说明：如上案例是服务器真实的执行计划。由于测试需要，制造SQL统计信息异常，预估执行计划可能是不准确的，但是实际set autotrace TRACE 后就是其真实服务器的执行计划，使用set autotrace TRACE后，我们可以看到带箭头的[2360, 2619310->0, 60][实际计划代价，结果，显示长度],而预估执行计划应该是这样显示[2360, 2619310, 60]，和实际计划对比相差太远；预估计划结果2619310，实际是0，这就明显不对了，根据经验看，是未收集统计信息导致，收集后计划后，记录数就准确了。

跟踪慢SQL的常用方式

4.1、慢SQL实时抓取
达梦数据库开启参数后，SQL实时执行就可以抓取了，通过简单SQL就可以抓取。通常在运行过程中，遇到用户反馈系统比较慢的时候，我们都可以先用此SQL查询下，如果加压大量SQL长时间未处理，而且都是重复的，则有2个可能性：第1种可能性，这个是频繁SQL，但是遇到了某个慢SQL导致数据库整体事务处理能下降了；第2种可能性，就是本身此SQL就有问题，这样给我们快速响应数据库带来了问题。

Select
'sP_close_session('
||sess_id
||');'                                          ,
datediff(ss, last_recv_time, sysdate) ss        ,
cast(sf_get_session_sql(sess_id) as varchar) sql,
*
from
v$sessions
where
state='ACTIVE'
order by
last_send_time;

说明：拼接了结束会话的sP_close_session，可以快速导出结束异常会话，如果时间无法获取则要v$dm_ini检查下ENABLE_MONITOR是否开启（1：打开；0：关闭），否则时间获取不到，就无法统计时间了；

4.2、SQL_INFO跟踪性能语句

通常存储过程慢SQL并不好抓取，在不是很好抓取的时候，我们可以采用性能分析工具里说的达梦DEBUG工具，但是有些无法调试的，我们怎么办呢？这个时候，我们可以改写待跟踪的存储过程，在把要跟踪的SQL嵌套到SQL_INFO中，这样有性能的SQL就可以写入日志表SQL_LOG了；实践中，改写存储过程需要有一定数据库开发能力的小伙伴来处理，我这里提供的是一种跟踪的性能方式；

--创建性能跟踪表
--DROP TABLE SQL_LOG;
CREATE TABLE SQL_LOG
(
GID         VARCHAR2(100),  --GUID
"USER_NAME" VARCHAR2(32) ,  --当前用户
"START_TIME" TIMESTAMP(0),  --SQL开始执行时间
"END_TIME" TIMESTAMP(0)  ,  --SQL执行结束时间
"SQL_TEXT"   VARCHAR2(4000),--SQL执行语句
"ROW_COUNT"  NUMBER(15) ,   --记录数
"LOG_TYPE"  VARCHAR2(20) ,  --完成是SUCCESSFUL,报错是FAILED
"SESSION_ID" NUMBER(38, 0) , --会话ID
"LOG_DETAIL" VARCHAR2(2000), --错误信息
"INT_STACK" VARCHAR2(4000),  ----堆栈信息 
"SQL_TIMES"    NUMBER(10, 2) --记录SQL执行时间
);
CREATE INDEX IND_SQL_LOG_SQLTEXT ON SQL_LOG(SQL_TEXT);
/
--创建跟踪过程
CREATE OR REPLACE PROCEDURE SQL_INFO("I_SQLSTART" IN VARCHAR2(32000))
AS
--说明：跟踪存储过程中某些慢SQL,跟踪大于0.1的SQL,根据场景可以改写
V_ROWCOUNT     INT;
V_TIME         DATE;             --定义时间
V_ERRMSG      VARCHAR2(5000);   --获取异常记录
V_SQLTEXT      VARCHAR2(32767);  --获取传递的记录
V_SQLTEXT_INS  VARCHAR2(9000);   --SQL记录
V_CURTIME      NUMBER(20,2);     --开始时间
V_USETIME      NUMBER(20,2);     --使用时间
V_SESSION      NUMBER(30);       --SID记录
V_INT_STACK   VARCHAR2(4000);
BEGIN
V_TIME:=SYSDATE;
V_CURTIME:=DBMS_UTILITY.GET_TIME;
V_SQLTEXT:=TRIM(I_SQLSTART);
WHILE INSTR(V_SQLTEXT,'  ')>0 LOOP
V_SQLTEXT:=REPLACE(V_SQLTEXT,'  ',' ');
END LOOP;
--执行主体EXECUTEIMMEDIATE
EXECUTE IMMEDIATE V_SQLTEXT;
V_ROWCOUNT:=SQL%ROWCOUNT;
V_USETIME:=(DBMS_UTILITY.GET_TIME-V_CURTIME)/100;
--这里调整多少秒的记录写入到SQL_LOG日志表中,这个可以嵌套到性能表里
IF V_USETIME>=0.1 THEN
V_SQLTEXT_INS:=TRIM(SUBSTRB(V_SQLTEXT,1,4000));--达梦单列VARCHAR2可以存储9000以上，如果SQL够长，我们可以改大些
V_SESSION:=SESSID;--会话SID
V_INT_STACK:=DBMS_UTILITY.FORMAT_CALL_STACK;
INSERT INTO SQL_LOG 
(GID,USER_NAME,START_TIME,END_TIME,SQL_TEXT,ROW_COUNT,LOG_TYPE,SESSION_ID,INT_STACK,SQL_TIMES)
VALUES (GUID(),USER,V_TIME,SYSDATE,V_SQLTEXT_INS,V_ROWCOUNT,'SUCCESSFUL',V_SESSION,V_INT_STACK,V_USETIME);
END IF;
EXCEPTION
--如果SQL中执行异常，也会写SQL_LOG中，方便我们分析错误语句
WHEN OTHERS THEN
ROLLBACK;
V_ERRMSG:=SQLERRM(SQLCODE);
V_INT_STACK:=DBMS_UTILITY.FORMAT_CALL_STACK;
V_SQLTEXT_INS:=TRIM(SUBSTRB(I_SQLSTART,1,4000));--达梦VARCHAR2可以存储9000以上，如果SQL够长，我们可以改大些
V_USETIME:=(DBMS_UTILITY.GET_TIME-V_CURTIME)/100;
INSERT INTO SQL_LOG 
(GID,USER_NAME,START_TIME,END_TIME,SQL_TEXT,LOG_TYPE,SESSION_ID,LOG_DETAIL,INT_STACK,SQL_TIMES)
VALUES (GUID(),USER,V_TIME,SYSDATE,V_SQLTEXT_INS,'FAILED',V_SESSION,V_ERRMSG,V_INT_STACK,V_USETIME);
COMMIT;
RAISE;  
END SQL_INFO;

样例演示：–查看跟踪到执行这个DEMO语句的时间：

BEGIN
–DEMO
SQL_INFO(‘CREATE TABLE FMISTMP.TEST_003 AS
SELECT * FROM DBA_OBJECTS’);
END;

SELECT * FROM SQL_LOG WHERE SQL_TEXT LIKE ‘CREATE TABLE FMISTMP.TEST_003%’

以上只是举例一个简单的创建备份表进行演示，实际上存储过程中很多动态拼接语句，都是可以放到SQL_INFO中，然后在执行过程中就可以跟踪到他的SQL是那一句慢了