传参和不传参时优化器选择的执行计划不一致时出现慢SQL

现象描述：
项目开发中出现一条在应用程序中执行很慢，但是在管理工具中执行很快的一条SQL，首先更新相关表的统计信息，统计信息更新后，清除原先旧的统计信息，查看新生成的统计信息，还是没有变化；排查是否是驱动版本与数据库不一致导致的，经查看数据库版本是8.1.2.128，驱动版本也是，排除这个可能。接着发现在管理工具中传参后的执行计划和传参前的执行计划相差极大。
具体流程：

SELECT 
        。。。
	。。。
	。。。
     FROM A.XXX1 C
LEFT JOIN A.XXX2 M 
       ON A.ORDER_ID = M.ORDER_ID
LEFT JOIN AAA.XXX3 R 
       ON C.REFUND_ID = R.REFUND_ID
LEFT JOIN AAA.XXX4 OLOG 
       ON C.ORDER_ID = OLOG.ORDER_ID
LEFT JOIN BBB.XXX5 O 
       ON M.ORG_ID = O.ORG_ID
    WHERE (O.ORG_ID = ? OR O.PARENT_IDS like CONCAT('%', ?, '%')) AND ( (O.ORG_ID =? OR O.PARENT_IDS like CONCAT('%', ?, '%')) ) AND (M.ORDER_ID = ? OR C.SUB_ORDER_ID = ?) AND M.PAY_STATUS IN ('00', '01', '02', '03',  '04')  AND M.ORDER_STATUS IN ('00', '01', '02', '03',  '04') AND M.CREATE_TIME >= ? AND M.CREATE_TIME <= ?
 ORDER BY M.CREATE_TIME DESC

以上为原始SQL改写，执行计划如下：

该语句在应用程序中执行时间约30秒；
下面是传参后SQL：

SELECT 
 	。。。
	。。。
	。。。
     FROM A.XXX1 C
LEFT JOIN A.XXX2 M 
       ON A.ORDER_ID = M.ORDER_ID
LEFT JOIN AAA.XXX3 R 
       ON C.REFUND_ID = R.REFUND_ID
LEFT JOIN AAA.XXX4 OLOG 
       ON C.ORDER_ID = OLOG.ORDER_ID
LEFT JOIN BBB.XXX5 O 
       ON M.ORG_ID = O.ORG_ID
    WHERE (O.ORG_ID = '3414675942' OR O.PARENT_IDS like CONCAT('%', '3414675942', '%')) AND ( (O.ORG_ID ='3414675942' OR O.PARENT_IDS like CONCAT('%', '3414675942', '%')) ) AND (M.ORDER_ID = '241112153236062403' OR C.SUB_ORDER_ID = '241112153236062403') AND M.PAY_STATUS IN (('00', '01', '02', '03',  '04') AND M.ORDER_STATUS IN ('00', '01', '02', '03',  '04') AND M.CREATE_TIME >= '2024-11-06 00:00:00' AND M.CREATE_TIME <= '2024-11-12 23:59:59'
 ORDER BY M.CREATE_TIME DESC

执行计划如下：

2     #PRJT2: [5, 4, 1312]; exp_num(30), is_atom(FALSE) 
3       #SORT3: [5, 4, 1312]; key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0)
4         #UNION FOR OR2: [4, 4, 1312]; key_num(5), outer_join(-)
5           #UNION FOR OR2: [3, 3, 1312]; key_num(5), outer_join(-)
6             #UNION FOR OR2: [1, 2, 1312]; key_num(5), outer_join(-)
7               #NEST LOOP INNER JOIN2: [1, 1, 1312]; 
8                 #INDEX JOIN LEFT JOIN2: [1, 1, 1160]  ret_null(0)
9                   #INDEX JOIN LEFT JOIN2: [1, 1, 1160]  ret_null(0)
10                    #NEST LOOP INNER JOIN2: [1, 1, 1160]; 
11                      #HASH RIGHT SEMI JOIN2: [1, 1, 632]; n_keys(1) KEY(DMTEMPVIEW_27307605.colname=M.ORDER_STATUS) KEY_NULL_EQU(0)
12                        #CONST VALUE LIST: [1, 5, 48]; row_num(5), col_num(1), 
13                        #HASH RIGHT SEMI JOIN2: [1, 1, 632]; n_keys(1) KEY(DMTEMPVIEW_27307604.colname=M.PAY_STATUS) KEY_NULL_EQU(0)
14                          #CONST VALUE LIST: [1, 5, 48]; row_num(5), col_num(1), 
15                          #SLCT2: [1, 1, 632]; (M.CREATE_TIME >= '2024-11-06 00:00:00' AND M.CREATE_TIME <= '2024-11-12 23:59:59' AND M.ORG_ID = '3414675942')
16                            #BLKUP2: [1, 1, 632]; INDEX33556525(M)
17                              #SSEK2: [1, 1, 632]; scan_type(ASC), INDEX33556525(XXX2 as M), scan_range['241112153236062403','241112153236062403']
18                      #BLKUP2: [1, 1, 528]; IDX_XXX2_DETAIL_ORDER_ID(C)
19                        #SSEK2: [1, 1, 528]; scan_type(ASC), IDX_XXX1_ORDER_ID(XXX1 as C), scan_range['241112153236062403','241112153236062403']
20                    #BLKUP2: [1, 1, 48]; INDEX33556624(R)
21                      #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33556624(XXX3 as R), scan_range[C.REFUND_ID,C.REFUND_ID]
22                  #BLKUP2: [1, 1, 48]; INDEX33556506(OLOG)
23                    #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33556506(XXX4 as OLOG), scan_range[C.ORDER_ID,C.ORDER_ID]
24                #BLKUP2: [1, 1, 152]; INDEX33556008(O)
25                  #SSEK2: [1, 1, 152]; scan_type(ASC), INDEX33556008(XXX5 as O), scan_range['3414675942','3414675942']
26              #NEST LOOP INNER JOIN2: [1, 1, 1312]; 
27                #INDEX JOIN LEFT JOIN2: [1, 1, 1160]  ret_null(0)
28                  #INDEX JOIN LEFT JOIN2: [1, 1, 1160]  ret_null(0)
29                    #NEST LOOP INDEX JOIN2: [1, 1, 1160] 
30                      #HASH RIGHT SEMI JOIN2: [1, 1, 632]; n_keys(1) KEY(DMTEMPVIEW_27307607.colname=M.ORDER_STATUS) KEY_NULL_EQU(0)
31                        #CONST VALUE LIST: [1, 5, 48]; row_num(5), col_num(1), 
32                        #HASH RIGHT SEMI JOIN2: [1, 1, 632]; n_keys(1) KEY(DMTEMPVIEW_27307606.colname=M.PAY_STATUS) KEY_NULL_EQU(0)
33                          #CONST VALUE LIST: [1, 5, 48]; row_num(5), col_num(1), 
34                          #SLCT2: [1, 1, 632]; (M.CREATE_TIME >= '2024-11-06 00:00:00' AND M.CREATE_TIME <= '2024-11-12 23:59:59')
35                            #BLKUP2: [1, 1, 632]; IDX_1(M)
36                              #SSEK2: [1, 1, 632]; scan_type(ASC), IDX_1(ORD_MALL_ORDER as M), scan_range['3414675942','3414675942']
37                      #BLKUP2: [1, 1, 96]; INDEX33556543(C)
38                        #SSEK2: [1, 1, 96]; scan_type(ASC), INDEX33556543(XXX1 as C), scan_range[(M.ORDER_ID,'241112153236062403'),(M.ORDER_ID,'241112153236062403')]
39                    #BLKUP2: [1, 1, 48]; INDEX33556624(R)
40                      #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33556624(XXX3 as R), scan_range[C.REFUND_ID,C.REFUND_ID]
41                  #BLKUP2: [1, 1, 48]; INDEX33556506(OLOG)
42                    #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33556506(XXX4 as OLOG), scan_range[C.ORDER_ID,C.ORDER_ID]
43                #BLKUP2: [1, 1, 152]; INDEX33556008(O)
44                  #SSEK2: [1, 1, 152]; scan_type(ASC), INDEX33556008(XXX5 as O), scan_range['3414675942','3414675942']
45            #SLCT2: [1, 1, 1160]; exp11 > 0
46              #INDEX JOIN LEFT JOIN2: [1, 1, 1160]  ret_null(0)
47                #INDEX JOIN LEFT JOIN2: [1, 1, 1160]  ret_null(0)
48                  #INDEX JOIN LEFT JOIN2: [1, 1, 1160]  ret_null(0)
49                    #NEST LOOP INNER JOIN2: [1, 1, 1160]; 
50                      #HASH RIGHT SEMI JOIN2: [1, 1, 632]; n_keys(1) KEY(DMTEMPVIEW_27307609.colname=M.ORDER_STATUS) KEY_NULL_EQU(0)
51                        #CONST VALUE LIST: [1, 5, 48]; row_num(5), col_num(1), 
52                        #HASH RIGHT SEMI JOIN2: [1, 1, 632]; n_keys(1) KEY(DMTEMPVIEW_27307608.colname=M.PAY_STATUS) KEY_NULL_EQU(0)
53                          #CONST VALUE LIST: [1, 5, 48]; row_num(5), col_num(1), 
54                          #SLCT2: [1, 1, 632]; (M.CREATE_TIME >= '2024-11-06 00:00:00' AND M.CREATE_TIME <= '2024-11-12 23:59:59')
55                            #BLKUP2: [1, 1, 632]; INDEX33556525(M)
56                              #SSEK2: [1, 1, 632]; scan_type(ASC), INDEX33556525(ORD_MALL_ORDER as M), scan_range['241112153236062403','241112153236062403']
57                      #BLKUP2: [1, 1, 528]; IDX_XXX1_ORDER_ID(C)
58                        #SSEK2: [1, 1, 528]; scan_type(ASC), IDX_XXX1_ORDER_ID(XXX1 as C), scan_range['241112153236062403','241112153236062403']
59                    #BLKUP2: [1, 1, 48]; INDEX33556624(R)
60                      #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33556624(XXX3 as R), scan_range[C.REFUND_ID,C.REFUND_ID]
61                  #BLKUP2: [1, 1, 48]; INDEX33556506(OLOG)
62                    #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33556506(XXX4 as OLOG), scan_range[C.ORDER_ID,C.ORDER_ID]
63                #BLKUP2: [1, 1, 48]; INDEX33556008(O)
64                  #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33556008(XXX5 as O), scan_range[M.ORG_ID,M.ORG_ID]
65          #SLCT2: [1, 1, 1160]; exp11 > 0
66            #INDEX JOIN LEFT JOIN2: [1, 1, 1160]  ret_null(0)
67              #INDEX JOIN LEFT JOIN2: [1, 1, 1160]  ret_null(0)
68                #INDEX JOIN LEFT JOIN2: [1, 1, 1160]  ret_null(0)
69                  #HASH RIGHT SEMI JOIN2: [1, 1, 1160]; n_keys(1) KEY(DMTEMPVIEW_27307611.colname=M.ORDER_STATUS) KEY_NULL_EQU(0)
70                    #CONST VALUE LIST: [1, 5, 48]; row_num(5), col_num(1), 
71                    #HASH RIGHT SEMI JOIN2: [1, 1, 1160]; n_keys(1) KEY(DMTEMPVIEW_27307610.colname=M.PAY_STATUS) KEY_NULL_EQU(0)
72                      #CONST VALUE LIST: [1, 5, 48]; row_num(5), col_num(1), 
73                      #SLCT2: [1, 1, 1160]; (M.CREATE_TIME >= '2024-11-06 00:00:00' AND M.CREATE_TIME <= '2024-11-12 23:59:59')
74                        #NEST LOOP INDEX JOIN2: [1, 1, 1160] 
75                          #BLKUP2: [1, 1, 528]; IDX_XXX1_SUB_ORDER_ID(C)
76                            #SSEK2: [1, 1, 528]; scan_type(ASC), IDX_XXX1_SUB_ORDER_ID(XXX1 as C), scan_range['241112153236062403','241112153236062403']
77                          #BLKUP2: [1, 1, 48]; INDEX33556525(M)
78                            #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33556525(ORD_MALL_ORDER as M), scan_range[C.ORDER_ID,C.ORDER_ID]
79                  #BLKUP2: [1, 1, 48]; INDEX33556624(R)
80                    #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33556624(XXX3 as R), scan_range[C.REFUND_ID,C.REFUND_ID]
81                #BLKUP2: [1, 1, 48]; INDEX33556506(OLOG)
82                  #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33556506(XXX4 as OLOG), scan_range[C.ORDER_ID,C.ORDER_ID]
83              #BLKUP2: [1, 1, 48]; INDEX33556008(O)
84                #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33556008(XXX5 as O), scan_range[M.ORG_ID,M.ORG_ID] 
(相关表、索引改了名字)

此语句在管理工具中执行约18毫秒。
经过查看表关联字段，以及查询条件，都有相关索引，甚至是主键索引，但是执行计划不走。
于是试图通过HINT加参数控制其执行计划

发现将HASH JOIN关闭后，虽然看起来代价变得特别高，但是关联字段都走索引了，于是让开发在应用程序中将/+ENABLE_HASH_JOIN(0)/加上再测试，发现此慢SQL执行很快，也是约18毫秒，于是将类似的语句都加上/+ENABLE_HASH_JOIN(0)/后，相关慢SQL都解决了。
现象分析及结论：
虽然HASH JOIN看起来代价低，所以优化器选择了走HASH JOIN的执行计划，但实际运行时执行很慢，将HASH JOIN关闭后，代价看起来特别高，但是执行起来特别快，所以实际应用中，有时还是需要人工干预执行计划。

传参和不传参时优化器选择的执行计划不一致时出现慢SQL

作者