Monitoring AQ

Although there were already several posts on querying AQ, like “in the queue” and “enqueue“, the next one is great when monitoring AQ. We are using this to monitor AQ, especially for Oracle ESB (hence the commented addition):

1
2
3
4
5
6
7
8
9
SELECT aq.name
,      aq.queue_table
,      aq.queue_type
,      v$aq.* 
FROM   v$aq
,      all_queues aq
WHERE  aq.qid = v$aq.qid
-- use if looking for ESB AQ: and    aq.queue_table = 'ESB_JAVA_DEFERRED';
;

The query will result in something like:

Monitoring AQ query results

It contains the following metrics:

  • Waiting – Number of waiting message;
  • Ready – Number of messages with status ready;
  • Expired – Number of expired messages;
  • Total Wait – Total waiting time per queue;
  • Average Wait – Average waiting time per queue.

A describtion of the AQ and Streams views can be found here.

4 thoughts on “Monitoring AQ

  1. Antonio Petricca

    Hi, does anybody know how to get the time of the last enqueued/dequeued message in order to monitor if the traffic through a given queue is still alive?

    Thank you very much!
    Antonio

  2. PeterPaul Post author

    Hi Antonio,

    For the enqueue time you could query the queue tables or the views (AQ$_queue_table_name) on them and sort on ENQ_TIME.

  3. Antonio Petricca

    Thank you Paul, I know this, but I need the enqueue time of the last handled message. Instead inside that I may find any record if all messages have been delivered.

    Is it right?

    Thank you again,
    Antonio

  4. PeterPaul Post author

    Hi Antonio, afaik you would have to create something for that yourself. For example by storing the this data when reading the message from the queue. Several companies have created stuff like that around AQ.

    hth,
    Peter Paul

Leave a Reply

Your email address will not be published. Required fields are marked *