Procwatcher是一种间隔检查和监视Oracle数据库或集群件进程的工具。该工具将使用Oracle工具（如oradebug short_stack）或OS调试器（如pstack，gdb，dbx或ladebug）收集这些进程的堆栈跟踪，并在指定时收集SQL数据。
可通过MOS文档：459694.1 下载
[root@host1 software]# cd /
[root@host1 /]# mkdir prw
[root@host1 /]# chown oracle:oinstall prw
[root@host1 /]# cd prw
[root@host1 prw]# unzip prw_12.2.18.5.0.zip
Archive: prw_12.2.18.5.0.zip
inflating: prw.sh
[root@host1 prw]# chown oracle:oinstall prw.sh
如果要监视集群件，则必须在平台上安装相关的OS调试程序; PRW寻找：
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
因为当前数据库安装在Linux系统上，使用Procwatcher是需要操作系统环境检查，首先要求执行用户PATH中包括/usr/bin和/bin目录。
[oracle@host1 ~]$ env | grep PATH
LD_LIBRARY_PATH=/u01/App/oracle/product/18.1.0/dbhome_1/lib:/u01/app/oracle/product/18.1.0/dbhome_1/oracm/lib:/lib:/usr/lib:/usr/local/lib
PATH=.:/usr/local/JAVA/bin:/usr/lib/oracle/11.2/client64/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin:/home/oracle/bin:/u01/app/oracle/product/18.1.0/dbhome_1/bin:/bin:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
CLASSPATH=/u01/app/oracle/product/18.1.0/dbhome_1/JRE:/u01/app/oracle/product/18.1.0/dbhome_1/jlib:/u01/app/oracle/product/18.1.0/dbhome_1/rdbms/jlib:/u01/app/oracle/product/18.1.0/dbhome_1.NETwork/jlib
环境变量中包括标准的$ORACLE_系列。
[oracle@host1 ~]$ env | grep ORA
ORACLE_UNQNAME=pdbcndba_p
ORA_NLS11=/u01/app/oracle/product/18.1.0/dbhome_1/nls/data
ORACLE_SID=cndba
ORACLE_BASE=/u01/app/oracle
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle/product/18.1.0/dbhome_1
执行用户要求是Oracle owner用户。如果是cluster（RAC）或者多个oracle user的情况，可以使用root来执行。
操作系统中必须包括系统调试工具。列表如下：
[oracle@host1 ~]$ rpm -qa | grep gdb
gdbm-1.10-8.el7.x86_64
gdb-7.6.1-94.el7.x86_64
[oracle@host1 prw]$ ./prw.sh start
Mon Jul 23 17:54:30 CST 2018: Starting Procwatcher as user oracle
Mon Jul 23 17:54:30 CST 2018: Thank you for using Procwatcher. :-)
Mon Jul 23 17:54:30 CST 2018: Please add a comment to Oracle Support Note 459694.1
Mon Jul 23 17:54:30 CST 2018: if you have any comments, suggestions, or issues with this tool.
Procwatcher files will be written to: /prw
Mon Jul 23 17:54:30 CST 2018: Started Procwatcher
[oracle@host1 prw]$ ./prw.sh stat
Mon Jul 23 17:41:12 CST 2018: PROCWATCHER VERSION: 12.2.18.5.0
Mon Jul 23 17:41:12 CST 2018: ### Parameters ###
Mon Jul 23 17:41:12 CST 2018: Procwatcher Directory (PRWDIR): /prw
Mon Jul 23 17:41:12 CST 2018: EXAMINE_CLUSTER=false
Mon Jul 23 17:41:12 CST 2018: EXAMINE_BG=true
Mon Jul 23 17:41:12 CST 2018: PRWPERM=744
Mon Jul 23 17:41:12 CST 2018: RETENTION=7
Mon Jul 23 17:41:12 CST 2018: WARNINGEMAIL=
Mon Jul 23 17:41:12 CST 2018: INTERVAL=60
Mon Jul 23 17:41:12 CST 2018: THROTTLE=5
Mon Jul 23 17:41:12 CST 2018: IDLECPU=3
Mon Jul 23 17:41:12 CST 2018: SIDLIST=
Mon Jul 23 17:41:12 CST 2018: ### Advanced Parameters (non-default) ###
Mon Jul 23 17:41:12 CST 2018: ### End Parameters ###
Mon Jul 23 17:41:12 CST 2018: Procwatcher is not running on local node host1
Mon Jul 23 17:41:12 CST 2018: Procwatcher files are be written to: /prw
[oracle@host1 prw]$ ./prw.sh stop
Mon Jul 23 17:57:49 CST 2018: Stopping Procwatcher
Mon Jul 23 17:57:49 CST 2018: Checking for stray debugging sessions...(waiting 1 second)
Mon Jul 23 17:57:50 CST 2018: No debugging sessions found, all good, exiting...
Mon Jul 23 17:57:50 CST 2018: Thank you for using Procwatcher. :-)
Mon Jul 23 17:57:50 CST 2018: Please add a comment to Oracle Support Note 459694.1
Mon Jul 23 17:57:50 CST 2018: if you have any comments, suggestions, or issues with this tool.
Mon Jul 23 17:57:50 CST 2018: Procwatcher Stopped
[oracle@host1 prw]$ ./prw.sh stat
Mon Jul 23 17:56:07 CST 2018: PROCWATCHER VERSION: 12.2.18.5.0
Mon Jul 23 17:56:07 CST 2018: ### Parameters ###
Mon Jul 23 17:56:07 CST 2018: Procwatcher Directory (PRWDIR): /prw
Mon Jul 23 17:56:07 CST 2018: EXAMINE_CLUSTER=false
Mon Jul 23 17:56:07 CST 2018: EXAMINE_BG=true
Mon Jul 23 17:56:07 CST 2018: PRWPERM=744
Mon Jul 23 17:56:07 CST 2018: RETENTION=7
Mon Jul 23 17:56:07 CST 2018: WARNINGEMAIL=
Mon Jul 23 17:56:07 CST 2018: INTERVAL=60
Mon Jul 23 17:56:07 CST 2018: THROTTLE=5
Mon Jul 23 17:56:07 CST 2018: IDLECPU=3
Mon Jul 23 17:56:07 CST 2018: SIDLIST=
Mon Jul 23 17:56:07 CST 2018: ### Advanced Parameters (non-default) ###
Mon Jul 23 17:56:07 CST 2018: ### End Parameters ###
Mon Jul 23 17:56:07 CST 2018: Procwatcher is currently running on local node host1
Mon Jul 23 17:56:07 CST 2018: Procwatcher files are be written to: /prw
Mon Jul 23 17:56:07 CST 2018: There are 0 concurrent debug sessions running...
执行脚本prw.sh，可以自动的进行进程级别的数据收集。可以调用参数help来进行帮助信息。
[oracle@host1 ~]$ cd /prw/
[oracle@host1 prw]$ ./prw.sh help
Mon Jul 23 17:38:56 CST 2018: Building default prwinit.ora at /prw/prwinit.ora
Usage: prw.sh <verb>
TFA Syntax: tfactl prw <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
deinstall [clean] - Stop Procwatcher and remove the Procwatcher directory (clean)
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
init [directory] - Create a default prwinit.ora file
dir - Display Procwatcher directory
help - What you are looking at...
从Procwatcher版本12.1.14.12开始，这些参数在Procwatcher目录的prwinit.ora文件中设置。如果没有看到prwinit.ora文件，则可以在集群环境中使用“prw.sh init ”或“prw.sh deploy”生成一个文件。
[oracle@host1 prw]$ cat prwinit.ora
#
# PROCWATCHER PARAMETERS - REVIEW CAREFULLY:
#
######################### CONFIG SETTINGS #############################
# Set EXAMINE_CLUSTER variable if you want to examine clusterware processes (default is false - or set to true):
# Note that if this is set to true you must deploy/run procwatcher as root unless using oracle restart
EXAMINE_CLUSTER=false
# Set EXAMINE_BG variable if you want to examine all BG processes (default is true - or set to false):
EXAMINE_BG=true
# Set permissions on Procwatcher files and directories (default: 744):
PRWPERM=744
# Set RETENTION variable to the number of days you want to keep historical procwatcher data (default: 7)
RETENTION=7
……
会话1执行更新表
[oracle@host1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 24 15:02:45 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> create table test (id number);
Table created.
SQL> insert into test values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> update test set id=4;
1 row updated.
会话2更新相同的表模拟阻塞
SQL> update test set id=4;
1 row updated.
会话2 hang住
现在演示如何使用Procwatcher对此进行故障排除，将启动Procwatcher：
[oracle@host1 prw]$ ./prw.sh start
现在将检查Procwatcher日志以确保它正在收集数据：
[oracle@host1 prw]$ cat prw_host1.log
…...
Tue Jul 24 15:02:43 CST 2018: Collecting process specific SQLs for SID cndba
Tue Jul 24 15:02:46 CST 2018: ..SQL: Running SQLsqltext.sql on SID cndba
Tue Jul 24 15:02:46 CST 2018: ..SQL: Running SQLash.sql on SID cndba
Tue Jul 24 15:02:49 CST 2018: SQL collection complete after 17 seconds (6 SQLs - average seconds: 2)
Tue Jul 24 15:02:49 CST 2018: Was going to debug process 13740 but it vanished...
Tue Jul 24 15:02:50 CST 2018: Getting stack for ora_fg_cndba 13918 using short_stack in /prw/PRW_DB_cndba/prw_ora_fg_cndba_13918_07-24-18.out
Tue Jul 24 15:02:51 CST 2018: Getting stack for ora_pmon_cndba 4479 using short_stack in /prw/PRW_DB_cndba/prw_ora_pmon_cndba_4479_07-24-18.out
Tue Jul 24 15:02:51 CST 2018: Getting stack for ora_lgwr_cndba 4517 using short_stack in /prw/PRW_DB_cndba/prw_ora_lgwr_cndba_4517_07-24-18.out
Tue Jul 24 15:02:52 CST 2018: Getting stack for ora_ckpt_cndba 4519 using short_stack in /prw/PRW_DB_cndba/prw_ora_ckpt_cndba_4519_07-24-18.out
Tue Jul 24 15:02:55 CST 2018: Getting stack for ora_smon_cndba 4521 using short_stack in /prw/PRW_DB_cndba/prw_ora_smon_cndba_4521_07-24-18.out
Tue Jul 24 15:02:57 CST 2018: WARNING: CPU is 0 % idle - less than 3 % idle, sleeping 5 seconds
Tue Jul 24 15:03:03 CST 2018: Getting stack for ora_arc0_cndba 4569 using short_stack in /prw/PRW_DB_cndba/prw_ora_arc0_cndba_4569_07-24-18.out
Tue Jul 24 15:03:03 CST 2018: Getting stack for ora_arc1_cndba 4573 using short_stack in /prw/PRW_DB_cndba/prw_ora_arc1_cndba_4573_07-24-18.out
Tue Jul 24 15:03:04 CST 2018: Getting stack for ora_arc2_cndba 4575 using short_stack in /prw/PRW_DB_cndba/prw_ora_arc2_cndba_4575_07-24-18.out
Tue Jul 24 15:03:07 CST 2018: Getting stack for ora_arc3_cndba 4577 using short_stack in /prw/PRW_DB_cndba/prw_ora_arc3_cndba_4577_07-24-18.out
Tue Jul 24 15:03:10 CST 2018: Waiting for these debug procs to finish:
ksh /prw/prw.sh shortstack start 4575 oracle cndba /u01/app/oracle/product/12.2.0/db_1 /prw/PRW_DB_cndba/prw_ora_arc2_cndba_4575_07-24-18
ksh /prw/prw.sh shortstack start 4577 oracle cndba /u01/app/oracle/product/12.2.0/db_1 /prw/PRW_DB_cndba/prw_ora_arc3_cndba_4577_07-24-18
……
Preacher的一个重要功能在于分析。经过分析后，它会自动将分析结果作为一系列的结果文件，直接告诉我们哪个进程出现问题。所以我们对于结果分析，可以直接从这个部分入手。
[oracle@host1 prw]$ cd PRW_DB_cndba/
[oracle@host1 PRW_DB_cndba]$ ll
total 96
-rwxr--r--. 1 oracle oinstall 3618 Jul 24 15:03 prw_ora_arc0_cndba_4569_07-24-18.out
-rwxr--r--. 1 oracle oinstall 3618 Jul 24 15:03 prw_ora_arc1_cndba_4573_07-24-18.out
-rwxr--r--. 1 oracle oinstall 3618 Jul 24 15:03 prw_ora_arc2_cndba_4575_07-24-18.out
-rwxr--r--. 1 oracle oinstall 3618 Jul 24 15:03 prw_ora_arc3_cndba_4577_07-24-18.out
-rwxr--r--. 1 oracle oinstall 4281 Jul 24 15:03 prw_ora_ckpt_cndba_4519_07-24-18.out
-rwxr--r--. 1 oracle oinstall 5540 Jul 24 15:02 prw_ora_fg_cndba_13740_07-24-18.out
-rwxr--r--. 1 oracle oinstall 17960 Jul 24 15:02 prw_ora_fg_cndba_13918_07-24-18.out
-rwxr--r--. 1 oracle oinstall 4281 Jul 24 15:03 prw_ora_lgwr_cndba_4517_07-24-18.out
-rwxr--r--. 1 oracle oinstall 4355 Jul 24 15:02 prw_ora_pmon_cndba_4479_07-24-18.out
-rwxr--r--. 1 oracle oinstall 3702 Jul 24 15:03 prw_ora_smon_cndba_4521_07-24-18.out
-rwxr--r--. 1 oracle oinstall 250 Jul 24 15:02 pw_latchholder_cndba_07-24-18.out
-rwxr--r--. 1 oracle oinstall 1545 Jul 24 15:02 pw_lock_cndba_07-24-18.out
-rwxr--r--. 1 oracle oinstall 1547 Jul 24 15:02 pw_sessionwait_cndba_07-24-18.out
-rwxr--r--. 1 oracle oinstall 1714 Jul 24 15:08 pw_sqltimings_cndba_07-24-18.out
-rwxr--r--. 1 oracle oinstall 4904 Jul 24 15:08 pw_waitchains_cndba_07-24-18.out
因此，对于上述所有数据，我们应该拥有所需的一切（大多数情况下）来解决争用问题。另外一个好处是，Procwatcher将自动收集这些数据而无需任何用户干预（一旦Procwatcher启动）。
京公网安备 11010802035086号