达梦查询阻塞SQL相关操作

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, row_idx blk_trxid from locks where blocked=1
        )
        ,
        res as
        (
                select
                        sysdate stattime                           ,
                        t1.name                                    ,
                        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;

2、记录死锁的历史信息

select
        TRX_ID AS 事务ID          ,
        SESS_ID AS 会话ID         ,
        SESS_SEQ AS 会话序列号_唯一标识会话,
        SQL_TEXT AS 死锁SQL       ,
        HAPPEN_TIME AS 死锁发生时间
from
        v$deadlock_history;

3、定位锁等待问题
–①查看被挂起的事务（TRX_ID）

SELECT
        VTW.ID AS TRX_ID,
        VS.SESS_ID      ,
        VS.SQL_TEXT     ,
        VS.APPNAME      ,
        VS.CLNT_IP
FROM
        V$TRXWAIT VTW
LEFT JOIN V$TRX VT
ON
        (
                VTW.ID=VT.ID
        )
LEFT JOIN V$SESSIONS VS
ON
        (
                VT.SESS_ID=VS.SESS_ID
        );

–②通过挂起事务ID（TRX_ID）找到它等待的事务（WAIT_FOR_ID）。

SELECT WAIT_FOR_ID,WAIT_TIME FROM V$TRXWAIT WHERE ID=321646; 

–③通过等待事务ID（WAIT_FOR_ID）定位到连接以及执行的语句

SELECT
        VT.ID AS TRX_ID,
        VS.SESS_ID     ,
        VS.SQL_TEXT    ,
        VS.APPNAME     ,
        VS.CLNT_IP
FROM
        V$TRX VT
LEFT JOIN V$SESSIONS VS
ON
        (
                VT.SESS_ID=VS.SESS_ID
        )
WHERE
        VT.ID = 321643;

5、关闭所等待的SQL

SP_CLOSE_SESSION关闭等待事务（SESS_ID）
SP_CLOSE_SESSION(142344256);