Skip to content
Oracle DBA Scripts & Database Utilities

Gvaq (gvaq.sql)

What This Script Does

Section titled “What This Script Does”

This script provides comprehensive monitoring of Oracle Advanced Queuing (AQ) across all RAC instances, showing queue statistics, configuration, and performance metrics. Essential for troubleshooting message processing and queue bottlenecks.

The Script

Section titled “The Script”
set linesize 400
set pagesize 999


col owner                 format a17
col name                  format a30       heading 'Queue|Name'
col queue_table           format a25       heading 'Table|Name'
col qid                   format 9999999   heading 'Queue|ID'
col queue_type            format a20       heading 'Queue|Type'
col max_retries           format 9999999   heading 'Max|Retries'
col retry_delay           format 9999999   heading 'Retry|Delay|(Sec)'
col enqueue_enabled       format a7        heading 'ENQ|Enable'
col dequeue_enabled       format a7        heading 'DEQ|Enable'
col retention             format a7        heading 'Sec|Retain|Before|DEQ'
col user_comment          format a100
col network_name          format a100
col waiting_cnt           format 9,999  heading 'Wait|CNT'
col ready                 format 999,999,999  heading 'Ready|CNT'
col expired               format 9,999  heading 'Expire|CNT'
col total_wait            format 999,999,999,999.99 heading 'Wait|Sec'
col average_wait          format 999,999,999.99 heading 'Wait|DEQ|Sec'
col OBJECT_TYPE           format a35
col SORT_ORDER            format a13
col RECIPIENTS            format a10
col MESSAGE_GROUPING      format a13        heading 'Message|Grouping'
col COMPATIBLE            format a10
col PRIMARY_INSTANCE      format 9999       heading 'Prim|Inst'
col SECONDARY_INSTANCE    format 9999       heading 'Sec|Inst'
col OWNER_INSTANCE        format 9999       heading 'Own|Inst'






select  q.owner
       ,q.name
       ,q.qid
       ,q.queue_table
       ,aq.ready
       ,aq.expired
       ,aq.waiting  waiting_cnt
       ,q.queue_type
       ,qt.PRIMARY_INSTANCE
       ,qt.SECONDARY_INSTANCE
       ,qt.OWNER_INSTANCE
       ,q.max_retries
       ,q.retry_delay
       ,q.enqueue_enabled
       ,q.dequeue_enabled
       ,q.retention
--       ,q.user_comment
--       ,q.network_name
       ,qt.OBJECT_TYPE
       ,qt.SORT_ORDER
       ,qt.RECIPIENTS
       ,qt.MESSAGE_GROUPING
       ,qt.COMPATIBLE
--       ,qt.USER_COMMENT
       ,qt.SECURE
--       ,aq.waiting  waiting_cnt
--       ,aq.ready
--       ,aq.expired
       ,aq.total_wait
       ,aq.average_wait
  from gv$aq aq
      ,dba_queues q
      ,dba_queue_tables qt
 where aq.qid = q.qid
   and nvl(upper(q.owner),'null') like upper(nvl('&owner', '%'))
   and nvl(upper(q.queue_table),'null') like upper(nvl('&queue_table','%'))
   and q.owner = qt.owner
   and q.queue_table = qt.queue_table
 order by q.owner
 ;




--to read from the queue_table, you need to add AQ$ in front of queue_table_name

Parameters

Section titled “Parameters”

The script prompts for:

  • &Queue - owner to filter (use % for all)
  • &Queue - table name pattern to filter (use % for all)

Usage

Section titled “Usage”
-- Basic usage
@gvaq.sql


-- When prompted, enter:
-- owner: Queue owner to filter (use % for all)
-- queue_table: Queue table name pattern to filter (use % for all)

Sample Output

Section titled “Sample Output”
                 Queue                    Table                                 Ready  Expire  Wait       Queue                 Prim  Sec  Own     Max    Retry  ENQ  DEQ     Sec                                                          Message                                   Wait            Wait
OWNER            Name                     Name                Queue ID           CNT    CNT  CNT          Type                 Inst Inst Inst Retries    Delay Enable Enable Retain OBJECT_TYPE                         SORT_ORDER    RECIPIENTS Grouping      COMPATIBLE SECURE              Sec         DEQ Sec
---------------- ------------------------ -------------------- -------- ------------ ------ ----- -------------------- ---- ---- ---- ------- -------- ------ ------ ------ ----------------------------------- ------------- ---------- ------------- ---------- ------ ------------------ ----------------
APPS             EVENT_QUEUE              EVENT_QUEUE_TAB         12345          125      0     5 NORMAL_QUEUE            1    2    1       5       60 YES    YES    0      SYS.AQ$_JMS_MESSAGE                 ENQ_TIME      SINGLE     TRANSACTIONAL 8.1.3      NO               15,230.00            25.50
                 NOTIFICATION_QUEUE       NOTIFICATION_TAB        12346        1,250      2    15 NORMAL_QUEUE            1    2    1       3      120 YES    YES    86400  SYS.AQ$_JMS_TEXT_MESSAGE           PRIORITY      SINGLE     NONE          8.1.3      NO              125,500.00           105.25
                 ERROR_QUEUE              ERROR_QUEUE_TAB         12347           45     10     0 EXCEPTION_QUEUE         1    0    1       0        0 NO     YES    0      SYS.AQ$_JMS_MESSAGE                 ENQ_TIME      SINGLE     NONE          8.1.3      NO                    0.00             0.00