DM-定位慢SQL

通过系统视图定位慢SQL

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

配置方式

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

修改配置

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

-- 记录执行时间超过2000 毫秒的SQL
SP_SET_LONG_TIME(2000);

查看配置

select SF_GET_LONG_TIME();

查看慢SQL

V$LONG_EXEC_SQLS 视图

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

SELECT * FROM V$LONG_EXEC_SQLS;

V$LONG_EXEC_SQLS 字段说明

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

查询活动会话数

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;

阻塞查询

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;

查询等待事务

-- 1. 确认语句处于活动状态
SELECT * FROM v$sessions WHERE state='ACTIVE'
AND dbms_lob.substr(sf_get_session_sql(sess_id)) LIKE '%语句片段%'

-- 2. 
SELECT * FROM v$trxwait WHERE id = <上述语句得到结果得 TRX_ID>

V$TRXWAIT 查询得到结果的 WAIT_FOR_ID 字段标记的事务即为当前语句正在等待的事务

通过开始SQL跟踪日志定位慢查询

详见"实施-> 7-DM 开启SQL日志跟踪分析.md"