DM8达梦数据库性能诊断

一个项目的运行，总伴随着性能问题，数据库的性能问题不容忽视，本章节主要介绍数据库性能问题定位方法以及解决方案。

数据库性能优化的目标

根据角色的不同，数据库优化分为以下几个目标：

业务角度（用户）：减少用户页面响应时间。
数据库角度（开发）：减少数据库 SQL 响应时间。
数据库服务器角度（运维）：充分使用数据库服务器物理资源减少数据库服务器 CPU 使用率，减少数据库服务器 IO 使用率减少数据库服务器内存使用率。

数据库优化指标如下：

SQL 平均响应时间变短。
数据库服务器 CPU 占用率变少。
数据库服务器 IO 使用率变低。

收集信息

首先我们需要观察确认现场环境的信息，环境是承载数据库稳定运行的基础保障。

数据库硬件信息收集

硬件	硬件配置	运行情况
CPU	cat/proc/cpuinfo	top\vmstat\sar
内存	cat/proc/meminfo	top\vmstat\free
磁盘	RAID	iostat
网络	ifconfig	ping\route\netstat

通过对硬件环境的收集分析我们可以判断是否是因为硬件问题造成的数据库性能瓶颈。在排除硬件问题的情况下，我们可以通过收集软件信息进一步进行问题定位。

数据库软件信息收集

分类	说明
数据库版本	V$VERSION
数据库架构	单机\主备\读写分离\MPP\DSC
交易类型	OLTP\OLAP\OLTP与 OLAP 混合
配置参数	资源类参数\查询优化参数
数据库规模	库大小\大表\分区表\索引
关注的对象	触发器\大字段\物化视图\外部链接
会话数	V$SESSIONS
事务数	TRX
等待事件	V$TRXWAIT/V$LOCK
内存相关使用	V$SESSIONS
热点	热点表\热点 SQL

在进行收集软硬件的同时，我们也要沟通数据库的使用者来确认近期的环境变化。

用户信息收集

分类	说明
硬件	是否有硬件更换\重启\升级
软件	是否有新业务上线\新模块适配
用户	是否有业务用户增加\业务访问时间调整\业务访问模块集中

问题定位

我们可以通过动态视图+SQL 日志+JDBC 驱动日志来进行性能问题定位。

动态性能视图

查询活动会话数，语句如下所示：

SELECT COUNT(*) FROM V$SESSIONS WHERE STATE='ACTIVE';

已执行超过 2 秒的活动 SQL，语句如下所示：

SELECT* FROM (
SELECT SESS_ID,SQL_TEXT,DATEDIFF(SS,LAST_RECV_TIME,SYSDATE) Y_EXETIME,
       SF_GET_SESSION_SQL(SESS_ID) FULLSQL,CLNT_IP
FROM V$SESSIONS WHERE STATE='ACTIVE')
WHERE Y_EXETIME>=2;

锁查询，语句如下所示：

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

阻塞查询，语句如下所示：

WITH LOCKS
     AS (SELECT O.NAME,L.*,S.SESS_ID,S.SQL_TEXT,S.CLNT_IP,S.LAST_SEND_TIME
           FROM V$LOCK L, SYSOBJECTS O, V$SESSIONS S
          WHERE L.TABLE_ID = O.ID AND L.TRX_ID = S.TRX_ID),
     LOCK_TR
     AS (SELECT TRX_ID WT_TRXID, ROW_IDX BLK_TRXID
           FROM LOCKS
          WHERE BLOCKED = 1),
     RES
     AS (SELECT SYSDATE STATTIME,T1.NAME,T1.SESS_ID WT_SESSID,S.WT_TRXID,
                T2.SESS_ID BLK_SESSID,S.BLK_TRXID,T2.CLNT_IP,
                SF_GET_SESSION_SQL (T1.SESS_ID) FULSQL,
                DATEDIFF (SS, T1.LAST_SEND_TIME, SYSDATE) SS,
                T1.SQL_TEXT WT_SQL
           FROM LOCK_TR S, LOCKS T1, LOCKS T2
          WHERE     T1.LTYPE = 'OBJECT'
                AND T1.TABLE_ID <> 0
                AND T2.LTYPE = 'OBJECT'
                AND T2.TABLE_ID <> 0
                AND S.WT_TRXID = T1.TRX_ID
                AND S.BLK_TRXID = T2.TRX_ID)
SELECT DISTINCT WT_SQL,CLNT_IP,SS,WT_TRXID,BLK_TRXID
  FROM RES;

SQL 日志

--设置 SQL 过滤规则，只记录必要的 SQL，生产环境不要设成 1
-- 2 只记录 DML 语句    3 只记录 DDL 语句   22 记录绑定参数的语句
-- 25 记录 SQL 语句和它的执行时间    28 记录 SQL 语句绑定的参数信息
SELECT SF_SET_SYSTEM_PARA_VALUE('SQL_TRACE_MASK','2:3:22:25:28',0,1);

--同步日志会严重影响系统效率，生产环境必须设置为异步日志
SELECT SF_SET_SYSTEM_PARA_VALUE('SVR_LOG_ASYNC_FLUSH',1,0,1);

--下面这个语句设置只记录执行时间超过 200ms 的语句
SELECT SF_SET_SYSTEM_PARA_VALUE('SVR_LOG_MIN_EXEC_TIME',200,0,1);

--下面的语句查看设置是否生效
SELECT * FROM V$DM_INI where para_name='SVR_LOG_ASYNC_FLUSH';
SELECT * FROM V$DM_INI where para_name='SQL_TRACE_MASK';
SELECT * FROM V$DM_INI where para_name='SVR_LOG_MIN_EXEC_TIME';

--开启 SQL 日志：
SP_SET_PARA_VALUE(1, 'SVR_LOG', 1);

--关闭 SQL 日志：
SP_SET_PARA_VALUE(1, 'SVR_LOG', 0);

AWR日志

启用系统包和 AWR 包：
CALL SP_INIT_AWR_SYS(1);
CALL SP_CREATE_SYSTEM_PACKAGES(1);

查询 AWR 快照：
SELECT *FROM SYS.WRM$_SNAPSHOT;

设置快照间隔，如果不设置快照间隔，手动执行快照后 SYS.WRM$_SNAPSHOT 视图中没有记录：
CALL DBMS_WORKLOAD_REPOSITORY.AWR_SET_INTERVAL(50);

在两个时间点分别手动创建快照，或者等待系统自动生成：
10:00时创建第一快照：
CALL DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

30分钟后再创建一个，10:30，
CALL DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

查询 AWR 快照：
SELECT* FROM SYS.WRM$_SNAPSHOT;

创建AWR报告，SYS.AWR_REPORT_HTML(快照ID1,快照ID2,'AWR报告存放路径','AWR报告名称.HTLM');：
SYS.AWR_REPORT_HTML(1,2,'C:\','AWR1.HTML');