会话1:
SQL> create table test1(id int,name varchar2(20));
Table created.SQL> insert into test1 values(1,'bing');
1 row created.
SQL> commit
Commit complete.
SQL> select userenv('sid') from dual;
USERENV('SID')
--------------
147
SQL> select sid from v$mystat where rownum<2;
SID
----------
147
SQL> select * from test1;
ID NAME
---------- --------------------
1 bing
SQL> update test1 set name='zhao' where id=1;------这里不提交
1 row updated.
会话2:
SQL> conn scott/tiger
Connected.
SQL> select sid from v$mystat where rownum<2;
SID
----------
138
SQL> select userenv('sid') from dual;
USERENV('SID')
--------------
138
SQL> update test1 set name='zhao' where id=1;----这里就hang
会话3:
SQL> oradebug hanganalyze 3;
Hang Analysis in /u01/app/oracle/diag/rdbms/ogg2/ogg2/trace/ogg2_ora_5600.trc
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
查看里面的信息
Oracle session identified by:
{
instance: 1 (ogg2.ogg2)
os id: 5526
process id: 27, oracle@ogg5 (TNS V1-V3)
session id: 138
session serial #: 9
and is blocked by
=> Oracle session identified by:
{
instance: 1 (ogg2.ogg2)
os id: 5453
process id: 25, oracle@ogg5 (TNS V1-V3)
session id: 147
session serial #: 13
}
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump systemstate 266
Statement processed.
SQL> oradebug dump systemstate 266
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/ogg2/ogg2/trace/ogg2_ora_5691.trc
SQL> exit
通过ass109.awk文件,可以很容易将trace文件里的内容理出脉络来,清晰的发现问题所在。
ass109.awk这个要下载的,有系统也有自带。
总结
会话级别:SQL>ALTER SESSION SET EVENTS 'immediate trace name HANGANALYZE level <level>';
实例级别:SQL>ORADEBUG hanganalyze <level>
1-2:只有hanganalyze输出,不dump任何进程
3:Level2+Dump出在IN_HANG状态的进程
4:Level3+Dump出在等待链里面的blockers(状态为LEAF/LEAF_NW/IGN_DMP)
5:Level4+Dump出所有在等待链中的进程(状态为NLEAF)
Oracle官方建议不要超过level 3,一般level 3也能够解决问题,超过level 3会给系统带来额外负担。
Hanganalyze工具使用内核调用检测会话在等待什么资源,报告出占有者和等待者的相互关系。另外,它还会将一些比较”interesting”的进程状态dump出来,这个取决于我们使用hanganalyze的分析级别。