6、结果分析——分析结果信息
Prowatcher的一个重要功能在于分析。经过分析后,它会自动将分析结果作为一系列的结果文件,直接告诉我们哪个进程出现问题。所以我们对于结果分析,可以直接从这个部分入手。
Latchholder表示几次采样过程中,发现的严重latch持有者信息。当前我们实验过程进行的是lock的演示,没有latch过程。
latch holder信息:
[Oracle@SimpleLinux PRW_DB_ora11g]$ ls -l | grep pw_latchholder_ora11g_04-30-14.out
-rwxrwxrwx. 1 oracle oinstall 526 Apr 30 09:40 pw_latchholder_ora11g_04-30-14.out
[oracle@SimpleLinux PRW_DB_ora11g]$ cat pw_latchholder_ora11g_04-30-14.out
################################################################################
Procwatcher latchholder report
No rows found at Wed Apr 30 09:32:37 CST 2014
No rows found at Wed Apr 30 09:33:31 CST 2014
No rows found at Wed Apr 30 09:34:31 CST 2014
No rows found at Wed Apr 30 09:35:33 CST 2014
No rows found at Wed Apr 30 09:36:34 CST 2014
No rows found at Wed Apr 30 09:37:33 CST 2014
No rows found at Wed Apr 30 09:38:32 CST 2014
No rows found at Wed Apr 30 09:39:34 CST 2014
No rows found at Wed Apr 30 09:40:32 CST 2014
No rows found at Wed Apr 30 09:41:33 CST 2014
可以看到,在执行过程的时间内,脚本进行多次的latch信息监测收集。
Lock锁定信息:
[oracle@SimpleLinux PRW_DB_ora11g]$ cat pw_lock_ora11g_04-30-14.out
################################################################################
Procwatcher lock report
################################################################################
SQL> SQL> V LOCK Snapshot Taken At: Wed Apr 30 09:32:35 CST 2014
PROC PROC TY ID1 ID2 LMODE REQUEST BLOCK
-------------------- -------------------- -- ---------- ---------- ---------- ---------- ----------
PROC 1988 INST ora11g TX 655382 801 6 0 1
PROC 2012 INST ora11g TX 655382 801 0 6 0
Elapsed: 00:00:00.47
################################################################################
(篇幅原因,有省略……)
SQL> SQL> V LOCK Snapshot Taken At: Wed Apr 30 09:41:31 CST 2014
PROC PROC TY ID1 ID2 LMODE REQUEST BLOCK
-------------------- -------------------- -- ---------- ---------- ---------- ---------- ----------
PROC 1988 INST ora11g TX 655382 801 6 0 1
PROC 2012 INST ora11g TX 655382 801 0 6 0
Elapsed: 00:00:00.32
在若干次的监测中,发现了锁定会话进程,编号为1988和2012。而且锁定类型均为独占锁类型,对相同的对象形成锁定。
Session waiting会话等待。
################################################################################
Procwatcher sessionwait report
################################################################################
SQL> SQL> V SESSIONWAIT Snapshot Taken At: Wed Apr 30 09:32:34 CST 2014
PROC INST STATE EVENT P1 P2 P3 SEC
-------------------- --------------- ---------- ------------------------------ ---------- ---------- ---------- ----------
PROC 2012 INST ora11g WAITING enq: TX - row lock contention 1415053318 655382 801 68
Elapsed: 00:00:00.14
################################################################################
SQL> SQL> V SESSIONWAIT Snapshot Taken At: Wed Apr 30 09:33:29 CST 2014
PROC INST STATE EVENT P1 P2 P3 SEC
-------------------- --------------- ---------- ------------------------------ ---------- ---------- ---------- ----------
PROC 2012 INST ora11g WAITING enq: TX - row lock contention 1415053318 655382 801 123
Elapsed: 00:00:00.09
################################################################################
SQL> SQL> V SESSIONWAIT Snapshot Taken At: Wed Apr 30 09:34:29 CST 2014
PROC INST STATE EVENT P1 P2 P3 SEC
-------------------- --------------- ---------- ------------------------------ ---------- ---------- ---------- ----------
PROC 2012 INST ora11g WAITING enq: TX - row lock contention 1415053318 655382 801 183
Elapsed: 00:00:00.02
上次等待信息收集,均为2012编号进程在等待中,锁类型为row lock contention。
等待锁定waitchain信息。
################################################################################
Procwatcher waitchains report
################################################################################
SQL> SQL> V WAITCHAINS (top 100 rows) Snapshot Taken At: Wed Apr 30 09:32:31 CST 2014
PROC 1988 : Current Process: 1988 SID: 35 SER#: 45 INST ora11g INST #: 1
PROC 1988 : Blocking Process: <none> from Instance Number of waiters: 1
PROC 1988 : Final Blocking Process: <none> from Instance Program:
PROC 1988 : Wait Event: SQL*Net message from client P1: 1413697536 P2: 1 P3: 0
PROC 1988 : Seconds in Wait: 137 Seconds Since Last Wait:
PROC 1988 : Wait Chain: 1: 'SQL*Net message from client'<='enq: TX - row lock contention'
PROC 1988 : Blocking Wait Chain: <none>
------------------------------
PROC 2012 : Current Process: 2012 SID: 42 SER#: 7 INST ora11g INST #: 1
PROC 2012 : Blocking Process: 1988 from Instance 1 Number of waiters: 0
PROC 2012 : Final Blocking Process: 1988 from Instance 1 Program: oracle@SimpleLinux.localdomain
PROC 2012 : Wait Event: enq: TX - row lock contention P1: 1415053318 P2: 655382 P3: 801
PROC 2012 : Seconds in Wait: 63 Seconds Since Last Wait:
PROC 2012 : Wait Chain: 1: 'SQL*Net message from client'<='enq: TX - row lock contention'
PROC 2012 : Blocking Wait Chain: <none>
------------------------------
Elapsed: 00:00:00.15
----------blkr----------
Wed Apr 30 09:32:41 CST 2014: Suspected final blocker is: Process: 1988 SID: 35 SER#: 45 INST ora11g INST #: 1
-------end blkr---------
ora11g Waitchains SessionCount:2-Instance:1
Oracle信息收集过程中,脚本已经发现2012和1988两个会话的锁定关系。分别列出之间的等待关系和锁定关系。2012会话被1988会话锁定。