Tuesday, November 10, 2009

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

4 comments:

OracleFellow said...

Interesting, I too am working in Utrecht on a project for Oracle:-)

Tani said...

You have defined very well Metrics but i like the most that you have mentioned in notes like:* 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.

sap testing

Rosemarie raƱa said...

All the contents you provide in post is too good and useful . I will keep it in mind, thanks for sharing the information keep updating, looking forward for more posts.Oracle Goldengate

Anonymous said...

Hi! I am a digital marketer. The previous seo guy working for my client left a spammy comment at your blog with the username rosemarie rana which links to my client's site.
Such comments are or might cause serious damage to my client's site with respect to SEO which is why I am request you here by to remove it asap.
If you don't remove it by 11:59 pm, Pacific Standard Time, 3rd January 2014, we will have use Google's Disavow Tool to get back-link removed and, sorry to say this, but Google may not look too nicely upon you either for not having removed the comment. Thanks in advance for your cooperation.