【dm8问题分析】sql执行长时间没有返回数据

排查方向： 执行过程中某些对象/资源发生等待，导致执行语句长时间等待不能返回

                   所执行语句本身存在性能问题，需要进行优化调整执行计划

                   其他异常情况

-- 确认该语句是否发生事务等待
SELECT * FROM v$sessions WHERE state='WAIT'
AND dbms_lob.substr(sf_get_session_sql(sess_id)) LIKE '%语句片段%';    
注：如果查询不到信息，说明没有发生事务等待，大概率是该语句自身执行存在效率问题，需要对执行计划进行调整

有等待：

♦️ 由于某些会话或者客户端忘记进行提交或者回滚操作，后续就一直空闲了，导致其他的事务由于跟该事务存在一些事务上的依赖关系发生等待

-- 查询等待事务
SELECT * FROM v$trxwait WHERE id = 上述语句得到结果得 TRX_ID
-- 查询会话id
SELECT T.THRD_ID,T.TRX_ID,T.SESS_ID,T.SQL_TEXT,T.STATE FROM V$SESSIONS T WHERE T.TRX_ID = 上述语句得到的结果的WAIT_FOR_ID; 得到阻塞事务的SESS_ID
--杀掉阻塞事务的会话
SP_CLOSE_SESSION(上述语句得到的结果的SESS_ID);

如果是由事务等待导致的sql长时间不能返回结果，那么此时sql可以正常返回结果

♦️ 另外一种等待不会通过 V​TRXWAIT 查询得到结果，比如某些表发生 DDL 操作过程中，其他的会话尝试对该表进行查询，由于字典对象发生变化，所以发生字典对象的等待

SELECT * FROM v$lock WHERE blocked = 1 拿到TID

select SESS_ID,SQL_TEXT，STATE from v$sessions where TRX_ID = 上述结果的TID;  

以上两步也可使用该sql代替：
select t2.SESS_ID,t2.SQL_TEXT,t2.STATE FROM v$lock T1 left join v$sessions T2 on t1.TID = T2.TRX_ID where T1.BLOCKED = 1;
SP_CLOSE_SESSION(上述结果的SESS_ID);

无等待：
分析SQL性能，查看执行计划

达梦数据库查看执行计划的常用方法：

1.直接在执行的sql语句前加explain后执行

2.查看ET

ET的结果可展示执行时间最长的操作符

--开启ET功能

SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1); 
SP_SET_PARA_VALUE(1,'MONITOR_TIME',1); 
SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC',1);

执行SQL语句，我们会看到一个执行号

执行CALL ET(执行号);

关闭时改为0

 3.autotrace

使用disql登录数据库
set autotrace trace;
执行待优化的sql语句

4.10053事件

10053 trace SQL语句的计划生成过程输出到TRACE 文件，TRACE出的是SQL 的实际执行计划；生成的TRACE文件默认在数据目录生成trace文件夹，文件以.trc结尾。

alter session set events '10053 trace name context forever,level 1';
此处执行待优化的sql
alter session set events '10053 trace name context off';

此处列举一个优化案例：

-SQL：

SELECT   
    taskList.SALESPERSON_NAME,
    taskList.PROVINCE_BUSINESS_OFFICE_NAME,
    taskList.BRANCH_NAME,
    taskList.TEAM_NAME,
    taskList.DATA_SOURCE,
    taskList.customer_name,
    taskList.LICENSE_PLATE_NO,
    taskList.CREATE_DATE,
    tmp.firstCall,
    tmp.lastCall,
    tmp.contactNum 
FROM
    TABLE1 PARTITION(P_61000000) taskList
LEFT JOIN TABLE2 trialcalc ON
    trialcalc.TASK_ID = taskList.TASK_ID
    AND trialcalc.SCHEME_USR_STATUS = '1'
LEFT JOIN TABLE3 naiOrder ON
    naiOrder.SS_ID = taskList.TASK_ID
LEFT JOIN (
    SELECT
        track.task_id,
        min(track.TRACK_TIME) firstCall,                 
        max(track.TRACK_TIME) lastCall,
        count(track.task_id) contactNum
    FROM
        TABLE4 track    --188585条数据
    WHERE
        1 = 1
        AND track.PROVINCE_BUSINESS_OFFICE = '61000000'
    GROUP BY
        track.task_id ) tmp ON
    tmp.task_id = taskList.task_id
WHERE
    taskList.CREATE_DATE >= '2022-02-12 00:00:00'                 --3856条数据
    AND taskList.CREATE_DATE <= '2022-02-16 23:59:59'
    AND taskList.DATA_SOURCE IN ( 'EAO' , 'EAK' )

-原执行计划：

1   #NSET2: [1123, 4795, 504] 
2     #PRJT2: [1123, 4795, 504]; exp_num(11), is_atom(FALSE) 
3       #HASH LEFT JOIN2: [1123, 4795, 504]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(TASKLIST.TASK_ID=TMP.TASK_ID)
4         #HASH LEFT JOIN2: [74, 4795, 396]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(TASKLIST.TASK_ID=NAIORDER.SS_ID)
5           #INDEX JOIN LEFT JOIN2: [66, 4277, 396]  ret_null(0)
6             #HASH RIGHT SEMI JOIN2: [6, 4226, 396]; n_keys(1)   KEY(DMTEMPVIEW_17649939.colname=TASKLIST.DATA_SOURCE) KEY_NULL_EQU(0)
7               #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1), 
8               #BLKUP2: [6, 4261, 396]; INDEX33557132_33557101(TASKLIST)
9                 #SSEK2: [6, 4261, 396]; scan_type(ASC), INDEX33557132_33557101(TABLE1_P_61000000 as TASKLIST), scan_range[exp_cast('2022-02-12 00:00:00'),exp_cast('2022-02-16 23:59:59')]
10            #SSEK2: [18, 1, 0]; scan_type(ASC), I_TRIALCALCSCHEME_LH(TABLE2 as TRIALCALC), scan_range[(TASKLIST.TASK_ID,'1',min),(TASKLIST.TASK_ID,'1',max))
11          #SSCN: [4, 36958, 48]; IDX_TS_NAI_ORDER_SS_ID(TS_NAI_ORDER as NAIORDER)
12        #PRJT2: [982, 837021, 108]; exp_num(4), is_atom(FALSE) 
13          #SAGR2: [982, 837021, 108]; grp_num(1), sfun_num(3) slave_empty(0) keys(TRACK.TASK_ID) 
14            #BLKUP2: [916, 837021, 108]; I_DMTASKTRACK_LH(TRACK)
15              #SSEK2: [916, 837021, 108]; scan_type(ASC), I_DMTASKTRACK_LH(TABLE4 as TRACK), scan_range[('61000000',min),('61000000',max))

I_TABLE4_LH 是PROVINCE_BUSINESS_OFFICE和TRACK_ID的联合索引
把该索引改为
 

CREATE INDEX I_TABLE4_LH2 ON TSS.TABLE4（PROVINCE_BUSINESS_OFFICE，TASK_ID，TRACK_TIME）;

-优化后的执行计划

1   #NSET2: [332, 4816, 504] 
2     #PRJT2: [332, 4816, 504]; exp_num(11), is_atom(FALSE) 
3       #HASH LEFT JOIN2: [332, 4816, 504]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(TASKLIST.TASK_ID=TMP.TASK_ID)
4         #HASH LEFT JOIN2: [74, 4816, 396]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(TASKLIST.TASK_ID=NAIORDER.SS_ID)
5           #INDEX JOIN LEFT JOIN2: [66, 4288, 396]  ret_null(0)
6             #HASH RIGHT SEMI JOIN2: [6, 4232, 396]; n_keys(1)   KEY(DMTEMPVIEW_17693082.colname=TASKLIST.DATA_SOURCE) KEY_NULL_EQU(0)
7               #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1), 
8               #BLKUP2: [6, 4261, 396]; INDEX33557132_33557101(TASKLIST)
9                 #SSEK2: [6, 4261, 396]; scan_type(ASC), INDEX33557132_33557101(TABLE1_P_61000000 as TASKLIST), scan_range[exp_cast('2022-02-12 00:00:00'),exp_cast('2022-02-16 23:59:59')]
10            #SSEK2: [18, 1, 0]; scan_type(ASC), I_TRIALCALCSCHEME_LH(TABLE2 as TRIALCALC), scan_range[(TASKLIST.TASK_ID,'1',min),(TASKLIST.TASK_ID,'1',max))
11          #SSCN: [4, 36958, 48]; IDX_TS_NAI_ORDER_SS_ID(TS_NAI_ORDER as NAIORDER)
12        #PRJT2: [191, 838510, 108]; exp_num(4), is_atom(FALSE) 
13          #SAGR2: [191, 838510, 108]; grp_num(1), sfun_num(3) slave_empty(0) keys(TRACK.TASK_ID) 
14            #SSEK2: [126, 838510, 108]; scan_type(ASC), I_DMTASKTRACK_LH2(TABLE4 as TRACK), scan_range[('61000000',min,min),('61000000',max,max))

减少回表BLKUP2的计划操作，可以加快SQL执行时间，优化后sql执行时间缩短