（SERIES12）DM性能优化

1 前言

1.1 性能优化的概念

  性能优化是指在不影响系统运行正确性的前提下，使之运行地更快，完成特定功能所需的时间更短。达梦数据库性能优化主要包含以下内容：

• 数据库架构优化
• 数据库参数优化
• SQL 优化
• 统计信息

1.2 工具与术语

  数据库性能优化中可能使用到的相关工具：

• 达梦 SQL 日志分析工具 DMLOG：通过分析数据库的 SQL 日志文件，直观地反映 SQL 执行情况。
• 命令行调试工具 dmdbg：DM 数据库安装目录的“bin”子目录下可找到 dmdbg 执行程序，可调试直接执行的 DMSQL 程序或非 DDL 的 SQL 语句。
• DM 性能监控工具 Monitor：Monitor 是 DM 系统管理员用来监视服务器的活动和性能情况的客户端工具。它允许系统管理员在本机或远程监控服务器的运行状况，并根据系统情况对系统参数进行调整，以提高系统效率。

  数据库性能优化中可能使用到的相关术语：

• 通配符：通配符是一种特殊语句，主要有星号 (*) 和问号 (?)，用来模糊搜索文件。
• 回滚段 (rollback segments)：用于临时存储数据库还原信息。
• 统计信息：对象统计信息描述了对象数据的分布特征。统计信息是优化器的代价计算的依据，可以帮助优化器较精确地估算成本，对执行计划的选择起着至关重要的作用。
• CBO（基于代价的优化器）：它是看语句的代价，这里的代价主要指 cpu 和内存。优化器在判断是否用这种方式时，主要参照的是表及索引的统计信息，统计信息给出表的大小、多少行、每行的长度等信息。
• 执行计划：执行计划是一条 SQL 语句在数据库中的执行过程或访问路径的描述。

2 数据库架构优化

  达梦数据库提供多种数据库架构，用于解决多种场景的数据库安全、性能等问题。在实际业务场景中，根据业务的特性，选择合适的数据库架构：

3 数据库参数优化

3.1 INI参数配置说明

3.2 参数修改方法

  参数分为：静态、动态、手动。如下表所示：

3.2.1 调用系统过程

3.2.1.1 参数查询

1. 查询数值类型参数值：

# 语法格式：
select SF_GET_PARA_VALUE (scope int, paraname varchar(256));
--SCOPE 参数为 1 表示获取 INI 文件中配置参数的值
--SCOPE 参数为 2 表示获取内存中配置参数的值

# 例如：获取 DM.INI 文件中动态参数 HFS_CACHE_SIZE 的当前值
select SF_GET_PARA_VALUE (1,'HFS_CACHE_SIZE');

2. 查询浮点型参数值：

# 语法格式
select SF_GET_PARA_DOUBLE_VALUE(scope int, paraname varchar(8187));
--SCOPE 参数为 1 表示获取 INI 文件中配置参数的值
--SCOPE 参数为 2 表示获取内存中配置参数的值

# 例如：获取内存中 参数SEL_RATE_EQU 的当前值
select SF_GET_PARA_DOUBLE_VALUE(2,'SEL_RATE_EQU');

3. 查询字符串类型参数值：

# 语法格式
select SF_GET_PARA_STRING_VALUE(scope int, paraname varchar(8187));
--SCOPE 参数为 1 表示获取 INI 文件中配置参数的值
--SCOPE 参数为 2 表示获取内存中配置参数的值

# 例如：获取 DM.INI 文件中动态参数 SQL_TRACE_MASK 的当前值
select SF_GET_PARA_STRING_VALUE(1,'SQL_TRACE_MASK');

4. 获得当前会话的某个会话级 INI 参数的值：

# 语法格式
select SF_GET_SESSION_PARA_VALUE (paraname varchar(8187));
# 例如：获取当前会话 USE_HAGR_FLA 参数的值
select SF_GET_SESSION_PARA_VALUE ('USE_HAGR_FLAG');

3.2.1.2 参数修改

1. 修改整型静态配置参数和动态配置参数：

# 语法格式
SP_SET_PARA_VALUE (scope int, paraname varchar(256), value int64);
--SCOPE 参数为 1 表示在内存和 INI 文件中都修改参数值，此时只能修改动态的配置参数。当 SCOPE 等于 1，试图修改静态配置参数时服务器会返回错误信息
--SCOPE 参数为 2 表示只在 INI 文件中修改配置参数，此时可用来修改静态配置参数和动态配置参数

# 例如：将 DM.INI 文件中动态参数 HFS_CACHE_SIZE 设置为 320，在 disql 中执行以下命令即可立即生效
SP_SET_PARA_VALUE (1,'HFS_CACHE_SIZE',320);

2. 修改浮点型静态配置参数和动态配置参数：

# 语法格式
SP_SET_PARA_DOUBLE_VALUE(scope int,paraname varchar(8187),value double);
--SCOPE 参数为 1 表示在内存和 INI 文件中都修改参数值，此时只能修改动态的配置参数。当 SCOPE 等于 1，试图修改静态配置参数时服务器会返回错误信息
--SCOPE参数为 2 表示只在 INI 文件中修改配置参数，此时可用来修改静态配置参数和动态配置参数

# 例如：将 DM.INI 文件中动态参数 SEL_RATE_EQU 设置为 0.3，在 disql 中执行以下命令即可立即生效
SP_SET_PARA_DOUBLE_VALUE(1, 'SEL_RATE_EQU', 0.3);

3. 修改系统整型、double、 varchar 的静态配置参数或动态配置参数：

# 语法格式
SF_SET_SYSTEM_PARA_VALUE(paraname varchar(256),value int64\double\varchar(256),deferred int,scope int64);
--DEFERRED 参数为 0 表示当前 session 修改的参数立即生效，默认为 0
--DEFERRED 参数为 1 表示当前 session 不生效，后续再生效
--SCOPE 参数为 1 表示在内存和 INI 文件中都修改参数值，此时只能修改动态的配置参数
--SCOPE 参数为 2 表示只在 INI 文件中修改配置参数，此时可用来修改静态配置参数和动态配置参数

# 例如：将动态参数 ENABLE_DDL_ANY_PRIV 设置为 1，且当前 session 立即生效
select SF_SET_SYSTEM_PARA_VALUE('ENABLE_DDL_ANY_PRIV',1,0,1);

4. 修改某个会话级 INI 参数的值，设置的参数值只对本会话有效：

# 语法格式
SF_SET_SESSION_PARA_VALUE (paraname varchar(8187), value bigint);
# 例如：将 USE_HAGR_FLAG 设置为 1，且只对本会话有效
select SF_SET_SESSION_PARA_VALUE ('USE_HAGR_FLAG',1); 

5. 重置某个会话级 INI 参数的值，使得这个 INI 参数的值和系统 INI 参数的值保持一致：

# 语法格式
SP_RESET_SESSION_PARA_VALUE (paraname varchar(8187));
# 例如：重置 USE_HAGR_FLAG
select SP_RESET_SESSION_PARA_VALUE ('USE_HAGR_FLAG');

3.2.2 ALTER 命令修改

  修改系统参数：

# 语法格式
ALTER SYSTEM SET ‘<参数名称>’ =<参数值> [DEFERRED] [MEMORY|BOTH|SPFILE];

# 静态参数修改
ALTER SYSTEM SET ‘MTAB_MEM_SIZE’ =1200 spfile;
# PURGE关键字指是否清理执行计划
ALTER SESSION SET ‘<参数名称>’ =<参数值> [PURGE];
# 修改当前会话参数
ALTER SESSION SET ‘HAGR_HASH_SIZE’ =2000000;

3.2.3 修改 INI 文件

  dm.ini 文件一般情况下位于数据库实例路径下，可以通过 vi dm.ini 命令修改。修改完成后，可以通过 v$dm_ini 或者 v$parameter 查询参数值。例如：

--查询v$dm_ini
select * from v$dm_ini where para_name LIKE 'PK_WITH%';
--查询v$parameter
select * from v$parameter where name LIKE 'PK_WITH%'

4 SQL优化

4.1 定位慢 SQL

  定位执行效率低的 SQL 语句是 SQL 优化的第一步。待优化的 SQL 可大致分为两类：
  SQL 执行时间在十几秒到数十秒之间，但执行频率不高，此类 SQL 对数据库整体性能影响并不大，可以放到最后进行优化。
  SQL 单独执行时间可能很快，在几百毫秒到几秒之间，但执行频率非常高，甚至达到每秒上百次，高并发下执行效率降低，很可能导致系统瘫痪，此类 SQL 是优化的首要对象。
  以下介绍两种定位慢 SQL 的方法，可记录下具体 SQL 语句以及对应执行时间，为后续 SQL 优化工作奠定基础：

4.1.1 开启跟踪日志记录

  跟踪日志文件是一个纯文本文件，以”dmsql_实例名_日期_时间命名.log”，默认生成在 DM 安装目录的 log 子目录下。跟踪日志内容包含系统各会话执行的 SQL 语句、参数信息、错误信息、执行时间等。跟踪日志主要用于分析错误和分析性能问题，基于跟踪日志可以对系统运行状态进行分析。

4.1.1.1 跟踪日志记录配置

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

SP_SET_PARA_VALUE(1,'SVR_LOG',1);

2. 配置数据文件目录下的 sqllog.ini 文件：

[dmdba@localhost DAMENG]$ cat sqllog.ini 
BUF_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     = 1
    SWITCH_LIMIT    = 100000
# 为避免记录 SQL log 对服务器产生较大的影响，可以配置异步日志刷新（参数 ASYNC_FLUSH 设置为 1）。
    ASYNC_FLUSH     = 0
    FILE_NUM        = 200
    ITEMS           = 0
    SQL_TRACE_MASK  = 2:3:23:24:25
    MIN_EXEC_TIME   = 0
    USER_MODE       = 0
    USERS           =

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

SP_REFRESH_SVR_LOG_CONFIG();

4. 各配置项详细说明如下表4-1所示：

4.1.1.2 查询方法

  sqllog.ini 文件配置成功后可在 dmsql 指定目录下生成 dmsql 开头的 log 日志文件。可以通过正则表达式在 dmsql 日志文件中查找执行时间超过一定阈值的 SQL 语句。例如：查找执行时间超过 10 秒的 SQL 语句：

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

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

4.1.2 通过系统视图查看

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

4.1.2.1 SQL 记录配置

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

--修改参数值
SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1);
SP_SET_PARA_VALUE(1,'MONITOR_TIME',1);

--查看参数值
select SF_GET_PARA_VALUE(1,'ENABLE_MONITOR');
select SF_GET_PARA_VALUE(1,'MONITOR_TIME');

  需要注意的是：

• 两个参数均为动态参数，可直接调用系统函数进行修改，无须重启数据库实例服务；
• 通过 SP_SET_PARA_VALUE 方式修改的参数值仅对当前会话以及新建会话生效，对其它已建立会话不生效。

4.1.2.2 查询方式

1. 查询当前正在执行的会话信息：

SELECT * FROM (
SELECT 'SP_CLOSE_SESSION('||SESS_ID||');' AS CLOSE_SESSION,
       DATEDIFF(SS,LAST_SEND_TIME,SYSDATE) sql_exectime,
       TRX_ID,
       CLNT_IP,
       B.IO_WAIT_TIME AS IO_WAIT_TIME,
       SF_GET_SESSION_SQL(SESS_ID) FULLSQL,
       A.SQL_TEXT
 FROM V$SESSIONS a,V$SQL_STAT B WHERE STATE IN ('ACTIVE','WAIT') 
 AND A.SESS_ID = B.SESSID
 )

  SQL_TEXT 列记录的是部分 SQL 语句；FULLSQL 列存储了完整的执行 SQL 语句。

2. 查询超过执行时间阈值的 SQL 语句

  可通过查询 V$LONG_EXEC_SQLS 系统视图获取结果：

SELECT * FROM V$LONG_EXEC_SQLS;

  查询结果字段详细信息介绍如下表所示：

  社区地址：https://eco.dameng.com