dm定位慢sql

查看ENABLE_MONITOR、MONITOR_SQL_EXEC参数设置

select SF_GET_PARA_VALUE(2,'ENABLE_MONITOR');

select SF_GET_PARA_VALUE(2,'MONITOR_SQL_EXEC');

如果值为0 说明没开启，开启参数如下

CALL SP_SET_PARA_VALUE(1, 'ENABLE_MONITOR', 1);
CALL SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC', 1);

查看慢sql设置执行时长

select SF_GET_LONG_TIME(); 默认1000毫秒

设置慢sql设置执行时长10秒

call SP_SET_LONG_TIME(10000);

查询历史慢sql

SELECT * FROM V$LONG_EXEC_SQLS order by exec_time desc

查询正在执行超过10秒的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>=10;

SELECT* FROM (
SELECT instance_name,SESS_ID,SQL_TEXT,DATEDIFF(SS,LAST_RECV_TIME,SYSDATE) Y_EXETIME,
SF_GET_SESSION_SQL(SESS_ID) FULLSQL,CLNT_IP
FROM gV$SESSIONS WHERE STATE='ACTIVE')
WHERE Y_EXETIME>=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, 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;