1前言

随着国产化替代战略的深入推进，达梦数据库作为我国自主研发的关系型数据库管理系统，在政府、金融、能源、电信等关键行业得到广泛应用。数据库性能是衡量其能否承载真实业务系统的关键指标，而在影响数据库性能的诸多因素中，SQL的执行效率往往是最直接、最普遍的瓶颈来源，因为一条低效的SQL在高并发下可能拖垮整个系统。
为进一步掌握达梦数据库SQL优化的方法与规律，本文围绕"SQL优化"主题，系统介绍达梦数据库慢SQL的定位、分析与优化方法，内容包括：慢SQL定位（动态视图与SQL日志两种路径）、执行计划分析与ET诊断（explain、SET AUTOTRACE、ET）、统计信息与计划缓存（统计信息对执行计划的影响、计划缓存机制、优化器跟踪概念），以及hint优化与INJECT注入（强制指定执行方式与不改应用代码的注入手段）。

2环境准备

2.1环境信息说明

2.2数据准备

创建一张无索引的大表并灌入数据
1、建表（此处我们先故意不建索引，为了后面慢SQL的定位制造条件）
CREATE TABLE T_BIG (ID INT, NAME VARCHAR(50), VAL INT);

2、分4批、每批250万插入1000万行
INSERT INTO T_BIG SELECT LEVEL, 'NAME'||LEVEL, MOD(LEVEL,100000) FROM DUAL CONNECT BY LEVEL <= 2500000;
INSERT INTO T_BIG SELECT 2500000+LEVEL, 'NAME'||(2500000+LEVEL), MOD(2500000+LEVEL,100000) FROM DUAL CONNECT BY LEVEL <= 2500000;
INSERT INTO T_BIG SELECT 5000000+LEVEL, 'NAME'||(5000000+LEVEL), MOD(5000000+LEVEL,100000) FROM DUAL CONNECT BY LEVEL <= 2500000;
INSERT INTO T_BIG SELECT 7500000+LEVEL, 'NAME'||(7500000+LEVEL), MOD(7500000+LEVEL,100000) FROM DUAL CONNECT BY LEVEL <= 2500000;
COMMIT;

3、验证数据量
SELECT COUNT(*) FROM T_BIG;

3慢SQL定位

3.1介绍

什么是慢SQL：
慢SQL是指执行时间明显超过正常预期、或消耗资源异常的SQL语句。分为两类：一种是单次很慢，一条SQL跑几秒甚至几分钟（如全表扫描千万行），仅仅这一条语句就拖垮业务；另一种是单条不慢但是频率很高的语句，它被大量会话高频反复执行，累积把CPU/IO占满，导致整体变慢。

3.2慢SQL的定位

3.2.1定位慢SQL的前提

我们不是从一开始的系统性能慢就直接推断出是慢SQL导致的，而是从不同层面的数据和指标逐一排查的。
一般而言我们先看系统资源层面，执行top命令后，发现CPU的占用率很高，推断出系统负载偏高；紧接着进行CPU的细化分析、进行内存检查，排除掉CPU/内存/IO等资源层面的瓶颈等等，进而确定消耗CPU的元凶在数据库内部，才进入数据库排查；后面才会涉及到慢SQL定位的操作。

3.2.2慢SQL定位的方法

3.2.2.1动态视图

动态视图，即查询数据库的动态性能视图，直接看"此时此刻"正在执行的会话和SQL。数据库把每个会话的状态、正在跑的SQL、等待情况实时记录在这些视图里，查询它们就能看到现场。
用到的核心对象：

动态视图定位通过查询V$SESSIONS等动态性能视图，实时查看当前正在执行的会话与SQL，具有实时、结构化、开销小三大优点，问题发生时能一眼看到现场，查询结果也可以直接使用，还能与V$LOCK/V$TRX联动排查锁阻塞，且仅查询视图对系统性能影响极小。但是它的本质是"当前状态快照"，只能抓"正在跑的"SQL，SQL语句一旦执行结束就会从视图中消失，因此需要持续监控或者把握好时机才行，否则一闪而过时就会很容易错过，也没办法回溯。
因此，该方法适用于问题正在发生时的现场定位、实时监控，以及配合会话、锁排查判断瓶颈究竟是慢SQL还是锁阻塞。
现在我们有表T_BIG，在它的基础上进行动态视图的操作：

1、多终端并发执行慢SQL（即在新的两个终端上执行这条SQL）：
SELECT * FROM T_BIG ORDER BY NAME DESC;

结果如下图，一直在输出，我们就在主终端上进行下述查询操作

2、查询活动会话数：
select (select count() from v$sessions where state='ACTIVE' and sess_id != sessid()) act_ses,
(select count() from v$sessions) tot_ses;

3、查询已执行超过2秒的活动SQL：
select * from (SELECT sess_id, sql_text, state, datediff(ss,last_recv_time,sysdate) Y_EXETIME,
to_char(SF_GET_SESSION_SQL(SESS_ID)) fullsql, clnt_ip
FROM V$SESSIONS WHERE STATE='ACTIVE') where Y_EXETIME>=2;

4、聚合定位（即按SQL分组，统计会话数与最久耗时）：
select sql_text, count(*) 会话数, max(datediff(ss,last_recv_time,sysdate)) 最久秒数
from v$sessions where state='ACTIVE' group by sql_text;

根据以上执行的动态视图，我们可以看到：聚合查询显示慢SQL为“SELECT * FROM T_BIG ORDER BY NAME DESC;”，被2个会话并发执行，最久已运行16秒（再次查询涨到26秒，第三次消失，这也更加验证了动态视图只能抓正在执行的会话这一缺点，SQL一旦结束即消失）。

3.2.2.2SQL日志

开启SVR_LOG后，达梦把所有执行的SQL写进日志文件，永久留存，因此，事后可以通过翻日志还原当时跑了什么SQL、哪条SQL慢。
SQL日志定位通过开启SVR_LOG，将数据库执行的所有SQL写入日志文件留存，具备事后可查、全量记录、永久留存。这样即使问题发生时不在场或没来得及查，日志里也会保存操作和记录，随时可复盘，不受"抓现场"的时效限制。
但是必须要提前开启日志，否则没有记录，同时日志写入也会有性能与磁盘开销；此外，日志为文本形式，不如视图结构化，需借助grep正则或DMLOG工具进行分析。
该方法适用于问题发生后的复盘、周期性慢SQL分析、审计，以及无法实时盯现场的场景。
类似的，和动态视图一样，在表T_BIG的基础上进行SQL日志的操作：
1、查看sqllog.ini配置：
cat /opt/data/DAMENG/sqllog.ini
参数说明如下：


2、开启SQL日志（以下参数是动态参数，即时生效）：
SP_SET_PARA_VALUE(1,'SVR_LOG',1);

3、执行一次慢SQL，使其被日志记录；
SELECT * FROM (SELECT A., ROWNUM RN FROM (SELECT * FROM T_BIG ORDER BY ID DESC) A WHERE ROWNUM <= 5) WHERE RN > 0;

4、关闭日志（定位完即关，防止日志膨胀）：
SP_SET_PARA_VALUE(1,'SVR_LOG',0);

5、查找日志文件（该日志命名dmsql_实例名_日期时间.log）：
ls -lt /opt/data/log/ 2>/dev/null | head -5
ls -lt /opt/dmdbms/log/ 2>/dev/null | head -5
find / -name "dmsql_" 2>/dev/null | head -5

6、查看日志内容
cat /opt/dmdbms/log/dmsql_实例名_XXXXXX.log

以上内容中，我们可以看到：
EXECTIME: 7075(ms)，即表示执行耗时7.075秒，这就是慢SQL的证明
ROWCOUNT: 5(rows)，即表示扫描了千万行数据却只返回了5行，说明效率不高
这就是"返回少、扫得多"的典型慢SQL特征。
7、用正则从日志中捞慢SQL（执行时间>1秒）：
生产日志会非常大，cat命令会把全部内容展现在屏幕上，人眼可能一时半会看不完；而grep正则命令就可以帮助人进行筛选操作，同时还可以配合其他命令进行统计、排序。
grep -E "[1-9][0-9][0-9][0-9](ms)" /opt/dmdbms/log/dmsql_实例名_XXXXX.log

命令解释：-E表示启用扩展正则表达式，即让[0-9]、(这类元字符生效，如果不加-E，某些元字符会被当普通字符；正则[1-9][0-9][0-9][0-9]\(ms\)中的[]表示N位数的不同位置上的数
四个字符拼起来[1-9][0-9][0-9][0-9] = 匹配4位数字且第一位是1~9 ，就代表着数值范围1000~9999，转换为执行时间则表示大于1000毫秒，即1秒。

通过以上结果，我们可以看到日志中记录的分页SQL执行耗时7075毫秒、仅返回5行，这是典型的慢SQL特征。

4SQL分析基础方法

4.1概念介绍

4.1.1执行计划

什么是执行计划：也就是SQL语句在数据库中的执行过程或者说访问路径的描述，由查询优化器基于统计信息，根据代价选择出它认为的最小代价的方式，再交给执行器执行。执行计划的每一行是一个操作符节点，三元组[代价,记录行数,字节数]描述节点估算开销；并且缩进越深则代表着越先执行，当有相同缩进深度的行数时，按照先上后下的顺序。

4.1.2查看执行计划的方式

1、explain：只生成计划但并不执行，输出优化器"预计"的计划（它是估算，并不是真实值）；
2、DM管理工具按钮或者F9：在管理工具中选中SQL语句，点击工具栏按钮或按F9后可以查看执行计划（本质上是explain的图形化呈现）；
3、SET AUTOTRACE TRACEONLY：真实执行，输出实际计划与执行统计；
4、ET（全称Execute Trace）：是达梦自带SQL性能分析工具，统计语句执行过程中每个操作符的实际开销，为优化提供依据。有两种查看方式，在命令行中用CALL ET(执行号)查看，在管理工具中通过点击执行号来查看图形化结果。

4.2explain查看预计计划

在SQL语句前加EXPLAIN关键字，优化器只做解析与计划生成，并不实际执行，输出它"预计"的执行计划。
（1）命令行查看:
EXPLAIN SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM T_BIG ORDER BY ID DESC) A WHERE ROWNUM <= 5) WHERE RN > 0;

（2）DM管理工具中点击执行计划的按钮或者F9进行查看

以上图片中标红处就是执行计划的按钮。
对以上输出的执行计划进行分析，我们可以看到以下两行：
7 #SORT3: [1908, 5, 68]; key_num(1), top_flag(1)
即进行了排序，并且排序好后只取前5行数据
8 #CSCN2: [1188, 10000000, 68]; INDEX33555629(T_BIG)
意思是进行了全表扫描，并且估算扫描了1000万行
逐节点分析：
CSCN2：即全表扫描，由于ORDER BY ID DESC需要全量数据排序，但排序列无可用索引，只能全表扫描，且估算需要全表扫描10000000行；
SORT3：对扫描结果全排序，即把CSCN2全表扫描出的估算1000万行按ID排好序后再取前5行。
补充：这里的三元组依次是估算代价、估算行数、页数，估算代价的从下往上值是累计的。SORT3的1908已包含其子节点CSCN2全表扫描的1188，因此不能把两个操作符的代价直接比大小来断定谁更耗时。同时，估算代价只是优化器基于统计信息选路的依据，如果想要判断某个操作符的真实开销，需要通过ET查看实际执行时各操作符的耗时与占比。（后续会介绍ET）

因为是越深越先执行，结合起来的意思也就是为了最终只取5行数据，数据库需要先把整张表从头到尾扫描一遍，再对这1000万行做全排序，因为排序列ID上没有索引，无法利用索引的天然有序性直接定位，只能全表扫描加全量排序，最后才从排序结果中取出前5行。扫描量与结果量严重不匹配，绝大部分IO与排序开销都花在了"最终只要5行"上。

为什么会这么慢：
1、计划中出现CSCN2，说明优化器选择了全表扫描路径，要么是无可用索引，要么是有索引但是选择进行全表扫描时代价更小，需要进一步分析；
（1）数据分布分析：
dbms_stats.COLUMN_STATS_SHOW('SYSDBA','T_BIG','ID')
通过列统计信息查询，得到ID列NUM_DISTINCT=10000000（意思是唯一列，选择性=1，每个值仅对应1行）；因为ID为唯一列，所以不存在数据倾斜。（这里统计信息具体操作后续进行详细分析）

也就是说如果存在可用的ID索引，那么走索引的话只需要回表1行，这样代价就远优于全表扫描1000万行，因此优化器根本没有理由不走索引而进行全表扫描操作；
（2）索引事实核查：通过ALL_IND_COLUMNS查询确认，表T_BIG上没有ID二级索引

所以合理推测，没有走索引是因为没有可用索引。
（3）计划中出现SORT3：说明当前访问路径产生的数据无序，但是如果排序列有索引并且选择了索引操作，那么这一步完全可以省去
综上所述，该SQL低效的原因是缺失索引导致的全表扫描和全排序，于是，接下来我们就可以为排序列建立索引。（建立索引及其对执行计划的实际影响，将在后续'统计信息与计划缓存'章节中结合统计信息实验进行验证。）

现在回到EXPLAIN命令：
explain只进行计划生成而不实际执行SQL，所以执行速度快，并且因为不会触碰数据，所以该命令没有风险，适合快速查看计划形态及建索引前后的计划对比；
但是它输出的是优化器基于统计信息估算的"预计"计划，由于估算的准确性依赖统计信息的准确性，如果统计信息过期，那么就会导致估算失真，并且explain无法给出真实执行时间。
所以，SQL的实际性能需通过真实执行（如SET AUTOTRACE）或ET进一步验证。
4.3SET AUTOTRACE真实执行与统计
SET AUTOTRACE会真实执行SQL，后面可以跟TRACE、TRACEONLY等选项，下面分别对比TRACE和TRACEONLY使用场景的区别。
1、TRACEONLY选项输出实际执行计划与执行统计（例如逻辑读、物理读、IO等待、执行时间、排序等），当监控开启后，执行计划三元组中的"行数"列会变成"估算->实际"的格式，能反映SQL的真实执行代价，尤其physical reads这一指标可以直接体现"是否大量读盘"，弥补了explain看不到真实执行时间的不足。
SQL> SET AUTOTRACE TRACEONLY
SQL> SELECT * FROM (SELECT A., ROWNUM RN FROM (SELECT * FROM T_BIG ORDER BY ID DESC) A WHERE ROWNUM <= 5) WHERE RN > 0;

根据上图运行结果，我们可以看到，开启AUTOTRACE后不仅会输出实际执行计划，还会连同Statistics执行统计也一起展示，关键项如下：
physical reads: 13464，即物理读13464页，这意味着数据量超出BUFFER，大部分数据页不在内存里，所以全表扫描时只能一块块从磁盘读
io wait time: 2274ms，说明其中约2.27秒在等待磁盘IO
exec time: 6063ms，总执行约6秒
补充：如果logical reads和rows processed不成比例，则说明扫描不高效；如果sorts(disk)>0则说明排序区的内存不够，还用到了物理磁盘。
explain的"代价1908"只是估算数字，而autotrace给出以上真实代价，可以证明SQL实际慢在全表扫描和进行了大量物理读上，这是explain无法直接体现的。
2、TRACE选项同样会真实执行SQL并输出执行计划与执行统计，与TRACEONLY不同的是，它会把查询结果集也一并显示出来：
SQL> SET AUTOTRACE TRACE
SQL> SELECT * FROM (SELECT A., ROWNUM RN FROM (SELECT * FROM T_BIG ORDER BY ID DESC) A WHERE ROWNUM <= 5) WHERE RN > 0;

根据上图运行结果，我们可以看到开启TRACE后首先输出查询结果集，之后就和TRACEONLY输出的内容基本一致，说明两者执行方式相同、统计方式一致，唯一的区别就在于是否展示查询结果集。
TRACEONLY与TRACE的适用场景
由以上内容可知，TRACE会连同查询结果集一起显示，输出内容较多，适合结果集较小、需要同时查看查询结果与执行统计的场景；而TRACEONLY则不显示查询结果集，只显示执行计划与执行统计，输出的内容较少，适合结果集很大并且只关心执行统计的场景，可以避免结果集刷屏。
日常调优一般使用TRACEONLY即可。

4.4 ET定位操作符级瓶颈

ET是达梦自带的SQL性能分析工具，用于统计语句执行过程中每个操作符的实际开销，它精确到操作符级别，为优化提供可靠依据。
在使用它之前需要先开启监控（这一点在上面autotrace中有提到）：
打开监控总开关：
SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1);
打开操作符级监控：
SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC',1);

注意：MONITOR_SQL_EXEC是会话级参数，生效前提是ENABLE_MONITOR打开。生产环境调优时，应仅对当前会话开启操作符级监控，而不是用SP_SET_PARA_VALUE全局开启，因为全局开启会对实例中所有会话产生监控开销，影响整体业务性能。同时，在调优结束后，可以用SP_RESET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC'')将当前会话参数恢复为系统默认值，或者直接断开当前会话使其自动失效。
这些是动态参数，开启后立即生效，之后再执行SQL命令，数据库就会记录每次执行的操作符级明细，包括每个节点实际花多久、处理多少行、进入几次等信息，这些明细存在内存视图里，按执行号回查就是ET的结果。
我们可以用执行号查看ET结果，可以在命令行通过CALL命令查看，也可以在DM管理工具中直接点击按钮或F9后图形化查看。
再执行一次慢SQL：
SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM T_BIG ORDER BY ID DESC) A WHERE ROWNUM <= 5) WHERE RN > 0;

（1）命令行查看执行号：
CALL ET(1132);

以上结果中每行包括OP（操作符）、TIME(us)（耗时微秒）、PERCENT（占比）、RANK（排名）、N_ENTER（进入次数）。具体拿本次实验的结果进行分析：
CSCN2 3593608us 55.56% RANK=1，即全表扫描花了3.59秒，占56%，等级第一，是整个执行计划中的最大瓶颈
SORT3 2873666us 44.43% RANK=2，说明排序花了2.87秒，占44%，等级第二。
光这两步加起来就占了99.99%，因此后续优化就知道可以从这里下手。
（2）用DM管理工具查看：
由于此处的图是后续修改数据后再补的，所以执行号、数据有些不同，但重点还是展示一下DM管理工具中用ET查看执行计划的方法。

直接点击上图中的执行号

ET基于已执行的执行号查询，是三者中粒度最细的分析手段，能精确到操作符级的实际开销，瓶颈也一目了然。但开启监控（ENABLE_MONITOR、MONITOR_SQL_EXEC）会对数据库整体性能造成一定影响，因此优化工作结束后应及时关闭监控。

4.5三种方式对比总结

三者由粗到细、由"预计"到"实际"：
explain 看"打算怎么跑"，只生成计划不执行，给出优化器估算的访问路径，适合快速看计划形态与建索引前后对比，但看不到真实耗时；
SET AUTOTRACE 看"实际跑得怎么样"，真实执行后给出总体统计，直观反映真实代价，但无法定位时间花在哪个操作符；
ET 看"慢在哪一步"，基于已执行的操作符级明细，精确到每个操作符的实际耗时与占比，直接指明瓶颈与优化方向，但需先开启监控且有一定性能开销。
我们先用 explain 快速看计划形态，再用 autotrace 确认真实代价，最后用 ET 精确定位瓶颈操作符，三者配合使用可完整定位SQL瓶颈。

5统计信息与计划缓存

5.1统计信息

什么是统计信息：统计信息描述数据库表与索引的数据分布特征，是优化器计算代价、选择执行计划的依据。统计信息准确是生成最优执行计划的必要前提，如果实际数据已经发生改变，而统计信息却没有更新，就会导致执行计划不准，走成错误的路线，代价也会增大。
1、收集统计信息：
DBMS_STATS.GATHER_TABLE_STATS('SYSDBA','T_BIG',NULL,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');

2、查看统计信息：
表统计：dbms_stats.table_stats_show('SYSDBA','T_BIG');

ID列统计：dbms_stats.COLUMN_STATS_SHOW('SYSDBA','T_BIG','ID');
VAL列统计：dbms_stats.COLUMN_STATS_SHOW('SYSDBA','T_BIG','VAL');

通过上图中的统计信息，我们可以看到：
表账本中记录着NUM_ROWS=10000000、LEAF_BLOCKS=13472，这和前面autotrace得到的记录中的physical reads=13464很接近，说明全表扫描读取的正是这些数据块；
VAL列有10万个不同值，平均每个值对应约100行，如果走索引定位单个值只需回表100行，占全表的0.001%，远优于全表扫描1000万行，故该列同样值得建索引。
ID列账本记录着NUM_DISTINCT=10000000，每个ID值恰好对应1行，选择性为1，对比VAL列的每个值对应100行、走索引要回表100次，ID列建索引定位效果会更好。
同时这些信息也验证了explain估算的值正是从这份账本中读取的，再次印证：只有统计信息准确，估算值才会准，统计信息的准确性直接决定优化器估算与计划的可靠性。

5.2核心实验：统计信息过期会"欺骗"优化器

5.1末尾说到，统计信息的准确性直接决定优化器估算与计划的可靠性。当数据发生变化而统计信息未更新时，优化器会依据旧账本生成错误计划；所以我们需要重新收集统计信息，让其基于新账本生成新的执行计划（但真实执行是否立即生效，见后文（6）的验证）。
以下是该想法的验证：
（1）为ID列建立索引并收集索引统计信息
CREATE INDEX IDX_T_BIG_ID ON T_BIG(ID);
DBMS_STATS.GATHER_INDEX_STATS('SYSDBA','IDX_T_BIG_ID');

新建索引后，重新explain查看执行计划：
EXPLAIN SELECT * FROM T_BIG WHERE ID > 9999999;

无索引时，因为CSCN2全表扫1000万行和SORT3全排序的存在，时间开销为6秒；有索引时，SSEK2估算3行，时间毫秒级。
（2）插入500万行新数据，并且故意不重新收集统计信息
此处操作是为了看当数据更新后，执行计划是否会发生改变，还是说会利用旧的统计信息保持不变：
INSERT INTO T_BIG SELECT 10000000+LEVEL, 'NAME'||(10000000+LEVEL), MOD(10000000+LEVEL,100000) FROM DUAL CONNECT BY LEVEL <= 5000000;
COMMIT;

如何判断统计信息与数据分布是否一致：查看统计信息只是"读账本"，要判断账本准不准，还需与实际数据对比。通过SELECT COUNT(*), COUNT(DISTINCT 列名)查询实际行数与不同值个数，与TABLE_STATS_SHOW、COLUMN_STATS_SHOW中的NUM_ROWS、NUM_DISTINCT对比。一致则统计信息准确、计划可信；不一致则说明数据变更后统计信息未更新，已过期。以本实验为例，插入500万行后，T_BIG实际行数已变为1500万，而此前收集的统计信息NUM_ROWS仍为1000万，二者不一致，即可判断统计信息已过期，此时优化器仍按旧账本生成计划。以下内容就是说明这一点。
（3）验证：explain同一条SQL
EXPLAIN SELECT * FROM T_BIG WHERE ID > 9999999;

通过上图内容，很明显即使是新插入了500万行后，但explain仍显示走索引；这很明显是优化器拿着旧的统计信息，选了索引，而实际上走索引需回表500万次，比全表扫描更慢，应该走全表。
（4）重新收集统计信息
DBMS_STATS.GATHER_TABLE_STATS('SYSDBA','T_BIG',NULL,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');

（5）再explain同一条SQL
EXPLAIN SELECT * FROM T_BIG WHERE ID > 9999999;

更新统计信息后，我们可以看到优化器的执行计划发生了变化，它判定出走全表扫描的代价反而更小，进而修改成了走全表。
注意：以上第（3）和第（5）步都是通过EXPLAIN验证的。但EXPLAIN是现场重新生成计划、不经过计划缓存，它依赖的是数据库对象的元数据与统计信息，因此统计信息一更新，EXPLAIN现场生成的计划立即变化。
但真实执行时SQL走的是计划缓存，两者机制不同，所以EXPLAIN的结果只能代表"重新生成计划"的情况，不代表真实执行立即如此。

该小节验证了：优化器只依据统计信息做决策，数据变化后如果没有更新统计信息，优化器就会按照旧的信息生成错误计划；重新收集统计信息后，EXPLAIN现场生成的计划会立即修正。
但真实执行时，业务SQL是否立即改用新计划，取决于缓存中的旧计划是否失效，这由收集统计信息时的NO_INVALIDATE参数控制：
NO_INVALIDATE = TRUE（默认）：收集后不失效缓存计划，业务SQL继续沿用旧计划，需手动清理缓存计划才能生效；
NO_INVALIDATE = FALSE：收集完成后自动使依赖该表统计信息的缓存执行计划失效，下次真实执行时重新生成计划，新统计信息立即生效。
因此，统计信息收集后不一定立即走到最佳计划，这是因为缓存计划未失效。所以当数据量大幅变化后，要记得重新收集统计信息，并显式确认NO_INVALIDATE为FALSE或手动清理缓存计划，否则SQL可能因为走了错误计划而突然变慢。下面用实验验证NO_INVALIDATE的作用。
（6）验证：NO_INVALIDATE对缓存计划失效的影响

1）真实执行目标SQL，使其计划进入缓存，并通过V$CACHEPLN确认缓存计划存在：

SELECT * FROM T_BIG WHERE ID > 9999999;

SELECT CACHE_ITEM, SQLSTR FROM V$CACHEPLN WHERE SQLSTR LIKE '%ID > 9999999%';

不指定NO_INVALIDATE参数（默认TRUE）重新收集统计信息，再查缓存计划：DBMS_STATS.GATHER_TABLE_STATS('SYSDBA','T_BIG',NULL,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');

SELECT CACHE_ITEM, SQLSTR FROM V$CACHEPLN WHERE SQLSTR LIKE '%ID > 9999999%';

3）显式指定NO_INVALIDATE=FALSE重新收集统计信息，再查缓存计划：
DBMS_STATS.GATHER_TABLE_STATS('SYSDBA','T_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 > 9999999%';

可以看到目标SQL的缓存计划已被自动失效，说明显式指定NO_INVALIDATE=FALSE后自动作废了旧计划。
结论：对比第2）步与第3）步，不指定NO_INVALIDATE（默认TRUE）收集统计信息后，目标SQL的缓存计划仍然保留，真实执行会继续沿用旧计划；显式设置NO_INVALIDATE=FALSE后，缓存计划被移除，真实执行才会重新生成计划。
由此说明：统计信息收集后，真实执行是否立即走最佳计划，由NO_INVALIDATE决定，也说明统计信息更新 ≠ 计划立即修正。

5.3计划缓存

5.3.1计划缓存的定义

什么是缓存计划：一般而言一条SQL语句的运行会经历很多流程，包括数据库解析、生成执行计划、执行的过程，其中解析和生成执行计划这两个过程需要消耗CPU和内存。如果每一条SQL语句执行都需要这一套完整流程，带来的开销会很大。因此，为了提升效率，数据库会把执行过的SQL文本连同其生成好的执行计划一起缓存在内存中，当相同文本的SQL再次执行时，就可以直接复用缓存中的计划，从而节省开销、提升执行效率。
当然，与explain生成的计划不同，缓存计划是曾经真正被执行过的计划，并不是估算出来的一条执行路线。

5.3.2查看计划缓存

SELECT * FROM V$SCP_CACHE;
SELECT SUM(ITEM_SIZE)/1024/1024 缓存池大小_M FROM V$CACHEITEM;

我们可以看到，当前缓存了21个执行计划、56条SQL语句；SQL计划缓存池占用36MB，这就是执行过的SQL计划存内存。
计划缓存可以让相同SQL重复执行时直接使用已经记录的计划，省去重复解析生成计划的CPU开销；但缓存的存在也会占用内存空间，并且由于缓存中的计划是之前执行后存的计划，当数据发生大幅改变且统计信息也更新后，旧的缓存计划可能就不再是最优计划，代价也可能不是最小。

5.3.3计划缓存与传参执行

优化器选择执行计划的依据是通过估算当前场景大概会命中多少行来决定，而当前场景可能有两种情况。一种是直接拼值执行，这种可以让优化器在解析阶段直接看到具体字面量，根据直方图和各种信息计算可以精确估算出命中行数，从而判断应该走索引还是全表；另一种则是传参执行，因为优化器在解析阶段看不到具体值，它的选择要么是直接使用第一次传的值来估算生成计划要么用统计信息的平均值来估算，无论是哪一种都是靠猜得出来的。
同时，由于计划在首次解析时会按首次传入的值或平均值被确定并缓存，所以后续即使传入了新的值时，执行计划也不会随着当前值变化；而不同会话的首次值可能不同，每个会话都是根据自己这边首次收到的值进行缓存，这个时候就会出现传不同的值时，计划也会不同的现象。

5.3.4获取缓存中的执行计划

在5.3.2中，我们通过V$SCP_CACHE等视图看到计划缓存中"有多少"执行计划；但排查生产问题时，我们往往还需要知道"某条SQL在缓存中实际保存的执行计划到底长什么样"。生产环境分析"explain看着没问题但实际SQL却慢"这类问题时，需要查看缓存中实际保存的计划。
查看执行计划相关信息有以下两种常用手段（前提：INI参数USE_PLN_POOL非0，默认即开启计划重用）；此外，生产排查时还需要获取SQL当前时刻在缓存中实际保存的计划，见（3）。下面先介绍两种常用的辅助查看手段，再介绍获取当前缓存计划的方法。

（1）视图方式：通过V$PLN_HISTORY查看历史执行计划
V$PLN_HISTORY是"执行计划历史"视图，显示近期执行过的SQL语句及其对应的执行计划。先执行一次目标SQL使其进入缓存：
SELECT * FROM T_BIG WHERE ID = 8888888;

然后查询V$PLN_HISTORY，取出该SQL在缓存中的执行计划：
SELECT SQL_ID, TOP_SQL_TEXT, SQL_PLAN FROM V$PLN_HISTORY WHERE TOP_SQL_TEXT LIKE '%8888888%';

其中SQL_PLAN字段是该SQL近期执行的计划文本，与explain输出格式相同。需要说明的是，V$PLN_HISTORY保存的是"近期执行过"的计划，是历史记录，并不是当前时刻缓存中的计划。
本次查询命中两条记录：查询V$PLN_HISTORY的SQL本身和目标SQL。
查询V$PLN_HISTORY这句话本身也被缓存记录了（因为它的文本里也含"8888888"，匹配了LIKE），这也正好说明任何执行过的SQL都会被记进缓存，查缓存的SQL自己也不例外。
（2）函数方式：通过V$SQL_HISTORY获取执行号，再用ET函数查看操作级明细
V$SQL_HISTORY是"SQL执行历史"视图，记录每次执行的会话、执行号、耗时等流水信息，通过CALL ET(执行号)查看某次执行中每个操作符的实际开销。它回答的是"某次执行慢在哪一步"，同样不是缓存中的计划文本。

SELECT SESS_ID, EXEC_ID, TOP_SQL_TEXT, TIME_USED FROM V$SQL_HISTORY WHERE TOP_SQL_TEXT LIKE '%8888888%';

结果中EXEC_ID就是执行号，我们可以通过执行号，用ET函数查看该次执行每个操作符的实际开销。例如说，这里的两条结果，501就是目标SQL的执行号：
CALL ET(501);

我们顺带来看一下查询缓存的SQL的开销：
CALL ET(502);

我们观察结果可以看到，SLCT2过滤操作耗时占比高达94.72%，因为LIKE '%8888888%'无法使用索引，需要逐行过滤缓存中的计划记录，这也说明"查看缓存的SQL"本身也会有开销。
（3）获取当前时刻缓存中的执行计划
以上两种方式一个是历史计划、一个是执行明细，都不是SQL"当前时刻在计划缓存中"实际保存的计划。
真正获取当前缓存计划，需要先在计划缓存视图V$CACHEPLN中定位目标SQL。
获取方式与数据库版本相关：8.1.4.170之前的版本没有系统函数，需通过plndump事件将缓存计划导出到trace文件查看；8.1.4.170之后的版本可直接使用系统函数SF_TRACE_DUMP_PLN打印缓存计划。因此先确认本机数据库版本：

SELECT * FROM V$VERSION;

SELECT BUILD_VERSION FROM V$INSTANCE;

本实验环境返回"DM Database Server 64 V8"，构建号03134284552-20260414-322369-20221，属于8.1.4.170之后的版本，可直接使用系统函数。
先真实执行目标SQL使其计划进入缓存：
SELECT * FROM T_BIG WHERE ID = 8888888;

再在V$CACHEPLN中定位（其中CACHE_ITEM即该计划在缓存中的地址）：

SELECT CACHE_ITEM, SQLSTR FROM V$CACHEPLN WHERE SQLSTR LIKE '%8888888%';

再通过系统函数获取该缓存计划：
SELECT SF_TRACE_DUMP_PLN(140614241385880) FROM DUAL;

由图可知，返回结果包含三部分：缓存条目元信息、PLN_CMD计划指令序列，以及sqlnode可读计划树。

对于8.1.4.170之前的版本，无该函数，可通过plndump事件将缓存计划输出到trace文件后查看（本实验也一并验证该方式）：
ALTER SESSION SET EVENTS 'immediate trace name plndump, level 140614241385880, dump_file ''plndump_test.trc''';

执行成功后，在数据文件目录的trace文件夹下生成plndump_test.trc文件。
cat /opt/data/DAMENG/trace/plndump_test.trc

打开文件可以看到与函数方式一致的缓存计划内容，两种方式互为印证。需要说明的是，dump_file指定绝对路径（如/home/dmdba/xxx.trc）时会报"存在父目录引用"错误，使用纯文件名即可正常生成。
另外，SVR_LOG日志也支持缓存计划的打印。通过V$PARAMETER查询，本实验环境SVR_LOG默认关闭，按需开启后即可记录SQL及其执行计划：
SELECT * FROM V$PARAMETER WHERE NAME='SVR_LOG';

打开SQL日志：SP_SET_PARA_VALUE(1,'SVR_LOG',1);
在日志中打印执行计划：
SP_SET_PARA_VALUE(1,'SVR_LOG_PLN_STR',1);
执行目标SQL：
SELECT * FROM T_BIG WHERE ID = 8888888;

以上命令执行后，会在DM安装目录log子目录下生成dmsql_实例名_日期_时间.log文件，查看日志：
ls -lt /opt/dmdbms/log/dmsql_*.log 2>/dev/null | head
tail -80 /opt/dmdbms/log/dmsql_DMSERVER_XXXXXX.log

由上图内容可知，日志中记录了目标SQL及其执行计划，本次打印的计划与函数方式、plndump方式完全一致，三种方式互为印证。
但不同的是，sqllog属于被动式记录，适合生产环境事后排查；排查完毕应关闭SVR_LOG避免持续写日志。
小结：V$PLN_HISTORY回答"历史执行计划长什么样"，V$SQL_HISTORY配合ET回答"某次执行慢在哪一步"，V$CACHEPLN配合SF_TRACE_DUMP_PLN或plndump回答"当前缓存中实际保存的计划是什么"。
排查慢SQL时，通常先通过V$CACHEPLN确认当前缓存中的计划，再用ET定位瓶颈操作符，V$PLN_HISTORY作为历史计划回顾使用，三者各有定位、互为补充。

5.410053事件说明

5.4.1概念

什么是10053事件：10053是数据库的优化器跟踪事件，相当于是数据库优化器的内心独白。开启后会打印优化器选择执行计划的完整内部过程，帮助理解"优化器为什么选这个计划"。
当一条SQL很慢，通过explain看计划时发现明明应该走索引，但实际上却选择了走全表，很疑惑为什么优化器会这样决定时 ，我们就可以把10053打开，通过查看它的思考流程，就能找到它考虑出问题的地方。

5.4.210053的使用

（1）开启方式
SQL> ALTER SESSION SET EVENTS '10053 trace name context forever';
（2）执行待分析SQL
SQL> SELECT * FROM T_BIG WHERE ID = 8888888;

（3）关掉10053，避免一直生成trace
SQL> ALTER SESSION SET EVENTS '10053 trace name context off';

（4）在TRACE_PATH目录下寻找、查看trace文件
ls -lt /opt/dmdbms/log/ | head -8
find / -name "*.trc" -newer /tmp 2>/dev/null | head

grep -E "Start trace|Current SQL|estimate match|path [0-9]|best access|BEST PLAN|FULL SEARCH|G search" /opt/data/DAMENG/trace/DMSERVER_XXXXX.trc

estimate match rows: 1，即按统计信息估算命中1行
*** path 1: INDEX33555629 (FULL search), cost: 1925.69156，表示全表扫描代价1925
*** path 2: IDX_T_BIG_ID (EQU search), cost: 0.06524，表示索引扫描代价 0.065

best access path: IDX_T_BIG_ID (EQU search)，推断出索引代价远小，所以优化器选则走索引

完整思考过程查看：
cat /opt/data/DAMENG/trace/DMSERVER_XXXXX.trc


注意：
（1）10053仅在生成新计划时触发，因为10053打印的是优化器做决策的过程，而优化器只有在需要生成新计划时才会真正做决策；如果一条SQL执行过且计划已被缓存，那么再次执行时就直接用缓存的计划，不会重新做决策和打印trace。
（2）10053使用后应及时关闭，因为在开启期间，每条第一次执行的SQL都会生成trace文件，这会持续占用磁盘空间，带来一定的性能开销。
（3）该设置为会话级，只影响当前会话。

6Hint使用与INJECT注入

6.1概念介绍

（1）什么是hint：
hint是在SQL里加一行特殊注释/*+ ... */，用于引导优化器按指定方式生成执行计划。种类包括：强制走某个索引、强制用某种连接方式、指定连接顺序、强制先查某某表等。
但需要特别注意：hint是"建议"而非"死命令"，当前提条件不满足或代价差异过大时，优化器可能忽略hint。
因此当统计信息已收集、索引已经建立，但SQL仍然慢时才考虑使用hint，属于特定场景或应急手段，不推荐作为常规优化方法。
（2）什么是inject：
由于hint需要修改SQL的代码，但在生产环境中，应用代码往往不能随便修改，INJECT注入正是为了解决这一问题：在数据库端通过SF_INJECT_HINT将hint与SQL文本绑定，而应用代码不变，数据库在执行该SQL时自动应用绑定的hint，从而影响执行计划，适用于应用无法改动时的应急优化。
不过，注入的hint绑定在SQL文本上会持续生效，如果后续数据量或统计信息发生变化、最优计划改变，那么这条被"锁死"的hint反而会走错误计划；同时，由于应用代码里看不到任何改动，数据库端却存在隐藏的hint绑定，如果运维人员不知道存在注入规则，在排查时会很难找到原因。
因此，INJECT注入仅适用于应用无法改动时的应急优化，问题缓解后仍应从SQL本身或索引层面解决，不推荐作为常规优化方法。

6.2Hint操作示例

（1）准备表TEST
CREATE TABLE TEST AS SELECT ID, VAL FROM T_BIG WHERE ID <= 10000;

（2）查看默认执行计划（此处是优化器自己选择的）
EXPLAIN SELECT COUNT() FROM T_BIG A, TEST B WHERE A.ID = B.ID;

（3）用hint强制哈希连接
EXPLAIN SELECT /+ USE_HASH(A, B) / COUNT() FROM T_BIG A, TEST B WHERE A.ID = B.ID;

对比默认计划与添加hint后的计划可以看出，默认情况下连接操作符为XXX（优化器自选）；添加/+ USE_HASH(A, B) /后，连接操作符变为HASH2 INNER JOIN，即USE_HASH生效，强制优化器采用了哈希连接方式。
在hint正确写法下，hint可以强制优化器采用指定的连接方式；但是当SQL使用了别名时，hint中也要使用相同的别名，否则不会生效，这和前面6.1中说的一致，即当前提条件不满足或代价差异过大时，优化器仍可能忽略hint。
6.3INJECT注入实操
SF_INJECT_HINT是达梦提供的系统函数，用于为SQL注入HINT规则，无需修改应用代码即可强制指定执行计划。
标准调用模板如下：
CALL SF_INJECT_HINT(sql_text, hint_text, name, description, validate, fuzzy, need_clear);
各参数含义如下：

注：参数可省略后两个，即fuzzy缺省时仅支持精准匹配，need_clear缺省时注入后不清空缓存计划。同时，HINT规则一经注入即全局存在，规则名称必须全局唯一；注入后可通过SYSINJECTHINT视图验证规则是否生效，不再需要时可通过SF_DEINJECT_HINT删除规则
（1）开启注入功能
INJECT注入功能由INI参数ENABLE_INJECT_HINT控制，该参数是动态会话级，默认开启。可通过系统函数确认本机当前值：
SELECT SF_GET_PARA_VALUE(2,'ENABLE_INJECT_HINT');

返回结果为1，即该参数默认开启。
SF_SET_SESSION_PARA_VALUE('ENABLE_INJECT_HINT',1);

注：该参数默认开启，且未注入任何hint时，对优化器生成计划没有任何影响；只有通过SF_INJECT_HINT注入规则并命中SQL时才会改变执行计划。因此保持默认开启对生产几乎无影响，无需特意关闭，也不存在"全局开启带来额外开销"的问题。
（2）记录基线执行计划
先真实执行一次目标SQL，使其执行计划进入计划缓存：
SELECT COUNT() FROM T_BIG A, TEST B WHERE B.ID = A.ID;
再用EXPLAIN查看基线计划：
EXPLAIN SELECT COUNT() FROM T_BIG A, TEST B WHERE B.ID = A.ID;

（3）注入hint
使用SF_INJECT_HINT将USE_HASH绑定到该SQL，注册名为TEST_HINT3的规则。本次未指定need_clear参数，因此注入时不会清理该SQL的缓存计划，这正是后续"注入未生效"现象的原因，后续进一步说明。
CALL SF_INJECT_HINT('SELECT COUNT() FROM T_BIG A, TEST B WHERE B.ID = A.ID', 'USE_HASH(A, B)', 'TEST_HINT3', 'inject test', TRUE, TRUE);

（4）SET AUTOTRACE查看执行计划
SET AUTOTRACE TRACEONLY;
SELECT COUNT() FROM T_BIG A, TEST B WHERE B.ID = A.ID;
SET AUTOTRACE OFF;

我们发现，真实执行的实际计划仍然是NEST LOOP INDEX JOIN2，并没有变成哈希连接。因为我们在做基线执行的时候，该SQL的NEST LOOP计划已被缓存，之后相同文本的SQL再次执行时会直接复用缓存中的旧计划，根本不会重新生成计划，注入的hint根本不会被应用。我们可以通过V$PLN_HISTORY再次确认，缓存中保存的仍是NEST LOOP计划：

SELECT TOP_SQL_TEXT, SQL_PLAN FROM V$PLN_HISTORY WHERE TOP_SQL_TEXT LIKE '%B.ID = A.ID%';

（5）看EXPLAIN
EXPLAIN SELECT COUNT(*) FROM T_BIG A, TEST B WHERE B.ID = A.ID;
但是我们通过EXPLAIN来查看执行计划是否被改变时，会惊奇的发现执行计划变成了HASH2 INNER JOIN，看起来"注入立即生效"了，但实际上这里有一个容易误会的地方：EXPLAIN是现场生成计划，每次执行都会重新调用优化器计算一遍，不经过计划缓存，所以它展示的是"重新生成计划"的结果；而真实执行时，优化器会优先复用计划缓存中的旧计划，并不会重新生成。两者机制不同，这也再次印证：EXPLAIN的结果并不能代表业务SQL真实执行时的计划。

（6）清理计划缓存，再次验证
清理该SQL的缓存计划，可以先定位其缓存项CACHE_ITEM，再使用命令定向清理：
SELECT CACHE_ITEM, SQLSTR FROM V$CACHEPLN WHERE SQLSTR LIKE '%B.ID = A.ID%';
以上第一条语句按模糊匹配查找目标SQL，返回其缓存计划在内存中的地址（CACHE_ITEM）以及匹配上的SQL文本，匹配上的SQL用于检查确认是否正确。

CALL SP_CLEAR_PLAN_CACHE(<CACHE_ITEM>);
（<CACHE_ITEM>替换为第1步查到的值）
当然我们还有更加简便的方法，即直接通过字符串拼接一次性生成清理命令：
SELECT cache_item, sqlstr, 'call sp_clear_plan_cache('||cache_item||');'
FROM v$cachepln
WHERE sqlstr LIKE '%目标SQL片段%';
它会在第三列直接将对应的地址拼接好，但后续仍需手动复制粘贴执行清理语句。

补充说明：重启实例虽然也能清空内存中的计划缓存（因为缓存保存在内存中，重启后消失），但重启会中断所有会话、影响全部业务，生产环境不可取。因此这里将"重启清缓存"改为"命令清缓存"：SP_CLEAR_PLAN_CACHE只清理目标SQL的缓存计划，不影响其他会话和业务，生产环境应优先使用。
另外，前面也提到过，新版本的SF_INJECT_HINT支持第7个参数need_clear，注入时直接指定need_clear=2，就可以同时清理相关SQL的缓存计划，无需再手动执行SP_CLEAR_PLAN_CACHE。

本实验为演示"缓存计划未失效时hint不生效"的现象，特意在（3）中使用未指定need_clear的6参数语法；实际生产中若希望注入即生效，直接使用带need_clear=2即可，例如：
CALL SF_INJECT_HINT('SELECT COUNT() FROM T_BIG A, TEST B WHERE B.ID = A.ID', 'USE_HASH(A, B)', 'TEST_HINT3', 'inject test', TRUE, TRUE, 2);
再次用SET AUTOTRACE真实执行：
SET AUTOTRACE TRACEONLY;
SELECT COUNT() FROM T_BIG A, TEST B WHERE B.ID = A.ID;
SET AUTOTRACE OFF;

这个时候我们发现：真实执行的实际计划变为了HASH2 INNER JOIN，也就是缓存清空后，SQL重新生成执行计划，注入的USE_HASH才会真正生效。
这里也印证了两点：注入规则保存在磁盘上的系统表中，重启后不会丢失，清空的只是内存计划缓存；注入后，如果需要让hint立即作用于业务，必须先清理目标SQL的缓存计划，否则业务会继续复用旧计划。