Forum Stats

  • 3,836,735 Users
  • 2,262,175 Discussions
  • 7,900,088 Comments

Discussions

How to monitor zero activity in a AQ

DeepaliG-Oracle
DeepaliG-Oracle Member Posts: 48 Employee
edited Jul 13, 2016 3:02AM in Advanced Queueing

We have implemented multiple AQs in our application. Due to few exception scenarios messages don't reach to one of the AQ. So we need to implement monitoring to generate alert if there is no enqueue/deque in a queue from last 10 mins. I tried using SCN_TO_TIMESTAMP(MAX(ora_rowscn)) function on queue table, but it doesn't return any result if queue is empty at that moment.

Please suggest the best way to implement this..thanks!

DeepaliG-Oracle

Answers

  • WGabriel
    WGabriel Member Posts: 202 Bronze Badge
    edited Jul 6, 2016 5:12AM

    Hello,

    in order to check the AQ activities you can use the system view GV$PERSISTENT_QUEUES.

    There are two interesting columns LAST_ENQUEUE_TIME and LAST_DEQUEUE_TIME for your AQ monitoring

    Then you could set up a check process (at operating system level using a cron job or a database scheduler job) to get any

    AQ enqueue and dequeue activities. The periodic check would be e.g.: time difference ( SYSDATE - LAST_ENQUEUE_TIME ) > 10 min.

    Kind regards,

    WoG

    DeepaliG-OracleDeepaliG-Oracle
  • DeepaliG-Oracle
    DeepaliG-Oracle Member Posts: 48 Employee
    edited Jul 12, 2016 2:53PM

    Thanks WGabriel. This is what I was looking for.

    I tried to write the monitoring job using the GV$PERSISTENT_QUEUES view but I noticed another issue:  the LAST_ENQUEUE_TIME and LAST_DEQUEUE_TIME in any AQ are in timezone +00:00 always. While default timezone of our DB is set as different. I am on version 12.1.0.2.0.

    In following thread I read that it was a bug which got resolved in 11.2.0, but I still face this issue:

    Create AQ table with invalid timezone

    Please suggest is there any other way to compare LAST_ENQUEUE_TIME/LAST_DEQUEUE_TIME with sysdate without converting the timezones explicitly?

    Thanks a lot!

  • WGabriel
    WGabriel Member Posts: 202 Bronze Badge
    edited Jul 13, 2016 3:02AM

    Hello,

    did you try to convert both timestamp data using the UTC converting function?

    This is a workaround in order to get rid of timezone problems.

    I already suggested this in the thread Create AQ table with invalid timezone.

    Example (time difference sysdate vs. AQ last enqueue time):

    SELECT QUEUE_NAME, 
           SYS_EXTRACT_UTC( CAST( SYSTIMESTAMP AS TIMESTAMP) ) - SYS_EXTRACT_UTC( CAST( last_enqueue_time AS TIMESTAMP ))  
      FROM GV$PERSISTENT_QUEUES;
    

    Kind regards,

    WoG

This discussion has been closed.