Skip Headers
Oracle® Streams Concepts and Administration
11g Release 1 (11.1)

B28321-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

24 Monitoring Oracle Streams Queues and Propagations

The following topics describe monitoring Oracle Streams queues and propagations:

Note:

The Oracle Streams tool in the Oracle Enterprise Manager Console is also an excellent way to monitor an Oracle Streams environment. See Oracle Database 2 Day + Data Replication and Integration Guide and the online Help for the Oracle Streams tool for more information.

See Also:

Monitoring Queues and Messaging

The following topics describe displaying information about queues and messaging:

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;

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.

Viewing the Messaging Clients in a Database

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:

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 clients

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 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

Determining the Consumer of Each Message in a Persistent Queue

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 Streams Advanced Queuing User's Guide for an example that enqueues messages into an ANYDATA queue

Viewing the Contents of Messages in a Persistent 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 an ANYDATA 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 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')

Monitoring Buffered Queues

A buffered queue includes the following storage areas:

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:

Determining the Number of Messages in 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

Viewing the Capture Processes for the LCRs in Each Buffered Queue

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

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.EXAMPLE.COM

CAPTURE_HR    "STRMADMIN"."STREAMS_QUEUE" STREAMS_QUEUE         387       840
              @MULT2.EXAMPLE.COM

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.example.com 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.example.com 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.

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:

  • 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
--------------- ---------- --------------- ---------- --------------------
MULT1_TO_MULT3  STRMADMIN  STREAMS_QUEUE   MULT3.EXAM SCHEDULE ENABLED
                                           PLE.COM
MULT1_TO_MULT2  STRMADMIN  STREAMS_QUEUE   MULT2.EXAM SCHEDULE ENABLED
                                           PLE.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 configuration.

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:

  • 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

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:

  • 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

Viewing the Propagations Dequeuing Messages from Each Buffered Queue

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.

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 Oracle 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 
      s.SUBSCRIBER_ADDRESS LIKE '%' || p.DESTINATION_DBLINK;

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.EXAMP       157       144       129        24            0
                LE.COM
MULT1_TO_MULT3  MULT3.EXAMP        98        88        81        53            0
                LE.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.

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:

  • 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

Viewing the Apply Processes Dequeuing Messages from Each Buffered Queue

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.

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 Oracle 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

Monitoring Oracle Streams Propagations and Propagation Jobs

The following topics describe monitoring propagations and propagation jobs:

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.

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.EXA STREAMS_APPLY_Q ENABLED  FALSE
                                      MPLE.COM

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.

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.EXAMPL            @INST2.EXAMPLE
                                E.COM                      .COM

Determining the Rule Sets for Each Propagation

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

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:

  • 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 s.DESTINATION LIKE '%' || p.DESTINATION_DBLINK
    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 an Oracle 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:

Determining the Total Number of Messages and Bytes Propagated

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;

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 Oracle Database Reference for more information about the DBA_QUEUE_SCHEDULES data dictionary view

Displaying Information About Propagation Senders

A 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 configuration, 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 configuration, 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 configuration, 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        951             1 22594                    Capturing change
_SPOKE1                                                        records

This output shows that the propagation sender is part of a combined capture and apply configuration because the capture process state is shown. Also, when column SCHEDULE_STATUS in the V$PROPAGATION_SENDER view shows SCHEDULE OPTIMIZED, it means that the propagation is part of a combined capture and apply configuration.

Displaying Information About Propagation Receivers

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 A11
COLUMN SESSION_ID HEADING 'Session ID' FORMAT 99999999
COLUMN SERIAL# HEADING 'Session|Serial Number' FORMAT 99999999
COLUMN SPID HEADING 'Operating System|Process ID' FORMAT 99999999999
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_RECEIVER s
  WHERE p.SOURCE_QUEUE_OWNER      = s.SRC_QUEUE_SCHEMA AND
        p.SOURCE_QUEUE_NAME       = s.SRC_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        940             1 22636                    Waiting for mess
_SPOKE1                                                       age from client
 
PROPAGATION        928             4 29154                    Waiting for mess
_SPOKE2                                                       age from client