锁查询

SELECT O.NAME,L.*
FROM V$LOCKL,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.LATS_SEND_TIME
FROM V$LOCKL,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.LYPE='OBJECT'
AND T1.TABLE_ID<>0
AND T2.LYPE='OBJECT'
AND T2.TABLE_ID<>0
AND S.WT_TRXID=T1.TRX_ID
AND S.BLK_TRXID=T2.TRX_ID

)

SELECT DISTINCT SYSDATE STAT_TIME,'0X'||TRIM(TO_CHAR(WT_SESSID,RPAD('X',15,'X')))WT_SESSID,
'0X'||TRIM(TO_CHAR(BLK_SESSID,RPAD('X',15,'X')))BLK_SESSID,
CLNT_IP,WT_TRXID,BLK_TRXID,SS,WT_SQL
FROM RES ORDER BY SS DESC;

得到阻塞事务 ID 后，通过达梦数据库提供的事务日志，查找到阻塞事务的具体 SQL，然后再进一步优化。也可通过动态视图直接查找阻塞事务的具体 SQL 语句：

SELECT SESS_ID,START_TIME,TOP_SQL_TEXT,SEC_SQL_TEXT,THRD_SQL_TEXT
FROM V$SQL_HISTORY WHERE TRX_ID='1003';