使用Procwatcher监控Oracle数据库锁定Contention(5)

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会话锁定。

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/8cf754c1597c8509f1cefe9ec07fec7b.html