Use Procwatcher to monitor Oracle database lock Contention

Database locking Contention is a common problem in practice. Serious Contention may cause overall database performance degradation, or even connection failure. In actual production, Contention is usually an urgent situation. It is a challenge for O & M personnel to quickly locate the error and solve the problem.
 
Contention has many causes, but in general there are two major types: one is the coordination fault between the background processes of the database, and the other is the waiting lock caused by application or user operations. The former is usually caused by Oracle bugs, and the latter is caused by immature design and development.
 
Contention locking is usually the relationship between processes, so the general method to solve the problem is often to use oradebug and the Trace monitoring tool carried by the operating system. It is often difficult for beginners to use these tools in emergencies. Therefore, Oracle released the Procwatcher tool script for unified lock analysis.
 
Procwatcher can be downloaded from the official MOS website. The corresponding Document ID is 459694.1. This article describes how to install and use Procwatcher.

-------------------------------------- Split line --------------------------------------

CentOS 5.2x86 install Oracle 11g R2 notes

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

-------------------------------------- Split line --------------------------------------

1. Environment Introduction

 

The operating system we use is Red Hat Linux 6.5.

 

[Root @ SimpleLinux/] # uname-r

2.6.32-431. el6.i686

 

2. Procwatcher installation and configuration

 

The script downloaded from MOS is a zip package. The author's version is prw_12.1.13.11.1.zip. In Linux, create a special directory for upload.

 

[Root @ SimpleLinux ~] # Cd/

[Root @ SimpleLinux/] # mkdir prw

[Root @ SimpleLinux/] # chown oracle: oinstall prw

[Root @ SimpleLinux/] # ls-l | grep prw

Drwxr-xr-x. 2 oracle oinstall 4096 Apr 30 prw

 

Use the sftp client to upload a zip package.

 

[Root @ SimpleLinux prw] # ls-l

Total 36

-Rw-r --. 1 root 36203 Apr 30 prw_12.1.13.11.1.zip

[Root @ SimpleLinux prw] # unzip prw_12.1.13.11.1.zip

Archive: prw_12.1.13.11.1.zip

Inflating: prw. sh

[Root @ SimpleLinux prw] # ls-l

Total 220

-Rw-r --. 1 root 36203 Apr 30 prw_12.1.13.11.1.zip

-Rwxr-xr-x. 1 root 187245 Nov 26 prw. sh

[Root @ SimpleLinux prw] # chown oracle: oinstall *

[Root @ SimpleLinux prw] # ls-l

Total 220

-Rw-r --. 1 oracle oinstall 36203 Apr 30 prw_12.1.13.11.1.zip

-Rwxr-xr-x. 1 oracle oinstall 187245 Nov 26 prw. sh

 

Prw. sh is the unique entity of the tool. You can execute scripts to perform operations.

To use Procwatcher, you need to check the operating system environment. First, you must run the/usr/bin and/bin directories in the user PATH.

 

 

[Oracle @ SimpleLinux ~] $ Env | grep PATH

PATH =/usr/lib/qt-3.3/bin:/usr/local/sbin:/usr/sbin: /sbin:/home/oracle/bin:/u01/app/oracle/bin
 
MODULEPATH =/usr/share/Modules/modulefiles:/etc/modulefiles

 

Environment variables include the standard $ ORACLE _ series.

 

[Oracle @ SimpleLinux ~] $ Env | grep ORA

ORACLE_SID = ora11g

ORACLE_BASE =/u01/app

ORACLE_HOME =/u01/app/oracle

 

The execution user must be an Oracle owner user. For cluster (RAC) or multiple oracle users, run the command as root.

System debugging tools must be included in the operating system. The list is as follows:

 

Linux-/usr/bin/gdb

HP-UX and HP Itanium-/opt/langtools/bin/gdb64 or/usr/ccs/bin/gdb64

Sun-/usr/bin/pstack

Ibm aix-/bin/procstack or/bin/dbx

HP Tru64-/bin/ladebug

 

 

[Oracle @ SimpleLinux ~] $ Rpm-qa | grep gdb

Gdb-7.2-60.el6_4.1.i686

Gdbm-1.8.0-36.el6.i686

Eggdbus-0.6-3.el6.i686

Gdbm-devel-1.8.0-36.el6.i686

 

Execute the script prw. sh to automatically collect process-level data. You can call the help parameter for help information.

 

[Oracle @ SimpleLinux prw] $./prw. sh help

 

Usage: prw. sh <verb>

Verbs are:

 

Start [all]-Start Procwatcher on local node, if 'all' is specified, start on all nodes
 
Stop [all]-Stop Procwatcher on local node, if 'all' is specified, stop on all nodes
 
Stat-Check the current status of Procwatcher

Pack-Package up Procwatcher files (on all nodes) to upload to support

Param-Check current Procwatcher parameters

Log [number]-See the last [number] lines of the procwatcher log file

Log [runtime]-See contiuous procwatcher log file info-use Cntrl-C to break

Help-What you are looking...

 

Parameter calling is relatively simple. You can learn how to use it through help information. Compared with calling at the oradebug and operating system level, it is much easier to use. You can use the stat parameter to view the current running status of the script.
 
 

[Oracle @ SimpleLinux prw] $./prw. sh stat

Wed Apr 30 09:25:46 CST 2014: procwatcher version: 12.1.13.11.1

Wed Apr 30 09:25:46 CST 2014: ### Parameters ###

Wed Apr 30 09:25:46 CST 2014: EXAMINE_CLUSTER = false

Wed Apr 30 09:25:46 CST 2014: EXAMINE_BG = true

Wed Apr 30 09:25:46 CST 2014: PRWPERM = 777

Wed Apr 30 09:25:46 CST 2014: RETENTION = 7

Wed Apr 30 09:25:46 CST 2014: WARNINGEMAIL =

Wed Apr 30 09:25:46 CST 2014: INTERVAL = 60

Wed Apr 30 09:25:46 CST 2014: THROTTLE = 5

Wed Apr 30 09:25:46 CST 2014: IDLECPU = 3

Wed Apr 30 09:25:46 CST 2014: SIDLIST =

Wed Apr 30 09:25:46 CST 2014: ### Advanced Parameters (non-default )###

Wed Apr 30 09:25:46 CST 2014: ### End Parameters ###

 

Wed Apr 30 09:25:46 CST 2014: Procwatcher is not running on local node SimpleLinux
 
Wed Apr 30 09:25:46 CST 2014: Procwatcher files are be written to:/prw

For more details, please continue to read the highlights on the next page:

1 2 3 4 5 6 Next Page