达梦数据库性能优化与故障排查的工作及学习心得

一、性能问题从哪里入手

拿到一个“数据库慢了”的工单，一般先不急着看数据库，而是从操作系统、实例配置、SQL 这三个层面挨个排除。达梦本身的诊断手段比较全，系统资源诊断、动态视图、跟踪日志、AWR 报告这些都能用得上。
操作系统层面先看系统日志，/var/log/messages 或者 /var/log/syslog，排除掉硬件、网络或者系统本身的问题，然后再用几个常用命令：
top 是最常用的，重点看前 5 行的 %Cpus(s)、MiB Mem 和 load average，还有下面各个进程的资源占用。想按内存排序的话按 shift+m。
sar 适合看历史趋势，sar -u 看 CPU，sar -b 看 I/O，sar -d 看块设备，sar -n DEV 看网卡，sar -P ALL 能看到每颗 CPU 的使用率和 IOWAIT，后者对判断 IO 瓶颈很有用。
CPU 高但找不到原因的时候，perf top 可以看到进程里各函数的占用率，能定位到具体是哪个功能模块在消耗 CPU。
数据库这边，把 ENABLE_MONITOR 设为 1 之后，动态视图里能看到不少东西。几个常用的查询：

-- 看当前哪些语句在活跃执行
SELECT COUNT(*), SQL_TEXT FROM V$SESSIONS WHERE STATE='ACTIVE'
GROUP BY SQL_TEXT ORDER BY 1 DESC;

-- 慢SQL，按耗时排前 50
SELECT * FROM V$LONG_EXEC_SQLS
ORDER BY EXEC_TIME DESC, N_RUNS DESC LIMIT 50;

-- 用 MTAB 临时空间最多的 SQL
SELECT * FROM V$MTAB_USED_HISTORY
ORDER BY MTAB_USED_BY_M DESC LIMIT 50;

-- 排序页数最多的 SQL
SELECT * FROM V$SORT_HISTORY ORDER BY N_PAGES DESC LIMIT 50;

-- 事务阻塞和被锁的表
SELECT * FROM V$TRXWAIT;

SELECT O.NAME, L.* FROM V$LOCK L, SYSOBJECTS O
WHERE L.TABLE_ID=O.ID AND L.BLOCKED=1;

如果是批量分析历史慢 SQL，可以打开跟踪日志用 DMLOG 工具跑一遍；有条件的话 DEM 平台做负载分析和实时监控更省事。

二、SQL 优化

1. 先搞清楚优化谁

不是所有慢 SQL 都值得马上优化。一般分两类看：一类执行执行时间长（十几秒到几十秒），但调用频率很低，这种一天也就跑几次，影响不大，往后放；另一类单次只要几百毫秒到几秒，但每秒被调用几十上百次，这种在并发上来的时候效率一旦下降，很容易把整个系统拖垮，是首要优化对象。所以定位慢 SQL 不能只看单次耗时，执行频率同样重要。

2. 执行计划怎么看

执行计划就是一条 SQL 在数据库里的执行过程或者说访问路径，优化器根据代价选路径。
查看方式有三种：

-- 看预估计划
EXPLAIN SELECT * FROM SYSOBJECTS;   

-- disql 里看真实执行计划
SQL> SET AUTOTRACE TRACEONLY;

-- 或者从 SQL 缓冲区里取已执行语句的计划
SELECT SF_TRACE_DUMP_PLN(cache_item值);

每个计划节点有三部分信息：操作符、代价三元组、补充信息。代价三元组是 [代价, 记录行数, 字节数]，比如 CSCN2: [1, 856, 52]，意思就是这个全表扫描节点估算代价 1ms，扫 856 行，输出 52 字节。
看计划的顺序有个口诀，最右最上先执行。缩进越深的越先跑，缩进相同的先上后下。控制流从上往下传，数据流从下往上传。

3. 常见操作符

CSCN2，全表扫描。CLUSTER INDEX SCAN 的缩写，达梦的表默认是索引组织的，全表扫描走的也是聚集索引。没有过滤条件或者没有可用索引的时候就走它，IO 开销大，高并发系统里能避就避。
SSEK2，二级索引范围扫描，典型形态是 SSEK2 上面挂一个 BLKUP2：

3   #BLKUP2: [0,1,156]; IDX1(T)
4   #SSEK2: [0,1,156]; scan_type(ASC), IDX1(T), scan_range[10,10]

先在索引上定位到 rowid，再回表取数据。
CSEK2，聚集索引扫描，只扫索引不用回表。如果发现 BLKUP 的开销占比很大，可以考虑把这个二级索引直接改成聚集索引：

CREATE CLUSTER INDEX IDX1 ON T2(C1);

SSCN，索引全扫描。查询涉及的列全部包含在索引里的时候，直接扫索引就能出结果，不用回表。所以建复合索引的时候，把常查的列都覆盖进去，有时候比单个列索引更划算。

再说 BLKUP，也就是回表，这个开销经常被低估。回表是逐行去取数据的，数据量一大，封锁和取页的代价就上去了。所以大范围过滤加回表的场景，未必比全表扫描划算，必要时可以用 /+ lkup_cpu(999999)/ 把回表代价放大，逼优化器去走分区子表全扫描。

连接操作符方面，NEST LOOP 嵌套循环，驱动表每一行去和被驱动表拼接过滤，驱动表行数就是循环次数，直接决定效率。连接列有没有索引都能走，但没索引的时候效率会很差。MERGE JOIN 要求两边连接列都有索引，按索引顺序归并。HASH JOIN 适用面最广。

其他一些零散的操作符：NSET 是结果集收集，PRJT 是投影算表达式，这两个没什么可优化的。SLCT 是过滤，要重点看实际返回行数和代价三元组里的估算行数差多少，差得多说明统计信息有问题，或者这个过滤条件选择性不错可以建索引。AAGR 是简单聚集，没过滤条件时取 MAX/MIN/COUNT 很快。HAGR 是 HASH 分组，SAGR 是流分组，分组列有序时走 SAGR，性能比 HAGR 好。SORT3 是排序，可以通过在排序字段上建索引消掉。TOPN2 和 RNSK 是 TOP N 和 ROWNUM 截断。
完整的操作符清单可以查 V$SQL_NODE_NAME 这个视图。

4. 用 ET 看真实耗时

EXPLAIN 给的是估算，想知道每个操作符实际跑了多久，用 ET。

SQL> SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1);
SQL> alter session set 'MONITOR_SQL_EXEC'=1;
SQL> SELECT * FROM t1 WHERE c2 LIKE 'XX%';
SQL> ET(1234);   -- 1234 是执行号

输出里有 OP（操作符）、TIME（时间开销）、PERCENT（占总时间的百分比）、RANK（耗时排序）。比如某条 SQL 里 SORT3 占了 59.13% 的时间，那优化目标就很明确了，把排序干掉。

5. 统计信息

达梦是 CBO 优化器，统计信息就是它算代价的依据。举个最直接的例子，嵌套循环要选小表当驱动表，哪个是“小表”完全取决于统计信息里的行数。统计信息不准，后面所有优化都白搭。

统计信息生成分三步：确定采样对象、确定采样率、生成。列和索引的采样会生成直方图，不同值少于 1 万个时用频率直方图，每桶高度不同；超过 1 万个用等高直方图，每桶高度一样，默认桶界限是 300。
这里有两个点，一是大表 distinct 值很多的时候，等高直方图会因为左边界问题导致统计不准，可以用 STAT 100 SIZE 桶数 ON tablename(colname) 把桶数放大，最多 10000，降低边界带来的偏差。二是分区表收集不准，有可能踩到 HP_STAT_SAMPLE_COUNT = 50 这个参数的坑，子表数量超过 50 之后是按比例估算的，可以在当前会话把这个参数调大再收集。

常用的收集方式：
SP_CREATE_SYSTEM_PACKAGES(1);
DBMS_STATS.GATHER_TABLE_STATS('TEST','T1',NULL,100,FALSE,'FOR ALL COLUMNS SIZE AUTO');
DBMS_STATS.GATHER_SCHEMA_STATS('TEST',100,FALSE,'FOR ALL COLUMNS SIZE AUTO');
DBMS_STATS.GATHER_INDEX_STATS('OA_TEST','INX_OA_TEST_IP');
STAT 100 ON table_name(column_name);
DBMS_STATS.UPDATE_ALL_STATS();

自动收集也支持，全表数据量变化超过阈值后自动更新：

SP_SET_PARA_VALUE(1,'AUTO_STAT_OBJ',2)

控制监控范围，1 是所有表，2 是只监控配置过的表。要提醒一句，收集统计信息对性能是有影响的，一定避开业务高峰。
已有统计信息可以用查看。

dbms_stats.table_stats_show、index_stats_show、COLUMN_STATS_SHOW

索引用久了会碎片化，查询性能下降，需要定期重建。普通 REBUILD 会阻塞基表 DML，REBUILD ONLINE 用的是异步创建逻辑，不影响基表操作，生产环境尽量用 ONLINE 的方式。

6. 一些常用的改法

建索引消全表扫描是最常见的。比如 SELECT * FROM t1 WHERE c2 LIKE 'XX%'，ET 分析发现全表扫描占了大头，给 C2 建索引再更新统计信息，SP_COL_STAT_INIT_EX('SYSDBA','T1','C2',100)，就好了。
改写方面，GROUP BY 的条件能放 WHERE 就别放 HAVING：

-- 原来这样
SELECT JOB,AVG(AGE) FROM TEMP
GROUP BY JOB HAVING JOB = 'STUDENT' OR JOB = 'MANAGER';

-- 改成这样，先过滤再分组
SELECT JOB,AVG(AGE) FROM TEMP
WHERE JOB = 'STUDENT' OR JOB = 'MANAGER' GROUP BY JOB;

业务允许重复的话，UNION 换 UNION ALL，省掉一次结果集排序。
前通配的 LIKE 是个老大难，LIKE '%D5' 默认只能全表扫描逐行匹配。有个取巧的办法是建 REVERSE 函数索引，把前通配变成后通配：

CREATE INDEX indf_t1_res_msg ON t1(REVERSE(id_msg));
SP_SET_PARA_VALUE(1,'STR_LIKE_IGNORE_MATCH_END_SPACE',0);
SELECT * FROM t1 WHERE id_msg LIKE '%D5';

同样一条查询，计划从全表扫描变成了 SSEK 范围扫描 scan_range['5D','5E')。
还有一个不太好排查的场景：应用里执行慢，管理工具里跑同一句话却很快。这种情况先在活动会话里找执行时间超过 1 秒的 SQL，然后到 v$cachepln 里找 cache_item

--查看缓存中的执行计划
SELECT *--t.cache_item
  FROM v$cachepln t
 WHERE t.sqlstr LIKE '%emplyee%';
 
 select SF_TRACE_DUMP_PLN(140203471979928);
 alter session set events 'immediate trace name plndump level 8097783064, dump_file ''1.trc'''; #注意后缀名要用.trc

对比缓存里的执行计划和管理工具里的是不是一回事。偏差大的话，call sp_clear_plan_cache() 清一下计划缓存。

7. HINT 干预

统计信息收集了、索引也建了，SQL 还是慢，就要考虑直接干预优化器了。

--索引提示(有别名时写别名）
 SELECT
       /*+ INDEX(a INX_ZH)*/
       a.employee_id,
       a.employee_name
  FROM dmhr.employee a
 WHERE a.employee_id = 1001;

--连接方法提示
通过指定两个表间的连接方法来检测不同连接方式的查询效率，指定的连接可能由于无法实现或代价过高而被忽略。
SELECT
       /*+ USE_NL(a, b) */
       a.EMPLOYEE_NAME,
       b.DEPARTMENT_NAME
  from dmhr.EMPLOYEE   a,
       dmhr.DEPARTMENT b
 where a.DEPARTMENT_ID = b.DEPARTMENT_ID;

生产系统上不想改应用代码的，可以用 SF_INJECT_HINT 在数据库端把 HINT 绑到 SQL 上。前提是 ENABLE_INJECT_HINT=1。

SF_INJECT_HINT(SQL_TEXT, HINT_TEXT, NAME, DESCRIPTION, VALIDATE)

8. 三个容易想错的地方

第一，索引消除排序不是万能的。排序字段建索引后计划确实从 CSCN2 加 SORT3 变成了 SSCN 加 BLKUP2，排序没了，但有几个前提：索引顺序要和排序列一致，DESC 排序时索引列也得是降序的；有些情况下全表扫描加排序反而比走索引便宜，要看实际数据量；另外索引连接下按驱动表的有序列排序，输出天然有序，而 hash 连接的结果排序是按被驱动表也就是右表的索引有序输出的。

第二，隐式转换废索引。phone 是字符列，WHERE phone = 12300000001 传了个数字进去，phone 被隐式转成数值，索引精确匹配失效，计划退化成 SSCN 加 SLCT 加 BLKUP，等于把整个索引遍历一遍再回表。改成 phone BETWEEN '12300000001' AND '12300000010' 传字符串范围，就恢复成 SSEK 定位加 BLKUP 了。多表关联时同理，关联列类型不一致也会隐式转换，关联效率很差。

第三，前面说的前通配 LIKE，REVERSE 函数索引加参数调整这个组合，注意要验证生效，想临时关掉优化器对 like 的特殊处理可以用 /+ LIKE_OPT_FLAG(0)/ 对比测试。

三、参数调整

INI 参数有三种改法，SP_SET_PARA_VALUE、ALTER SYSTEM SET，或者直接改 dm.ini 文件。
几个值得关注的：
BUFFER，系统缓冲区，数据量小于物理内存就设成数据量大小，否则设为总内存的三分之二。
BUFFER_POOLS 是缓冲区分区数，并发大的系统要配，RECYCLE 缓冲区在高并发、大量 with 子句、临时表、排序的场景可以调大。
WORKER_THREADS 建议设为 CPU 核数或者两倍。
ENABLE_MONITOR 平时跑的时候设 2，专门做性能诊断的时候设 3。SORT_BUF_SIZE 建索引的时候可以适当调大，一般不超过 20M。
调完可以用 V$BUFFERPOOL 验证效果：free 很多说明缓冲区闲置可以调低；free 是 0 或者 N_DISCARD64 不为零，说明缓冲区太小频繁淘汰，得调大。
另外可以用 AutoParaAdj 参数自动优化脚本，会根据机器内存和 CPU 核数自动调整 CPU、内存池、缓冲区、HASH、排序这些参数，先跑脚本再手工微调，比自己从零调靠谱。

四、故障排查

操作要谨慎，备份永远是第一位的。紧急问题的处理顺序是先恢复服务，再定位根因。判断故障类型、收集运行日志、CORE 文件、进程信息这些材料要及时。把每次异常当成提升自身经验的机会。
案例
OOM。系统日志出现 Out of memory: Kill process (dmserver)，数据库日志里有 mem_malloc_ex2 out of memory。处理思路：先核对 dm.ini 里内存相关参数和物理内存是否匹配，BUFFER 和共享内存池可以适当调小；看 OOM 发生前一段时间的运行日志；翻 SQL 日志看有没有大内存 SQL 在并发跑。另外建议提前做一步：echo "-1000" > /proc/<进程ID>/oom_score_adj，降低 dmserver 被 oom killer 选中杀掉的概率。

内存使用率过高告警。共享池超过 target 水位后反复拓展，频繁和操作系统交互产生内存碎片，而 MEMORY_TARGET 这些参数相对 48G 的总内存又偏小。解决办法是调大 target 相关参数，同时给 dmserver 启动脚本加上环境变量 MALLOC_ARENA_MAX=4。主备库都要改，重启后用 grep MALLOC_ARENA_MAX /proc/$pid/environ 确认生效。

索引损坏宕机。运行日志报 INDEX [XXX] is corrupt!，数据库直接宕了，只能对相关表做重建。

pwrite error。检查数据文件和归档文件所在磁盘能不能正常读写，最简单的办法是在那个目录下建个新文件试试，再看 df -h 有没有剩余空间。

备库 split。恢复步骤：主库全库备份，关掉备机的守护进程和实例，把备份传到备机，dmrman 依次执行 CHECK BACKUPSET、RESTORE、RECOVER、UPDATE DB_MAGIC，然后启动备机服务和守护进程，最后在监视器里确认集群状态恢复正常。

五、总结

1.用 V$LONG_EXEC_SQLS 或者 SQL 日志把高频、高耗时的 SQL 找出来，频率优先于单次耗时；
2.EXPLAIN 看估算计划，AUTOTRACE 或者 SF_TRACE_DUMP_PLN 看真实计划；
3.对比 SLCT 节点的实际行数和估算行数，偏差大就更新统计信息，考虑加桶数、调采样率或者改 HP_STAT_SAMPLE_COUNT；
4.ET 看每个操作符的真实耗时占比；
5.动手改：建索引（包括聚集索引、函数索引）、改写语句、消排序消回表；
6.还不行就 HINT、SF_INJECT_HINT、PLAN_OP_FLAG 上去干预，倾斜场景配合 ENHANCE_BIND_PEEKING；
7.日常维护：碎片化索引用 REBUILD ONLINE 重建，统计信息低谷期收集，定期核对准确性；
8.参数结合业务和硬件调，AutoParaAdj 调整参数；故障处理永远是先备份再操作，紧急的先恢复服务。
最后发现，SQL 优化这件事说白了就是在缩小优化器的估算和数据真实分布之间的差距。统计信息管住估算，索引和 HINT 管住访问路径，ET 和动态视图负责验证效果。把这个闭环跑顺了，大部分性能问题都能系统性地解决掉，而不是每次都靠碰运气。