 | Chapter 5 Oracle Advanced Queuing |  |
Oracle AQ relies on a variety of database objects to get its job done. Some objects are created for each queue table established. Other objects are created at the time that Oracle AQ is installed.
When a queue table is created, Oracle AQ defines a database table to hold all the messages for all queues in that queue table, as well as a view that allows a user to both query from and change (with caution and the guidance of Oracle Support) messages stored in queues of the queue table.
The name of the database table has the following form,
<queue_table>
where queue_table is the name of the queue table created. Table 5.3 shows the columns of this view.
Name | Description | Datatype |
|---|---|---|
Q_NAME | Name of the queue (remember that you can have more than one queue in a queue table) | VARCHAR2(30) |
MSGID | Unique identifier of the message | RAW(16) |
CORRID | Optional correlation identifier value provided by the user | VARCHAR2(30) |
PRIORITY | Message priority | NUMBER |
STATE | Message state | NUMBER |
DELAY | The point in time to which the message is delayed for dequeuing | DATE |
EXPIRATION | Number of seconds in which the message will expire after its message state is set to READY | NUMBER |
TIME_MANAGER_INFO | For internal use only | DATE |
LOCAL_ORDER_NO | For internal use only | NUMBER |
CHAIN_NO | For internal use only | NUMBER |
CSCN | For internal use only | NUMBER |
DSCN | For internal use only | NUMBER |
ENQ_TIME | Date-time at which the message was enqueued | DATE |
ENQ_UID | User ID of the session that enqueued the message | NUMBER |
ENQ_TID | ID number of the transaction that enqueued this message | VARCHAR2(30) |
DEQ_TIME | Date-time at which the message was dequeued | DATE |
DEQ_UID | User ID of the session that dequeued the message | NUMBER |
DEQ_TID | ID number of the transaction that dequeued this message | VARCHAR2(30) |
RETRY_COUNT | Number of retries at dequeuing the message | NUMBER |
EXCEPTION_QSCHEMA | Name of the schema containing the exception queue for this message | VARCHAR2(30) |
EXCEPTION_QUEUE | Name of the exception queue for this message | VARCHAR2(30) |
STEP_NO | For internal use only | NUMBER |
RECIPIENT_KEY | For internal use only | NUMBER |
DEQUEUE_MSGID | Message ID for the dequeue operation | RAW(16) |
USER_DATA | Payload of the queue (<user_data>); this might be a RAW value or the contents of the object that was placed in the queue | RAW or <object_type> |
You will find it useful to execute queries directly against this base table when you need to examine dequeue status information for messages that reside in a multiple consumer queue.
Here, for example, is the kind of query you might write to view the list of the agents that consumed a message with the following ID,
452F77CD652E11D1B999B14141A17646.
SELECT consumer, transaction_id, deq_time, deq_user
FROM THE
(SELECT CAST (history AS SYS.AQ$_DEQUEUE_HISTORY_T)
FROM msg_qtable
WHERE msgid = 452F77CD652E11D1B999B14141A17646).where SYS.AQ$_DEQUEUE_HISTORY_T is a nested table of type SYS.AQ$_DEQUEUE_HISTORY. This dequeue history object type is defined in catqueue.sql as follows:
CREATE TYPE sys.aq$_dequeue_history_t AS OBJECT ( consumer VARCHAR2(30), -- identifies dequeuer transaction_id VARCHAR2(22), -- M_LTID, transaction id of dequeuer deq_time DATE, -- time of dequeue deq_user NUMBER, -- user id of client performing dequeue remote_apps VARCHAR2(4000), -- string repn. of remote agents agent_naming NUMBER, -- how the message was sent to agent propagated_msgid RAW(16));
The name of the view for a queue table has the following form,
AQ$<queue_table>
where queue_table is the name of the queue table created. Table 5.4 shows the columns of this view. Notes about this view and its usage are included after the table.
Name | Description | Type |
|---|---|---|
QUEUE | Name of the queue (remember you can have more than one queue in a queue table) | VARCHAR2(30) |
MSG_ID | Unique identifier of the message | RAW(16) |
CORR_ID | Optional correlation identifier value provided by the user | VARCHAR2(128) |
MSG_PRIORITY | Message priority | NUMBER |
MSG_STATE | Message state | VARCHAR2(9) |
DELAY | Point in time to which the message is delayed for dequeuing | DATE |
EXPIRATION | Number of seconds in which the message will expire after its message state is set to READY | NUMBER |
ENQ_TIME | Date-time at which the message was enqueued | DATE |
ENQ_USER_ID | User ID of the enqueuing process | NUMBER |
ENQ_TXN_ID | Transaction ID of the enqueue action | VARCHAR2(30) |
DEQ_TIME | Date-time at which the message was dequeued | DATE |
DEQ_USER_ID | User ID of the dequeuing process | NUMBER |
DEQ_TXN_ID | Transaction ID of the dequeue action | VARCHAR2(30) |
RETRY_COUNT | Number of attempts to dequeue the message | NUMBER |
EXCEPTION_QUEUE_OWNER | Owner of exception queue | VARCHAR2(30) |
EXCEPTION_QUEUE | Name of exception queue for this message | VARCHAR2(30) |
USER_DATA | Payload of the queue (<user_data>); this might be a RAW value or the contents of the object which was placed in the queue | RAW or <object_type> |
Note the following about using the queue table view:
The AQ administrator can use the SQL language to examine the contents of any queue or queue table.
The dequeue columns are relevant only for single consumer queues. If you want to examine the dequeue history of messages in a multiple consumer queue, you will need to examine the underlying database table owned by SYS that contains the message data (see the next section).
This section documents the database objects in the data dictionary that contain information for all queue tables and queues to which you have access.
You can obtain information about all the queue tables created in your instance by examining the DBA_QUEUE_TABLES data dictionary view. The USER_QUEUE_TABLES view will show you all information about queue tables defined in your schema. Its columns are identical to the DBA version, except that there is no OWNER column. Table 5.5 lists the columns of the DBA_QUEUE_TABLES view.
Name | Description | Type |
|---|---|---|
OWNER | The schema owning the queue table | VARCHAR2(30) |
QUEUE_TABLE | Name of the queue table | VARCHAR2(30) |
TYPE | Type of payload in the queue table (either `RAW' or `OBJECT') | VARCHAR2(7) |
OBJECT_TYPE | Name of the object type if the type of the queue table is OBJECT | VARCHAR2(61) |
SORT_ORDER | A sort order for queues in the queue table, if specified | VARCHAR2(22) |
RECIPIENTS | A value indicating whether it is a single consumer queue table (DBMS_AQADM.SINGLE) or a multiple consumer queue table (DBMS_AQADM.MULTIPLE) | VARCHAR2(8) |
MESSAGE_GROUPING | The type of message grouping, either DBMS_AQADM.NONE or DBMS_AQADM.TRANSACTION | VARCHAR2(13) |
Comment provided by the user to associate with the queue table | VARCHAR2(50) |
You can obtain information about all the queues created in your instance by examining the DBA_QUEUES data dictionary view. The USER_QUEUES view will show you all information about queues defined in your schema. Its columns are identical to the DBA version except that there is no OWNER column. Table 5.6 lists the columns of the DBA_QUEUES view.
Name | Description | Type |
|---|---|---|
OWNER | The schema owning the queue | VARCHAR2(30) |
NAME | Name of the queue | VARCHAR2(30) |
QUEUE_TABLE | Name of the queue table that contains this queue | VARCHAR2(30) |
QID | Unique identifier for queue | NUMBER |
QUEUE_TYPE | Type of the queue, either DBMS_AQADM.NORMAL_QUEUE or DBMS_AQADM.EXCEPTION_QUEUE | VARCHAR2(5) |
MAX_RETRIES | Maximum number of dequeue attempts that are allowed on messages in this queue | NUMBER |
RETRY_DELAY | Number of seconds before a dequeue retry can be attempted | NUMBER |
ENQUEUE_ENABLED | Flag indicating whether or not (YES or NO) the enqueue operation is enabled for this queue | VARCHAR2(7) |
DEQUEUE_ENABLED | Flag indicating whether or not (YES or NO) the dequeue operation is enabled for this queue | VARCHAR2(7) |
RETENTION | Number of seconds a message is retained in the queue after dequeuing | NUMBER |
USER_COMMENT | Comment provided by the user to associate with the queue table | VARCHAR2(50) |
For Oracle 8.0.4 and later, AQ provides a view to the schedules currently defined for propagating messages. Table 5.7 shows the columns of the DBA_JOBS view.
Name | Description | Type |
|---|---|---|
SCHEMA | Schema owning the queue | VARCHAR2(30) |
QNAME | Name of the source queue | VARCHAR2(30) |
DESTINATION | Name of the destination; currently limited to being a database link (dblink) name | VARCHAR2(128) |
START_DATE | Date at which propagation will be started | DATE |
START_TIME | Time of day at which propagation will be started; this is stored in a string of format HH:MM:SS | VARCHAR2(8) |
WINDOW | Duration of the propagation window in seconds | NUMBER |
NEXT_TIME | String containing a date expression that evaluates to the starting date/time of the next propagation window | VARCHAR2(128) |
LATENCY | Maximum number of seconds AQ will wait before it attempts to propagate messages during a propagation window | NUMBER |
Check this view to see if a particular combination of source queue and destination have been scheduled for propagation. If so, you can determine the job ID or job number for the propagation by examining the SYS.AQ$_SCHEDULES table. Apply this job number to the DBA_JOBS view to find out:
Oracle AQ provides two views for retrieving dynamic statistics for AQ operations: GV$AQ and V$AQ. The columns for these views are exactly the same, but they contain different data:
Provides information about the numbers of messages in various states for the entire database. It consolidates information from all instances when it is queried in an Oracle parallel server environment.
Contains information about the messages in a specific database instance. It does this by examining AQ statistics stored in the System Global Area (SGA) of the instance.
Table 5.8 lists the columns of the GV$AQ and V$AQ views.
Name | Description | Type |
|---|---|---|
QID | Unique identifier of a queue; its value matches the same column in DBA_QUEUES and USER_QUEUES | NUMBER |
WAITING | Number of messages in the WAITING state | NUMBER |
READY | Number of messages in the READY state | NUMBER |
EXPIRED | Number of messages in the EXPIRED state | NUMBER |
TOTAL_WAIT | Number of seconds for which messages in the queue have been waiting in the READY state | NUMBER |
AVERAGE_WAIT | NUMBER |
|  | |
| 5.5 DBMS_AQADM: Performing AQ Administrative Tasks (Oracle8 only) |  | 5.7 Oracle AQ Examples |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.