Monitoring Oracle AQ message systems with SQL based User defined Metrics in Oracle Enterprise Manager

By default Oracle Advanced Queueing (AQ) provides monitoring views (in this posting we use the V_$AQ view) to monitor things like

• Number of waiting messages per queue
• Number of expired messages per queue
• Number of messages on status ready per queue
• Average waiting time per queue
• Total waiting time per queue

In large messaging systems it is important to detect possible delays and bottlenecks at an early stage. The above metrics can help you with this. Of course it is possible to write a custom monitoring application to make monitoring information accessible, but in most large environments it makes more sense to use a dedicated monitoring product that is able to monitor your complete application environment and provides a single monitoring console.

Oracle Enterprise Manager (OEM) is such a tool. OEM provides functionality to specify User-defined SQL-based metrics that allows you to easily integrate your own SQL monitoring scripts in OEM together with defined thresholds on the metrics, OEM will trigger alert notifications. The SQL queries below can be used for creating SQL based User-defined metrics in OEM to monitor specific queues on specific aspects:

-- Average wait metric
select dbaq.name, vaq.average_wait from sys.dba_queues dbaq, sys.V_$AQ vaq where dbaq.name in ('SOME_Q_A', 'SOME_Q_B') and dbaq.qid = vaq.qid

-- Total wait metric
select dbaq.name, vaq.total_wait from sys.dba_queues dbaq, sys.V_$AQ vaq where dbaq.name in ('SOME_Q_A', 'SOME_Q_B') and dbaq.qid = vaq.qid

-- Expired messages metric
select dbaq.name, vaq.expired from sys.dba_queues dbaq, sys.V_$AQ vaq where dbaq.name in ('SOME_Q_A', 'SOME_Q_B') and dbaq.qid = vaq.qid

-- Ready messages metric
select dbaq.name, vaq.ready from sys.dba_queues dbaq, sys.V_$AQ vaq where dbaq.name in ('SOME_Q_A', 'SOME_Q_B') and dbaq.qid = vaq.qid;

Notes:

• The queries are Two-Column queries and the Metric type is Number
• Determine a correct value for Consecutive Occurrences Preceding Notification option to prevent false positives
• Set a correct interval. Keep in mind that monitoring causes some minor overhead...so don't set the interval too small.. 30 minutes should be sufficient in most scenario's