Streams Replication | Monitoring Streams Queues and Propagations

This post lists sample queries to monitor Streams queues and propagations.

1)       Monitoring ANYDATA Queues and Messaging
2)       Monitoring Buffered Queues
3)       Monitoring Streams Propagations and Propagation Jobs

1)        Monitoring ANYDATA Queues and Messaging

a)     Displaying the ANYDATA Queues in a Database
b)     Viewing the Messaging Clients in a Database
c)     Viewing Message Notifications
d)     Determining the Consumer of Each User-Enqueued Message in a Queue
e)     Viewing the Contents of User-Enqueued Messages in a Queue

a)     Displaying the ANYDATA Queues in a Database : To display all of the ANYDATA queues in a database, run the following query:

COLUMN OWNER HEADING 'Owner' FORMAT A10
COLUMN NAME HEADING 'Queue Name' FORMAT A28
COLUMN QUEUE_TABLE HEADING 'Queue Table' FORMAT A22
COLUMN USER_COMMENT HEADING 'Comment' FORMAT A15

SELECT q.OWNER, q.NAME, t.QUEUE_TABLE, q.USER_COMMENT
 FROM DBA_QUEUES q, DBA_QUEUE_TABLES t
 WHERE t.OBJECT_TYPE = 'SYS.ANYDATA' AND
 q.QUEUE_TABLE = t.QUEUE_TABLE AND
 q.OWNER       = t.OWNER;

An exception queue is created automatically when you create an ANYDATA queue.

b) Viewing the Messaging Clients in a Database : Run the following query to view this information about messaging clients

COLUMN STREAMS_NAME HEADING 'Messaging|Client' FORMAT A25
COLUMN QUEUE_OWNER HEADING 'Queue|Owner' FORMAT A10
COLUMN QUEUE_NAME HEADING 'Queue Name' FORMAT A18
COLUMN RULE_SET_NAME HEADING 'Positive|Rule Set' FORMAT A11
COLUMN NEGATIVE_RULE_SET_NAME HEADING 'Negative|Rule Set' FORMAT A11

SELECT STREAMS_NAME,
 QUEUE_OWNER,
 QUEUE_NAME,
 RULE_SET_NAME,
 NEGATIVE_RULE_SET_NAME
 FROM DBA_STREAMS_MESSAGE_CONSUMERS;

c) Viewing Message Notifications : You can configure a message notification to send a notification when a message that can be dequeued by a messaging client is enqueued into a queue.
The notification can be sent to an email address, to an HTTP URL, or to a PL/SQL procedure. Run the following query to view the message notifications configured in a database:

COLUMN STREAMS_NAME HEADING 'Messaging|Client' FORMAT A10
COLUMN QUEUE_OWNER HEADING 'Queue|Owner' FORMAT A5
COLUMN QUEUE_NAME HEADING 'Queue Name' FORMAT A20
COLUMN NOTIFICATION_TYPE HEADING 'Notification|Type' FORMAT A15
COLUMN NOTIFICATION_ACTION HEADING 'Notification|Action' FORMAT A25

SELECT STREAMS_NAME,
 QUEUE_OWNER,
 QUEUE_NAME,
 NOTIFICATION_TYPE,
 NOTIFICATION_ACTION
 FROM DBA_STREAMS_MESSAGE_CONSUMERS
 WHERE NOTIFICATION_TYPE IS NOT NULL;

d) Configuring a Messaging Client and Message Notification : To determine the consumer for each user-enqueued message in a queue, query AQ$queue_table_name in the queue owner’s schema, where queue_table_name is the name of the queue table.  For example, to find the consumers of the user-enqueued messages in the oe_q_table_any queue table, run the following query:

COLUMN MSG_ID HEADING 'Message ID' FORMAT 9999
COLUMN MSG_STATE HEADING 'Message State' FORMAT A13
COLUMN CONSUMER_NAME HEADING 'Consumer' FORMAT A30

SELECT MSG_ID, MSG_STATE, CONSUMER_NAME
 FROM AQ$OE_Q_TABLE_ANY;

e) Viewing the Contents of User-Enqueued Messages in a Queue : In an ANYDATA queue, to view the contents of a payload that is encapsulated within an ANYDATA payload, you query the queue table using the Accessdata_type static functions of the ANYDATA type, where data_type is the type of payload to view. For example, to view the contents of payload of type NUMBER in a queue with a queue table named oe_queue_table, run the following query as the queue owner:

SELECT qt.user_data.AccessNumber() "Numbers in Queue"
 FROM strmadmin.oe_q_table_any qt;

Similarly, to view the contents of a payload of type VARCHAR2 in a queue with a queue table named oe_q_table_any, run the following query:

SELECT qt.user_data.AccessVarchar2() "Varchar2s in Queue"
 FROM strmadmin.oe_q_table_any qt;

2)       Monitoring Buffered Queues

A buffered queue includes the following storage areas:
Buffered queues are stored in the Streams pool, and the Streams pool is a portion of memory in the System Global Area (SGA) that is used by Streams.  In a Streams environment, LCRs captured by a capture process always are stored in the buffered queue of an ANYDATA queue.  Buffered queues enable Oracle databases to optimize messages by storing them in the SGA instead of always storing them in a queue table.  Messages in a buffered queue can spill from memory if they have been staged in the buffered queue for a period of time without being dequeued, or if there is not enough space in memory to hold all of the messages.
Messages that spill from memory are stored in the appropriate queue table.

The following sections describe queries that monitor buffered queues:

i) Determining the Number of Messages in Each Buffered Queue

COLUMN QUEUE_SCHEMA HEADING 'Queue Owner' FORMAT A15
COLUMN QUEUE_NAME HEADING 'Queue Name' FORMAT A15
COLUMN MEM_MSG HEADING 'Messages|in Memory' FORMAT 99999999
COLUMN SPILL_MSGS HEADING 'Messages|Spilled' FORMAT 99999999
COLUMN NUM_MSGS HEADING 'Total Messages|in Buffered Queue' FORMAT 99999999

SELECT QUEUE_SCHEMA,
 QUEUE_NAME,
 (NUM_MSGS - SPILL_MSGS) MEM_MSG,
 SPILL_MSGS,
 NUM_MSGS
 FROM V$BUFFERED_QUEUES;

ii) Viewing the Capture Processes for the LCRs in Each Buffered Queue
A capture process is a queue publisher that enqueues captured messages into a buffered queue. These LCRs can be propagated to other queues subsequently.
By querying the V$BUFFERED_PUBLISHERS dynamic performance view, you can display each capture process that captured the LCRs in the buffered queue. These LCRs might have been captured at the local database, or they might have been captured at a remote database and propagated to the queue specified in the query.

COLUMN SENDER_NAME HEADING 'Capture|Process' FORMAT A13
COLUMN SENDER_ADDRESS HEADING 'Sender Queue' FORMAT A27
COLUMN QUEUE_NAME HEADING 'Queue Name' FORMAT A15
COLUMN CNUM_MSGS HEADING 'Number|of LCRs|Enqueued' FORMAT 99999999
COLUMN LAST_ENQUEUED_MSG HEADING 'Last|Enqueued|LCR' FORMAT 99999999

SELECT SENDER_NAME,
 SENDER_ADDRESS,
 QUEUE_NAME,       
 CNUM_MSGS,
 LAST_ENQUEUED_MSG
 FROM V$BUFFERED_PUBLISHERS;

iii) Displaying Information About Propagations that Send Buffered Messages :
The query in this section displays the following information about each propagation that sends buffered messages from a buffered queue in the local database

COLUMN PROPAGATION_NAME HEADING 'Propagation' FORMAT A15
COLUMN QUEUE_SCHEMA HEADING 'Queue|Owner' FORMAT A10
COLUMN QUEUE_NAME HEADING 'Queue|Name' FORMAT A15
COLUMN DBLINK HEADING 'Database|Link' FORMAT A10
COLUMN SCHEDULE_STATUS HEADING 'Schedule Status' FORMAT A20
SELECT p.PROPAGATION_NAME,
 s.QUEUE_SCHEMA,
 s.QUEUE_NAME,
 s.DBLINK,
 s.SCHEDULE_STATUS
 FROM DBA_PROPAGATION p, V$PROPAGATION_SENDER s
 WHERE p.DESTINATION_DBLINK = s.DBLINK AND
 p.SOURCE_QUEUE_OWNER = s.QUEUE_SCHEMA AND
 p.SOURCE_QUEUE_NAME  = s.QUEUE_NAME;

iv) Displaying the Number of Messages and Bytes Sent By Propagations
The query in this section displays the number of messages and the number of bytes sent by each propagation that sends buffered messages from a buffered queue in the local database:

COLUMN PROPAGATION_NAME HEADING 'Propagation' FORMAT A15
COLUMN QUEUE_NAME HEADING 'Queue|Name' FORMAT A15
COLUMN DBLINK HEADING 'Database|Link' FORMAT A10
COLUMN TOTAL_MSGS HEADING 'Total|Messages' FORMAT 99999999
COLUMN TOTAL_BYTES HEADING 'Total|Bytes' FORMAT 99999999

SELECT p.PROPAGATION_NAME,
 s.QUEUE_NAME,
 s.DBLINK,
 s.TOTAL_MSGS,
 s.TOTAL_BYTES
 FROM DBA_PROPAGATION p, V$PROPAGATION_SENDER s
 WHERE p.DESTINATION_DBLINK = s.DBLINK AND
 p.SOURCE_QUEUE_OWNER = s.QUEUE_SCHEMA AND
 p.SOURCE_QUEUE_NAME  = s.QUEUE_NAME;

v) Displaying Performance Statistics for Propagations that Send Buffered Messages
The query in this section displays the amount of time that a propagation sending buffered messages spends performing various tasks. Each propagation sends messages from the source queue to the destination queue. Specifically, the query displays the following information:

COLUMN PROPAGATION_NAME HEADING 'Propagation' FORMAT A15
COLUMN QUEUE_NAME HEADING 'Queue|Name' FORMAT A13
COLUMN DBLINK HEADING 'Database|Link' FORMAT A9
COLUMN ELAPSED_DEQUEUE_TIME HEADING 'Dequeue|Time' FORMAT 99999999.99
COLUMN ELAPSED_PICKLE_TIME HEADING 'Pickle|Time' FORMAT 99999999.99
COLUMN ELAPSED_PROPAGATION_TIME HEADING 'Propagation|Time' FORMAT 99999999.99

SELECT p.PROPAGATION_NAME,
 s.QUEUE_NAME,
 s.DBLINK,
 (s.ELAPSED_DEQUEUE_TIME / 100) ELAPSED_DEQUEUE_TIME,
 (s.ELAPSED_PICKLE_TIME / 100) ELAPSED_PICKLE_TIME,
 (s.ELAPSED_PROPAGATION_TIME / 100) ELAPSED_PROPAGATION_TIME
 FROM DBA_PROPAGATION p, V$PROPAGATION_SENDER s
 WHERE p.DESTINATION_DBLINK = s.DBLINK AND
 p.SOURCE_QUEUE_OWNER = s.QUEUE_SCHEMA AND
 p.SOURCE_QUEUE_NAME  = s.QUEUE_NAME;

vi) Viewing the Propagations Dequeuing Messages from Each Buffered Queue
Propagations are queue subscribers that can dequeue messages from a queue. By querying the V$BUFFERED_SUBSCRIBERS dynamic performance view, you can display all the propagations that can dequeue buffered messages from a queue.

COLUMN PROPAGATION_NAME HEADING 'Propagation' FORMAT A15
COLUMN SUBSCRIBER_ADDRESS HEADING 'Destination|Database' FORMAT A11
COLUMN CURRENT_ENQ_SEQ HEADING 'Current|Enqueued|Sequence' FORMAT 99999999
COLUMN LAST_BROWSED_SEQ HEADING 'Last|Browsed|Sequence' FORMAT 99999999
COLUMN LAST_DEQUEUED_SEQ HEADING 'Last|Dequeued|Sequence' FORMAT 99999999
COLUMN NUM_MSGS HEADING 'Number of|Messages|in Queue|(Current)' FORMAT 99999999
COLUMN TOTAL_SPILLED_MSG HEADING 'Number of|Spilled|Messages|(Cumulative)'
 FORMAT 99999999

SELECT p.PROPAGATION_NAME,
 s.SUBSCRIBER_ADDRESS,
 s.CURRENT_ENQ_SEQ,
 s.LAST_BROWSED_SEQ,    
 s.LAST_DEQUEUED_SEQ,
 s.NUM_MSGS, 
 s.TOTAL_SPILLED_MSG
FROM DBA_PROPAGATION p, V$BUFFERED_SUBSCRIBERS s, V$BUFFERED_QUEUES q
WHERE q.QUEUE_ID = s.QUEUE_ID AND
 p.SOURCE_QUEUE_OWNER = q.QUEUE_SCHEMA AND
 p.SOURCE_QUEUE_NAME = q.QUEUE_NAME AND
 p.DESTINATION_DBLINK = s.SUBSCRIBER_ADDRESS;

vii) Displaying Performance Statistics for Propagations that Receive Buffered Messages
The query in this section displays the amount of time that each propagation receiving buffered messages spends performing various tasks. Each propagation receives the messages and enqueues them into the destination queue for the propagation. Specifically, the query displays the following information:

COLUMN SRC_QUEUE_NAME HEADING 'Source|Queue|Name' FORMAT A20
COLUMN SRC_DBNAME HEADING 'Source|Database' FORMAT A15
COLUMN ELAPSED_UNPICKLE_TIME HEADING 'Unpickle|Time' FORMAT 99999999.99
COLUMN ELAPSED_RULE_TIME HEADING 'Rule|Evaluation|Time' FORMAT 99999999.99
COLUMN ELAPSED_ENQUEUE_TIME HEADING 'Enqueue|Time' FORMAT 99999999.99

SELECT SRC_QUEUE_NAME,
 SRC_DBNAME,
 (ELAPSED_UNPICKLE_TIME / 100) ELAPSED_UNPICKLE_TIME,
 (ELAPSED_RULE_TIME / 100) ELAPSED_RULE_TIME,
 (ELAPSED_ENQUEUE_TIME / 100) ELAPSED_ENQUEUE_TIME
 FROM V$PROPAGATION_RECEIVER;

viii) Viewing the Apply Processes Dequeuing Messages from Each Buffered Queue
Apply processes are queue subscribers that can dequeue messages from a queue. By querying the V$BUFFERED_SUBSCRIBERS dynamic performance view, you can display all the apply processes that can dequeue messages from a queue.

COLUMN SUBSCRIBER_NAME HEADING 'Apply Process' FORMAT A16
COLUMN QUEUE_SCHEMA HEADING 'Queue|Owner' FORMAT A10
COLUMN QUEUE_NAME HEADING 'Queue|Name' FORMAT A15
COLUMN LAST_DEQUEUED_SEQ HEADING 'Last|Dequeued|Sequence' FORMAT 99999999
COLUMN NUM_MSGS HEADING 'Number of|Messages|in Queue|(Current)' FORMAT 99999999
COLUMN TOTAL_SPILLED_MSG HEADING 'Number of|Spilled|Messages|(Cumulative)'
 FORMAT 99999999

SELECT s.SUBSCRIBER_NAME,
 q.QUEUE_SCHEMA,
 q.QUEUE_NAME,
 s.LAST_DEQUEUED_SEQ,
 s.NUM_MSGS,
 s.TOTAL_SPILLED_MSG
FROM V$BUFFERED_QUEUES q, V$BUFFERED_SUBSCRIBERS s, DBA_APPLY a
WHERE q.QUEUE_ID = s.QUEUE_ID AND
 s.SUBSCRIBER_ADDRESS IS NULL AND
 s.SUBSCRIBER_NAME = a.APPLY_NAME;

3)       Monitoring Streams Propagations and Propagation Jobs

The following sections contain queries that you can run to display information about propagations and propagation jobs:
a) Displaying the Queues and Database Link for Each Propagation
b) Determining the Source Queue and Destination Queue for Each Propagation
c) Determining the Rule Sets for Each Propagation
d) Displaying the Schedule for a Propagation Job
e) Determining the Total Number of Messages and Bytes Propagated

a) Displaying the Queues and Database Link for Each Propagation
You can display information about each propagation by querying the DBA_PROPAGATION data dictionary view. This view contains information about each propagation with a source queue is at the local database.

COLUMN PROPAGATION_NAME        HEADING 'Propagation|Name'   FORMAT A19
COLUMN SOURCE_QUEUE_NAME       HEADING 'Source|Queue|Name'  FORMAT A17
COLUMN DESTINATION_DBLINK      HEADING 'Database|Link'      FORMAT A9
COLUMN DESTINATION_QUEUE_NAME  HEADING 'Dest|Queue|Name'    FORMAT A15
COLUMN STATUS                  HEADING 'Status'             FORMAT A8
COLUMN QUEUE_TO_QUEUE          HEADING 'Queue-|to-|Queue?'  FORMAT A6

SELECT PROPAGATION_NAME,
 SOURCE_QUEUE_NAME,
 DESTINATION_DBLINK,
 DESTINATION_QUEUE_NAME,
 STATUS,
 QUEUE_TO_QUEUE
 FROM DBA_PROPAGATION;

b) Determining the Source Queue and Destination Queue for Each Propagation
You can determine the source queue and destination queue for each propagation by querying the DBA_PROPAGATION data dictionary view.

COLUMN PROPAGATION_NAME HEADING 'Propagation|Name' FORMAT A20
COLUMN SOURCE_QUEUE_OWNER HEADING 'Source|Queue|Owner' FORMAT A10
COLUMN 'Source Queue' HEADING 'Source|Queue' FORMAT A15
COLUMN DESTINATION_QUEUE_OWNER HEADING 'Dest|Queue|Owner'   FORMAT A10
COLUMN 'Destination Queue' HEADING 'Destination|Queue' FORMAT A15

SELECT p.PROPAGATION_NAME,
 p.SOURCE_QUEUE_OWNER,
 p.SOURCE_QUEUE_NAME ||'@'||
 g.GLOBAL_NAME "Source Queue",
 p.DESTINATION_QUEUE_OWNER,
 p.DESTINATION_QUEUE_NAME ||'@'||
 p.DESTINATION_DBLINK "Destination Queue"
 FROM DBA_PROPAGATION p, GLOBAL_NAME g;

c)  Determining the Rule Sets for Each Propagation
The query in this section displays the following information for each propagation:

COLUMN PROPAGATION_NAME HEADING 'Propagation|Name' FORMAT A20
COLUMN RULE_SET_OWNER HEADING 'Positive|Rule Set|Owner' FORMAT A10
COLUMN RULE_SET_NAME HEADING 'Positive Rule|Set Name' FORMAT A15
COLUMN NEGATIVE_RULE_SET_OWNER HEADING 'Negative|Rule Set|Owner' FORMAT A10
COLUMN NEGATIVE_RULE_SET_NAME HEADING 'Negative Rule|Set Name' FORMAT A15

SELECT PROPAGATION_NAME,
 RULE_SET_OWNER,
 RULE_SET_NAME,
 NEGATIVE_RULE_SET_OWNER,
 NEGATIVE_RULE_SET_NAME
 FROM DBA_PROPAGATION;

d) Displaying the Schedule for a Propagation Job
The query in this section displays the following information about the propagation schedule for a propagation job used by a propagation named dbs1_to_dbs2:

COLUMN START_DATE HEADING 'Start Date'
COLUMN PROPAGATION_WINDOW HEADING 'Duration|in Seconds' FORMAT 99999
COLUMN NEXT_TIME HEADING 'Next|Time' FORMAT A8
COLUMN LATENCY HEADING 'Latency|in Seconds' FORMAT 99999
COLUMN SCHEDULE_DISABLED HEADING 'Status' FORMAT A8
COLUMN PROCESS_NAME HEADING 'Process' FORMAT A8
COLUMN FAILURES HEADING 'Number of|Failures' FORMAT 99

SELECT DISTINCT TO_CHAR(s.START_DATE, 'HH24:MI:SS MM/DD/YY') START_DATE,
 s.PROPAGATION_WINDOW,
 s.NEXT_TIME,
 s.LATENCY,
 DECODE(s.SCHEDULE_DISABLED,
 'Y', 'Disabled',
 'N', 'Enabled') SCHEDULE_DISABLED,
 s.PROCESS_NAME,
 s.FAILURES
 FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION p
 WHERE p.PROPAGATION_NAME = 'DBS1_TO_DBS2'
 AND p.DESTINATION_DBLINK = s.DESTINATION
 AND s.SCHEMA = p.SOURCE_QUEUE_OWNER
 AND s.QNAME = p.SOURCE_QUEUE_NAME;

e)  Determining the Total Number of Messages and Bytes Propagated
All propagation jobs from a source queue that share the same database link have a single propagation schedule. The query in this section displays the following information for each propagation:

COLUMN PROPAGATION_NAME HEADING 'Propagation|Name' FORMAT A20
COLUMN TOTAL_TIME HEADING 'Total Time|Executing|in Seconds' FORMAT 999999
COLUMN TOTAL_NUMBER HEADING 'Total Messages|Propagated' FORMAT 999999999
COLUMN TOTAL_BYTES HEADING 'Total Bytes|Propagated' FORMAT 9999999999999

SELECT p.PROPAGATION_NAME, s.TOTAL_TIME, s.TOTAL_NUMBER, s.TOTAL_BYTES
 FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION p
 WHERE p.DESTINATION_DBLINK = s.DESTINATION
 AND s.SCHEMA = p.SOURCE_QUEUE_OWNER
 AND s.QNAME = p.SOURCE_QUEUE_NAME;