Systemstatedump及Hanganalyze用法(分析数据库hang)
2022-04-09 21:35阅读：
数据库hang住了
1）是不是数据库归档满了？
2）查一下等待事件，看看在等什么呢？
3）alert日志有什么明显报错么？
4）系统内存占用？
当数据库出现严重的性能问题或者hang了的时候，我们非常需要通过systemstate
dump来知道进程在做什么，在等待什么，谁是资源的持有者，谁阻塞了别人。在出现上述问题时，及时收集systemstate
dump非常有助于问题原因的分析。
一、Systemstate dump及Hanganalyze介绍
systemstate dump有多个级别：
2: dump (不包括lock element)
10: dump
11: dump + global cache of RAC
256: short stack （函数堆栈）
258: 256+2 –>short stack +dump(不包括lock element)
266: 256+10 –>short stack+ dump
267: 256+11 –>short stack+ dump + global cache of RAC
注意：
1. 级别11和级别267会dump global cache,会产生较大的trace 文件，一般不推荐。
2.
一般情况下，如果进程不是太多，建议用256，因为这样可以dump出来进程的函数堆栈，可以用来分析进程在执行?么操作，但是生成short
stack比较耗时，如果进程非常多，比如2000个进程，那么可能耗时30分钟以上。这种情况下，可以生成level 10 或者
level 258， level 258 比 level 10会多收集short short stack,
但比level 10少收集一些lock element data。
3. 对于RAC系统，需要关注Bug 11800959 - A SYSTEMSTATE dump with level >=
10 in RAC dumps huge BUSY GLOB
AL CACHE ELEMENTS - can hang/crash instances (Doc ID
11800959.8)。这个Bug在11.2.0.3上被修复，对于<=11.2.0.2的RAC，当系统中的lock
element很多的时候，如果执?level 10、266或者267的systemstate
dump时，可能会导致数据库hang或者crash，这种情况下可以采用level 258。
hanganalyze各个级别的含义如下：
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会给系统带来额外负担。
二、命令介绍
单实例
1. 用sysdba登录到数据库上：
$sqlplus / as sysdba
或者
$sqlplus -prelim / as sysdba <==当数据库已经很慢或者hang到无法连接
SQL>oradebug setmypid
SQL>oradebug unlimit;
SQL>oradebug dump systemstate 266;
等1~2分钟
SQL>oradebug dump systemstate 266;
等1~2分钟
SQL>oradebug tracefile_name;==>这是生成的文件名
SQL>oradebug close_trace
2. 通常除了systemstate dump，最好同时生成hang analyze来直观地了解数据库进程间的等待关系。
$sqlplus / as sysdba
或者
$sqlplus -prelim / as sysdba <==当数据库已经很慢或者hang到无法连接
SQL>oradebug setmypid
SQL>oradebug unlimit;
SQL>oradebug dump hanganalyze 3
等1~2分钟
SQL>oradebug dump hanganalyze 3
等1~2分钟
SQL>oradebug tracefile_name;==>这是生成的文件名
RAC
对于RAC数据库，需要各个实例在同一时间的systemstate dump,那么登录到任意一个实例（无需在所有实例执行）：
$sqlplus / as sysdba或者$sqlplus -prelim / as sysdba
<==当数据库已经很慢或者hang到无法连接
SQL>oradebug setmypid
SQL>oradebug unlimit
SQL>oradebug -g all dump systemstate 266 <==-g all
表示针对所有实例生成dump
等1~2分钟
SQL>oradebug -g all dump systemstate 266
等1~2分钟
SQL>oradebug -g all dump systemstate 266
在RAC上生成hang analyze:
SQL>oradebug setmypid
SQL>oradebug unlimit
SQL>oradebug -g all hanganalyze 3
等1~2分钟
SQL>oradebug -g all hanganalyze 3
等1~2分钟
SQL>oradebug -g all hanganalyze 3
上面的命令执行后会在每个实例都生成systemstate
dump，生成的信息放到了每个实例的backgroud_dump_dest下的diag trace文件中。
上面的这些命令执行三次是为了比较进程的变化情况，查看是真的hang了，还是很慢。
或者
SQL>ORADEBUG setmypid
SQL>ORADEBUG setinst all
SQL>ORADEBUG -g def hanganalyze
插曲：该命令对于RAC，会收集所有节点上的信息
alter session set events 'immediate trace name HANGANALYZE level
3';
select value from v$diag_info where name='Default Trace
File';
The “HANGANALYZE” command is available since Oracle Release 8.1.6.
In Oracle9i it was enhanced to provide “cluster wide” information
in Real Application Cluster (RAC) environments on a single shot.
The meaning of this is that it will generate information for all
the sessions in the cluster regardless of the instance that issued
the command.
参考：https://blog.csdn.net/tianlesoftware/article/details/6321961
hanganalyze文件内解析：
([nodenum]/cnode/sid/sess_srno/session/ospid/state/[adjlist]):
nodenum：定义每个session的序列号
sid：session的sid
sess_srno：session的Serial＃
ospid：OS的进程ID
state：node的状态
adjlist：表示blocker node
predecessor：表示waiter node
State有如下几种状态：
（1）IN_HANG：如果Session处于这种状态，表示Session遇到deadlock或者处于hung状态。
（2）LEAF/LEAF_NW：这些Session通常是“blocker”或者是等待某些资源的“slow”
node，通过字段“predecessor” 可以很容易标识出这些node。
（3）NLEAF：这些Session通常被认为是“stuck”会话，意味着这些Session在等待某些Session的资源。通过字段“adjlist”可以很容易的定义该进程的blocker。
（4）IGN/IGN_DMP：这些Session通常是IDLE Session。
三、测试
参考https://blog.csdn.net/shiyu1157758655/article/details/79285962模拟死锁
session 36：
SQL> create table tb_hang(id int,name varchar2(20));
SQL> insert into tb_hang values(1,'yu');
SQL> commit;
SQL> select userenv('sid') from dual;
SQL> update tb_hang set name='shi' where id=1;
SQL>
session 20：
SQL> select userenv('sid') from dual;
SQL> update tb_hang set name='shi' where id=1;
SQL> oradebug hanganalyze 3;
Hang Analysis in
/opt/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_ora_22711.trc
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
/opt/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_ora_22711.trc
SQL>oradebug close_trace
用ass109工具分析生成的trace文件：
工具下载地址
网址：https://www.modb.pro/download/3368
[oracle@ol733 trace]$ awk -f ass109.awk
ora19c_ora_22711.trc
Starting Systemstate 1
....
Starting Systemstate 2
....
Ass.Awk Version 1.0.9 - Processing ora19c_ora_22711.trc
System State 1
~~~~~~~~~~~~~~~~
35: 0: waiting for 'SQL*Net message from client'
55:
56: 0: waiting for 'SQL*Net message from client'
58: 0: waiting for 'enq: TX - row lock contention'
Cmd: Update
NO BLOCKING PROCESSES FOUND
System State 2
~~~~~~~~~~~~~~~~
35: 0: waiting for 'SQL*Net message from client'
55:
56: 0: waiting for 'SQL*Net message from client'
58: 0: waiting for 'enq: TX - row lock contention'
Cmd: Update
NO BLOCKING PROCESSES FOUND
从上面可以看到pid 58 被pid 35的会话给阻塞了。
select sid,SERIAL# from v$session where paddr in (select addr
from v$process where pid in (58,35));
hanganalyze日志
[oracle@ol733 trace]$ more
/opt/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_ora_24651.trc
Trace file
/opt/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_ora_24651.trc
HANG ANALYSIS:
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
-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
Oracle session identified by:
{
instance: 1 (ora19c.ora19c)
os id: 22450
process id:
58, oracle@ol733 (TNS V1-V3)
session id:
20
session serial #: 4085
module name:
5 (sqlplus@ol733 (TNS V1-V3))
pdb id: 5 (PDBORCL)
}
is waiting for 'enq: TX - row lock
contention' with wait info:
{
p1: 'name|mode'=0x54580006
p2: 'usn<<16 | slot'=0x30007
p3: 'sequence'=0x72c
time in wait: 31
min 51 sec
timeout after:
never
wait id: 27
blocking: 0 sessions
current sql_id:
3699438164
current
sql: update tb_hang set name='shi' where id=1
short stack:
。。。。
Chain 1 Signature: 'SQL*Net message from client'<='enq: TX - row
lock contention'
Chain 1 Signature Hash: 0x38c48850
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]
State of ALL nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/[adjlist]):
[19]/1/20/4085/0x74524660/22450/NLEAF/[35]
[35]/1/36/18684/0x7454c4e0/21897/LEAF/
第1列为oracle给trace中每一个会话所取的唯一逻辑标识；
第3列表示会话sid；
第6列表示操作系统进程号；
第8列表示阻塞该会话的唯一逻辑标识，为空时表示无阻塞。
sid=20的会话被唯一逻辑标识为35的会话阻塞，即唯一逻辑标识为35的会话的sid=36,操作系统进程号21897，该行的第10列为空，即再也没有其他会话阻塞sid=598的会话。
也就是说，sid=36的会话就是数据库异常时的会话获取资源时阻塞的源头,那么sid=36的会话是什么用户什么程序什么机器发起，在执行什么SQL，进程的callstack是什么呢？所有这些信息，我们都可以在systemstate
dump中可以找到。
但是如果没有systemstate dump的话，怎么分析呢？
SID=36的会话，在操作系统上的进程号是21897。一个进程要么是前台进程（服务进程），要么是后台进程。
如果是后台进程，则我们可以在alert日志中，找到操作系统上进程号是553382对应的后台进程到底是什么！
此处引用小y的话
1)运维公式=快速收集系统即时状态信息+恢复业务
2)快速收集系统即时状态信息的目的是做RCA，根因分析，以便在大规模数据库运维中可以预防其他数据库也出现类似问题。
3)不定期做补丁分析，发现严重的BUG，提前预防。
4）技巧重要，原理更重要。
