达梦查看阻塞会话

select s.sess_id,s.SQL_TEXT,s.RUN_STATUS from v$sessions s , v$lock l where l.tid=s.trx_Id and l.blocked=1;

SP_CLOSE_SESSION(SESS_ID)来关闭对应的会话

select 'SP_CLOSE_SESSION(' ||s.sess_id||')' from v$sessions s,v$lock l where s.trx_id=l.tid and l.blocked=1;

查看那些事务处于等待状态

select * from v$trx where status ='LOCK WAIT';

 

 

select * from v$trxwait;

查询等待时间最长的事务对应的WAIT_FOR_ID，通过下面sql查对应的事务详细信息

select * from v$trx where id = WAIT_FOR_ID

结果中找到事务对应的会话SESS_ID，通过下面sql查询会话的详细信息

select * from v$sessions where sess_id = SESS_ID

--查看历史长时间执行的sql

select * from V$LONG_EXEC_SQLS  order by exec_time desc

--查看等待会话时长

select s0.sess_id as "被阻塞的sessid",
s1.sess_id as "阻塞的sessid",
s0.sql_text as "被阻塞语句",
s1.sql_text as "阻塞语句",
w.WAIT_TIME/1000 as "等待时长"
from v$sessions s0, v$sessions s1, v$trxwait w
where w.id = s0.trx_id
and w.WAIT_FOR_ID = s1.trx_id

--通过sess_id 查找完整sql

select  sf_get_session_sql(9095603704)  from dual

select
'SP_CLOSE_SESSION('||SESS_ID||')' as killsess,
a.SQL_TEXT ,SF_GET_SESSION_SQL(a.SESS_ID) FULLSQL,
DATEDIFF(SS,a.LAST_RECV_TIME,SYSDATE) Y_EXETIME,
b.LOGIC_READ_CNT ,
b.PHY_MULTI_READ_CNT,
b.PHY_READ_CNT ,
b.MAX_MEM_USED ,
b.HASH_JOIN_CNT ,
b.IO_WAIT_TIME ,
'sp_clear_plan_cache('||d.CACHE_ITEM||')' as clearplan,'alter session set events '''||'immediate trace name plndump ,level '||d.CACHE_ITEM||''';' as dplan
from
v$sessions a ,
V$SQL_STAT b ,
SYS."V$SQL_PLAN" c,
SYS."V$CACHEPLN" d
where
a.SESS_ID =b.SESSID
and a.STATE ='ACTIVE'
and b.SQL_ID =c.SQL_ID
and c.HASH_VALUE=d.HASH_VALUE;