004-DM数据库SQL的调优方法
摘要
本次学习,掌握DM数据库SQL的调优方法。
实验环境与准备
2.1 实验环境
|
类别 |
信息 |
|
系统环境 |
kylin10+x86 |
|
数据库版本 |
03134284604-20260707-335949-20228 |
|
数据库连接信息 |
10.*.*.16:5136 TATY/***** |
SQL 调优整体方法
遇到数据库性能问题,一般从3个方面分析:操作系统(内存、CPU、I/O)、
数据库实例(数据库架构、INI参数)、SQL(性能监控视图、SQL日志)。
达梦采用基于代价的优化器(CBO),SQL 的执行效率取决于三件事:优化器拿到了什么信息(统计信息、参数)、可选路径有哪些(索引、连接方式)、最终选了哪条路径(执行计划)。
因此调优不是单点动作,而是一个“定位 → 取计划 → 分析 → 干预 → 验证 → 固化”的闭环。
3.1 操作系统
操作系统资源已打满,先解决资源与容量问题,再谈 SQL,常用工具:top、vmstat、iostat、nmon、sar等。
3.2 数据库实例
数据库架构是否最优,参数是否根据服务器资源进行过优化,实例是否存在等待事件,如阻塞、死锁等。
3.3 SQL优化
一般先看高并发SQL,通过创建索引(普通、聚集、覆盖)将单条SQL耗时调整到最短,一般慢SQL,可以通过统计信息收集、索引、参数优化、HINT、SQL改写等降低耗时。
3.4 慢SQL定位
通常可以通过客户提供、系统视图、SQLLOG日志、DEM监控平台获取等手段。
3.4.1 实时慢 SQL 检索
|
-- 已执行超过 2 秒的活动 SQL SELECT S.SESS_ID, S.SQL_TEXT, (SYSDATE - S.LAST_RECV_TIME) * 86400 AS Y_EXETIME, S.CLNT_IP FROM V$SESSIONS S WHERE S.STATE = 'ACTIVE' AND (SYSDATE - S.LAST_RECV_TIME) * 86400 >= 2 ORDER BY (SYSDATE - S.LAST_RECV_TIME) * 86400 DESC;
|
3.4.2 SQL 日志开启
|
-- 开启 SQL 日志(系统级,需谨慎,生产环境短时开启) SP_SET_PARA_VALUE(1, 'SVR_LOG', 1); -- 查看当前值 SELECT PARA_NAME, PARA_VALUE FROM V$DM_INI WHERE PARA_NAME = 'SVR_LOG'; |
3.4.3执行超过1S的历史SQL
|
SELECT SESS_ID, SQL_ID, EXEC_ID, FINISH_TIME, EXEC_TIME, N_RUNS, SQL_TEXT FROM SYS.V$LONG_EXEC_SQLS WHERE EXEC_TIME > 1000 ORDER BY EXEC_TIME DESC;
|
SQL调优的手段
形成闭环:定位(视图/日志/DEM)→ 取计划(EXPLAIN / V$SQL_PLAN / V$CACHEPLN)→ 分析(算子、估算行数、实际行数、回表、逻辑读)→ 干预(统计信息 / 索引 / HINT / 参数 / 改写)→ 验证(执行时间、逻辑读、计划对比)→ 固化(计划绑定 / HINT 注入 / 参数固化)。
4.1 动态视图监控系统性能
4.1.1 查询活动会话数
|
select count(*) from v$sessions where state='ACTIVE';
--按用户、客户端 IP 分组,便于判断连接是否集中 SELECT USER_NAME, CLNT_IP, COUNT(*) AS SESS_CNT FROM V$SESSIONS GROUP BY USER_NAME, CLNT_IP ORDER BY SESS_CNT DESC;
|
4.1.2 锁与阻塞查询
|
--锁查询 SELECT O.NAME, L.* FROM V$LOCK L, SYSOBJECTS O WHERE L.TABLE_ID = O.ID AND L.BLOCKED = 1;
--阻塞查询 WITH A AS ( SELECT SESS_ID AS WAITOR_SESSID, SQL_TEXT AS WAITOR_SQL, ID AS WAITOR, WAIT_FOR_ID, WAIT_TIME FROM SYS."V$SESSIONS" V, V$TRXWAIT T WHERE TRX_ID = ID ), B AS ( SELECT SESS_ID AS WAIT_FOR_SESSID, SQL_TEXT AS WAIT_FOR_SQL, ID AS WAITOR FROM SYS."V$SESSIONS" V, V$TRXWAIT T WHERE TRX_ID = WAIT_FOR_ID ) SELECT A.*, B.WAIT_FOR_SESSID, B.WAIT_FOR_SQL, 'SP_CLOSE_SESSION(' || B.WAIT_FOR_SESSID || ');' AS KILL_SESS_SQL FROM A, B WHERE A.WAITOR = B.WAITOR ORDER BY A.WAIT_FOR_ID;
|
4.1.3 观察阻塞
|
-- 会话 1:更新一行但不提交 UPDATE T1 SET C3 = C3 + 1 WHERE C1 = 1;
-- 会话 2:更新同一行,此时会被阻塞 UPDATE T1 SET C3 = C3 + 1 WHERE C1 = 1;
-- 会话 1:提交或回滚释放锁 COMMIT;
|
4.1.4 等待事件监控
|
-- 实例级等待事件累计,按等待时间降序 SELECT EVENT, TOTAL_WAITS, TIME_WAITED, AVERAGE_WAIT_MICRO, SMAX_TIME FROM V$SYSTEM_EVENT ORDER BY TIME_WAITED DESC;
-- 当前会话等待历史(需 ENABLE_MONITOR) SELECT SESS_ID, EVENT, P1TEXT, P1, P2TEXT, P2, P3TEXT, P3 FROM V$SESSION_WAIT_HISTORY WHERE ROWNUM <= 50;
|
4.2 数据库性能参数调整
4.2.1 内存池与数据缓冲区类
|
参数 |
含义 |
说明 |
|
MEMORY_POOL |
公共内存池(MB),供各类运行期结构申请 |
高并发下调大可减少向 OS 频繁申请;属于静态参数 |
|
N_MEM_POOLS |
公共内存池的分片数 |
分片降低并发申请冲突 |
|
BUFFER |
数据缓冲区初始大小(MB) |
数据量小于可用内存时可贴近数据量;否则约取总内存 2/3 |
|
BUFFER_POOLS |
数据缓冲区划分的分区数 |
并发高时分区可减少热点冲突;注意与 BUFFER 规模匹配 |
|
MAX_BUFFER |
数据缓冲区可扩展到的上限(MB) |
通常设为等于 BUFFER,避免运行期反复扩张 |
|
RECYCLE |
回收/临时使用缓冲(MB) |
大量临时表、WITH、排序场景适当放大 |
|
CACHE_POOL_SIZE |
SQL、执行计划、结果集等缓存(MB) |
一般 1000~4000 区间 |
|
ICT_BUF_SIZE |
数据字典缓存(MB) |
对象数量多时适当加大 |
4.2.2 排序、哈希、聚集工作区类
|
参数 |
含义 |
说明 |
|
SORT_BUF_SIZE |
单次排序缓冲区(MB) |
建索引、大排序时临时调大,平时保持默认 |
|
HJ_BUF_GLOBAL_SIZE |
全实例哈希连接内存总上限(MB) |
多并发 HASH JOIN 时调大,防止整体超限 |
|
HJ_BUF_SIZE |
单个哈希连接可用内存(MB) |
大表 HASH 连接时调大 |
|
HAGR_BUF_GLOBAL_SIZE |
全实例聚集操作内存总上限(MB) |
大量 SUM/COUNT/GROUP BY 并发时调大 |
|
HAGR_BUF_SIZE |
单个聚集操作可用内存(MB) |
大表 HASH 分组时调大 |
4.2.3 监控与优化器行为类
|
参数 |
含义 |
说明 |
|
ENABLE_MONITOR |
是否开启监控 |
当前资料记载取值域为 0~1、默认 1,并非 1/2/3 分级;如需更细级别请以目标实例参数元数据为准 |
|
OLAP_FLAG |
联机分析处理倾向开关 |
纯 OLTP 保持 2,偏 OLAP 可设 1 |
|
OPTIMIZER_MODE |
优化器模式 |
新版本建议使用新优化器模式(1) |
|
WORKER_THREADS |
工作线程数 |
一般取 CPU 核数或 2 倍;该参数取值上限为 64,不是可无限放大 |
4.3 统计信息的收集
4.3.1查看统计信息
|
SELECT TABLE_NAME, NUM_ROWS, BLOCKS, LAST_ANALYZED FROM USER_TABLES WHERE TABLE_NAME = 'T1';
--收集后
--单例统计信息 CALL DBMS_STATS.COLUMN_STATS_SHOW('TATY', 'T1', 'C2');
|
4.3.2 按列收集
|
--不同的采样率收集的采集范围(行数)和耗时会存在区别 -- 对 T1 表的 C2 列按 100% 采样收集 STAT 100 ON TATY.T1(C2); -- 对 T1 表的 C2 列按 30% 采样收集 STAT 30 ON TATY.T1(C2);
|
4.3.3 按模式(表)收集
|
-- 收集单表统计信息(STAT 方式) STAT ON TATY.T1; -- 收集单表统计信息(DBMS_STATS 方式) DBMS_STATS.GATHER_TABLE_STATS('TATY', 'T1', NULL, 100, TRUE, 'FOR ALL COLUMNS SIZE AUTO'); -- 收集整个模式统计信息 DBMS_STATS.GATHER_SCHEMA_STATS('TATY', 100, TRUE, 'FOR ALL COLUMNS SIZE AUTO'); -- 收集索引统计信息(会提交当前事务) DBMS_STATS.GATHER_INDEX_STATS('TATY', 'IDX_T1_C2'); --收集前检查
--收集
--收集后检查
|
4.3.4 清空统计信息
|
-- 删除单表统计信息 DBMS_STATS.DELETE_TABLE_STATS('TATY', 'T1'); -- 删除整个模式的统计信息 DBMS_STATS.DELETE_SCHEMA_STATS('TATY');
-- 删除指定列的统计信息 DBMS_STATS.DELETE_COLUMN_STATS('TATY', 'T1', 'C2');
|
4.4 优化数据访问路径
4.4.1 索引优化测试
|
-- 建测试表 CREATE TABLE T1 ( C1 INT, C2 VARCHAR(10), C3 INT ); -- 插入 10 万行数据 INSERT INTO T1 SELECT LEVEL, CASE WHEN MOD(LEVEL, 10) = 0 THEN 'A' ELSE 'B' END, MOD(LEVEL, 1000) FROM DUAL CONNECT BY LEVEL <= 100000; COMMIT; -- 未建索引:全表扫描 EXPLAIN SELECT C1, C2, C3 FROM T1 WHERE C2 = 'A';
-- 建单列索引 CREATE INDEX IDX_T1_C2 ON T1(C2); DBMS_STATS.GATHER_TABLE_STATS('TATY', 'T1', NULL, 100, TRUE, 'FOR ALL COLUMNS SIZE AUTO'); -- 再次查看计划:应出现 SSEK + BLKUP(索引定位后回表) EXPLAIN SELECT C1, C2, C3 FROM T1 WHERE C2 = 'A';
-- 扩展为覆盖索引:查询列全部包含在索引中,消除回表 CREATE INDEX IDX_T1_C2_C1_C3 ON T1(C2, C1, C3); DBMS_STATS.GATHER_TABLE_STATS('TATY', 'T1', NULL, 100, TRUE, 'FOR ALL COLUMNS SIZE AUTO'); -- 再次查看计划:应变为 SSEK 直接扫描索引,不再出现 BLKUP EXPLAIN SELECT C1, C2, C3 FROM T1 WHERE C2 = 'A';
|
4.4.2 索引失效的常见场景
|
-- 场景 1:列上做运算,索引失效 EXPLAIN SELECT * FROM T1 WHERE C1 + 1 = 100; -- 可能全表扫描 EXPLAIN SELECT * FROM T1 WHERE C1 = 99; -- 可走索引 --表达式或函数会使索引失效
-- 场景 2:隐式类型转换,索引失效(C2 是字符列) EXPLAIN SELECT * FROM T1 WHERE C2 = 1; -- 可能全表扫描 EXPLAIN SELECT * FROM T1 WHERE C2 = '1'; -- 可走索引 --这里有覆盖索引,计划1走了索引全扫描,发生了隐式转换
-- 场景 3:前导模糊匹配,索引失效 EXPLAIN SELECT * FROM T1 WHERE C2 LIKE '%A'; -- 通常全表扫描 EXPLAIN SELECT * FROM T1 WHERE C2 LIKE 'A%'; -- 可走索引
|
4.4.3 表连接路径
1.嵌套循环连接(NEST LOOP)
- 有驱动表与被驱动表之分;
- 驱动表返回多少行,被驱动表就要被探测多少次;
- 被驱动表连接列有索引时效率高;无索引时会退化为反复全表扫描,计划很差。
|
--建测试表T2 CREATE TABLE T2 ( C1 INT ); INSERT INTO T2 SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 10000; COMMIT; CREATE INDEX IDX_T2_C1 ON T2 (C1); --无索引的差计划 EXPLAIN SELECT /*+ USE_NL(T1, T2) */ T1.C1, T2.C1 FROM T1 INNER JOIN T2 ON T1.C1 = T2.C1 WHERE T1.C2 = 'A';
--优化:建索引并收集统计信息 CREATE INDEX IDX_T1_C2 ON T1(C2); CREATE INDEX IDX_T2_C1 ON T2(C1); DBMS_STATS.GATHER_INDEX_STATS('TATY', 'IDX_T1_C2'); DBMS_STATS.GATHER_INDEX_STATS('TATY', 'IDX_T2_C1'); -- 再次查看计划:被驱动表应走索引探测 EXPLAIN SELECT /*+ USE_NL(T1, T2) */ T1.C1, T2.C1 FROM T1 INNER JOIN T2 ON T1.C1 = T2.C1 WHERE T1.C2 = 'A';
|
2.哈希连接(HASH JOIN)
- 一般没有索引或用不上索引时使用;
- 只适用于等值连接;
- 选择较小的 row source 构建哈希表,另一个 row source 做哈希探测;
- 比较消耗内存,并发高时需关注 HJ_BUF_GLOBAL_SIZE、HJ_BUF_SIZE、HJ_BLK_SIZE。
|
--连接列无索引 EXPLAIN SELECT /*+ USE_HASH(T1, T2) */ T1.C1, T2.C1 FROM T1 INNER JOIN T2 ON T1.C1 = T2.C1 WHERE T1.C2 = 'A';
--链接列有索引 CREATE INDEX IDX_T1_C1 ON T1(C1); CREATE INDEX IDX_T2_C1 ON T2(C1); DBMS_STATS.GATHER_INDEX_STATS('TATY', 'IDX_T1_C1'); DBMS_STATS.GATHER_INDEX_STATS('TATY', 'IDX_T2_C1'); EXPLAIN SELECT /*+ USE_HASH(T1, T2) */ T1.C1, T2.C1 FROM T1 INNER JOIN T2 ON T1.C1 = T2.C1 WHERE T1.C2 = 'A';
--查看当前哈希连接内存参数 SELECT PARA_NAME, PARA_VALUE FROM V$DM_INI WHERE PARA_NAME IN ('HJ_BUF_GLOBAL_SIZE', 'HJ_BUF_SIZE', 'HJ_BLK_SIZE'); |
3.合并排序连接(MERGE SORT JOIN)
特点:
- 无驱动表之分,随机读少;
- 两个表都需要按连接列排序,消耗大量 CPU 与额外内存;
- 若存在相关索引可消除排序,CBO 才可能考虑该方式。
|
--无索引 EXPLAIN SELECT /*+ USE_MERGE(T1, T2) */ T1.C1, T2.C1 FROM T1 INNER JOIN T2 ON T1.C1 = T2.C1 WHERE T1.C2 = 'A';
--有索引 CREATE INDEX IDX_T1_C1 ON T1(C1); CREATE INDEX IDX_T2_C1 ON T2(C1); DBMS_STATS.GATHER_INDEX_STATS('TATY', 'IDX_T1_C1'); DBMS_STATS.GATHER_INDEX_STATS('TATY', 'IDX_T2_C1'); EXPLAIN SELECT /*+ USE_MERGE(T1, T2) */ T1.C1, T2.C1 FROM T1 INNER JOIN T2 ON T1.C1 = T2.C1 WHERE T1.C2 = 'A';
|
4.5 使用 SQL 提示(HINT)
4.5.1 基本语法
HINT 写在 SQL 注释中,紧随 SELECT、UPDATE、DELETE 关键字之后:
|
SELECT /*+ HINT */ ...; UPDATE /*+ HINT */ ...; DELETE /*+ HINT */ ...; |
4.5.2 查看支持 HINT 的参数清单
|
SELECT HINT_TYPE, PARA_NAME FROM V$HINT_INI_INFO;
|
4.5.3 用 HINT 干预时机的判断
|
场景 |
建议 |
|
统计信息已准确、索引已合理,优化器仍选错计划 |
用 HINT 纠正 |
|
绑定变量场景计划不稳定 |
用 HINT 固定连接方式,或考虑计划绑定 |
|
子查询内部 HINT 不生效 |
把 HINT 放到外层对应位置 |
|
MERGE HINT 无法改变超限视图计划 |
不要死磕 HINT,转为 SQL 改写或调整参数 |
|
加了 HINT 无效果 |
先用 EXPLAIN 确认 HINT 是否被解析,检查语法与别名 |
4.5.4不修改 SQL 的 HINT 注入
生产上常常不能改应用 SQL,可用 SF_INJECT_HINT 把 HINT 与 SQL 文本绑定,存入 SYSINJECTHINT,业务 SQL 命中规则时自动附加 HINT。
|
-- 开启 HINT 注入(会话级动态参数) SF_SET_SESSION_PARA_VALUE('ENABLE_INJECT_HINT', 1);
-- 注入 HINT:SQL 文本、HINT 文本、规则名、描述、是否校验、是否模糊匹配 CALL SF_INJECT_HINT( 'SELECT C1, C2, C3 FROM T1 WHERE C2 = ''A''', 'INDEX(T1 IDX_T1_C2)', 'HINT_T1_C2', 'force index IDX_T1_C2 for T1 query', TRUE, TRUE );
-- 查看已注入的 HINT SELECT * FROM SYSINJECTHINT;
-- 删除注入 CALL SF_DEINJECT_HINT('HINT_T1_C2');
|
总结
调优先分层:操作系统 → 实例 → SQL,先判断瓶颈归属,避免在错误层面做优化。HINT 是纠偏工具,不是万能药,调优要闭环验证,每次调整后都要用 EXPLAIN、执行时间、逻辑读对比,并考虑计划绑定或 HINT 注入防止回退。参数调整有边界,内存类参数不是越大越好,优先会话级、优先评估并发、保留调整前后基线。
达梦官网:https://eco.dameng.com
达梦技术社区:https://eco.dameng.com/community/






































浙公网安备 33010602011771号