达梦SQL优化：如何定位慢的SQL

如何定位慢的 SQL

定位执行效率低的 SQL 语句是 SQL 优化的第一步。待优化的 SQL 可大致分为两类：

• 一类 SQL 执行时间在十几秒到数十秒之间，但执行频率不高，此类 SQL 对数据库整体性能影响并不大，可以放到最后进行优化。
• 另一类 SQL 单独执行时间可能很快，在几百毫秒到几秒之间，但执行频率非常高，甚至达到每秒上百次，高并发下执行效率降低，很可能导致系统瘫痪，此类 SQL 是优化的首要对象。

本章节将介绍两种定位慢 SQL 的简单方法，可记录下具体 SQL 语句以及对应执行时间，为后续 SQL 优化工作提供基础。

开启跟踪日志记录执行 SQL

跟踪日志文件是一个纯文本文件，以 dmsql_实例名_日期_时间命名， 默认生成在 DM 安装目录的 log 子目录下。

跟踪日志内容包含系统各会话执行的 SQL 语句、参数信息、错误信息、执行时间等。跟踪日志主要用于分析错误和分析性能问题，基于跟踪日志可以对系统运行状态进行分析。

跟踪日志配置方式

根据需要配置数据文件目录下的 sqllog.ini，如下所示：

CopyBUF_TOTAL_SIZE  = 10240  #SQLs Log Buffer Total Size(K)(1024~1024000)
BUF_SIZE        = 1024   #SQLs Log Buffer Size(K)(50~409600)
BUF_KEEP_CNT    = 6      #SQLs Log buffer keeped count(1~100)

[SLOG_ALL]
    FILE_PATH       = ../log
    PART_STOR       = 0
    SWITCH_MODE     = 2 #按文件大小切换
    SWITCH_LIMIT    = 256
    ASYNC_FLUSH     = 1
    FILE_NUM        = 10
    ITEMS           = 0
    SQL_TRACE_MASK  = 2:3:23:24:25  #LOG 记录的语句类型掩码查看下文

    MIN_EXEC_TIME   = 0    
    USER_MODE       = 0
    USERS           =

配置 dm.ini 中 SVR_LOG = 1 启用 sqllog.ini 配置，该参数为动态参数，可通过调用数据库函数直接修改，如下所示：

CALL SP_SET_PARA_VALUE(1,'SVR_LOG',1);

如果对 sqllog.ini 进行了修改，可通过调用以下函数即时生效，无需重启数据库，如下所示：

CALL SP_REFRESH_SVR_LOG_CONFIG();

各配置项详细说明

参数名	缺省值	说明
SQL_TRACE_MASK	1	LOG 记录的语句类型掩码，是一个格式化的字符串，表示一个 32 位整数上哪一位将被置为 1，置为 1 的位则表示该类型的语句要记录，格式为：号:位号:位号。如：3:5:7 表示第 3，第 5，第 7 位上的值被置为 1。每一位的含义见下面说明（2~17 前提是:SQL 标记位 24 也要设置)： 1 全部记录（全部记录并不包含原始语句） 2 全部 DML 类型语句 3 全部 DDL 类型语句 4 UPDATE 类型语句（更新） 5 DELETE 类型语句（删除） 6 INSERT 类型语句（插入） 7 SELECT 类型语句（查询） 8 COMMIT 类型语句（提交） 9 ROLLBACK 类型语句（回滚） 10 CALL 类型语句（过程调用） 11 BACKUP 类型语句（备分） 12 RESTORE 类型语句（恢复） 13 创建对象操作 (CREATE DDL) 14 修改对象操作 (ALTER DDL) 15 删除对象操作 (DROP DDL) 16 授权操作 (GRANT DDL) 17 回收操作 (REVOKE DDL) 22 绑定参数 23 存在错误的语句（语法错误，语义分析错误等） 24 是否需要记录执行语句 25 是否需要打印计划和语句和执行的时间 26 是否需要记录执行语句的时间 27 原始语句（服务器从客户端收到的未加分析的语句） 28 是否记录参数信息，包括参数的序号、数据类型和值 29 是否记录事务相关事件
FILE_NUM	0	总共记录多少个日志文件，当日志文件达到这个设定值以后，再生成新的文件时，会删除最早的那个日志文件，日志文件的命令格式为 dmsql_实例名_日期时间.log。 当这个参数配置成 0 时，只会生成两个日志相互切换着记录。有效值范围（0~1024）。例如，当 FILE_NUM=0，实例名为 PDM 时，根据当时的日期时间，生成的日志名称为： DMSQL_PDM_20180719_163701.LOG， DMSQL_PDM_20180719_163702.LOG
SWITCH_MODE	0	表示 SQL 日志文件切换的模式： 0：不切换 1：按文件中记录数量切换 2：按文件大小切换 3：按时间间隔切换
SWITCH_LIMIT	100000	不同切换模式 SWITCH_MODE 下，意义不同： 按数量切换时，一个日志文件中的 SQL 记录条数达到多少条之后系统会自动将日志切换到另一个文件中。一个日志文件中的 SQL 记录条数达到多少条之后系统会自动将日志切换到另一个文件中。有效值范围（1000-10000000） 按文件大小切换时，一个日志文件达到该大小后，系统自动将日志切换到另一个文件中，单位为 MB。有效值范围（1-2000） 按时间间隔切换时，每个指定的时间间隔，按文件新建时间进行文件切换，单位为分钟。有效值范围（1-30000）
ASYNC_FLUSH	0	是否打开异步 SQL 日志功能。0：表示关闭；1：表示打开
MIN_EXEC_TIME	0	详细模式下，记录的最小语句执行时间，单位为毫秒。执行时间小于该值的语句不记录在日志文件中。有效值范围（0-4294967294）
FILE_PATH	../log	日志文件所在的文件夹路径
BUF_TOTAL_SIZE	10240	SQL 日志 BUFFER 占用空间的上限，单位为 KB，取值范围（1024-1024000）
BUF_SIZE	1024	一块 SQL 日志 BUFFER 的空间大小，单位为 KB，取值范围（50-09600）
BUF_KEEP_CNT	6	系统保留的 SQL 日志缓存的个数， 有效值范围（1-100）
PART_STOR	0	SQL 日志分区存储，表示 SQL 日志进行分区存储的划分条件。0 表示不划分； 1 表示 USER：根据不同用户分布存储
ITEMS	0	配置 SQL 日志记录中的那些列要被记录。该参数是一个格式化的字符串，表示一个记录中的那些项目要被记录，格式为：列号:列号:列号。如：3:5:7 表示第 3，第 5，第 7 列要被记录。0 表示记录所有的列。 1 TIME 执行的时间 2 SEQNO 服务器的站点号 3 SESS 操作的 SESS 地址 4 USER 执行的用户 5 TRXID 事务 ID 6 STMT 语句地址 7 APPNAME 客户端工具 8 IP 客户端 IP 9 STMT_TYPE 语句类型 10 INFO 记录内容 11 RESULT 运行结果，包括运行用时和影响行数（可能没有）
USER_MODE	0	SQL 日志按用户过滤时的过滤模式，取值 0：关闭用户过滤 1：白名单模式，只记录列出的用户操作的 SQL 日志 2：黑名单模式，列出的用户不记录 SQL 日志
USERS	空串	打开 USER_MODE 时指定的用户列表。格式为：用户名：用户名：用户名

根据跟踪日志查找慢 SQL

配置成功后可在 dmsql 指定目录下生成 dmsql 开头的 log 日志文件。日志内容如下所示：

上图中选中记录执行 SQL 语句为：

select * from t1 left join t2 on t1.c1=t2.c1 and t1.c1=999933;

SQL 语句执行时间为 33.815 秒。

可以通过正则表达式在 dmsql 日志文件中查找执行时间超过一定阈值的 SQL 语句。例如：查找执行时间超过 10 秒的 SQL 语句。

[1-9][0-9][0-9][0-9][0-9](ms)

如需进行更为系统全面的分析，可使用 Dmlog 工具对 SQL 进行分类汇总。

分析结果如下所示：

Dmlog 工具下载：Dmlog_DM7_v5.1.jar

使用说明介绍：Dmlog 小工具使用简要.pdf

通过系统视图查看执行慢 SQL

DM 数据库提供系统动态视图，可自动记录执行时间超过设定阈值的 SQL 语句。

SQL 记录配置

当 INI 参数 ENABLE_MONITOR=1、MONITOR_TIME=1 打开时，显示系统最近 1000 条执行时间超过预定值的 SQL 语句。默认预定值为 1000 毫秒。可通过 SP_SET_LONG_TIME 系统函数修改，通过 SF_GET_LONG_TIME 系统函数查看当前值。

--两个参数均为动态参数，可直接调用系统函数进行修改
CALL SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1);
CALL SP_SET_PARA_VALUE(1,'MONITOR_TIME',1);

注意

通过 SP_SET_PARA_VALUE 方式修改的参数值仅对当前会话以及新建会话生效，对其它已建立会话不生效。

查询方式

超过执行时间阈值的 SQL 语句记录在 V$LONG_EXEC_SQLS 系统视图中。

查询该视图获取结果，如下所示：

SELECT * FROM V$LONG_EXEC_SQLS;

各字段详细信息介绍

列名	说明
SESS_ID	会话 ID，会话唯一标识
SQL_ID	语句 ID，语句唯一标识
SQL_TEXT	SQL 文本
EXEC_TIME	执行时间（毫秒）
FINISH_TIME	执行结束时间
N_RUNS	执行次数
SEQNO	编号
TRX_ID	事务号

 更多内容，请访问达梦社区地址：https:eco.dameng.com