Rem Filename   : queuestatus.sql
Rem 
Rem Parameters : &1 = [owner(pattern).]queue(pattern) 
Rem              The queues that have to be listed.
Rem
Rem Author     :
Rem RDBMS      : Oracle V8.1.x, V9.x
Rem
Rem Modification history
Rem --------------------
Rem 02-Mar-2004  Intial creation.
Rem 28-Apr-2005  Added report header, corrected typo in column header.
Rem 26-Oct-2005  Added "Average Incident timestamp" column.
Rem
Rem ============================================================================================================================
Rem
Rem Description : Lists the status of all matching queues.
Rem
Rem               owner               : Owner of the queue.
Rem               Queue table         : Name of the queue.
Rem               # Messages waiting  : Number of messages in the queue in the state ’WAITING’.
Rem               # Messages Raidy    : Number of messages in the queue in the state ’RAIDY’.
Rem               # Messages Expired  : Number of messages in the queue in the state ’EXPIRED’.
Rem               Total time waited   : Total wait time of all ’READY’ messages in the queue.
Rem               Avg time waited     : Average wait time of ’READY’ messages in the queue.
Rem               Avg Incident time   : Average timestamp when the messages where put on the queue.
Rem                                     This information comes in handy when investigation Streams issues.
Rem
Rem Sample output
Rem -------------
Rem MXXXXXXX<RDB> @queuestatus strm%.%
Rem 
Rem 
Rem ----------------------------------------------------------------------------------------------------------------------------
Rem                      Status of all queues with owner matching patttern <%> and name matching pattern <%>
Rem 
Rem                                              of database RDB2 on server beantem5
Rem ----------------------------------------------------------------------------------------------------------------------------
Rem Current date and time : Wednesday 26-OCT-2005 17:02:24
Rem ----------------------------------------------------------------------------------------------------------------------------
Rem 
Rem                                           # Messages # Messages # Messages      Total time     Avg time Average
Rem Owner           Queue name                   Waiting      Ready    Expired          Waited       Waited Incident timestamp
Rem --------------- ------------------------- ---------- ---------- ---------- --------------- ------------ --------------------
Rem STRM_ADMIN      AQ$_QU_PDB_TO_RDB_FAST_E           0      7.110          0     107.961.282       15.184 26-OCT-2005 12:49:21
Rem STRM_ADMIN      AQ$_QU_RDB_TO_HDB_E                0          0          0               0            0
Rem STRM_ADMIN      QU_PDB_TO_RDB_FAST                 0          0          0               0            0
Rem STRM_ADMIN      QU_RDB_TO_HDB                      0          0          0               0            0
Rem
Rem ============================================================================================================================

SET PAGESIZE  0
SET TRIMSPOOL ON
SET VERIFY    OFF
SET FEEDBACK  OFF
SET TERMOUT   OFF

-- Disect the input argument, and get the owner name and table name into two seperate 
-- substitution variables. The owner name defaults to the current user.

DEFINE InputOwnerFilter  = ' '
DEFINE InputQueueFilter  = ' '
COLUMN queue_owner NOPRINT NEW_VALUE InputOwnerFilter
COLUMN queue_name  NOPRINT NEW_VALUE InputQueueFilter

SELECT decode(instr('&&1','.'), 0, upper('&1'),  /*Default to current user.*/
                                upper(substr('&&1',1,instr('&&1','.')-1))) queue_owner,
       decode(instr('&&1','.'), 0, upper('&2'),  /*Only the queue name was passed in.*/
                                upper(substr('&&1',instr('&&1','.')+1))) queue_name
  FROM dual;

alter session set nls_numeric_characters = ',.';

CLEAR COLUMNS
SET PAGESIZE     9999
SET TERMOUT      ON

@@header_title 124 DC 'Status of all queues with owner matching patttern <&InputOwnerFilter> and name matching pattern <&InputQueueFilter>'

COLUMN Owner        FORMAT A15             HEADING "Owner"
COLUMN QueueName    FORMAT A25             HEADING "Queue name"
COLUMN NbrWait      FORMAT 999G999         HEADING "# Messages|Waiting"
COLUMN NbrReady     FORMAT 999G999         HEADING "# Messages|Ready"
COLUMN NbrExpired   FORMAT 999G999         HEADING "# Messages|Expired"
COLUMN TimeWaited   FORMAT 99G999G999G999  HEADING "Total time|Waited"
COLUMN AvgWaited    FORMAT 999G999G999     HEADING "Avg time|Waited"
COLUMN When         FORMAT A20             HEADING "Average|Incident timestamp"

SELECT dba_queues.owner  "Owner",
       dba_queues.name   "QueueName",
       v$aq.waiting      "NbrWait",
       v$aq.ready        "NbrReady",
       v$aq.expired      "NbrExpired",
       v$aq.total_wait   "TimeWaited",
       v$aq.average_wait "AvgWaited",
       case when v$aq.waiting+v$aq.ready+v$aq.expired > 0 then 
                 to_char(sysdate-(v$aq.total_wait/(v$aq.waiting+v$aq.ready+v$aq.expired))/(24*3600),'DD-MON-YYYY HH24:MI:SS')
            else '' 
       end               "When"
  FROM dba_queues, v$aq 
 WHERE dba_queues.qid = v$aq.qid
   AND dba_queues.owner like '%&InputOwnerFilter%'
   AND dba_queues.name  like '%&InputQueueFilter%'
 ORDER BY owner, name;


UNDEFINE InputOwnerFilter
UNDEFINE InputQueueFilter

SET FEEDBACK ON

PROMPT