ODC Appreciation Day: Procwatcher

odc

The ODC Appreciation Day was proposed by Tim Hall and you can find more information about it here.

Procwatcher is a tool to examine and monitor Oracle database and/or clusterware processes at an interval. The tool will collect stack traces of these processes using Oracle tools like oradebug short_stack and/or OS debuggers like pstack, gdb, dbx, or ladebug and collect SQL data if specified.

You can download Procwatcher from MOS or run it from within the latest Trace File Analyzer.

Procwatcher is Ideal for:

  • Session level hangs or severe contention in the database/instance. See Note: 1352623.1
  • Severe performance issues. See Note: 1352623.1
  • Instance evictions and/or DRM timeouts.
  • Clusterware or DB processes stuck or consuming high CPU (must set EXAMINE_CLUSTER=true and run as root for clusterware processes)
  • ORA-4031 and SGA memory management issues. (Set sgamemwatch=diag or sgamemwatch=avoid4031 (not the default). See Note: 1355030.1
  • ORA-4030 and DB process memory issues. (Set USE_SQL=true and process_memory=y).
  • RMAN slowness/contention during a backup. (Set USE_SQL=true and rmanclient=y).

 

Procwatcher is Not Ideal for…

  • Node evictions/reboots. In order to troubleshoot these you would have to enable Procwatcher for a process(es) that are capable of rebooting the machine. If the OS debugger suspends the processs for too long *that* could cause a reboot of the machine. I would only use Procwatcher for a node eviction/reboot if the problem was reproducing on a test system and I didn’t care of the node got rebooted. Even in that case the INTERVAL would need to be set low (30) and many options would have to be turned off to get the cycle time low enough (EXAMINE_BG=false, USE_SQL=false, probably removing additional processes from the CLUSTERPROCS list).
  • Non-severe database performance issues. AWR/ADDM/statspack are better options for this…
  • Most installation or upgrade issues. We aren’t getting data for this unless we are at a stage of the installation/upgrade where key processes are already started.

 

For more details please check:

MOS note Procwatcher: Script to Monitor and Examine Oracle DB and Clusterware Processes [ID 459694.1]

And this blog from Tanel: https://blog.tanelpoder.com/2013/05/27/debugger-dangers-part-2/

 

 

 

Advertisements

Author: reguchi

Oracle OCP 11g/12c, Oracle OCE Exadata, MySQL DBA, RHCE, technology geek, homebrewer, dad

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s