1 模拟锁等待
会话一执行：
create table t (id number,name varchar(10));
insert into t values t values(1,'test');
Commit;
update t set name='TEST1' where id=1;
会话二：
update t set name='TEST1' where id=1;
等待
2 从操作系统层查看线程角度出发排查
[dmdba@dba ~]$ ps -ef | grep dmserver
dmdba 11174 1 0 00:42 pts/0 00:00:40 /dm8/bin/dmserver path=/dm8/data/DM/dm.ini -noconsole
dmdba 20407 1 0 12:28 ? 00:01:09 /dm8/bin/dmserver path=/dm8/data/DAMENG/dm.ini -noconsole

top -H -p 20407

发现dm_sql_thd线程占的cpu稍多，获取dm_sql_thd 线程号为22606
从数据库中获取dm_sql_thd的信息，可以获取到有哪些session
select sess_id from v$threads where name='dm_sql_thd'
获取哪个事务是锁定其它表的：
select wait_for_id from v$trxwait

最终获取到锁定其它表的会话：
select sess_id,trx_id,sql_text,state from v$sessions where sess_id in
(select sess_id from v$threads where name='dm_sql_thd') and trx_id in
(select wait_for_id from v$trxwait);
Sess_id:140077406699176

杀会话：
call sp_close_session(140077406699176);
再次查事务被锁定的信息消失：
select * from v$trxwait;
3 从会话角度排查
重新模拟锁等待
查询事务堵塞信息：
select * from v$trxwait;

select * from v$lock where blocked=1;

获取堵塞其它会话的会话id:
select * from v$sessions where trx_id=236938;

Kill session:
call sp_close_session(140077405650408);
再次查询：
select * from v$trxwait;为空

至此结束！