Hanganalyze学习笔记
ORACLE从8i开始，推出hanganalyze工具来诊断出数据库hang住的原因，从9i开始增强了RAC环境下的集群环境下的信息，也就是hanganalyze工具会报告出整个集群环境下的所有会话信息。
有些时候，数据库可能因为hang住而产生严重的性能问题，通常情况下所说的ORACLEhang住了，其实并不是数据库内部发生死锁导致数据库hang住，而普通的DML死锁，ORACLE会自动检测他们的以来关系，最终回滚其中的一个，终止互相等待的局面，而内核资源争夺产生的死锁，比如latch，ORACLE并不能自动检测到。下面模拟DML死锁，ORACLE自动处理的情况。
SQL> create table test as select OBJECT_ID,OBJECT_NAME from user_objects where rownum < 6; 表已创建。 SQL> select * from test; OBJECT_ID OBJECT_NAME ---------- --------------- 81218 BEF_TRI_IMAGES 81184 IMAGE 81214 IMAGES 81194 IMAGE_1 76117 LIBFILE1004 session1set sqlprompt session1> session1>update test set OBJECT_ID=2012 where OBJECT_NAME='IMAGE'; 已更新 1 行。 SQL> set sqlprompt session2> session2>update test set OBJECT_ID=2011 where OBJECT_NAME='IMAGES'; 已更新 1 行。 session2>update test set OBJECT_ID=2013 where OBJECT_NAME='IMAGE';
此时，session2等待session提交或回滚，已经hang住。
session1>update test set OBJECT_ID=2010 where OBJECT_NAME='IMAGES';
此时session1等待session2提交或回退，产生死锁，接下来ORACLE自动回退session2的
第二个SQL，解决死锁问题。
update test set OBJECT_ID=2013 where OBJECT_NAME='IMAGE' * 第 1 行出现错误: ORA-00060: 等待资源时检测到死锁
注：此时ORACLE只是回滚了session2的这个操作，session会话并没有断开，
session2执行的第一个操作并没有被回滚，所以此时session1还处在等待状态，
session2做回滚操作，session1的操作完成。
session2>rollback; 回退已完成。 session1>update test set OBJECT_ID=2010 where OBJECT_NAME='IMAGES'; 已更新 1 行。 session1>rollback; 回退已完成。
上面演示了下，DML操作产生死锁时，ORACLE自动处理的案例， 像这种情况，数据库并没有真正hang住，而只是由于数据库的性能、锁等问题，处理的时间比较长而已，这种情况DBA也可以通过查看v$lock、v$locked_object、v$enqueue_lock、v$session、v$session_wait等视图来查看是哪些会话占用了资源，阻塞了其他会话，但本人认为HANGANALYZE更方便、更直观，发现问题也更准确。但是在数据库hang住的情况比较严重，通过SQLPLUS已经登录不上数据库的时候，就需要借助HANGANALYZE工具了。
目前有下面几种种使用HANGANALYZE的方法:
1.会话级，可以在SQLPLUS里直接使用下面的SQL使用HANGANALYZE。
SQL> ALTER SESSION SET EVENTS 'immediate trace name HANGANALYZE level 3'; 会话已更改。
此时，10g的数据库已经在udump目录下HANGANALYZE已经生成了相关的trace文件，可以查看trace文件查看数据库的资源占用信息。11g数据库HANGANALYZE的日志信息会存放在$ORACLE_BASE/diag/rdbms/$ORACLE_SID/trace目录下。这种情况如果是在RAC环境下操作，会将所有节点的信息都收集，当然，其他节点的日志会存放在其他节点的trace目录下。
2.实例级，需要sys用户执行。
SQL> ORADEBUG hanganalyze 3; Hang Analysis in d:oraclediagrdbmsstreamstreamtrace stream_ora_3188.trc
这种用法默认是对单实例操作，如果针对RAC环境，需要按照下面的步骤。
SQL> ORADEBUG setmypid; 已处理的语句 SQL> ORADEBUG setinst all; 已处理的语句 SQL> ORADEBUG -g def hanganalyze 3; ORA-32730: 命令无法在远程实例上执行
由于我的数据库是单实例数据库，所以会报ORA-32730错误。
3.ORACLE将HANGANALYZE的有关脚本封装成一个程序包，可以去MOS上下载，Note:362094.1，hangfg.tar，下载后传到服务器解压后就可以使用。
下面分析下HANGANALYZE生成的trace文件。我的数据库版本是11gR2，11g数据库HANGANALYZE生成的trace文件比10g要详细一些。
====================================================
Chains most likely to have caused the hang:
[a] Chain 1 Signature: 'SQL*Net message from
client'< ='enq: TX - row lock contention'
Chain 1 Signature Hash: 0x38c48850
====================================================
这部分说最有可能造成挂起的原因是行级锁竞争，HANGANALYZE生成的trace文件主要关注Chains部分，trace文件中如果有Chains，说明数据库很可能有锁产生。
Chain 1:
-----------------------------------------------------
Oracle session identified by:
{
instance: 1 (stream.stream)
os id: 7412
process id: 32, ORACLE.EXE (SHAD)
session id: 18
session serial #: 62
}
is waiting for 'enq: TX - row lock contention' with wait info:
{
p1: 'name|mode'=0x54580006
p2: 'usn< <16 | slot'=0x10009
p3: 'sequence'=0xfe7
time in wait: 21.012760 sec
timeout after: never
wait id: 24
blocking: 0 sessions
current sql: update test set OBJECT_ID=2012 where OBJECT_NAME="IMAGE"
这部分显示sid=18,serial#=62的会话执行update test set OBJECT_ID=2012 where OBJECT_NAME=‘IMAGES’操作被阻塞。
and is blocked by
=> Oracle session identified by:
{
instance: 1 (stream.stream)
os id: 6000
process id: 19, ORACLE.EXE (SHAD)
session id: 143
session serial #: 423
}
此部分表示阻塞sid=18,serial#=62的是sid=143，serial#=423的会话。
Extra information that will be dumped at higher levels: [level 4] : 1 node dumps -- [LEAF] [LEAF_NW] [level 5] : 1 node dumps -- [NO_WAIT] [INVOL_WT] [SINGLE_NODE] [NLEAF] [SINGLE_NODE_NW]
本实验做的是level 3级别，此部分说明如果使用level 4和level 5级别，HANGANALYZE在level 3级别基础上会多收集哪些信息。
State of ALL nodes ([nodenum]/cnode/sid/sess_srno/session/ospid/state/[adjlist]): [17]/1/18/62/21E0D4FC/7412/NLEAF/[142] [142]/1/143/423/21F5AC1C/6000/LEAF/
此部分说明[17]（sid=18,serial#=62）会话被[142]（sid=143,serial#=423）的会话阻塞，NLEAF表示被阻塞者，LEAF表示阻塞者。
很多人在查到阻塞者信息后，会直接KILL阻塞者的会话，建议不要直接就KILL阻塞者，应该先查看下阻塞者在做什么，确定阻塞者做的操作可以干掉后再KILL，可以通过下面的SQL查看阻塞者在做什么。
SQL> select sql_id from V$session where sid=143 and serial#=423; SQL_ID -------------
通常这个SQL都会查不到信息，因为这个会话的SQL可能已经执行完，但是没有提交，SQL执行完后，sql_id就会被存放到prev_sql_id字段内，也就是说sql_id是现在正在执行的SQL，而prev_sql_id是上一条执行的SQL，如果阻塞者在执行DELETE、INSERT、UPDATE操作后，没有做SELECT操作，那么可以通过查看prev_sql_id查到产生阻塞的SQL语句。
SQL> select prev_sql_id from V$session where sid=143 and serial#=423; PREV_SQL_ID ------------- 81bhjz3ss7zfr SQL> select sql_text from v$sqltext where sql_id='81bhjz3ss7zfr'; SQL_TEXT ---------------------------------------------------------------- update test set OBJECT_ID=2012 where OBJECT_NAME="IMAGE"
本实验可以KILL这个SESSION，因为做的都是UPDATE操作，而且是对同一条记录进行操作，即使这个SESSION提交，后一个SESSION的修改也会刷新这个条记录，但是很多情况并不能直接KILL阻塞者的SESSION，比如之前修改了2条记录，后者修改1条记录，但是前者修改的操作包含后者操作的记录，比如这两个SESSION修改的同一条记录的不同字段，比如前者做的是DELETE操作，后者再做UPDATE就没有意义了，这就需要去问业务员是由于什么原因没有提交，经确认可以KILL的时候再干掉这个SESSION。
SQL> alter system kill session '143,423'; 系统已更改。
如果不用HANGANALYZE，对ORACLE很熟悉的人也可以直接查到是什么阻塞了其他会话，可以通过下面的SQL查到阻塞者的信息。
SQL> select * from V$lock where block=1; ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- --- -- ------ ---- ----- ------- ----- ----- 213BB2C8 213BB308 143 TX 131090 5664 6 0 58 1
BLOCK=1表示这是个阻塞者，也就是SID=143的会话阻塞了其他会话，可以通过下面的SQL查到被阻塞者的信息。
SQL> select * from V$enqueue_lock where REQUEST=6; ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- --- -- ------ ---- ----- ------- ----- ----- 21B45AD4 21B45B00 18 TX 131090 5664 0 6 440 0
REQUEST=6表示被阻塞者，也就是SID=18的会话被阻塞，可以通过下面的SQL找到是哪个对象被锁住。
SQL> select OBJECT_ID,SESSION_ID,ORACLE_USERNAME,PROCESS,LOCKED_MODE from V$locked_object;
OBJECT_ID SESSION_ID ORACLE_USERNAME PROCESS LOCKED_MODE
---------- ---------- -------------------- ---------- -----------
93959 18 META 436:3248 3
93959 143 META 2540:5452 3
可以通过下面的SQL找到可能产生阻塞的SQL_ID。
SQL> select PREV_SQL_ID from v$session where sid=143 and process='2540:5452' and row_wait_obj# =93959; PREV_SQL_ID ------------- g99dqsgfurx69
然后找到可能阻塞的SQL。
SQL> select sql_text from v$sqltext where sql_id='g99dqsgfurx69'; SQL_TEXT ---------------------------------------------------------------- update test set OBJECT_ID=2012 where OBJECT_NAME="IMAGE"
仔细研究v$session视图，也可以看出阻塞者和被阻塞者的信息。
SQL> select sid,serial# s#,BLOCKING_SESSION bs,row_wait_obj# obj#,EVENT,WAIT_CLASS# wc#,STATE, PREV_SQL_ID FROM V$SESSION WHERE SID IN(18,143); SID S# BS OBJ# EVENT WC# STATE PREV_SQL_ID --- --- --- ---- ------------------------------ --- ------- ------------- 18 62 143 93959 enq: TX - row lock contention 1 WAITING 81bhjz3ss7zfr 143 802 -1 SQL*Net message from client 6 WAITING g99dqsgfurx69
在v$session视图中，WAIT_CLASS#=6表示阻塞者，WAIT_CLASS#=1表示被阻塞者，我们可以看到SID=18的SESSION被SID=143的SESSION阻塞。v$session视图往往被忽略，其实仔细挖掘v$session视图，可以找到很多有意义的信息。
Hanganalyze学习笔记：目前有1 条留言
发表评论
总结的不错。
2012-02-08 23:23