分析oracle的systemstate dump文件

dump systemstate产生的跟踪文件是从dump那一刻开始到dump任务完成之间一段事件内的系统内所有进程的信息。用来辅助分析系统很慢或HANG的原因。

产生systemsate dump的trace文件

方法1: 使用oradebug

SYS@orcl>oradebug setmypid
Statement processed.
SYS@orcl>oradebug unlimit;
Statement processed.
SYS@orcl>oradebug dump systemstate 10
Statement processed.

注:如果无法正常登录,尝试使用sqlplus -prelim进行登录

方法2: linux使用gdb,当数据库hang住无法登录的时候使用,红色部分为在gdb中输入的命令

oracle@adam-desktop:~$ gdb $ORACLE_HOME/bin/oracle 8429
GNU gdb (GDB) 7.0-ubuntu
Copyright (C) 2009 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law. Type "show copying"
and "show warranty" for details.
This GDB was configured as "i486-linux-gnu".
For bug reporting instructions, please see:
...
Reading symbols from /opt/ora10g/oracle/product/10.2.0/db_1/bin/oracle...done.
Attaching to program: /opt/ora10g/oracle/product/10.2.0/db_1/bin/oracle, process 8429
Reading symbols from /opt/ora10g/oracle/product/10.2.0/db_1/lib/libskgxp10.so...(no debugging symbols found)...done.
Loaded symbols for /opt/ora10g/oracle/product/10.2.0/db_1/lib/libskgxp10.so
Reading symbols from /opt/ora10g/oracle/product/10.2.0/db_1/lib/libhasgen10.so...done.
Loaded symbols for /opt/ora10g/oracle/product/10.2.0/db_1/lib/libhasgen10.so
Reading symbols from /opt/ora10g/oracle/product/10.2.0/db_1/lib/libskgxn2.so...(no debugging symbols found)...done.
Loaded symbols for /opt/ora10g/oracle/product/10.2.0/db_1/lib/libskgxn2.so
Reading symbols from /opt/ora10g/oracle/product/10.2.0/db_1/lib/libocr10.so...done.
Loaded symbols for /opt/ora10g/oracle/product/10.2.0/db_1/lib/libocr10.so
Reading symbols from /opt/ora10g/oracle/product/10.2.0/db_1/lib/libocrb10.so...done.
Loaded symbols for /opt/ora10g/oracle/product/10.2.0/db_1/lib/libocrb10.so
Reading symbols from /opt/ora10g/oracle/product/10.2.0/db_1/lib/libocrutl10.so...done.
Loaded symbols for /opt/ora10g/oracle/product/10.2.0/db_1/lib/libocrutl10.so
Reading symbols from /opt/ora10g/oracle/product/10.2.0/db_1/lib/libjox10.so...(no debugging symbols found)...done.
Loaded symbols for /opt/ora10g/oracle/product/10.2.0/db_1/lib/libjox10.so
Reading symbols from /opt/ora10g/oracle/product/10.2.0/db_1/lib/libclsra10.so...done.
Loaded symbols for /opt/ora10g/oracle/product/10.2.0/db_1/lib/libclsra10.so
Reading symbols from /opt/ora10g/oracle/product/10.2.0/db_1/lib/libdbcfg10.so...(no debugging symbols found)...done.
Loaded symbols for /opt/ora10g/oracle/product/10.2.0/db_1/lib/libdbcfg10.so
Reading symbols from /opt/ora10g/oracle/product/10.2.0/db_1/lib/libnnz10.so...(no debugging symbols found)...done.
Loaded symbols for /opt/ora10g/oracle/product/10.2.0/db_1/lib/libnnz10.so
Reading symbols from /lib/libaio.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib/libaio.so.1
Reading symbols from /lib/tls/i686/cmov/libdl.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib/tls/i686/cmov/libdl.so.2
Reading symbols from /lib/tls/i686/cmov/libm.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib/tls/i686/cmov/libm.so.6
Reading symbols from /lib/tls/i686/cmov/libpthread.so.0...(no debugging symbols found)...done.
[Thread debugging using libthread_db enabled]
Loaded symbols for /lib/tls/i686/cmov/libpthread.so.0
Reading symbols from /lib/tls/i686/cmov/libnsl.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib/tls/i686/cmov/libnsl.so.1
Reading symbols from /lib/tls/i686/cmov/libc.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib/tls/i686/cmov/libc.so.6
Reading symbols from /lib/ld-linux.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib/ld-linux.so.2
Reading symbols from /lib/tls/i686/cmov/libnss_compat.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib/tls/i686/cmov/libnss_compat.so.2
Reading symbols from /lib/tls/i686/cmov/libnss_nis.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib/tls/i686/cmov/libnss_nis.so.2
Reading symbols from /lib/tls/i686/cmov/libnss_files.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib/tls/i686/cmov/libnss_files.so.2
0xb77ca422 in __kernel_vsyscall ()
(gdb) print ksudss(10)
$1 = 214133340
(gdb) detach
Detaching from program: /opt/ora10g/oracle/product/10.2.0/db_1/bin/oracle, process 8429
(gdb) quit

分析trace文件
使用ass109.awk,下面是一个具体场景
sid 153, pid 19执行语句: update t set c = ‘a’ where n = 0;
sid 149, pid 22执行语句: update t set c = ‘a’ where n = 0;
在sid 153的session提交前,sid 149将处于等待行级TX锁状态,trace文件分析结果中的红色字体部分也表现出了这一点

oracle@adam-desktop:~$ awk -f ass109.awk /opt/ora10g/oracle/product/10.2.0/db_1/admin/orcl/udump/orcl_ora_8529.trc
Starting Systemstate 1
........................
Ass.Awk Version 1.0.9 - Processing /opt/ora10g/oracle/product/10.2.0/db_1/admin/orcl/udump/orcl_ora_8529.trc


System State 1
~~~~~~~~~~~~~~~~
1:
2: waiting for 'pmon timer' wait
3: waiting for 'rdbms ipc message' wait
4: waiting for 'rdbms ipc message' wait
5: waiting for 'rdbms ipc message' wait
6: waiting for 'rdbms ipc message' wait
7: waiting for 'rdbms ipc message' wait
8: waiting for 'smon timer' wait
9: waiting for 'rdbms ipc message' wait
10: waiting for 'rdbms ipc message' wait
11: waiting for 'rdbms ipc message' wait
12: waiting for 'rdbms ipc message' wait
13:
14:
15: waiting for 'jobq slave wait' wait
16: waiting for 'rdbms ipc message' wait
17: waiting for 'rdbms ipc message' wait
18: waiting for 'rdbms ipc message' wait
19: waiting for 'SQL*Net message from client' wait
20: waiting for 'Streams AQ: qmn coordinator idle wait' wait
21: for 'Streams AQ: waiting for time management or cleanup tasks' wait
22: waiting for 'enq: TX - row lock contention'[Enqueue TX-00030027-000001F0] wait
Cmd: Update
23: last wait for 'SQL*Net message from client'
29: waiting for 'Streams AQ: qmn slave idle wait' wait
Blockers
~~~~~~~~


Above is a list of all the processes. If they are waiting for a resource
then it will be given in square brackets. Below is a summary of the
waited upon resources, together with the holder of that resource.
Notes:
~~~~~
o A process id of '???' implies that the holder was not found in the
systemstate.

                    Resource Holder State
Enqueue TX-00030027-000001F0    19: waiting for 'SQL*Net message from client'

Object Names
~~~~~~~~~~~~
Enqueue TX-00030027-000001F0

参考资料:
工具: ass109.awk 分析 Oracle 的跟踪 文件
使用 oradebug 转储 systemstate / processstate
怎样用调试工具Dump Oracle系统状态

Comments are closed.