1.创建测试表http://www.cndba.cn/hbhe0316/article/107886

SQL> create table t_hbhe(id int,name varchar(10));

Table created.

SQL> insert into t_hbhe values (1,'a');

1 row created.

SQL> insert into t_hbhe values (2,'b');

1 row created.

SQL> commit;

Commit complete.

2.模拟会话被hang住http://www.cndba.cn/hbhe0316/article/107886http://www.cndba.cn/hbhe0316/article/107886

SQL> select * from t_hbhe;

        ID NAME
---------- ------------------------------
         1 a
         2 b

SQL> delete from t_hbhe where id = 1;

1 row deleted.

session 2:

SQL> delete from t_hbhe where id = 1;

0 rows deleted.

3.做systemstate

SQL>  oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump systemstate 10
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_5383.trc
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0

4.使用ass109.awk分析dump文件http://www.cndba.cn/hbhe0316/article/107886

[oracle@db02 ~]$ awk -f /tmp/ass109.awk /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6555.trc

Starting Systemstate 1
...
Ass.Awk Version 1.0.9 - Processing /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6555.trc

System State 1
~~~~~~~~~~~~~~~~
36: 0: waiting for 'SQL*Net message from client' 
81: 0: waiting for 'enq: TX - row lock contention' 
     Cmd: Delete
91:                                     

NO BLOCKING PROCESSES FOUND


8852 Lines Processed.

从这里马上就可以知道pid 81 请求Enqueue TX被pid 36阻塞
不过LTOM目前已经不再开发了，他的很多功能集成到ASH里面了：

IMPORTANT: LTOM is a legacy tool and is no longer being developed. 
Much of the functionality is now available within the Active Session History (ASH). 
For details see: Document 243132.1 Analysis of Active Session History (Ash) Online and Offline

版权声明：本文为博主原创文章，未经博主允许不得转载。

oracle