目录
前言
DM数据库性能诊断。
一、简介
可以通过动态视图+SQL 日志来进行性能问题定位。。
二、动态视图
1.活动会话数
查询活动会话数,语句如下所示:
SELECT COUNT(*) FROM V$SESSIONS WHERE STATE='ACTIVE';
2.长时间活动SQL
已执行超过 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;
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;
三、慢SQL日志
1.开启慢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);
SF_SET_SYSTEM_PARA_VALUE('SVR_LOG_MIN_EXEC_TIME',200,0,1); --200表示记录200ms的sql,按需修改
SP_SET_PARA_VALUE(1,'SVR_LOG',1); --开启慢日志
2.查询设置是否生效
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';
SELECT * FROM V$DM_INI where para_name='SVR_LOG';
3.查看慢日志
记录的慢sql日志文件位于/home/dmdba/dmdbms/log/下面,dmsqlxxx.log就是。
注:老版本慢日志是log_commitxx.log。具体路径需根据dmdbms的具体路径。
4.关闭慢SQL日志
SP_SET_PARA_VALUE(1,'SVR_LOG',0);
总结
更多资讯请上达梦技术社区了解:https://eco.dameng.com