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/

posted @ 2026-11-12 01:04  那个人后来  阅读(13)  评论(0)    收藏  举报