Sqlmon

SQL Monitoring For Oracle Databases

Sqlmon is a database utility that is meant to provide short term interval monitoring of SQL statements to allow the DBA to identify poorly performing SQL. This utility now tracks the most inefficient SQL statements that have been executed by evaluating the Logical I/O rate vs. the number of rows returned (previously it used executions and logical I/Os).  For SQL statements that have returned no rows, the number of executions is used. Any statement that generates more than 20 LIOs per row returned is captured (thanks to Cary Millsap for pointing this out more clearly in his OAUG sessions).

The information collected can help target application level tuning and provide a "workload" picture of database activity.  Because of the volume of information, and the relatively high impact of the information collection, we typically recommend the use of this tool for brief periods of time, as in several days or during peak workload periods.

Installation instructions and all scripts are provided. Sqlmon now supports a config file so using it with CVS across multiple nodes is much easier.

Utility Source: sqlmon.tar

Example Reports:

Sqlmon Summary Report

Sqlmon Detail Report