背景
oracle systemstate dump文件可以从全局显示进程及会话的等待详细信息结论
1, 通过AWK格式化即可显示每个进程等待哪些资源，也会显示持锁进程及会话的信息2, 通过查看原始SYSTEMSTATE DUMP文件可以查看哪些会话及进程在等待每个进程,也可以获知持锁进程及会话有相关信息
3 查看原始SYSTEMSTATE DUMP文件，可以通过关键字process x以及blocked by进行分析等待会话及持锁会话
4, 原始文件的owner及creator可以把树状结构的SO对象关联起来
5, so有进程SO,会话SO，队列SO，还有LATCH SO
6, 原始systemstate dump文件包含信息非常复杂，价值量巨大
测试
1，数据库版本SQL> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
2,模拟事务锁情况
会话1
SQL> select spid,pid from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum=1));
SPID PID
------------------------------------------------ ----------
13675 180
SQL> create table t_lock(a int);
Table created.
SQL> insert into t_lock values(1);
1 row created.
SQL> commit;
Commit complete.
会话2
SQL> select spid,pid from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum=1));
SPID PID
------------------------------------------------ ----------
13654 174
锁住
SQL> delete from t_lock;
3，在监控会话生成一个systemstate dump文件
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump systemstate 10
Statement processed.
SQL> oradebug tracefile_name
/oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_13950.trc
SQL>
[oracle@seconary ~]$ cd format_tool/
[oracle@seconary format_tool]$ awk -f ass109.awk /oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_13950.trc
Starting Systemstate 1
..............................................................................
...............................................................................
........................
Ass.Awk Version 1.0.9 - Processing /oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_13950.trc
System State 1
~~~~~~~~~~~~~~~~
1:
2: 0: waiting for 'pmon timer'
3: 0: waiting for 'VKTM Logical Idle Wait'
4: 0: waiting for 'rdbms ipc message'
5: 0: waiting for 'DIAG idle wait'
6: 0: waiting for 'rdbms ipc message'
7: 0: waiting for 'rdbms ipc message'
8: 0: waiting for 'DIAG idle wait'
9: 0: waiting for 'rdbms ipc message'
10: 0: waiting for 'rdbms ipc message'
11: 0: waiting for 'rdbms ipc message'
12: 0: waiting for 'rdbms ipc message'
13: 0: waiting for 'smon timer'
14: 0: waiting for 'rdbms ipc message'
15: 0: waiting for 'rdbms ipc message'
16: 0: waiting for 'rdbms ipc message'
17:
18:
19: 0: waiting for 'resmgr:cpu quantum'
20: 0: waiting for 'Streams AQ: qmn coordinator idle wait'
21: 0: waiting for 'Streams AQ: qmn slave idle wait'
22: 0: waited for 'Streams AQ: waiting for time management or cleanup tasks'
23: 0: waiting for 'jobq slave wait'
类似内容略
171:0: waiting for 'jobq slave wait'
172:0: waiting for 'jobq slave wait'
173:0: waiting for 'jobq slave wait'
174:0: waiting for 'enq: TX - row lock contention'[Enqueue TX-0098000F-0000037A] ---可见174进程即V$PROCESS的PID，为上述的会话2正在等待TX锁，等待命令为DELETE语句
Cmd: Delete
175:0: waiting for 'jobq slave wait'
176:0: waiting for 'jobq slave wait'
177:0: waiting for 'jobq slave wait'
178:0: waiting for 'jobq slave wait'
179:0: waiting for 'jobq slave wait'
180:0: waiting for 'SQL*Net message from client'
181:0: waiting for 'jobq slave 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-0098000F-0000037A 180: 0: waiting for 'SQL*Net message from client'
Object Names
~~~~~~~~~~~~
Enqueue TX-0098000F-0000037A
53477 Lines Processed.
[oracle@seconary format_tool]$
4,经过AWK格式化后，SYSTEMSTATE DUMP文件只能看到每个进程在等待什么，但如果想每个进程，都有哪些进程或会话在等待这个进程，还要是看原始的SYSTEMSTATE DUMP文件
所以我们只要在原始的SYSTEMSTATE DUMP文件查看process 180，因为这个会话是持锁会话，而174进程的会话需要等待它
如下为获取的到的原始文件部分
PROCESS 180: --180号进程，对应V$PROCESS的PID
----------------------------------------
SO: 0xdd6548c0, type: 2, owner: ( nil), flag: INIT/-/-/0x00 if: 0x3 c: 0x3 --owner为nil说明它是顶层SO
proc=0xdd6548c0, name=process, file=ksu.h LINE:11459, pg=0
(process) Oracle pid:180, ser:4, calls cur/top: (nil)/0xd27e7f20
flags : (0x0) -
flags2: (0x0), flags3: (0x0)
intr error: 0, call error: 0, sess error: 0, txn error 0
intr queue: empty
ksudlp FALSE at location: 0
(post info) last post received: 134 0 2
last post received-location: ksl2.h LINE:2165 ID:kslpsr
last process to post me: dd5a8e80 1 6
last post sent: 0 0 26
last post sent-location: ksa2.h LINE:282 ID:ksasnd
last process posted by me: dd5a8e80 1 6
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: 0xdd9a34d0
O/S info: user: oracle, term: UNKNOWN, ospid: 13675
OSD pid info: Unix process pid: 13675, image: oracle@seconary (TNS V1-V3) --UNIX PROCESS PID 13675对应V$PROCESS的SPID
Dump of memory from 0x00000000DD535A20 to 0x00000000DD535C28
0DD535A20 00000000 00000000 00000000 00000000 [................]
Repeat 31 times
0DD535C20 00000000 00000000 [........]
(FOB) flags=2050 fib=0xd8f353a0 incno=0 pending i/o cnt=0
fname=/oracle/oradata/guowang/users01.dbf
fno=4 lblksz=8192 fsiz=107360
(FOB) flags=2050 fib=0xd8f34188 incno=8 pending i/o cnt=0
fname=/oracle/oradata/guowang/system01.dbf
fno=1 lblksz=8192 fsiz=99840
----------------------------------------
SO: 0xdc808b40, type: 4, owner: 0xdd6548c0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3 --OWNER为0xdd6548c0，说明这个SO是孩子级的SO,它是180进程的子级SO
proc=0xdd6548c0, name=session, file=ksu.h LINE:11467, pg=0
(session) sid: 330 ser: 501 trans: 0xd98335c0, creator: 0xdd6548c0 --SID为V$SESSION的SID，为330，CREATOR与上级的PROCESS 180对应
flags: (0x45) USR/- flags_idl: (0x0) -/-/-/-/-/-
flags2: (0x40008) -/-
DID: , short-term DID:
txn branch: (nil)
oct: 0, prv: 0, sql: (nil), psql: 0xde1653d0, user: 84/SCOTT
ksuxds FALSE at location: 0
service name: SYS$USERS
client details:
O/S info: user: oracle, term: pts/3, ospid: 13674
machine: seconary program: sqlplus@seconary (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
Current Wait Stack: ---这会显示当前会话在等待什么事件
0: waiting for 'SQL*Net message from client'
driver id=0x62657100, #bytes=0x1, =0x0
wait_id=100 seq_num=101 snap_id=1
wait times: snap=1 min 25 sec, exc=1 min 25 sec, total=1 min 25 sec
wait times: max=infinite, heur=1 min 25 sec
wait counts: calls=0 os=0
in_wait=1 iflags=0x1a0
There are 1 sessions blocked by this session. --显示哪些会话在等待当前的会话及所属的进程
Dumping one waiter:
inst: 1, sid: 145, ser: 643 ---显示等待的会话及会话序列号
wait event: 'enq: TX - row lock contention' --显示等待事件信息
p1: 'name|mode'=0x54580006 --等待的P1,P2,P3
p2: 'usn<<16 | slot'=0x98000f
p3: 'sequence'=0x37a
row_wait_obj#: 74375, block#: 98891, row#: 0, file# 4 --显示具体等待的对象及数据块和行号及文件
min_blocked_time: 79 secs, waiter_cache_ver: 823
个人简介
8年oracle从业经验，具备丰富的oracle技能，目前在国内北京某专业oracle服务公司从事高级技术顾问。
服务过的客户：
中国电信
中国移动
中国联通
中国电通
国家电网
四川达州商业银行
湖南老百姓大药房
山西省公安厅
中国邮政
北京302医院
河北廊坊新奥集团公司
项目经验:
中国电信3G项目AAA系统数据库部署及优化
中国联通4G数据库性能分析与优化
中国联通4G数据库性能分析与优化
中国联通CRM数据库性能优化
中国移动10086电商平台数据库部署及优化
湖南老百姓大药房ERR数据库sql优化项目
四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化
四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化
北京高铁信号监控系统RAC数据库部署及优化
河南宇通客车数据库性能优化
中国电信电商平台核心采购模块表模型设计及优化
中国邮政储蓄系统数据库性能优化及sql优化
北京302医院数据库迁移实施
河北廊坊新奥data guard部署及优化
山西公安厅身份证审计数据库系统故障评估
国家电网上海灾备项目4 node rac+adg
贵州移动crm及客服数据库性能优化项目
贵州移动crm及客服务数据库sql审核项目
深圳穆迪软件有限公司数据库性能优化项目
贵州移动crm及客服数据库性能优化项目
贵州移动crm及客服务数据库sql审核项目
深圳穆迪软件有限公司数据库性能优化项目
联系方式：
手机：18201115468
qq : 305076427
qq微博: wisdomone1
新浪微博：wisdomone9
qq群：275813900
itpub博客名称：wisdomone1 http://blog.itpub.net/9240380/
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-1814960/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-1814960/
162
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
