达梦数据性能诊断与优化

1、数据库性能诊断

1.1 目标

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

业务角度（用户）：减少用户页面响应时间。

数据库角度（开发）：减少数据库 SQL 响应时间。

数据库服务器角度（运维）：充分使用数据库服务器物理资源减少数据库服务器 CPU 使用率，减少数据库服务器 IO 使用率，减少数据库服务器内存使用率。

数据库优化指标如下：

SQL 平均响应时间变短。

数据库服务器 CPU 占用率变少。

数据库服务器 IO 使用率变低。

1.2 收集现场信息

1）数据库硬件信息

2）数据库软件信息

3）数据库用户信息

1.3 问题定位

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

1）查询会话数

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

2）查询执行超过多少秒的会话

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;

3）锁查询

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

4）阻塞查询

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, TID 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;

5）sql日志

--设置 SQL 过滤规则，只记录必要的 SQL，生产环境不要设成 1

-- 2 只记录 DML 语句    3 只记录 DDL 语句   22 记录绑定参数的语句

-- 25 记录 SQL 语句和它的执行时间    28 记录 SQL 语句绑定的参数信息

SF_SET_SYSTEM_PARA_VALUE('SQL_TRACE_MASK','2:3:22:25:28',0,1);

--同步日志会严重影响系统效率，生产环境必须设置为异步日志

SF_SET_SYSTEM_PARA_VALUE('SVR_LOG_ASYNC_FLUSH',1,0,1);

--下面这个语句设置只记录执行时间超过 200 ms 的语句

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);

6）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:\dmdbms\awr','AWR1.HTML');

7）JDBC驱动日志

达梦数据库 - 新一代大型通用关系型数据库 | 达梦在线服务平台