记录一次SQL 查询 LEFT JOIN 相关优化

1 环境说明

• 某项目的公文办公系统在生产环境刚部署好 , 发现业务系统打开慢 , 使用DM性能监视器(monitor.exe) 找出相关慢sql
• 慢sql 涉及3张表 , 三张表数据量和oracle 一样 , 一样的sql 查询语句在oracle 执行 1秒以内完成 , 在DM库需要30s
• 数据库版本
• oracle 11g
• dm8.1-3-100-2024.01.15-215128-20081-ENT

表名	数据量
A_INFOS	11458330
FW	616757
c_remotesend	10496798

2 sql 在dm库查询用时30秒

2.1 sql 语句

select
        *
from
        (
                SELECT 
                        sum(CASE WHEN send.STATUS >= 0 THEN 1 ELSE 0 END) AS TOTAL    ,
                        sum(CASE WHEN send.STATUS  = 1 THEN 1 ELSE 0 END) AS SIGNTOTAL,
                        A_INFOS.BT                                                    ,
                        A_INFOS.CWRQ                                                  ,
                        A_INFOS.ID                                                    ,
                        A_INFOS.MODULE_ID                                             ,
                        A_INFOS.OBJCLASS                                              ,
                        A_INFOS.WH                                                    ,
                        A_INFOS.XFORM_ID                                              ,
                        A_INFOS.MAJORUNIT                                             ,
                        A_INFOS.NGRQ                                                  ,
                        A_INFOS.DOCTYPE
                FROM
                        A_INFOS A_INFOS --数据量11458330
                INNER JOIN FW FW		--数据量616757
                ON
                        A_INFOS.ID = FW.INFO_ID
                LEFT JOIN c_remotesend send	--数据量10496798 (不带left join  查询用时 1秒, 带left join查询用时 30秒,最终结果集 2400 行数据量)
                ON
                        A_INFOS.id = send.info_id
                WHERE
                        A_INFOS.DOCTYPE LIKE '平行收文'
                    AND A_INFOS.module_id = 1912587286812359
                    AND A_INFOS.MAINUNIT  = 140683
                    AND A_INFOS.ROWSTATE >= 0
                    AND
                        (
                                (
                                        '%%' = '%%'
                                )
                             OR A_INFOS.BT LIKE '%%'
                        )
                    AND
                        (
                                (
                                        '%%' = '%%'
                                )
                             OR A_INFOS.WH LIKE '%%'
                        )
                    AND A_INFOS.CWRQ >= to_date('1900-01-01 00:00:00', 'yyyy-MM-dd HH24:mi:ss')
                    AND A_INFOS.CWRQ <= to_date('2099-12-12 



23:59:59', 'yyyy-MM-dd HH24:mi:ss')
                GROUP BY
                        A_INFOS.BT       ,
                        A_INFOS.CWRQ     ,
                        A_INFOS.ID       ,
                        A_INFOS.MODULE_ID,
                        A_INFOS.OBJCLASS ,
                        A_INFOS.WH       ,
                        A_INFOS.XFORM_ID ,
                        A_INFOS.MAJORUNIT,
                        A_INFOS.NGRQ     ,
                        A_INFOS.DOCTYPE
                ORDER BY
                        A_INFOS.CWRQ DESC
        )
where
        rownum <= 10

2.2 sql 执行计划

3 调优数据库参数

3.1 使用hint 调整数据库参数

• enable_hash_join
• phc_mode_enforce
• 查询数据库参数正在使用值

SELECT * FROM V$DM_INI WHERE PARA_NAME IN ('ENABLE_HASH_JOIN','PHC_MODE_ENFORCE');
				   生效
ENABLE_HASH_JOIN    1	0	1	1	N	1	1	enable hash join	SESSION	ALL_SYNC	CAN_SYNC
PHC_MODE_ENFORCE	0	0	15	0	N	0	0	enforce the join mode	SESSION	ALL_SYNC	CAN_SYNC

• 调整以下两个参数 sql查询时间 在3-4秒 , sql 未改动

select /*+ enable_hash_join(0) */
       /*+ phc_mode_enforce(2) */
        *
from
        (
                SELECT 
                        sum(CASE WHEN send.STATUS >= 0 THEN 1 ELSE 0 END) AS TOTAL    ,
                        sum(CASE WHEN send.STATUS  = 1 THEN 1 ELSE 0 END) AS SIGNTOTAL,
                        A_INFOS.BT                                                    ,
                        A_INFOS.CWRQ                                                  ,
                        A_INFOS.ID                                                    ,
                        A_INFOS.MODULE_ID                                             ,
                        A_INFOS.OBJCLASS                                              ,
                        A_INFOS.WH                                                    ,
                        A_INFOS.XFORM_ID                                              ,
                        A_INFOS.MAJORUNIT                                             ,
                        A_INFOS.NGRQ                                                  ,
                        A_INFOS.DOCTYPE
                FROM
                        A_INFOS A_INFOS --数据量11458330
                INNER JOIN FW FW		--数据量616757
                ON
                        A_INFOS.ID = FW.INFO_ID
                LEFT JOIN c_remotesend send	--数据量10496798 (不带left join  查询用时 1秒, 带left join查询用时 30秒,最终结果集 2400 行数据量)
                ON
                        A_INFOS.id = send.info_id
                WHERE
                        A_INFOS.DOCTYPE LIKE '平行收文'
                    AND A_INFOS.module_id = 1912587286812359
                    AND A_INFOS.MAINUNIT  = 140683
                    AND A_INFOS.ROWSTATE >= 0
                    AND
                        (
                                (
                                        '%%' = '%%'
                                )
                             OR A_INFOS.BT LIKE '%%'
                        )
                    AND
                        (
                                (
                                        '%%' = '%%'
                                )
                             OR A_INFOS.WH LIKE '%%'
                        )
                    AND A_INFOS.CWRQ >= to_date('1900-01-01 00:00:00', 'yyyy-MM-dd HH24:mi:ss')
                    AND A_INFOS.CWRQ <= to_date('2099-12-12 



23:59:59', 'yyyy-MM-dd HH24:mi:ss')
                GROUP BY
                        A_INFOS.BT       ,
                        A_INFOS.CWRQ     ,
                        A_INFOS.ID       ,
                        A_INFOS.MODULE_ID,
                        A_INFOS.OBJCLASS ,
                        A_INFOS.WH       ,
                        A_INFOS.XFORM_ID ,
                        A_INFOS.MAJORUNIT,
                        A_INFOS.NGRQ     ,
                        A_INFOS.DOCTYPE
                ORDER BY
                        A_INFOS.CWRQ DESC
        )
where
        rownum <= 10

3.2 hint 的执行计划

4 永久修改数据库参数

• 动态会话级 , 同时修改内存值 与 dm.ini 文件 , 新的会话生效 , 不用重启数据库

SP_SET_PARA_VALUE(1,'ENABLE_HASH_JOIN',0);
SP_SET_PARA_VALUE(1,'PHC_MODE_ENFORCE',2);
--(清除执行计划缓存）
CALL SP_CLEAR_PLAN_CACHE();

5 参数说明

参数名	默认值	类型	说明
ENABLE_HASH_JOIN	1	动态，会话级	是否允许使用哈希连接，0：不允许；1：允许。
PHC_MODE_ENFORCE	0	动态，会话级	控制连接的实现方式。0：优化器根据代价情况自由选择连接方式；1：允许使用 NEST LOOP INNER JOIN；2：允许使用索引连接；4：允许使用哈希连接；8：允许使用归并连接支持使用上述有效值的组合值，如 6 表示优化器根据代价情况在索引连接和哈希连接间进行选择。当参数值不为 0 或 15 且包含 2/4/8 其中之一或者它们的组合值时，会将包含的值对应的连接模式参数置 为 1， 没 有包 含 的 值对 应的 连 接 模式 参 数 置为 0（2 对 应 ENABLE_INDEX_JOIN，4 对 应ENABLE_HASH_JOIN，8 对应 ENABLE_MERGE_JOIN）。例如，取值为 6 时没有包含 8，则会将ENABLE_INDEX_JOIN 和 ENABLE_HASH_JOIN 置为 1，将 ENABLE_MERGE_JOIN 置为 0（仅强制设置内存中的值，不改变 dm.ini 文件中的值）

6 达梦数据库学习使用列表

• 达梦数据库学习使用列表 - - 点击跳转