The following topics describe monitoring Oracle Streams queues and propagations:
Note:
The Oracle Streams tool in Oracle Enterprise Manager Cloud Control is also an excellent way to monitor an Oracle Streams environment. See the online help for the Oracle Streams tool for more information.See Also:
Oracle Database Reference for information about the data dictionary views described in this chapter
The following topics describe displaying information about queues and messaging:
See Also:
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;
Your output looks similar to the following:
Owner Queue Name Queue Table Comment ---------- ---------------------------- ---------------------- --------------- STRMADMIN DB$APPQ DB$APPQT STRMADMIN AQ$_DB$APPQT_E DB$APPQT exception queue STRMADMIN DA$CAPQ DA$CAPQT STRMADMIN AQ$_DA$CAPQT_E DA$CAPQT exception queue IX STREAMS_QUEUE STREAMS_QUEUE_TABLE IX AQ$_STREAMS_QUEUE_TABLE_E STREAMS_QUEUE_TABLE exception queue
An exception queue is created automatically when you create an ANYDATA queue.
See Also:
"Managing Queues"You can view the messaging clients in a database by querying the DBA_STREAMS_MESSAGE_CONSUMERS data dictionary view. The query in this section displays the following information about each messaging client:
The name of the messaging client
The queue used by the messaging client
The positive rule set used by the messaging client
The negative rule set used by the messaging client
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;
Your output looks similar to the following:
Messaging Queue Positive Negative Client Owner Queue Name Rule Set Rule Set ------------------------- ---------- ------------------ ----------- ----------- SCHEDULER_PICKUP SYS SCHEDULER$_JOBQ RULESET$_8 SCHEDULER_COORDINATOR SYS SCHEDULER$_JOBQ RULESET$_4 HR STRMADMIN STREAMS_QUEUE RULESET$_15
See Also:
Chapter 3, "Oracle Streams Staging and Propagation" for more information about messaging clientsYou 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 e-mail 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;
Your output looks similar to the following:
Messaging Queue Notification Notification Client Owner Queue Name Type Action ---------- ----- -------------------- --------------- ------------------------- OE OE NOTIFICATION_QUEUE MAIL mary.smith@example.com
To determine the consumer for each message in a persistent 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 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;
Your output looks similar to the following:
Message ID Message State Consumer -------------------------------- ------------- ------------------------------ B79AC412AE6E08CAE034080020AE3E0A PROCESSED OE B79AC412AE6F08CAE034080020AE3E0A PROCESSED OE B79AC412AE7008CAE034080020AE3E0A PROCESSED OE
Note:
This query lists only messages in a persistent queue, not captured LCRs or other messages in a buffered queue.See Also:
Oracle Database Advanced Queuing User's Guide for an example that enqueues messages into anANYDATA queueIn 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.
See Also:
Oracle Database Advanced Queuing User's Guide for an example that enqueues the messages shown in the queries in this section into anANYDATA queueFor 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;
Your output looks similar to the following:
Numbers in Queue
----------------
16
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;
Your output looks similar to the following:
Varchar2s in Queue -------------------------------------------------------------------------------- Chemicals - SW
To view the contents of a user-defined data type, you query the queue table using a custom function that you create. For example, to view the contents of a payload of oe.cust_address_typ, create a function similar to the following:
CREATE OR REPLACE FUNCTION oe.view_cust_address_typ(
in_any IN ANYDATA)
RETURN oe.cust_address_typ
IS
address oe.cust_address_typ;
num_var NUMBER;
BEGIN
IF (in_any.GetTypeName() = 'OE.CUST_ADDRESS_TYP') THEN
num_var := in_any.GetObject(address);
RETURN address;
ELSE RETURN NULL;
END IF;
END;
/
GRANT EXECUTE ON oe.view_cust_address_typ TO strmadmin;
GRANT EXECUTE ON oe.cust_address_typ TO strmadmin;
Query the queue table using the function, as in the following example:
SELECT oe.view_cust_address_typ(qt.user_data) "Customer Addresses" FROM strmadmin.oe_q_table_any qt WHERE qt.user_data.GetTypeName() = 'OE.CUST_ADDRESS_TYP';
Your output looks similar to the following:
Customer Addresses(STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID
--------------------------------------------------------------------------------
CUST_ADDRESS_TYP('1646 Brazil Blvd', '361168', 'Chennai', 'Tam', 'IN')
A buffered queue includes the following storage areas:
System Global Area (SGA) memory associated with a queue
Part of the queue table for a queue that stores messages that have spilled from memory
Buffered queues are stored in the Oracle Streams pool, and the Oracle Streams pool is a portion of memory in the SGA that is used by Oracle Streams. In an Oracle Streams environment, LCRs captured by a capture process always are stored in the buffered queue of an ANYDATA queue. Users and application can also enqueue messages into buffered queues, and these buffered queues be part of ANYDATA queues or part of typed queues.
Buffered queues enable Oracle databases to optimize messages by storing them in the SGA instead of always storing them in a queue table. Captured LCRs always are stored in buffered queues, but other types of messages can be stored in buffered queues or persistently in queue tables. 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:
Viewing the Capture Processes for the LCRs in Each Buffered Queue
Displaying Information About Propagations that Send Buffered Messages
Displaying the Number of Messages and Bytes Sent By Propagations
Displaying Performance Statistics for Propagations that Send Buffered Messages
Viewing the Propagations Dequeuing Messages from Each Buffered Queue
Displaying Performance Statistics for Propagations That Receive Buffered Messages
Viewing the Apply Processes Dequeuing Messages from Each Buffered Queue
The V$BUFFERED_QUEUES dynamic performance view contains information about the number of messages in a buffered queue. The messages can be captured LCRs, buffered LCRs, or buffered user messages.
You can determine the following information about each buffered queue in a database by running the query in this section:
The queue owner
The queue name
The number of messages currently in memory
The number of messages that have spilled from memory into the queue table
The total number of messages in the buffered queue, which includes the messages in memory and the messages spilled to the queue table
To display this information, run the following query:
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;
Your output looks similar to the following:
Messages Messages Total Messages
Queue Owner Queue Name in Memory Spilled in Buffered Queue
--------------- --------------- ------------- ------------- -------------------
STRMADMIN STREAMS_QUEUE 534 21 555
A capture process is a queue publisher that enqueues captured LCRs 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.
The query in this section assumes that the buffered queues in the local database only store captured LCRs, not buffered LCRs or buffered user messages. The query displays the following information about each capture process:
The name of a capture process that captured the LCRs in the buffered queue
If the capture process is running on a remote database, and the captured LCRs have been propagated to the local queue, then the name of the queue and database from which the captured LCRs were last propagated
The name of the local queue staging the captured LCRs
The total number of LCRs captured by a capture process that have been staged in the buffered queue since the database instance was last started
The message number of the LCR last enqueued into the buffered queue from the sender
The percentage of the Streams pool used at the capture process database
The state of the publisher. The capture process is the publisher, and the following states are possible:
PUBLISHING MESSAGES
IN FLOW CONTROL: TOO MANY UNBROWSED MESSAGES
IN FLOW CONTROL: OVERSPILLED MESSAGES
IN FLOW CONTROL: INSUFFICIENT MEMORY AND UNBROWSED MESSAGES
To display this information, run the following query:
COLUMN SENDER_NAME HEADING 'Capture|Process' FORMAT A10
COLUMN SENDER_ADDRESS HEADING 'Sender Queue' FORMAT A15
COLUMN QUEUE_NAME HEADING 'Queue Name' FORMAT A10
COLUMN CNUM_MSGS HEADING 'Number|of LCRs|Enqueued' FORMAT 99999999
COLUMN LAST_ENQUEUED_MSG HEADING 'Last|Enqueued|LCR' FORMAT 9999999999
COLUMN MEMORY_USAGE HEADING 'Percent|Streams|Pool|Used' FORMAT 999
COLUMN PUBLISHER_STATE HEADING 'Publisher|State' FORMAT A10
SELECT SENDER_NAME,
SENDER_ADDRESS,
QUEUE_NAME,
CNUM_MSGS,
LAST_ENQUEUED_MSG,
MEMORY_USAGE,
PUBLISHER_STATE
FROM V$BUFFERED_PUBLISHERS;
Your output looks similar to the following:
Percent
Number Last Streams
Capture of LCRs Enqueued Pool Publisher
Process Sender Queue Queue Name Enqueued LCR Used State
---------- --------------- ---------- --------- ----------- ------- ----------
DB1$CAP DB1$CAPQ 3670 1002253 21 PUBLISHING
MESSAGES
DB2$CAP "STRMADMIN"."DB DB2$APPQ 3427 981066 21 PUBLISHING
2$CAPQ"@DB2.EXA MESSAGES
MPLE.COM
This output shows following:
3670 LCRs from the local db1$cap capture process were enqueued into the local queue named db1$capq. The capture process is local because the Sender Queue column is NULL. The message number of the last enqueued LCR from this capture process was 1002253. 21% of the Streams pool is used at the capture process database, and the capture process is publishing messages normally.
3427 LCRs from the db2$cap capture process running on a remote database were propagated from a queue named db2$capq on database db2.example.com to the local queue named db2$appq. The message number of the last enqueued LCR from this sender was 961066. 21% of the Streams pool is used at the remote capture process database, and the capture process is publishing messages normally.
The query in this section displays the following information about each propagation that sends buffered messages from a buffered queue in the local database:
The name of the propagation
The queue owner
The queue name
The name of the database link used by the propagation
The status of the propagation schedule
To display this information, run the following query:
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.SOURCE_QUEUE_OWNER = s.QUEUE_SCHEMA AND
p.SOURCE_QUEUE_NAME = s.QUEUE_NAME AND
p.DESTINATION_QUEUE_OWNER = s.DST_QUEUE_SCHEMA AND
p.DESTINATION_QUEUE_NAME = s.DST_QUEUE_NAME;
Your output looks similar to the following:
Queue Queue Database
Propagation Owner Name Link Schedule Status
--------------- ---------- --------------- ---------- --------------------
PROPAGATION$_6 STRMADMIN DB1$CAPQ "STRMADMIN SCHEDULE OPTIMIZED
"."DB1$APP
Q"@DB2.EXA
MPLE.COM
When the SCHEDULE_STATUS column in the V$PROPAGATION_SENDER view shows SCHEDULE OPTIMIZED for a propagation, it means that the propagation is part of a combined capture and apply optimization.
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:
The name of the propagation
The queue name
The name of the database link used by the propagation
The total number of messages sent since the database instance was last started
The total number of bytes sent since the database instance was last started
To display this information, run the following query:
COLUMN PROPAGATION_NAME HEADING 'Propagation' FORMAT A15
COLUMN QUEUE_NAME HEADING 'Queue|Name' FORMAT A15
COLUMN DBLINK HEADING 'Database|Link' FORMAT A20
COLUMN TOTAL_MSGS HEADING 'Total|Messages' FORMAT 99999999
COLUMN TOTAL_BYTES HEADING 'Total|Bytes' FORMAT 999999999999
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.SOURCE_QUEUE_OWNER = s.QUEUE_SCHEMA AND
p.SOURCE_QUEUE_NAME = s.QUEUE_NAME AND
p.DESTINATION_QUEUE_OWNER = s.DST_QUEUE_SCHEMA AND
p.DESTINATION_QUEUE_NAME = s.DST_QUEUE_NAME;
Your output looks similar to the following:
Queue Database Total Total
Propagation Name Link Messages Bytes
--------------- --------------- -------------------- --------- ---------
MULT1_TO_MULT3 STREAMS_QUEUE MULT3.EXAMPLE.COM 79 71467
MULT1_TO_MULT2 STREAMS_QUEUE MULT2.EXAMPLE.COM 79 71467
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:
The name of the propagation
The queue name
The name of the database link used by the propagation
The amount of time spent dequeuing messages from the queue since the database instance was last started, in seconds
The amount of time spent pickling messages since the database instance was last started, in seconds. Pickling involves changing a message in memory into a series of bytes that can be sent over a network.
The amount of time spent propagating messages since the database instance was last started, in seconds
To display this information, run the following query:
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.SOURCE_QUEUE_OWNER = s.QUEUE_SCHEMA AND
p.SOURCE_QUEUE_NAME = s.QUEUE_NAME AND
p.DESTINATION_QUEUE_OWNER = s.DST_QUEUE_SCHEMA AND
p.DESTINATION_QUEUE_NAME = s.DST_QUEUE_NAME;
Your output looks similar to the following:
Queue Database Dequeue Pickle Propagation
Propagation Name Link Time Time Time
--------------- ------------- --------- ------------ ------------ ------------
MULT1_TO_MULT2 STREAMS_QUEUE MULT2.EXA 30.65 45.10 10.91
MPLE.COM
MULT1_TO_MULT3 STREAMS_QUEUE MULT3.EXA 25.36 37.07 8.35
MPLE.COM
Propagations are queue subscribers that can dequeue messages. By querying the V$BUFFERED_SUBSCRIBERS dynamic performance view, you can display all the propagations that can dequeue buffered messages.
Apply processes also are queue subscribers. This query joins with the DBA_PROPAGATION and V$BUFFERED_QUEUES views to limit the output to propagations only and to show the propagation name of each propagation.
The query in this section displays the following information about each propagation that can dequeue messages from queues:
The name of the propagation.
The owner and name of the queue to which the propagation subscribes
This queue is the source queue for the propagation.
The subscriber address
For a propagation, the subscriber address is the propagation's destination queue and destination database
The time when the propagation last started
The cumulative number of messages dequeued by the propagation since the database last started
The total number of messages dequeued by the propagation since the propagation last started
The message number of the message most recently dequeued by the propagation
To display this information, run the following query:
COLUMN PROPAGATION_NAME HEADING 'Propagation' FORMAT A11
COLUMN QUEUE_SCHEMA HEADING 'Queue|Owner' FORMAT A5
COLUMN QUEUE_NAME HEADING 'Queue|Name' FORMAT A5
COLUMN SUBSCRIBER_ADDRESS HEADING 'Subscriber|Address' FORMAT A15
COLUMN STARTUP_TIME HEADING 'Startup|Time' FORMAT A9
COLUMN CNUM_MSGS HEADING 'Cumulative|Messages' FORMAT 99999999
COLUMN TOTAL_DEQUEUED_MSG HEADING 'Total|Messages' FORMAT 99999999
COLUMN LAST_DEQUEUED_NUM HEADING 'Last|Dequeued|Message|Number' FORMAT 99999999
SELECT p.PROPAGATION_NAME,
s.QUEUE_SCHEMA,
s.QUEUE_NAME,
s.SUBSCRIBER_ADDRESS,
s.STARTUP_TIME,
s.CNUM_MSGS,
s.TOTAL_DEQUEUED_MSG,
s.LAST_DEQUEUED_NUM
FROM DBA_PROPAGATION p, V$BUFFERED_SUBSCRIBERS s
WHERE p.SOURCE_QUEUE_OWNER = s.QUEUE_SCHEMA AND
p.SOURCE_QUEUE_NAME = s.QUEUE_NAME AND
p.PROPAGATION_NAME = s.SUBSCRIBER_NAME AND
s.SUBSCRIBER_ADDRESS LIKE '%' || p.DESTINATION_DBLINK;
Your output looks similar to the following:
Last
Dequeued
Queue Queue Subscriber Startup Cumulative Total Message
Propagation Owner Name Address Time Messages Messages Number
----------- ----- ----- --------------- --------- ---------- --------- ---------
PROPAGATION STRMA DB1$C "STRMADMIN"."DB 25-JUN-09 11079 11079 1525762
$_5 DMIN APQ 1$APPQ"@DB2.EXA
MPLE.COM
Note:
If there are multiple propagations using the same database link but propagating messages to different queues at the destination database, then the statistics returned by this query are approximate rather than accurate.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:
The name of the source queue from which messages are propagated.
The name of the source database.
The amount of time spent unpickling messages since the database instance was last started, in seconds. Unpickling involves changing a series of bytes that can be sent over a network back into a buffered message in memory.
The amount of time spent evaluating rules for propagated messages since the database instance was last started, in seconds.
The amount of time spent enqueuing messages into the destination queue for the propagation since the database instance was last started, in seconds.
To display this information, run the following query:
COLUMN SRC_QUEUE_NAME HEADING 'Source|Queue|Name' FORMAT A20
COLUMN SRC_DBNAME HEADING 'Source|Database' FORMAT A20
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;
Your output looks similar to the following:
Source Rule Queue Source Unpickle Evaluation Enqueue Name Database Time Time Time -------------------- -------------------- ------------ ------------ ------------ STREAMS_QUEUE MULT2.EXAMPLE.COM 45.65 5.44 45.85 STREAMS_QUEUE MULT3.EXAMPLE.COM 53.35 8.01 50.41
Apply processes are queue subscribers that can dequeue messages. By querying the V$BUFFERED_SUBSCRIBERS dynamic performance view, you can display all the apply processes that can dequeue messages.
This query joins with the V$BUFFERED_QUEUES views to show the name of the queue. In addition, propagations also are queue subscribers, and this query limits the output to subscribers where the SUBSCRIBER_ADDRESS is NULL to return only apply processes.
The query in this section displays the following information about the apply processes that can dequeue messages from queues:
The name of the apply process
The queue owner
The queue name
The time when the apply process last started
The cumulative number of messages dequeued by the apply process since the database last started
The total number of messages dequeued by the apply process since the apply process last started
The message number of the message most recently dequeued by the apply process
To display this information, run the following query:
COLUMN SUBSCRIBER_NAME HEADING 'Apply Process' FORMAT A16
COLUMN QUEUE_SCHEMA HEADING 'Queue|Owner' FORMAT A5
COLUMN QUEUE_NAME HEADING 'Queue|Name' FORMAT A5
COLUMN STARTUP_TIME HEADING 'Startup|Time' FORMAT A9
COLUMN CNUM_MSGS HEADING 'Cumulative|Messages' FORMAT 99999999
COLUMN TOTAL_DEQUEUED_MSG HEADING 'Number of|Dequeued|Messages'
FORMAT 99999999
COLUMN LAST_DEQUEUED_NUM HEADING 'Last|Dequeued|Message|Number' FORMAT 99999999
SELECT s.SUBSCRIBER_NAME,
q.QUEUE_SCHEMA,
q.QUEUE_NAME,
s.STARTUP_TIME,
s.CNUM_MSGS,
s.TOTAL_DEQUEUED_MSG,
s.LAST_DEQUEUED_NUM
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;
Your output looks similar to the following:
Last
Number of Dequeued
Queue Queue Startup Cumulative Dequeued Message
Apply Process Owner Name Time Messages Messages Number
---------------- ----- ----- --------- ---------- ---------- ---------
APPLY$_DB2_2 STRMA DB2$A 25-JUN-09 11039 11039 1509859
DMIN PPQ
The following topics describe monitoring propagations and propagation jobs:
Displaying the Queues and Database Link for Each Propagation
Determining the Source Queue and Destination Queue for Each Propagation
Displaying Information About the Schedules for Propagation Jobs
Determining the Total Number of Messages and Bytes Propagated
See Also:
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.
The query in this section displays the following information about each propagation:
The propagation name
The source queue name
The database link used by the propagation
The destination queue name
The status of the propagation, either ENABLED, DISABLED, or ABORTED
Whether the propagation is a queue-to-queue propagation
To display this information about each propagation in a database, run the following query:
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;
Your output looks similar to the following:
Source Dest Queue-
Propagation Queue Database Queue to-
Name Name Link Name Status Queue?
------------------- ----------------- --------- --------------- -------- ------
PROPAGATION$_6 DA$CAPQ DB.EXAMPL DA$APPQ ENABLED TRUE
E.COM
You can determine the source queue and destination queue for each propagation by querying the DBA_PROPAGATION data dictionary view.
The query in this section displays the following information about each propagation:
The propagation name
The source queue owner
The source queue name
The database that contains the source queue
The destination queue owner
The destination queue name
The database that contains the destination queue
To display this information about each propagation in a database, run the following query:
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;
Your output looks similar to the following:
Source Dest
Propagation Queue Source Queue Destination
Name Owner Queue Owner Queue
-------------------- ---------- --------------- ---------- ---------------
PROPAGATION$_6 STRMADMIN DA$CAPQ@DA.EXAM STRMADMIN DA$APPQ@DB.EXAM
PLE.COM PLE.COM
The query in this section displays the following information for each propagation:
The propagation name
The owner of the positive rule set for the propagation
The name of the positive rule set used by the propagation
The owner of the negative rule set used by the propagation
The name of the negative rule set used by the propagation
To display this general information about each propagation in a database, run the following query:
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;
Your output looks similar to the following:
Positive Negative
Propagation Rule Set Positive Rule Rule Set Negative Rule
Name Owner Set Name Owner Set Name
-------------------- ---------- --------------- ---------- ---------------
PROPAGATION$_6 STRMADMIN RULESET$_7 STRMADMIN RULESET$_9
The query in this section displays the following information about the propagation schedules for each propagation job used by a propagation in the database:
The name of the propagation
The latency of the propagation job, which is the maximum wait time to propagate a new message during the duration, when all other messages in the queue to the relevant destination have been propagated
Whether the propagation job is enabled
The name of the process that most recently executed the schedule
The number of consecutive times schedule execution has failed, if any
After 16 consecutive failures, a propagation job is aborted automatically.
Whether the propagation is queue-to-queue or queue-to-dblink
The error message text of the last unsuccessful propagation execution
Run this query at the database that contains the source queue:
COLUMN PROPAGATION_NAME HEADING 'Propagation' FORMAT A15
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 'Failures' FORMAT 999
COLUMN QUEUE_TO_QUEUE HEADING 'Queue|to|Queue'
COLUMN LAST_ERROR_MSG HEADING 'Last Error|Message' FORMAT A15
SELECT p.PROPAGATION_NAME,
s.LATENCY,
DECODE(s.SCHEDULE_DISABLED,
'Y', 'Disabled',
'N', 'Enabled') SCHEDULE_DISABLED,
s.PROCESS_NAME,
s.FAILURES,
p.QUEUE_TO_QUEUE,
s.LAST_ERROR_MSG
FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION p
WHERE s.MESSAGE_DELIVERY_MODE = 'BUFFERED'
AND s.DESTINATION LIKE '%' || p.DESTINATION_DBLINK
AND s.SCHEMA = p.SOURCE_QUEUE_OWNER
AND s.QNAME = p.SOURCE_QUEUE_NAME
ORDER BY PROPAGATION_NAME;
Your output looks similar to the following:
Queue
Latency to Last Error
Propagation in Seconds Status Process Failures Queue Message
--------------- ---------- -------- -------- -------- ------ ---------------
PROPAGATION$_6 19 Enabled CS00 0 TRUE
See Also:
"Propagation Scheduling and Oracle Streams Propagations" for more information about the default propagation schedule for an Oracle Streams propagation job
"Is the Propagation Enabled?" if the propagation job is disabled
Oracle Database Advanced Queuing User's Guide and Oracle Database Reference for more information about the DBA_QUEUE_SCHEDULES data dictionary view
A propagation can be queue-to-queue or queue-to-database link (queue-to-dblink). A queue-to-queue propagation always has its own exclusive propagation job to propagate messages from the source queue to the destination queue. Because each propagation job has its own propagation schedule, the propagation schedule of each queue-to-queue propagation can be managed separately. All queue-to-dblink propagations that share the same database link have a single propagation schedule.
The query in this section displays the following information for each propagation:
The name of the propagation
The total time spent by the system executing the propagation schedule
The total number of messages propagated by the propagation schedule
The total number of bytes propagated by the propagation schedule
Run the following query to display this information for each propagation with a source queue at the local database:
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 s.DESTINATION LIKE '%' || p.DESTINATION_DBLINK
AND s.SCHEMA = p.SOURCE_QUEUE_OWNER
AND s.QNAME = p.SOURCE_QUEUE_NAME
AND s.MESSAGE_DELIVERY_MODE = 'BUFFERED';
Your output looks similar to the following:
Total Time
Propagation Executing Total Messages Total Bytes
Name in Seconds Propagated Propagated
-------------------- ---------- -------------- --------------
PROPAGATION$_6 0 432615 94751013
See Also:
Oracle Database Advanced Queuing User's Guide and Oracle Database Reference for more information about theDBA_QUEUE_SCHEDULES data dictionary viewA propagation sender sends messages from a source queue to a destination queue.
The query in this section displays the following information about each propagation sender in a database:
The name of the propagation
The session identifier of the propagation sender
The session serial number of the propagation sender
The operating system process identification number of the propagation sender
The state of the propagation sender
In a combined capture and apply optimization, the capture process acts as the propagation sender and transmits messages directly to the propagation receiver. When a propagation is part of a combined capture and apply optimization, this query shows the capture process session ID, session serial number, operating system process ID, and state.
When a propagation is not part of a combined capture and apply optimization, this query shows the propagation job session ID, session serial number, operating system process ID, and state.
To view this information, run the following query:
COLUMN PROPAGATION_NAME HEADING 'Propagation|Name' FORMAT A11
COLUMN SESSION_ID HEADING 'Session ID' FORMAT 9999
COLUMN SERIAL# HEADING 'Session|Serial Number' FORMAT 9999
COLUMN SPID HEADING 'Operating System|Process ID' FORMAT A24
COLUMN STATE HEADING 'State' FORMAT A16
SELECT p.PROPAGATION_NAME,
s.SESSION_ID,
s.SERIAL#,
s.SPID,
s.STATE
FROM DBA_PROPAGATION p, V$PROPAGATION_SENDER s
WHERE p.SOURCE_QUEUE_OWNER = s.QUEUE_SCHEMA AND
p.SOURCE_QUEUE_NAME = s.QUEUE_NAME AND
p.DESTINATION_QUEUE_OWNER = s.DST_QUEUE_SCHEMA AND
p.DESTINATION_QUEUE_NAME = s.DST_QUEUE_NAME;
Your output looks similar to the following:
Propagation Session Operating System Name Session ID Serial Number Process ID State ----------- ---------- ------------- ------------------------ ---------------- PROPAGATION 61 17 21145 Waiting on empty $_6 queue
Note:
When columnSCHEDULE_STATUS in the V$PROPAGATION_SENDER view shows SCHEDULE OPTIMIZED, it means that the propagation is part of a combined capture and apply optimization.A propagation receiver enqueues messages sent by propagation senders into a destination queue. The query in this section displays the following information about each propagation receiver in a database:
The name of the propagation
The session ID of the propagation receiver
The session serial number propagation receiver
The operating system process identification number of the propagation receiver
The state of the propagation receiver
To view this information, run the following query:
COLUMN PROPAGATION_NAME HEADING 'Propagation|Name' FORMAT A15
COLUMN SESSION_ID HEADING 'Session ID' FORMAT 999999
COLUMN SERIAL# HEADING 'Session|Serial|Number' FORMAT 999999
COLUMN SPID HEADING 'Operating|System|Process ID' FORMAT 999999
COLUMN STATE HEADING 'State' FORMAT A16
SELECT PROPAGATION_NAME,
SESSION_ID,
SERIAL#,
SPID,
STATE
FROM V$PROPAGATION_RECEIVER;
Your output looks similar to the following:
Session Operating
Propagation Serial System
Name Session ID Number Process ID State
--------------- ---------- ------- ------------------------ ----------------
PROPAGATION$_5 60 5 21050 Waiting for mess
age from propaga
tion sender
The query in this section displays the following session information about each session associated with a propagation in a database:
The Oracle Streams component
The session identifier
The serial number
The operating system process identification number
The process names of the propagation sender and propagation receiver processes
To display this information for each propagation in a database, run the following query:
COLUMN ACTION HEADING 'Streams Component' FORMAT A28
COLUMN SID HEADING 'Session ID' FORMAT 99999
COLUMN SERIAL# HEADING 'Session|Serial|Number' FORMAT 9999999
COLUMN PROCESS HEADING 'Operating System|Process Number' FORMAT A20
COLUMN PROCESS_NAME HEADING 'Process|Names' FORMAT A7
SELECT /*+PARAM('_module_action_old_length',0)*/ ACTION,
SID,
SERIAL#,
PROCESS,
SUBSTR(PROGRAM,INSTR(PROGRAM,'(')+1,4) PROCESS_NAME
FROM V$SESSION
WHERE MODULE ='Streams' AND
ACTION LIKE '%Propagation%';
Your output looks similar to the following:
Session
Serial Operating System Process
Streams Component Session ID Number Process Number Names
---------------------------- ---------- -------- -------------------- -------
APPLY$_DB_3 - Propagation Re 60 5 21048 TNS
ceiver CCA
PROPAGATION$_6 - Propagation 61 17 21145 CS00
Sender CCA
The CCA in the Streams component sample output indicates that the propagation is part of a combined capture and apply optimization. The TNS process name indicates that the propagation receiver was initiated remotely by a capture process.