慢 SQL 优化分析

慢 SQL 是指执行时间明显超过正常预期、或消耗资源异常的 SQL 语句。单次执行慢的语句可能全表扫描千万行，单次就跑几秒甚至几分钟；单条执行不慢但频率极高的语句，被大量会话反复执行后，累积把 CPU/IO 占满，同样会导致整体变慢。优化工作本质上在回答三个问题：这条 SQL 现在是怎么执行的？慢在哪一步？怎么让它执行得更快？

一、实验环境准备

1.1 创建测试表
在 DM 管理工具中新建查询窗口，执行以下 SQL：
CREATE TABLE TEST_BIG(
ID INT PRIMARY KEY,
NAME VARCHAR(100),
PHONE VARCHAR(20),
CREATE_TIME DATETIME,
STATUS INT,
REMARK VARCHAR(500)
);
表结构覆盖了常见字段类型：主键ID、普通字符串NAME、手机号PHONE、时间CREATE_TIME、状态STATUS和备注REMARK。后续实验中，NAME用于验证无索引全表扫描，PHONE用于验证隐式类型转换，STATUS用于验证低选择性索引，REMARK用于模拟无索引排序慢 SQL。
1.2 插入 100 万行数据
INSERT INTO TEST_BIG
SELECT LEVEL, 'NAME_' || LEVEL, '138' || LPAD(LEVEL, 8, '0'),
SYSDATE - LEVEL, MOD(LEVEL, 5), 'REMARK_' || LEVEL
FROM DUAL CONNECT BY LEVEL <= 1000000;
COMMIT;
数据特征：NAME形如NAME_1至NAME_1000000，选择性为 1；STATUS = MOD(ID, 5)，0~4 均匀分布，每个值约 20 万行，选择性很低；PHONE唯一；REMARK无索引。
1.3 建立索引并收集统计信息
CREATE INDEX IDX_TEST_STATUS ON TEST_BIG(STATUS);
CREATE INDEX IDX_TEST_PHONE ON TEST_BIG(PHONE);
CALL SP_TAB_STAT_INIT('SYSDBA','TEST_BIG');
CALL SP_TAB_INDEX_STAT_INIT('SYSDBA','TEST_BIG');
先建立STATUS和PHONE索引，用于后续对比实验。收集统计信息让优化器掌握数据分布，后续索引优化实验才有准确的代价比较基础。

TEST_BIG 查询结果展示SELECT COUNT(*) FROM TEST_BIG返回 1000000 的结果界面。截图中显示 1,000,000 行，耗时 9.458秒，证实数据量规模。值得注意的是，这条SELECT *查询本身因为没有索引、需要全表扫描加排序，已经是一个典型的慢 SQL，也侧面说明了 100 万行数据下全表扫描的代价。

二、慢 SQL 定位

2.1 动态视图
动态视图用于查看“此时此刻”正在执行的会话和 SQL，具有实时、结构化、开销小的特点，适用于问题正在发生时的现场定位，可与V$LOCK、V$TRX联动排查锁阻塞。局限在于它只是当前状态快照，SQL 一旦执行结束就从视图中消失。
步骤 1：第一个查询窗口执行慢 SQL：
SELECT * FROM TEST_BIG ORDER BY REMARK DESC;
步骤 2：第二个查询窗口查询正在运行的慢 SQL：
select sess_id, last_recv_time,
datediff(ss, last_recv_time, sysdate) exetime,
cast(sf_get_session_sql(sessid)asvarchar) fullsql,
clnt_ip
from v$sessions
where state='ACTIVE'
order by exetime desc;
该查询会列出所有活动会话的SESS_ID、最近接收时间、已执行秒数、完整 SQL 文本和客户端 IP。datediff(ss, last_recv_time, sysdate)计算 SQL 从开始执行到当前的秒数，值越大说明执行越久。
步骤 3：按 SQL 分组聚合：
select sql_text, count(*) 会话数,
max(datediff(ss, last_recv_time, sysdate)) 最久秒数
from v$sessions
where state='ACTIVE'
group by sql_text;
聚合查询能快速找出最耗时的 SQL 文本及其并发会话数。实验中可以看到慢 SQL 为SELECT * FROM TEST_BIG ORDER BY REMARK DESC，被 2 个会话并发执行，最久已运行数十秒。再次查询时耗时继续增长，但第三次查询时该 SQL 可能已消失，验证了动态视图只能抓正在执行的会话这一局限。
步骤 4：由于V$SESSIONS只能抓正在执行的 SQL，慢 SQL 执行结束后即消失，改用V$LONG_EXEC_SQLS事后查询：
SELECT SESS_ID, SQL_TEXT, EXEC_TIME, N_RUNS, FINISH_TIME
FROM V$LONG_EXEC_SQLS
ORDER BY EXEC_TIME DESC;
V$LONG_EXEC_SQLS默认显示最近 1000 条执行时间较长的 SQL 语句，默认阈值为 1000 毫秒。V$SYSTEM_LONG_EXEC_SQLS显示服务器启动以来执行时间最长的 SQL 语句。这两个视图与V$SESSIONS互补：前者用于事后复盘，后者用于实时抓现场。
步骤 5：查询历史慢 SQL：
SELECT TOP 10 t.TOP_SQL_TEXT, t.TIME_USED, t.START_TIME
FROM V$SQL_HISTORY t
ORDER BY TIME_USED DESC;
V$SQL_HISTORY记录数由SQL_HISTORY_CNT参数指定（默认 10000），需开启ENABLE_MONITOR=1（默认开启）。该视图记录每次执行的会话、执行号、耗时等流水信息，适合更长期的历史回顾。

2.2 SQL 日志
SQL 日志通过SVR_LOG参数开启，将所有执行的 SQL 写入日志文件，具备事后可查、全量记录、永久留存的特点，适用于问题发生后的复盘和周期性慢 SQL 分析。局限是必须提前开启，否则没有记录，且日志写入有性能与磁盘开销。
步骤 1：开启 SQL 日志：
SP_SET_PARA_VALUE(1, 'SVR_LOG', 1);
步骤 2：执行目标 SQL 后关闭日志：
SP_SET_PARA_VALUE(1, 'SVR_LOG', 0);
步骤 3：在实例安装目录下的log目录里面，找到dmsql_DMSERVER_日期时间.log文件，用记事本打开，搜索TEST_BIG，查看EXECTIME和ROWCOUNT字段。

说明：SQL 日志记录了所有执行的 SQL 及其执行耗时和返回行数，可用于事后复盘。日志中EXECTIME和ROWCOUNT是定位慢 SQL 的关键字段。如果EXECTIME很大而ROWCOUNT很小，说明扫描效率低，是典型的“返回少、扫得多”慢 SQL 特征。

三、执行计划分析

3.1 EXPLAIN 查看全表扫描执行计划
执行计划是 SQL 在数据库中的访问路径描述，由查询优化器基于统计信息选择代价最小的方案。执行计划每一行是一个操作符节点，格式为[代价, 估算行数, 字节数]。缩进越深越先执行，相同缩进时先上后下。
在管理工具中输入以下 SQL，选中后按 F9 或点击工具栏“执行计划”按钮：
EXPLAIN SELECT * FROM TEST_BIG WHERE NAME = 'NAME_888888';
执行计划显示：
第 4 层：CSCN2（聚集索引全扫描），代价 150，估算行数 1000000
第 3 层：SLCT2（过滤），过滤条件NAME = 'NAME_888888'
第 2 层：PRJT2（投影）
第 1 层：NSET2（结果集收集）
CSCN2表示全表扫描，由于NAME列无索引，优化器只能选择全表扫描路径。SLCT2对扫描出的 100 万行逐行过滤，但估算过滤后仍返回 25000 行（实际只有 1 行），说明估算存在偏差。PRJT2负责输出所需列，NSET2是结果集收集节点。
常用操作符说明：
操作符 含义
CSCN2 聚集索引全扫描（全表扫描）
SSEK2 二级索引范围扫描
BLKUP2 根据二级索引 ROWID 回表读取其他列
SLCT2 过滤
SORT3 排序
NEST LOOP INDEX JOIN2 嵌套循环索引连接
HASH2 INNER JOIN 哈希连接
阅读计划时，从缩进最深的节点开始，先确认访问了哪些表，再看是否使用索引，再看过滤条件的位置，最后看表连接方式。若计划中出现CSCN2，说明优化器选择了全表扫描路径，要么是无可用索引，要么是有索引但全表扫描代价更小。

全表扫描执行计划截图中展示了EXPLAIN SELECT * FROM TEST_BIG WHERE NAME = 'NAME_888888'的图形化执行计划结果。全表扫描CSCN2估算扫描 1000000 行，代价为 150，证实无索引情况下优化器只能选择全表扫描路径。
3.2 ET 操作符级瓶颈定位
ET 是达梦自带的 SQL 性能分析工具，统计语句执行过程中每个操作符的实际开销，精确到操作符级别。使用前需开启会话级监控：
SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC', 1);
MONITOR_SQL_EXEC为动态参数（会话级），设置后仅对当前会话生效。生产环境调优时，应仅对当前会话开启操作符级监控，而不是全局开启，因为全局开启会对实例中所有会话产生监控开销。
执行目标 SQL：
SELECT * FROM TEST_BIG ORDER BY REMARK DESC LIMIT 100;
执行后在管理工具底部“消息”窗口点击执行号，弹出 ET 结果对话框。ET 输出字段包括OP（操作符）、TIME(US)（耗时微秒）、PERCENT（占比）、RANK（排名）、SEQ（对应执行计划中的行号）、N_ENTER（进入次数）。重点关注TIME(US)和PERCENT，占比高的操作符就是优化重点。

说明：CSCN2全表扫描耗时占比高达 95.66%，是整个执行计划中的最大瓶颈，优化方向明确锁定在减少全表扫描上。
关闭监控：
SP_RESET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC');

四、索引优化

4.1 为 NAME 建立索引
NAME无索引时，EXPLAIN SELECT * FROM TEST_BIG WHERE NAME = 'NAME_888888'走CSCN2全表扫描。建立索引：
CREATE INDEX IDX_TEST_NAME ON TEST_BIG(NAME);
CALL SP_TAB_STAT_INIT('SYSDBA','TEST_BIG');
CALL SP_TAB_INDEX_STAT_INIT('SYSDBA','TEST_BIG');
再次执行相同 SQL，执行计划变为SSEK2 + BLKUP2，代价从 150 降到 1。SSEK2表示二级索引范围扫描，直接在IDX_TEST_NAME中定位NAME_888888；由于SELECT *需要返回索引之外的PHONE、CREATE_TIME、STATUS、REMARK等列，所以还需要BLKUP2回表取完整记录。
高选择性的等值查询列如果经常作为过滤条件，可以考虑建立索引。但SELECT *会带来回表代价，大量回表时优化器可能放弃索引。

建索引后的执行计划截图中展示了建索引后的执行计划，变为SSEK2 + BLKUP2，代价 1，估算 1 行。对比优化前CSCN2代价 150、估算 1000000 行，说明建立IDX_TEST_NAME索引后，执行计划从全表扫描变为二级索引范围扫描加回表，代价从 150 降至 1。
4.2 隐式类型转换导致索引失效
PHONE字段定义为VARCHAR(20)，并且已经存在IDX_TEST_PHONE。如果查询时把手机号写成数字常量，字段与常量类型不一致，优化器需要进行隐式类型转换。
-- 数字常量：索引失效
SELECT * FROM TEST_BIG WHERE PHONE = 19898245646;
-- 字符串常量：索引生效
SELECT * FROM TEST_BIG WHERE PHONE = '19898245646';
第一条 SQL 的 Predicate Information 中出现exp_cast(TEST_BIG.PHONE)，说明数据库需要对PHONE做隐式转换。此时底层使用CSCN2扫描TEST_BIG，原本存在的IDX_TEST_PHONE没有被使用。第二条 SQL 把常量改成字符串后，隐式转换消失，执行计划变为SSEK2 + BLKUP2，直接使用IDX_TEST_PHONE的scan_range定位目标手机号。
这类问题在业务 SQL 中非常常见。SQL 参数类型应尽量与数据库字段类型一致，否则即使已经建好索引，也可能因为对列做函数/转换而无法按索引范围定位。

说明：同样的查询条件，仅因常量类型不同，执行时间相差 80 倍。数字常量触发了隐式类型转换，导致索引失效，走了全表扫描。
4.3 函数索引
普通 B 树索引遇到函数包裹列时会失效：
EXPLAIN SELECT * FROM TEST_BIG WHERE MOD(STATUS, 2) = 0;
走CSCN全表扫描。可为函数表达式建索引：
CREATE INDEX IDX_TEST_STATUS_MOD ON TEST_BIG(MOD(STATUS, 2));
函数索引对函数有要求，必须使用DETERMINISTIC属性声明的确定性函数，非确定性函数无法创建函数索引。
4.4 复合索引与最左前缀
CREATE INDEX IDX_TEST_STATUS_PHONE ON TEST_BIG(STATUS, PHONE);
生效场景：
SELECT * FROM TEST_BIG WHERE STATUS = 3;
SELECT * FROM TEST_BIG WHERE STATUS = 3 AND PHONE = '13800000003';
失效场景：
SELECT * FROM TEST_BIG WHERE PHONE = '13800000003';
跳过最左列STATUS，索引失效。SQL 条件书写顺序不影响，优化器会自动调整，由索引定义顺序决定最左前缀。范围条件会截断后面字段的索引有序性。

五、统计信息

5.1 统计信息过期实验
统计信息描述数据库表与索引的数据分布特征，是 CBO 优化器计算代价、选择执行计划的依据。统计信息过期或缺失会导致估算行数失真，优化器可能选错执行计划。
步骤 1：查看当前预估行数：
EXPLAIN SELECT * FROM TEST_BIG WHERE STATUS = 1;
步骤 2：删除部分数据：
DELETE FROM TEST_BIG WHERE STATUS = 1;
COMMIT;
步骤 3：删除统计信息，模拟过期：
CALL SP_TAB_STAT_DEINIT('SYSDBA','TEST_BIG');
CALL SP_TAB_COL_STAT_DEINIT('SYSDBA','TEST_BIG');
步骤 4：再次执行EXPLAIN，预估行数仍为旧值，说明统计信息已过期。优化器拿着旧的分布数据，错误估算返回行数，进而错误评估索引、全表扫描、表连接的代价，选错执行计划。
步骤 5：重新收集统计信息：
CALL SP_TAB_STAT_INIT('SYSDBA','TEST_BIG');
CALL SP_TAB_INDEX_STAT_INIT('SYSDBA','TEST_BIG');
EXPLAIN SELECT * FROM TEST_BIG WHERE STATUS = 1;
预期结果：计划从CSCN2 + SLCT2变为IDX_TEST_STATUS的SSEK2 + BLKUP2，估算行数大幅下降，说明优化器对STATUS=1的选择性判断明显改善。

5.2 统计信息收集方式
-- DBMS_STATS 包收集（推荐）
DBMS_STATS.GATHER_TABLE_STATS('SYSDBA','TEST_BIG',NULL,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
-- 存储过程收集
CALL SP_TAB_STAT_INIT('SYSDBA', 'TEST_BIG');
CALL SP_TAB_INDEX_STAT_INIT('SYSDBA', 'TEST_BIG');
CALL SP_TAB_COL_STAT_INIT('SYSDBA', 'TEST_BIG');
CALL SP_STAT_ON_TABLE_COLS('SYSDBA', 'TEST_BIG', 10);
-- STAT 语法收集
STAT 30 ON TEST_DB.TEST_BIG(ID);
采样率建议：小于 1GB 的表 100% 采样，1GB~5GB 采样 50%，大于 5GB 采样 30%，采样率不建议低于 30%。统计信息收集会消耗系统资源，应在业务空闲期执行。
查看统计信息：
EXEC DBMS_STATS.TABLE_STATS_SHOW('SYSDBA', 'TEST_BIG');
EXEC DBMS_STATS.COLUMN_STATS_SHOW('SYSDBA', 'TEST_BIG', 'STATUS');

5.3 NO_INVALIDATE 验证
EXPLAIN是现场重新生成计划，统计信息一更新，EXPLAIN可能立即变化。但真实执行走的是计划缓存，业务 SQL 是否立即改用新计划，取决于收集统计信息时的NO_INVALIDATE参数：
NO_INVALIDATE = TRUE（默认）：不失效缓存计划，业务继续用旧计划。
NO_INVALIDATE = FALSE：自动使依赖该表统计信息的缓存计划失效，下次真实执行重新生成。
步骤 1：真实执行目标 SQL 使计划进入缓存：
SELECT * FROM TEST_BIG WHERE ID = 888888;
SELECT CACHE_ITEM, SQLSTR FROM V$CACHEPLN WHERE SQLSTR LIKE '%ID = 888888%';
步骤 2：默认方式（NO_INVALIDATE=TRUE）收集统计信息：
DBMS_STATS.GATHER_TABLE_STATS('SYSDBA','TEST_BIG',NULL,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
SELECT CACHE_ITEM, SQLSTR FROM V$CACHEPLN WHERE SQLSTR LIKE '%ID = 888888%';
预期：缓存计划仍然保留。
步骤 3：显式指定NO_INVALIDATE=FALSE收集：
DBMS_STATS.GATHER_TABLE_STATS('SYSDBA','TEST_BIG',NULL,100,TRUE,'FOR ALL COLUMNS SIZE AUTO',8,NULL,FALSE,NULL,NULL,NULL,FALSE);
SELECT CACHE_ITEM, SQLSTR FROM V$CACHEPLN WHERE SQLSTR LIKE '%ID = 888888%';
预期：缓存计划已被自动失效。结论：数据量大幅变化后，除了重新收集统计信息，还要确认缓存计划是否失效，必要时手动清理。

六、计划缓存

6.1 查看计划缓存
SQL 执行经历解析、生成计划、执行。为省去重复解析和生成计划的开销，数据库把 SQL 文本和执行计划缓存在内存中。缓存计划是曾经真正被执行过的计划，与EXPLAIN生成的估算计划不同。
SELECT * FROM V$SCP_CACHE;
SELECT SUM(ITEM_SIZE)/1024/1024 缓存池大小_M FROM V$CACHEITEM;

说明：当前缓存了 76 个执行计划、108 条 SQL 语句，计划缓存池占用约 8MB。计划缓存可让相同 SQL 重复执行时直接使用已记录的计划，但数据大幅变化后旧计划可能不再最优。
6.2 查看缓存中的执行计划
-- 定位缓存项
SELECT CACHE_ITEM, SQLSTR FROM V$CACHEPLN WHERE SQLSTR LIKE '%888888%';
-- 打印缓存计划
SELECT SF_TRACE_DUMP_PLN(1923505352) FROM DUAL;
V$CACHEPLN用于定位当前缓存中的计划，CACHE_ITEM即该计划在缓存中的地址。SF_TRACE_DUMP_PLN是 8.1.4.170 之后版本提供的系统函数，可直接打印缓存计划。

说明：缓存计划打印结果包含缓存条目元信息、PLN_CMD 计划指令序列和 SQL 文本，可用于确认当前缓存中实际保存的计划内容。
6.3 清理缓存计划
CALL SP_CLEAR_PLAN_CACHE(1923505352);

说明：精准清理指定缓存项的执行结果。SP_CLEAR_PLAN_CACHE不加参数时清理所有 SQL 缓存计划。生产环境应优先使用命令清缓存，而不是重启实例，因为重启会中断所有会话、影响全部业务。

七、Hint 与 INJECT 注入

7.1 Hint 操作示例
Hint 是 SQL 中的特殊注释/+ ... /，用于引导优化器按指定方式生成执行计划，包括强制索引、强制连接方式、指定连接顺序、指定并行度等。Hint 是建议而非强制命令，前提条件不满足或代价差异过大时优化器可能忽略。
步骤 1：准备测试表：
CREATE TABLE TEST AS SELECT ID, STATUS FROM TEST_BIG WHERE ID <= 10000;
步骤 2：查看默认执行计划：
SELECT COUNT() FROM TEST_BIG A, TEST B WHERE A.ID = B.ID;
默认执行计划为NEST LOOP INDEX JOIN2，代价 45。

说明：两表关联查询的默认连接方式为嵌套循环索引连接，优化器根据统计信息自动选择。
步骤 3：使用 Hint 强制哈希连接：
SELECT /+ USE_HASH(A, B) / COUNT() FROM TEST_BIG A, TEST B WHERE A.ID = B.ID;

说明：USE_HASH(A, B)生效，连接方式从嵌套循环变为哈希连接。代价从 45 升至 173，但哈希连接在大表关联场景下通常效率更高。
7.2 INJECT 注入与缓存计划
生产环境应用代码往往不能随便修改，SF_INJECT_HINT可在数据库端将 Hint 与 SQL 文本绑定，应用代码不变，数据库在执行该 SQL 时自动应用绑定的 Hint。
CALL SF_INJECT_HINT(
'SELECT COUNT(*) FROM TEST_BIG A, TEST B WHERE B.ID = A.ID',
'USE_HASH(A, B)',
'TEST_HINT3',
'inject test',
TRUE, TRUE
);
注入后，用EXPLAIN查看计划显示为HASH2 INNER JOIN，但真实执行仍为旧计划，因为缓存计划未失效。清理缓存后，真实执行才变为HASH2 INNER JOIN。

注意事项：注入规则保存在磁盘上，重启不丢失，清空的只是内存计划缓存；注入后若要立即作用于业务，必须先清理目标 SQL 的缓存计划；INJECT 注入适用于应用无法改动时的应急优化，问题缓解后仍应从 SQL 本身或索引层面解决。第 7 个参数need_clear=2表示注入时同时清理相关 SQL 的缓存计划。

八、10053 事件：优化器决策追踪

10053 是优化器跟踪事件，打印优化器选择执行计划的完整内部过程，包括单表访问路径的代价评估、多表连接顺序和连接方法的代价计算，帮助理解“优化器为什么选这个计划”。
步骤 1：查询 TRACE_PATH：
SELECT PARA_NAME, PARA_VALUE FROM V$DM_INI WHERE PARA_NAME LIKE '%TRACE_PATH%';
步骤 2：开启 10053：
ALTER SESSION SET EVENTS '10053 trace name context forever, level 2';
level 1输出单表过滤或多表连接的优化过程（若同时包含则只输出多表连接）；level 2输出单表过滤和多表连接的优化过程，实验推荐用这个。
步骤 3：执行目标 SQL 后关闭：
SELECT * FROM TEST_BIG WHERE ID = 888888;
ALTER SESSION SET EVENTS '10053 trace name context off';
说明：trace 文件中记录了 SQL 语句、优化器参数（如enable hash join=1、use pln pool=1）以及后续的访问路径代价评估，可据此分析优化器为何选择某条路径。
注意：10053 仅在生成新计划时触发，缓存计划复用时不会打印。使用后应及时关闭，避免持续生成 trace 文件。该设置为会话级，只影响当前会话。

九、优化检查清单