| Oracle® Streams Concepts and Administration 10g Release 2 (10.2) Part Number B14229-01 |
|
|
View PDF |
| Oracle® Streams Concepts and Administration 10g Release 2 (10.2) Part Number B14229-01 |
|
|
View PDF |
This chapter provides sample queries that you can use to monitor Streams queues and propagations.
This chapter contains these topics:
|
Note: The Streams tool in the Oracle Enterprise Manager Console is also an excellent way to monitor a Streams environment. See the online help for the Streams tool for more information. |
|
See Also:
|
The following sections contain instructions for displaying information about ANYDATA queues and messaging:
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
---------- ---------------------------- ---------------------- ---------------
SYS AQ$_SCHEDULER$_JOBQTAB_E SCHEDULER$_JOBQTAB exception queue
SYS SCHEDULER$_JOBQ SCHEDULER$_JOBQTAB Scheduler job q
ueue
SYS AQ$_DIR$EVENT_TABLE_E DIR$EVENT_TABLE exception queue
SYS DIR$EVENT_QUEUE DIR$EVENT_TABLE
SYS AQ$_DIR$CLUSTER_DIR_TABLE_E DIR$CLUSTER_DIR_TABLE exception queue
SYS DIR$CLUSTER_DIR_QUEUE DIR$CLUSTER_DIR_TABLE
STRMADMIN AQ$_STREAMS_QUEUE_TABLE_E STREAMS_QUEUE_TABLE exception queue
STRMADMIN STREAMS_QUEUE STREAMS_QUEUE_TABLE
An exception queue is created automatically when you create an ANYDATA queue.
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
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;
Your output looks similar to the following:
Messaging Queue Notification Notification Client Owner Queue Name Type Action ---------- ----- -------------------- --------------- ------------------------- OE OE NOTIFICATION_QUEUE MAIL mary.smith@mycompany.com
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;
Your output looks similar to the following:
Message ID Message State Consumer -------------------------------- ------------- ------------------------------ B79AC412AE6E08CAE034080020AE3E0A PROCESSED OE B79AC412AE6F08CAE034080020AE3E0A PROCESSED OE B79AC412AE7008CAE034080020AE3E0A PROCESSED OE
|
See Also: Oracle Streams Advanced Queuing User's Guide and Reference for an example that enqueues messages into anANYDATA 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.
|
See Also: "Wrapping User Message Payloads in an ANYDATA Wrapper and Enqueuing Them" for an example that enqueues the messages shown in the queries in this section into anANYDATA queue |
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;
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 datatype, 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, connect as the Streams administrator and create a function similar to the following:
CONNECT oe/oe
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:
CONNECT strmadmin/strmadminpw 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 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. 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 messages always are stored in buffered queues, but user-enqueued LCRs and user 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 messages, or user-enqueued messages, or both.
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 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.
The query in this section assumes that the buffered queues in the local database only store captured messages, not user-enqueued 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 messages have been propagated to the local queue, then the name of the queue and database from which the captured messages were last propagated
The name of the local queue staging the captured messages
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
To display this information, run the following 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;
Your output looks similar to the following:
Number Last
Capture of LCRs Enqueued
Process Sender Queue Queue Name Enqueued LCR
------------- --------------------------- --------------- --------- ---------
CAPTURE_HR "STRMADMIN"."STREAMS_QUEUE" STREAMS_QUEUE 382 844
@MULT3.NET
CAPTURE_HR "STRMADMIN"."STREAMS_QUEUE" STREAMS_QUEUE 387 840
@MULT2.NET
CAPTURE_HR STREAMS_QUEUE 75 833
This output shows following:
382 LCRs from the capture_hr capture process running on a remote database were propagated from a queue named streams_queue on database mult3.net to the local queue named streams_queue. The message number of the last enqueued LCR from this sender was 844.
387 LCRs from the capture_hr capture process running on a remote database were propagated from a queue named streams_queue on database mult2.net to the local queue named streams_queue. The message number of the last enqueued LCR from this sender was 840.
75 LCRs from the local capture_hr capture process were enqueued into the local queue named streams_queue. 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 833.
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.DESTINATION_DBLINK = s.DBLINK AND
p.SOURCE_QUEUE_OWNER = s.QUEUE_SCHEMA AND
p.SOURCE_QUEUE_NAME = s.QUEUE_NAME;
Your output looks similar to the following:
Queue Queue Database Propagation Owner Name Link Schedule Status --------------- ---------- --------------- ---------- -------------------- MULT1_TO_MULT3 STRMADMIN STREAMS_QUEUE MULT3.NET SCHEDULE ENABLED MULT1_TO_MULT2 STRMADMIN STREAMS_QUEUE MULT2.NET SCHEDULE ENABLED
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 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;
Your output looks similar to the following:
Queue Database Total Total Propagation Name Link Messages Bytes --------------- --------------- ---------- --------- --------- MULT1_TO_MULT3 STREAMS_QUEUE MULT3.NET 79 71467 MULT1_TO_MULT2 STREAMS_QUEUE MULT2.NET 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.DESTINATION_DBLINK = s.DBLINK AND
p.SOURCE_QUEUE_OWNER = s.QUEUE_SCHEMA AND
p.SOURCE_QUEUE_NAME = s.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.NET 30.65 45.10 10.91 MULT1_TO_MULT3 STREAMS_QUEUE MULT3.NET 25.36 37.07 8.35
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.
You can also use the V$BUFFERED_SUBSCRIBERS dynamic performance view to determine the performance of a propagation. For example, if a propagation has a high number of spilled messages, then that propagation might not be dequeuing messages fast enough from the buffered queue. Spilling messages to a queue table has a negative impact on the performance of your Streams environment.
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 destination database, which is the database that contains the destination queue for the propagation.
The sequence number for the message most recently enqueued into the queue. The sequence number for message shows the order of the message in the queue.
The sequence number for the message in the queue most recently browsed by the propagation.
The sequence number for the message most recently dequeued from the queue by the propagation.
The current number of messages in the queue waiting to be dequeued by the propagation.
The cumulative number of messages spilled from memory to the queue table for the propagation since the database last started.
To display this information, run the following query:
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;
Your output looks similar to the following:
Number of Number of
Current Last Last Messages Spilled
Destination Enqueued Browsed Dequeued in Queue Messages
Propagation Database Sequence Sequence Sequence (Current) (Cumulative)
--------------- ----------- --------- --------- --------- --------- ------------
MULT1_TO_MULT2 MULT2.NET 157 144 129 24 0
MULT1_TO_MULT3 MULT3.NET 98 88 81 53 0
|
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 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;
Your output looks similar to the following:
Source Rule Queue Source Unpickle Evaluation Enqueue Name Database Time Time Time -------------------- --------------- ------------ ------------ ------------ STREAMS_QUEUE MULT2.NET 45.65 5.44 45.85 STREAMS_QUEUE MULT3.NET 53.35 8.01 50.41
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.
You can also use the V$BUFFERED_SUBSCRIBERS dynamic performance view to determine the performance of an apply process. For example, if an apply process has a high number of spilled messages, then that apply process might not be dequeuing messages fast enough from the buffered queue. Spilling messages to a queue table has a negative impact on the performance of your Streams environment.
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 sequence number for the message most recently dequeued by the apply process. The sequence number for message shows the order of the message in the queue.
The current number of messages in the queue waiting to be dequeued by the apply process.
The cumulative number of messages spilled from memory to the queue table for the apply process since the database last started.
To display this information, run the following query:
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;
Your output looks similar to the following:
Last Number of Number of
Queue Queue Dequeued Messages Spilled
Apply Process Owner Name Sequence in Queue Messages
(Current)(Cumulative)
---------------- ---------- --------------- --------- --------- ------------
APPLY_FROM_MULT3 STRMADMIN STREAMS_QUEUE 49 148 0
APPLY_FROM_MULT2 STRMADMIN STREAMS_QUEUE 85 241 1
The following sections contain queries that you can run to display information about propagations and propagation jobs:
Displaying the Queues and Database Link for Each Propagation
Determining the Source Queue and Destination Queue for Each Propagation
Determining the Total Number of Messages and Bytes Propagated
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? ------------------- ----------------- --------- --------------- -------- ------ STREAMS_PROPAGATION STREAMS_CAPTURE_Q INST2.NET STREAMS_APPLY_Q ENABLED FALSE
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
-------------------- ---------- --------------- ---------- ---------------
STREAMS_PROPAGATION STRMADMIN STREAMS_CAPTURE STRMADMIN STREAMS_APPLY_Q
_Q@INST1.NET @INST2.NET
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 -------------------- ---------- --------------- ---------- --------------- STRM01_PROPAGATION STRMADMIN RULESET$_22 STRMADMIN RULESET$_31
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:
The date and time when the propagation schedule started (or will start).
The duration of the propagation job, which is the amount of time the job propagates messages before restarting.
The next time the propagation will start.
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 or not 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 becomes disabled automatically.
Run this query at the database that contains the source queue:
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;
Your output looks similar to the following:
Duration Next Latency Number of Start Date in Seconds Time in Seconds Status Process Failures ----------------- ---------- -------- ---------- -------- -------- --------- 15:23:40 03/02/02 5 Enabled J002 0
This propagation job uses the default schedule for a Streams propagation job. That is, the duration and next time are both NULL, and the latency is five seconds. When the duration is NULL, the job propagates changes without restarting automatically. When the next time is NULL, the propagation job is running currently.
|
See Also:
|
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:
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 p.DESTINATION_DBLINK = s.DESTINATION
AND s.SCHEMA = p.SOURCE_QUEUE_OWNER
AND s.QNAME = p.SOURCE_QUEUE_NAME;
Your output looks similar to the following:
Total Time Propagation Executing Total Messages Total Bytes Name in Seconds Propagated Propagated -------------------- ---------- -------------- -------------- MULT3_TO_MULT1 351 872 875252 MULT3_TO_MULT2 596 872 875252
|
See Also: Oracle Streams Advanced Queuing User's Guide and Reference and Oracle Database Reference for more information about theDBA_QUEUE_SCHEDULES data dictionary view |
