Monday, December 15, 2008

RAC Diagnostics Script - racdiag.sql

Oracle Support provides several tools for RAC diagnostics.

One of them is racdiag.sql available through metalink Note: 135714.1 Script to Collect RAC Diagnostic Information (racdiag.sql)

This script is broken up into SQL statements that can be used individually. Each SQL statement adds information to help in debugging RAC hangs/severe performance scenerios.

The script provide the following information:

- INITIALIZATION PARAMETERS (Non Default)
- WAITING SESSIONS
- EVENT PARAMETER LOOKUP
- GES LOCK BLOCKERS
- GES LOCK WAITERS
- LOCAL ENQUEUES
- LATCH HOLDERS
- LATCH STATS
- No Wait Latches
- GLOBAL CACHE CR PERFORMANCE
- GLOBAL CACHE LOCK PERFORMANCE
- RESOURCE USAGE
- DLM TRAFFIC INFORMATION
- DLM MISC
- LOCK CONVERSION DETAIL
- TOP 10 WRITE PINGING/FUSION OBJECTS
- TOP 10 READ PINGING/FUSION OBJECTS
- TOP 10 FALSE PINGING OBJECTS
- TOP 10 WAIT EVENTS ON SYSTEM
- SESSION/PROCESS REFERENCE
- SYSTEM STATISTICS
- CURRENT SQL FOR WAITING SESSIONS
- WAITING SESSIONS
Oracle Support provides several tools for RAC diagnostics.




This script is broken up into different SQL statements that can be used individually. Each SQL statement adds information to help in debugging an RAC hang/severe performance scenerio.


The script provide the following information:


- WAITING SESSIONS

- EVENT PARAMETER LOOKUP

- GES LOCK BLOCKERS

- GES LOCK WAITERS

- LOCAL ENQUEUES

- LATCH HOLDERS

- LATCH STATS

- No Wait Latches

- GLOBAL CACHE CR PERFORMANCE

- GLOBAL CACHE LOCK PERFORMANCE

- RESOURCE USAGE

- DLM TRAFFIC INFORMATION

- DLM MISC

- LOCK CONVERSION DETAIL

- TOP 10 WRITE PINGING/FUSION OBJECTS

- TOP 10 READ PINGING/FUSION OBJECTS

- TOP 10 FALSE PINGING OBJECTS

- INITIALIZATION PARAMETERS

- TOP 10 WAIT EVENTS ON SYSTEM

- SESSION/PROCESS REFERENCE

- SYSTEM STATISTICS

- CURRENT SQL FOR WAITING SESSIONS

- WAITING SESSIONS: