Search code examples
oracle-databaseoracle11gqueueadvanced-queuing

GV$PERSISTENT_QUEUES Fields

I am wondering what the fields on the oracle table GV$PERSISTENT_QUEUES really mean.

The Documentation:

ENQUEUED_MSGS           NUMBER  Number of messages enqueued
DEQUEUED_MSGS           NUMBER  Number of messages dequeued
  Note: This column will not be incremented until all the subscribers of the message have dequeued the message and its retention time has elapsed.
...
ENQUEUED_EXPIRY_MSGS    NUMBER  Number of messages enqueued with expiry
ENQUEUED_DELAY_MSGS     NUMBER  Number of messages enqueued with delay
MSGS_MADE_EXPIRED       NUMBER  Number of messages expired by time manager
MSGS_MADE_READY         NUMBER  Number of messages made ready by time manager
...
ENQUEUE_TRANSACTIONS    NUMBER  Number of enqueue transactions
DEQUEUE_TRANSACTIONS    NUMBER  Number of dequeue transactions

Oracle Documentation (11.2)

My Questions:

  • How can the number of dequeued messages be larger than the number of enqueued messages?
  • If messages with a certain delay get added to the queue, do they get counted at ENQUEUED_MSGS and ENQUEUED_DELAY_MSGS?
  • If a message with a certain delay gets delivered after the delay, will it get counted at DEQUEUED_MSGS and MSGS_MADE_READY?
    • If so, how can MSGS_MADE_READY be larger than ENQUEUED_DELAY_MSGS?
  • What do the fields ENQUEUED_EXPIRY_MSGS and MSGS_MADE_EXPIRED mean?
  • What's the difference between ENQUEUED_MSGS and ENQUEUE_TRANSACTIONS, same with dequeueing?

Thank you in advance for help!

Solution

  • I am pretty sure of having found the solution to most of the above questions.

    • DEQUEUED_MSGS can be greater than ENQUEUED_MSGS in case of reboot of a database. Queue Entries that are still in the Queue Table will remain there. After database reboot, the entries will get dequeued and added to the number of dequeued messages, but they won't get added to the number of enqueued messages.
    • The Field ENQUEUED_MSGS is the sum of all messages that got enqueued into the Queue.
    • The Field ENQUEUED_DELAY_MSGS is the sum of all messages enqueued with delay.
    • ENQUEUED_MSGS - ENQUEUED_DELAY_MSGS = All messages that got enqueued without delay
    • The same is for DEQUEUED_MSGS (all) and MSGS_MADE_READY (only with delay).

    I don't know yet what ENQUEUE_TRANSACTIONS and DEQUEUE_TRANSACTIONS mean (maybe DEQUEUE_TRANSATIONS describes the number of dequeues of one message in a multi consumer queue), but I won't use those fields.