问题和环境描述
数据库：达梦8
现场和测试环境的数据差10倍左右
出现的问题：测试环境执行速度为毫秒，但是现场环境的执行速度为5~8秒，传给客户端就需要10秒左右了
出问题的sql：
select
count(*)
from
(
select
p.id as pid ,
tc1.taskCount,
tc1.finishCount
from
t_project p
join t_business b
on
p.fk_business_id = b.id
join
(
select
t.projectid ,
sum(case when t.projectid > 0 then 1 else 0 end) taskCount,
sum(case when t.status = 87 then 1 else 0 end ) finishCount
from
t_task t
where
t.ACTIVITYNAME != '状态C'
group by
t.PROJECTID
)
tc1
on
tc1.projectid = p.id
where
b.fk_businesstype_id = 95
and b.id <> 875
and p.processstate in ('状态A')
and p.lifestate in ('状态B')
and exists
(
select
1
from
(
SELECT projectid FROM t_task t1 where t1.orgid in(1234)
union all
SELECT
projectid
FROM
t_task t2
where
t2.userid in
(
select userid from DB3.org_user where orgid in(1234)
)
)
tt
where
tt.projectid=p.id
)
)
t
工具和常用命令
基本工具和命令
- et命令 DM8_DBA P549：可以查看所有操作符的执行时间
- Eplain for 查看执行计划
- Hint DM8_DBA P198进行优化器提示
- Dbms_stats.column_stats_show 查看直方图的信息
- 达梦数据库如何利用DBMS_STATS收集统计信息
处理现场环境
查看是否开了执行计划重用->开了
select * from V$PARAMETER
WHERE NAME IN ('USE_PLN_POOL');
ID NAME TYPE VALUE SYS_VALUE FILE_VALUE DESCRIPTION
83 USE_PLN_POOL IN FILE 1 1 1 Query Plan Reuse Mode, 0: Forbidden; 1:strictly reuse, 2:parsing reuse, 3:mixed parsing reuse
- 为了使用et命令，需要设置 INI 参数 ENABLE_MONITOR=1、MONITOR_TIME=1 和 MONITOR_SQL_EXEC=1
先看数据库的参数设置情况，发现没开 MONITOR_SQL_EXEC参数：
select * from V$PARAMETER
WHERE NAME IN ('ENABLE_MONITOR','MONITOR_TIME','MONITOR_SQL_EXEC');
ID NAME TYPE VALUE SYS_VALUE FILE_VALUE DESCRIPTION
530 ENABLE_MONITOR SYS 1 1 1 Whether to enable monitor
531 MONITOR_TIME SYS 1 1 1 Whether to enable monitor timing
533 MONITOR_SQL_EXEC SESSION 0 0 0 Whether to enable monitor sql execute
需要设置 MONITOR_SQL_EXEC = 1，由于该参数是session级别的，不需要重启数据库；
alter system set 'MONITOR_SQL_EXEC' = 1;
select * from V$PARAMETER
WHERE NAME IN ('ENABLE_MONITOR','MONITOR_TIME','MONITOR_SQL_EXEC');
ID NAME TYPE VALUE SYS_VALUE FILE_VALUE DESCRIPTION
530 ENABLE_MONITOR SYS 1 1 1 Whether to enable monitor
531 MONITOR_TIME SYS 1 1 1 Whether to enable monitor timing
533 MONITOR_SQL_EXEC SESSION 1 1 0 Whether to enable monitor sql execute
查一下是否建了统计数据工具包
SP_CREATE_SYSTEM_PACKAGES (1,'DBMS_STATS');
信息收集
现场信息
执行计划
1 #NSET2: [29, 1, 264]
2 #PRJT2: [29, 1, 264]; exp_num(1), is_atom(FALSE)
3 #AAGR2: [29, 1, 264]; grp_num(0), sfun_num(1) slave_empty(0)
4 #PRJT2: [29, 1, 264]; exp_num(0), is_atom(FALSE)
5 #NEST LOOP SEMI JOIN2: [29, 1, 264]; join condition(P.ID = DMTEMPVIEW_18213733.colname)[with var]
6 #SLCT2: [9, 1, 264]; (B.FK_BUSINESSTYPE_ID = var4 AND B.ID <> var5)
7 #HASH2 INNER JOIN: [9, 1, 264]; RKEY_UNIQUE KEY_NUM(1); KEY(exp_cast(P.FK_BUSINESS_ID)=B.ID) KEY_NULL_EQU(0)
8 #SLCT2: [9, 1, 264]; (B.FK_BUSINESSTYPE_ID = var2 AND B.ID <> var3)
9 #NEST LOOP INDEX JOIN2: [9, 1, 264]
10 #ACTRL: [9, 1, 264];
11 #HASH2 INNER JOIN: [9, 1, 204]; LKEY_UNIQUE KEY_NUM(1); KEY(P.ID=TC1.PROJECTID) KEY_NULL_EQU(0)
12 #BLKUP2: [0, 4, 104]; IDX4YCDLECSAEWEX8GQCQB5BSGB(P)
13 #SSEK2: [0, 4, 104]; scan_type(ASC), IDX4YCDLECSAEWEX8GQCQB5BSGB(T_PROJECT as P), scan_range[('状态1','状态2'),('状态1','状态2')]
14 #PRJT2: [8, 27, 100]; exp_num(1), is_atom(FALSE)
15 #HAGR2: [8, 27, 100]; grp_num(1), sfun_num(0); slave_empty(0) keys(DMTEMPVIEW_18213731.TMPCOL0)
16 #PRJT2: [7, 2710, 100]; exp_num(1), is_atom(FALSE)
17 #SLCT2: [7, 2710, 100]; T.ACTIVITYNAME <> '活动名'
18 #CSCN2: [7, 54205, 100]; INDEX33558632(T_TASK as T)
19 #BLKUP2: [0, 1, 0]; INDEX33557988(B)
20 #SSEK2: [0, 1, 0]; scan_type(ASC), INDEX33557988(DAP_BUSINESS as B), scan_range[exp_cast(P.FK_BUSINESS_ID),exp_cast(P.FK_BUSINESS_ID)]
21 #CSCN2: [0, 84, 60]; INDEX33557987(T_BUSINESS as B)
22 #PRJT2: [9, 59, 8]; exp_num(1), is_atom(FALSE)
23 #PRJT2: [9, 59, 8]; exp_num(1), is_atom(FALSE)
24 #UNION ALL: [9, 59, 8]
25 #PRJT2: [3, 33, 8]; exp_num(1), is_atom(FALSE)
26 #SLCT2: [3, 33, 8]; T1.ORGID = 31233
27 #BLKUP2: [3, 1355, 8]; IDXSDRR7DRYEF814Q4Q296973RUG(T1)
28 #SSEK2: [3, 1355, 8]; scan_type(ASC), IDXSDRR7DRYEF814Q4Q296973RUG(T_TASK as T1), scan_range[var1,var1]
29 #PRJT2: [4, 25, 8]; exp_num(1), is_atom(FALSE)
30 #HASH RIGHT SEMI JOIN2: [4, 25, 8]; n_keys(1) join condition(ORG_USER.ORGID = exp_cast(123555)) KEY(ORG_USER.USERID=exp_cast(T2.USERID)) KEY_NULL_EQU(0)
31 #SSCN: [0, 226, 60]; IDX_ORG_USER_USERID_ORGID(ORG_USER)
32 #BLKUP2: [3, 1355, 8]; IDXSDRR7DRYEF814Q4Q296973RUG(T2)
33 #SSEK2: [3, 1355, 8]; scan_type(ASC), IDXSDRR7DRYEF814Q4Q296973RUG(T_TASK as T2), scan_range[var1,var1]
操作时间
查看现场所有操作符的时间记录
OP TIME(US) PERCENT RANK SEQ N_ENTER
PRJT2 2 0% 32 2 4
PRJT2 2 0% 32 4 2
ACTRL 6 0% 30 10 14
DLCK 6 0% 30 0 2
AAGR2 7 0% 29 3 3
PRJT2 8 0% 28 14 16
PRJT2 23 0% 27 16 112
NSET2 87 0% 26 1 3
SSEK2 258 0% 25 13 2
PRJT2 322 0% 24 25 1526
PRJT2 382 0% 23 23 1526
PRJT2 388 0% 22 22 1526
SLCT2 462 0% 21 17 112
BLKUP2 895 0.01% 20 12 4
PRJT2 923 0.01% 19 29 1526
UNION_ALL2 1209 0.01% 18 24 2289
HI3 1265 0.01% 17 7 1528
SLCT2 1418 0.01% 16 6 1528
IJI2 1481 0.01% 15 9 2297
HI3 2094 0.02% 14 11 17
NLS2 2293 0.02% 13 5 1528
SLCT2 3419 0.03% 12 26 2289
SSEK2 5006 0.05% 11 33 1526
SLCT2 5614 0.05% 10 8 1528
HAGR2 7057 0.06% 9 15 64
BLKUP2 8704 0.08% 8 19 3052
SSEK2 9501 0.09% 7 28 1526
BLKUP2 11266 0.10% 6 32 3052
CSCN2 14430 0.13% 5 18 56
SSEK2 19190 0.18% 4 20 1526
SSCN 20908 0.19% 3 31 1526
BLKUP2 26127 0.24% 2 27 3052
HRS2 10783706 98.68% 1 30 3815
公司
执行计划
#NSET2: [6, 1, 264]
2 #PRJT2: [6, 1, 264]; exp_num(1), is_atom(FALSE)
3 #AAGR2: [6, 1, 264]; grp_num(0), sfun_num(1) slave_empty(0)
4 #PRJT2: [6, 1, 264]; exp_num(0), is_atom(FALSE)
5 #HASH LEFT SEMI JOIN2: [6, 1, 264]; KEY_NUM(1); KEY(P.ID=DMTEMPVIEW_33481505.colname) KEY_NULL_EQU(0)
6 #SLCT2: [2, 2, 264]; (B.FK_BUSINESSTYPE_ID = var3 AND B.ID <> var4)
7 #HASH2 INNER JOIN: [2, 2, 264]; RKEY_UNIQUE KEY_NUM(1); KEY(exp_cast(P.FK_BUSINESS_ID)=B.ID) KEY_NULL_EQU(0)
8 #SLCT2: [2, 2, 264]; (B.FK_BUSINESSTYPE_ID = var1 AND B.ID <> var2)
9 #NEST LOOP INDEX JOIN2: [2, 2, 264]
10 #ACTRL: [2, 2, 264];
11 #SLCT2: [2, 2, 204]; (P.PROCESSSTATE = '状态A' AND P.LIFESTATE = '状态B')
12 #HASH2 INNER JOIN: [2, 2, 204]; RKEY_UNIQUE KEY_NUM(1); KEY(TC1.PROJECTID=P.ID) KEY_NULL_EQU(0)
13 #SLCT2: [2, 2, 204]; (P.PROCESSSTATE = '状态A' AND P.LIFESTATE = '状态B')
14 #NEST LOOP INDEX JOIN2: [2, 2, 204]
15 #ACTRL: [2, 2, 204];
16 #PRJT2: [1, 2, 100]; exp_num(1), is_atom(FALSE)
17 #HAGR2: [1, 2, 100]; grp_num(1), sfun_num(0); slave_empty(0) keys(DMTEMPVIEW_33481503.TMPCOL0)
18 #PRJT2: [0, 295, 100]; exp_num(1), is_atom(FALSE)
19 #SLCT2: [0, 295, 100]; T.ACTIVITYNAME <> '活动名'
20 #CSCN2: [0, 5902, 100]; INDEX33557651(T_TASK as T)
21 #CSEK2: [0, 1, 0]; scan_type(UNIQUE), INDEX33557571(T_PROJECT as P), scan_range[TC1.PROJECTID,TC1.PROJECTID]
22 #CSCN2: [0, 2849, 104]; INDEX33557571(T_PROJECT as P)
23 #BLKUP2: [0, 1, 0]; INDEX33559997(B)
24 #SSEK2: [0, 1, 0]; scan_type(ASC), INDEX33559997(T_BUSINESS as B), scan_range[exp_cast(P.FK_BUSINESS_ID),exp_cast(P.FK_BUSINESS_ID)]
25 #CSCN2: [0, 41, 60]; INDEX33559996(T_BUSINESS as B)
26 #PRJT2: [3, 258, 8]; exp_num(1), is_atom(FALSE)
27 #PRJT2: [3, 258, 8]; exp_num(1), is_atom(FALSE)
28 #UNION ALL: [3, 258, 8]
29 #PRJT2: [0, 147, 8]; exp_num(1), is_atom(FALSE)
30 #SLCT2: [0, 147, 8]; T1.ORGID = 34111
31 #CSCN2: [0, 5902, 8]; INDEX33557651(T_TASK as T1)
32 #PRJT2: [2, 110, 8]; exp_num(1), is_atom(FALSE)
33 #HASH RIGHT SEMI JOIN2: [2, 110, 8]; n_keys(1) join condition(ORG_USER.ORGID = exp_cast(12345)) KEY(ORG_USER.USERID=exp_cast(T2.USERID)) KEY_NULL_EQU(0)
34 #SSCN: [0, 225, 60]; IDX_ORG_USER_USERID_ORGID(RG_USER)
35 #CSCN2: [0, 5902, 8]; INDEX33557651(T_TASK as T2)
操作时间
OP TIME(US) PERCENT RANK SEQ N_ENTER
PRJT2 4 0.01% 35 4 6
PRJT2 6 0.01% 34 16 6
ACTRL 7 0.01% 33 15 7
PRJT2 8 0.01% 30 2 4
ACTRL 8 0.01% 30 10 4
PRJT2 8 0.01% 30 27 24
PRJT2 10 0.02% 29 29 12
PRJT2 11 0.02% 28 26 24
PRJT2 14 0.02% 27 32 14
SLCT2 15 0.03% 25 6 22
DLCK 15 0.03% 25 0 2
PRJT2 16 0.03% 24 18 14
HI3 17 0.03% 22 7 22
UNION_ALL2 17 0.03% 22 28 25
AAGR2 20 0.03% 21 3 5
IJI2 35 0.06% 20 9 33
SLCT2 76 0.13% 19 11 6
SLCT2 78 0.13% 17 8 22
SSCN 78 0.13% 17 34 2
BLKUP2 107 0.18% 16 23 40
SLCT2 164 0.28% 15 30 13
NSET2 226 0.39% 14 1 3
SSEK2 229 0.39% 12 24 20
SLCT2 229 0.39% 12 19 14
CSCN2 988 1.69% 11 31 7
CSCN2 1057 1.81% 10 35 7
IJI2 1151 1.97% 9 14 3004
CSCN2 1851 3.17% 8 22 4
SLCT2 2097 3.59% 7 13 1004
CSCN2 2574 4.41% 6 20 7
HLS2 2980 5.10% 5 5 26
HAGR2 3960 6.78% 4 17 10
CSEK2 7764 13.30% 3 21 1999
HI3 14784 25.32% 2 12 10
HRS2 17774 30.45% 1 33 16
情况分析
通过比较执行计划，发现执行代价差很远，公司环境的是6，现场环境的是29；
通过代价的比较，可以看到差异最大的是连接方式；
-
Row 30 用的都是hash right wemi join2，行数据处理长度是一样的，公司环境的PRJT2的操作返回的结果集比现场的还要多三倍，但是现场的代价要比公司环境高；
-
Row5 在进行表连接的时候，公司环境采用了HASH LEFT SEMI JOIN2(row 5)，现场环境采用了NEST LOOP SEMI JOIN2(row 5)
根据DM的官方文档，他的优化器只有在非等值连接的时候才会采用嵌套循环连接；
通过操作时间记录的比较，可以看出，现场环境在HRS2所消耗的时间明显不对劲，直接占了10.78秒，占比98%
图2 右边是现场，左边是公司
先从最异常的地方切入，查HRS2是什么
从HRS2定位出问题的子查询
先从最异常的地方切入，查HRS2是什么
查询DM的动态视图，发现HRS2的意思是hash右半连接，看DM官方文档，可以判断出hash右半连接是在子查询等值连接的时候调用的
select * from V$SQL_NODE_NAME
where name in ('HRS2');
TYPE$ NAME DESC_CONTENT
223 HRS2 HASH RIGHT SEMI JOIN
查现场的执行计划，执行计划里面只有一个是HASH RIGHTSEMI JOIN的。根据执行计划的代价计算以及结果集行数，虽然有差异但是感觉差的不是很大
右边是现场，左边是公司
根据执行计划的特征，认为进行HRS2操作的应该是这个sql语句
SELECT
projectid
FROM
T_TASK t2
where
t2.userid in
(
select userid from DB1.org_user where orgid in(123444)
s
在公司环境和现场环境分别执行这个语句，速度都很快，但是看执行计划的时候发现了一个问题，现场环境里，执行计划的结果集非常的大，到了40000行，而实际执行的时候其实只返回了135行数据。
现场
公司
执行计划里，hash半连接产生的结果集很明显跟实际查询出来的值不对，是这个是什么问题呢？
我们知道执行计划里的代价估计是和统计信息息息相关的，那么会不会是统计信息出了问题？
尝试更新统计数据
我们知道执行计划里的代价估计是和统计信息息息相关的，那么会不会是统计信息出了问题？让我们重新看一下这个sql
如果是统计信息出现问题的话，可能是t_task的userid的统计信息出现问题，开下统计信息的收集，收集一下现场环境T_TASK的USERID的统计信息
DBMS_STATS.COLUMN_STATS_SHOW('DB1','T_TASK','USERID');
DBMS_STATS.TABLE_STATS_SHOW('DB1','T_TASK');
结果如下
NUM_DISTINCT LOW_VALUE HIGH_VALUE NUM_NULLS NUM_BUCKETS SAMPLE_SIZE HISTOGRAM
166 1 97103 215 166 8340 FREQUENCY
OWNER TABLE_NAME COLUMN_NAME HISTOGRAM ENDPOINT_VALUE ENDPOINT_HEIGHT ENDPOINT_KEYHEIGHT ENDPOINT_DISTINCT
DB1 T_TASK USERID FREQUENCY 1 162 NULL NULL
DB1 T_TASK USERID FREQUENCY 4796 157 NULL NULL
DB1 T_TASK USERID FREQUENCY 4844 162 NULL NULL
DB1 T_TASK USERID FREQUENCY 20645 2433 NULL NULL
DB1 T_TASK USERID FREQUENCY 20646 2210 NULL NULL
DB1 T_TASK USERID FREQUENCY 20647 2492 NULL NULL
DB1 T_TASK USERID FREQUENCY 20648 5 NULL NULL
DB1 T_TASK USERID FREQUENCY 20649 1732 NULL NULL
DB1 T_TASK USERID FREQUENCY 20650 983 NULL NULL
DB1 T_TASK USERID FREQUENCY 20651 1146 NULL NULL
DB1 T_TASK USERID FREQUENCY 20652 21 NULL NULL
DB1 T_TASK USERID FREQUENCY 20653 10 NULL NULL
DB1 T_TASK USERID FREQUENCY 20655 678 NULL NULL
DB1 T_TASK USERID FREQUENCY 20656 3052 NULL NULL
DB1 T_TASK USERID FREQUENCY 20657 2992 NULL NULL
DB1 T_TASK USERID FREQUENCY 20658 787 NULL NULL
DB1 T_TASK USERID FREQUENCY 20659 1406 NULL NULL
DB1 T_TASK USERID FREQUENCY 20661 16 NULL NULL
DB1 T_TASK USERID FREQUENCY 20665 5 NULL NULL
DB1 T_TASK USERID FREQUENCY 20667 673 NULL NULL
DB1 T_TASK USERID FREQUENCY 20668 10 NULL NULL
DB1 T_TASK USERID FREQUENCY 20669 48 NULL NULL
DB1 T_TASK USERID FREQUENCY 20672 391 NULL NULL
DB1 T_TASK USERID FREQUENCY 20673 320 NULL NULL
DB1 T_TASK USERID FREQUENCY 20675 5 NULL NULL
DB1 T_TASK USERID FREQUENCY 20676 38 NULL NULL
DB1 T_TASK USERID FREQUENCY 20678 841 NULL NULL
DB1 T_TASK USERID FREQUENCY 20679 32 NULL NULL
DB1 T_TASK USERID FREQUENCY 20680 59 NULL NULL
DB1 T_TASK USERID FREQUENCY 20681 65 NULL NULL
DB1 T_TASK USERID FREQUENCY 20682 65 NULL NULL
DB1 T_TASK USERID FREQUENCY 20683 10 NULL NULL
DB1 T_TASK USERID FREQUENCY 20684 92 NULL NULL
DB1 T_TASK USERID FREQUENCY 20685 21 NULL NULL
DB1 T_TASK USERID FREQUENCY 20686 10 NULL NULL
DB1 T_TASK USERID FREQUENCY 20687 32 NULL NULL
DB1 T_TASK USERID FREQUENCY 20688 10 NULL NULL
DB1 T_TASK USERID FREQUENCY 20689 70 NULL NULL
DB1 T_TASK USERID FREQUENCY 20690 21 NULL NULL
DB1 T_TASK USERID FREQUENCY 20691 38 NULL NULL
DB1 T_TASK USERID FREQUENCY 20692 43 NULL NULL
DB1 T_TASK USERID FREQUENCY 20694 2085 NULL NULL
DB1 T_TASK USERID FREQUENCY 20695 320 NULL NULL
DB1 T_TASK USERID FREQUENCY 20696 146 NULL NULL
DB1 T_TASK USERID FREQUENCY 20697 5 NULL NULL
DB1 T_TASK USERID FREQUENCY 20698 564 NULL NULL
DB1 T_TASK USERID FREQUENCY 20700 10 NULL NULL
DB1 T_TASK USERID FREQUENCY 20701 988 NULL NULL
DB1 T_TASK USERID FREQUENCY 20702 5 NULL NULL
DB1 T_TASK USERID FREQUENCY 20703 108 NULL NULL
DB1 T_TASK USERID FREQUENCY 20704 43 NULL NULL
DB1 T_TASK USERID FREQUENCY 20707 38 NULL NULL
DB1 T_TASK USERID FREQUENCY 20708 92 NULL NULL
DB1 T_TASK USERID FREQUENCY 20709 48 NULL NULL
DB1 T_TASK USERID FREQUENCY 20710 92 NULL NULL
DB1 T_TASK USERID FREQUENCY 20711 59 NULL NULL
DB1 T_TASK USERID FREQUENCY 20712 146 NULL NULL
DB1 T_TASK USERID FREQUENCY 20713 124 NULL NULL
DB1 T_TASK USERID FREQUENCY 20714 1064 NULL NULL
DB1 T_TASK USERID FREQUENCY 20715 190 NULL NULL
DB1 T_TASK USERID FREQUENCY 20717 1151 NULL NULL
DB1 T_TASK USERID FREQUENCY 20718 10 NULL NULL
DB1 T_TASK USERID FREQUENCY 20719 59 NULL NULL
DB1 T_TASK USERID FREQUENCY 20720 21 NULL NULL
DB1 T_TASK USERID FREQUENCY 20721 945 NULL NULL
DB1 T_TASK USERID FREQUENCY 20722 10 NULL NULL
DB1 T_TASK USERID FREQUENCY 20725 5 NULL NULL
DB1 T_TASK USERID FREQUENCY 20726 38 NULL NULL
DB1 T_TASK USERID FREQUENCY 20727 81 NULL NULL
DB1 T_TASK USERID FREQUENCY 20728 418 NULL NULL
DB1 T_TASK USERID FREQUENCY 20729 32 NULL NULL
DB1 T_TASK USERID FREQUENCY 20731 287 NULL NULL
DB1 T_TASK USERID FREQUENCY 20732 5 NULL NULL
DB1 T_TASK USERID FREQUENCY 20733 70 NULL NULL
DB1 T_TASK USERID FREQUENCY 20734 1069 NULL NULL
DB1 T_TASK USERID FREQUENCY 20735 434 NULL NULL
DB1 T_TASK USERID FREQUENCY 20736 162 NULL NULL
DB1 T_TASK USERID FREQUENCY 20737 1455 NULL NULL
DB1 T_TASK USERID FREQUENCY 20738 315 NULL NULL
DB1 T_TASK USERID FREQUENCY 20739 108 NULL NULL
DB1 T_TASK USERID FREQUENCY 20741 244 NULL NULL
DB1 T_TASK USERID FREQUENCY 20742 38 NULL NULL
DB1 T_TASK USERID FREQUENCY 20743 27 NULL NULL
DB1 T_TASK USERID FREQUENCY 20744 48 NULL NULL
DB1 T_TASK USERID FREQUENCY 20745 27 NULL NULL
DB1 T_TASK USERID FREQUENCY 20746 27 NULL NULL
DB1 T_TASK USERID FREQUENCY 20747 200 NULL NULL
DB1 T_TASK USERID FREQUENCY 20748 124 NULL NULL
DB1 T_TASK USERID FREQUENCY 20750 260 NULL NULL
DB1 T_TASK USERID FREQUENCY 20752 287 NULL NULL
DB1 T_TASK USERID FREQUENCY 20753 5 NULL NULL
DB1 T_TASK USERID FREQUENCY 20754 5 NULL NULL
DB1 T_TASK USERID FREQUENCY 20755 5 NULL NULL
DB1 T_TASK USERID FREQUENCY 20756 5 NULL NULL
DB1 T_TASK USERID FREQUENCY 20757 776 NULL NULL
DB1 T_TASK USERID FREQUENCY 20758 10 NULL NULL
DB1 T_TASK USERID FREQUENCY 20759 16 NULL NULL
DB1 T_TASK USERID FREQUENCY 20761 5 NULL NULL
DB1 T_TASK USERID FREQUENCY 20765 5 NULL NULL
DB1 T_TASK USERID FREQUENCY 20766 5 NULL NULL
重新看一下之前的sql的执行计划，可以看到，执行计划的数据集确实更新了，但是和实际数据还是有很大的区别，这应该是达梦数据库的问题，先不管了。
这样的话需要考虑一下别的解决方案，让我们回想一下前面看两个环境的执行计划对比的时候，还发现他们的连接方式不一样，那么在现场环境使用hint会不会好一点呢?
尝试使用hint
让我们回想一下前面看两个环境的执行计划对比的时候，还发现他们的连接方式不一样，那么在现场环境使用hint会不会好一点呢
这里使用了hint /*+ SEMI_GEN_CROSS OPTIMIZER_MODE(1) */进行优化，优先采用半连接转换为等价的内连接。
执行代价变大了，变成了31，但是sql执行速度变成23ms，也就是1s不到。
执行计划对比后，发现差异最大的是HRS2的结果集返回数，以及下面的子查询。
（看红色的部分）
而且其父节点的嵌套半连接也变成了hash半连接
查询一下变化了的这几个操作的含义
select * from V$SQL_NODE_NAME
where name in ('HRS2','BLKUP2','SSEK2','CSCN2');
--通过查询DBA手册 又自己加了第四列的文字描述
--Tips:二级索引指的是非聚簇索引，也就是非主键的索引
TYPE$ NAME DESC_CONTENT 描述
200 CSCN2 CLUSTER SCAN 聚集索引扫描
218 BLKUP2 BOOKMARK LOOKUP 定位查找
219 SSEK2 SECONDARY INDEX SEEK 二级索引数据定位
223 HRS2 HASH RIGHT SEMI JOIN HASH右半连接
问题应该是BLKUP2导致的，因为它的查找操作会带来第二次IO开销（应该就是俗称的回表）
可以看出来，在优化之后的执行计划中，用CSCN2代替了BLKUP2+SSEK2的操作
个人推断应该是因为用了BLKUP2+SSEK2返回了完整的数据（非索引），导致父节点没有用到索引，而是用了嵌套连接
所以出现问题的应该还是统计信息，由于统计信息的不准确导致了优化器选择了错误的执行路径
但是要怎么让数据库收集正确的统计信息呢？
我注意到出问题的sql里是有一个union all的，一般来说由于union all不去重，而union去重，所以当是否去重不影响返回结果的时候，一般是提倡用union all来进行合并的，这样减少了一步去重操作，sql的执行速度会变快。但是在这个例子里，union all返回的结果还需要用来进一步的查询，这只是个子查询而已，那么会不会由于数据库在收集统计信息的时候，认为union all的返回的结果集很大，所以导致错误的执行计划呢？我尝试用union来替换union all，看看是否会有所改善。
尝试用union代替union all
我注意到出问题的sql里是有一个union all的，一般来说由于union all不去重，而union去重，所以当是否去重不影响返回结果的时候，一般是提倡用union all来进行合并的，这样减少了一步去重操作，sql的执行速度会变快。但是在这个例子里，union all返回的结果还需要用来进一步的查询，这只是个子查询而已，那么会不会由于数据库在收集统计信息的时候，认为union all的返回的结果集很大，所以导致错误的执行计划呢？我尝试用union来替换union all，看看是否会有所改善。
我尝试了把union all 改成union，执行速度就飞起来了，但是可以看到，实际上执行计划的代价是变多了。
我在现场环境分别比较了union all和union的执行计划，发现实际上两个执行计划仅仅是差了一个distinct，带来的是结果集的大小差异，差了100倍，而实际执行时间差了6倍
也就是说，虽然union all的执行时间确实会比union的速度快，但是返回的结果集数量可能会差很远
普遍来讲，在可以确定返回的数据一样的情况下，由于union all不去重，没有去重操作的资源消耗，确实是union all 比union效率更高，执行结果是符合这个认知的
所以union得到的结果集的数量确实是一个很重要的影响因素，这也可能解释了为什么公司环境用的是hash半连接，而现场用的嵌套循环，可能因为优化器估算的数据量的差异，他认为导致走索引反而还没这么划算（由于回表的问题，建议是当返回的数据占总数据量的5%才建索引）
CSCN2 = BLKUP2+SSEK2
让我们重新再看一下公司环境和现场环境的执行计划以及统计数据的对比
惊喜地发现，两个环境的执行计划区别和优化前后的区别是一样的，就是公司环境用CSCN2代替了现场BLKUP2+SSEK2的操作
再进一步，可以查出现场环境用的二级索引IDXSDRR7DRYEF814Q4Q296973RUG的索引定义是这样的
CREATE INDEX "IDXSDRR7DRYEF814Q4Q296973RUG" ON "DB1"."T_TASK"("PROJECTID" ASC) STORAGE(ON "MAIN", CLUSTERBTR) ;
而公司环境用的INDEX33557651索引其实就是T_TASK.ID主键的索引
我们可以在现场环境做个实验，判断如果现场用了CSCN2的话，执行计划会是怎样的
select
count(*)
from
(
select
p.id as pid ,
tc1.taskCount,
tc1.finishCount
from
DB3.t_project p
join DB2.business b
on
p.fk_business_id = b.id
join
(
select
t.projectid ,
sum(case when t.projectid > 0 then 1 else 0 end) taskCount,
sum(case when t.status = 87 then 1 else 0 end ) finishCount
from
t_task t
where
t.ACTIVITYNAME != '活动名'
group by
t.PROJECTID
)
tc1
on
tc1.projectid = p.id
where
b.fk_businesstype_id = 92
and b.id <> 655
and p.processstate in ('状态A')
and p.lifestate in ('状态B')
and exists
(
select
1
from
(
SELECT
projectid FROM t_task index INDEX33558632 t1 -- *
where t1.orgid in(123456)
union all
SELECT
projectid
FROM
t_task index INDEX33558632 t2 -- *
where
t2.userid in
(
select userid from DB3.org_user where orgid in(123456S)
)
)
tt
where
tt.projectid=p.id
)
)
t;
执行成功, 执行耗时94毫秒. 执行号:226826120
1 #NSET2: [31, 1, 264]
2 #PRJT2: [31, 1, 264]; exp_num(1), is_atom(FALSE)
3 #AAGR2: [31, 1, 264]; grp_num(0), sfun_num(1) slave_empty(0)
4 #PRJT2: [31, 1, 264]; exp_num(0), is_atom(FALSE)
5 #HASH LEFT SEMI JOIN2: [31, 1, 264]; KEY_NUM(1); KEY(P.ID=DMTEMPVIEW_18659545.colname) KEY_NULL_EQU(0)
6 #SLCT2: [9, 1, 264]; (B.FK_BUSINESSTYPE_ID = var3 AND B.ID <> var4)
7 #HASH2 INNER JOIN: [9, 1, 264]; RKEY_UNIQUE KEY_NUM(1); KEY(exp_cast(P.FK_BUSINESS_ID)=B.ID) KEY_NULL_EQU(0)
8 #SLCT2: [9, 1, 264]; (B.FK_BUSINESSTYPE_ID = var1 AND B.ID <> var2)
9 #NEST LOOP INDEX JOIN2: [9, 1, 264]
10 #ACTRL: [9, 1, 264];
11 #HASH2 INNER JOIN: [9, 1, 204]; LKEY_UNIQUE KEY_NUM(1); KEY(P.ID=TC1.PROJECTID) KEY_NULL_EQU(0)
12 #BLKUP2: [0, 4, 104]; IDX4YCDLECSAEWEX8GQCQB5BSGB(P)
13 #SSEK2: [0, 4, 104]; scan_type(ASC), IDX4YCDLECSAEWEX8GQCQB5BSGB(T_PROJECT as P), scan_range[('状态A','状态B'),('状态A','状态B')]
14 #PRJT2: [8, 27, 100]; exp_num(1), is_atom(FALSE)
15 #HAGR2: [8, 27, 100]; grp_num(1), sfun_num(0); slave_empty(0) keys(DMTEMPVIEW_18659543.TMPCOL0)
16 #PRJT2: [7, 2721, 100]; exp_num(1), is_atom(FALSE)
17 #SLCT2: [7, 2721, 100]; T.ACTIVITYNAME <> '活动名'
18 #CSCN2: [7, 54430, 100]; INDEX33558632(T_TASK as T)
19 #BLKUP2: [0, 1, 0]; INDEX33557988(B)
20 #SSEK2: [0, 1, 0]; scan_type(ASC), INDEX33557988(T_B as B), scan_range[exp_cast(P.FK_BUSINESS_ID),exp_cast(P.FK_BUSINESS_ID)]
21 #CSCN2: [0, 84, 60]; INDEX33557987(T_B as B)
22 #PRJT2: [19, 25952, 8]; exp_num(1), is_atom(FALSE)
23 #PRJT2: [19, 25952, 8]; exp_num(1), is_atom(FALSE)
24 #UNION ALL: [19, 25952, 8]
25 #PRJT2: [6, 1360, 8]; exp_num(1), is_atom(FALSE)
26 #SLCT2: [6, 1360, 8]; T1.ORGID = 1234
27 #CSCN2: [6, 54430, 8]; INDEX33558632(T_TASK as T1)
28 #PRJT2: [10, 24591, 8]; exp_num(1), is_atom(FALSE)
29 #HASH RIGHT SEMI JOIN2: [10, 24591, 8]; n_keys(1) KEY(RG_USER.USERID=exp_cast(T2.USERID)) KEY_NULL_EQU(0)
30 #SLCT2: [0, 5, 60]; RG_USER.ORGID = var5
31 #SSCN: [0, 226, 60]; IDX_ORG_USER_USERID_ORGID(RG_USER)
32 #CSCN2: [5, 54430, 8]; INDEX33558632(T_TASK as T2)
OP TIME(US) PERCENT RANK SEQ N_ENTER
PRJT2 1 0% 32 4 2
DLCK 4 0% 30 0 2
PRJT2 4 0% 30 2 4
PRJT2 5 0.01% 29 14 16
AAGR2 6 0.01% 28 3 3
ACTRL 7 0.01% 27 10 14
PRJT2 10 0.01% 26 25 62
PRJT2 18 0.02% 25 16 112
SLCT2 20 0.02% 24 30 4
PRJT2 24 0.03% 22 28 62
PRJT2 24 0.03% 22 22 122
PRJT2 29 0.04% 21 23 122
UNION_ALL2 35 0.04% 20 24 123
SSCN 43 0.05% 19 31 2
NSET2 83 0.10% 18 1 3
SSEK2 240 0.30% 17 13 2
SLCT2 268 0.33% 16 6 1640
HI3 301 0.37% 15 7 1640
SLCT2 470 0.58% 14 26 87
SLCT2 505 0.62% 13 17 112
IJI2 515 0.63% 12 9 2465
SLCT2 954 1.17% 11 8 1640
BLKUP2 957 1.18% 10 12 4
HI3 2055 2.53% 9 11 17
BLKUP2 2093 2.57% 8 19 3276
SSEK2 3410 4.19% 7 20 1638
HRS2 5558 6.84% 6 29 89
HAGR2 7066 8.69% 5 15 64
CSCN2 11155 13.72% 4 32 56
CSCN2 11424 14.05% 3 27 56
CSCN2 15437 18.99% 2 18 56
HLS2 18569 22.84% 1 5 882
可以看到，hint确实成功了，执行计划的路径改成了用T_TASK的主键索引进行查询，得到的执行计划的总代价确实比不加hint的高，加了之后是31，没加之前是29，但是实际上执行速度要比原sql快得多，仅94ms，原来的接近6s
我们还可以再对比一下公司环境和现场环境这个子查询用union all时候的区别
执行成功, 执行耗时39毫秒. 执行号:295811416
返回1185条数据
1 #NSET2: [3, 201, 8]
2 #PRJT2: [3, 201, 8]; exp_num(1), is_atom(FALSE)
3 #UNION ALL: [3, 201, 8]
4 #PRJT2: [0, 147, 8]; exp_num(1), is_atom(FALSE)
5 #SLCT2: [0, 147, 8]; T1.ORGID = 654777
6 #CSCN2: [0, 5902, 8]; INDEX33557651(T_TASK as T1)
7 #PRJT2: [2, 53, 8]; exp_num(1), is_atom(FALSE)
8 #HASH RIGHT SEMI JOIN2: [2, 53, 8]; n_keys(1) join condition(ORG_USER.ORGID = exp_cast(63454)) KEY(ORG_USER.USERID=exp_cast(T2.USERID)) KEY_NULL_EQU(0)
9 #SSCN: [0, 225, 60]; IDX_ORG_USER_USERID_ORGID(ORG_USER)
10 #CSCN2: [0, 5902, 8]; INDEX33557651(T_TASK as T2)
统计信息：
PRJT2 3 0.48% 6 2 4
UNION_ALL2 3 0.48% 6 3 4
PRJT2 4 0.64% 5 4 4
DLCK 11 1.76% 4 0 1
SLCT2 74 11.84% 3 5 5
NSET2 119 19.04% 2 1 3
CSCN2 411 65.76% 1 6 3
可以看到，除了现场环境多了一个SLCT2(select)以外，其他执行计划是一样的，但是代价却差了16，已经可以确定是T_TASK数据量的区别导致的执行计划偏差了。到这里结论就很明显了
结论和可能的优化
解决方案：将子查询里的union all 改成union，从而迫使优化器使用主键索引进行查询
公司环境（100ms以内）和现场环境（6s到8s）的执行速度差这么远，是因为达梦在相同sql的情况下选了不同的执行计划，达梦之所以会选这种执行路径的原因如下：
- 由于数据量的差距（相差10倍），在公司环境取主键索引进行查询和现场取主键索引进行查询，优化器得出的代价是不一样的；
- 达梦之所以会选这种执行路径，是由于现场得出的代价较大，所以没有走按主键索引查询的这个执行计划；因为进行了代价的比较之后，得出了更好的执行路径进行执行（虽然实际上并不是更优的）；
现场环境的sql执行速度之所以这么慢，是因为：
- 达梦得出这条执行计划的代价估算本身确实没有问题，union all的代价确实比union大
- 达梦的优化器还有待提高，毕竟经过一通实验可以发现，虽然达梦优化出了代价更小的执行计划，但是执行的时间却并不比代价更大的执行计划来的快
结论：
- 并不是所有情况下，union all（不去重）都比union好，需要根据数据量而定，尤其是需要将union后的数据集进一步进行连接/嵌套的时候
- 虽然sql相同，但是由于数据量的影响，得到的执行代价是会有所区别的，因此sql的优化是要贴合实际环境进行的，不要盲目遵守规则
可能的优化
查到公司环境和现场环境的执行计划里，差异最大的是HRS2的结果集返回数，以及下面的子查询的时候，即公司环境用用CSCN2代替了现场的BLKUP2+SSEK2的操作的时候，就应该可以定位到出现问题的sql语句
应该尝试直接用hint来改变现场的执行计划，这时候是可以发现由于数据量的差异带来的union all 里返回的数据集大小区别的
其实存在的核心问题应该是：
由于达梦优化器对union返回的结果集的估算问题，导致达梦认为走嵌套循环连接比hash连接快或者说达梦认为不走索引会更快一点，但实际上由于估算的结果集大小和实际的结果集大小相差很大，导致实际执行的时候开销非常大，预想和实际相差甚远
这样思路就可以改成如何减少执行计划里对数据集的估算，从而让执行计划走主键索引
- 把union all 改成union（实际就是这么做了）
另一种可行的方式，或许可以更新一下T_TASK的orgid和userid的统计数据，从而影响优化器对代价的估算。试了但是没啥用，就算针对数据不均衡的列进行统计数据收集，也并没有改善达梦优化器对代价的估算 - 观察SQL建组合索引：观察sql，发现实际上最后需要的是p.orgid，p.userid和p.projectid的信息，或许可以尝试一下建p.projectid,p.userid,p.orgid的组合索引-》实际上建了组合索引的之后，问题就迎刃而解了
- 直接用hint指定执行计划走主键索引
