6 Monitoring XStream

Displaying General Information About an Outbound Server

You can display the following information for an outbound server by running the query in this section:

• The outbound server name

• The name of the connect user for the outbound server

  The connect user is the user who can attach to the outbound server to retrieve the logical change record (LCR) stream. The client application must attach to the outbound server as the specified connect user.

• The name of the capture user for the capture process that captures changes for the outbound server to process

• The name of the capture process that captures changes for the outbound server to process

• The name of the source database for the captured changes

• The owner of the queue used by the outbound server

• The name of the queue used by the outbound server

The DBA_XSTREAM_OUTBOUND view contains information about the capture user, the capture process, and the source database in either of the following cases:

• The outbound server was created using the CREATE_OUTBOUND procedure in the DBMS_XSTREAM_ADM package.

• The outbound server was created using the ADD_OUTBOUND procedure in the DBMS_XSTREAM_ADM package, and the capture process for the outbound server runs on the same database as the outbound server.

If the outbound server was created using the ADD_OUTBOUND procedure, and the capture process for the outbound server is on a different database, then the DBA_XSTREAM_OUTBOUND view does not contain information about the capture user, the capture process, or the source database.

To display this general information about an outbound server: 

1. Connect to the database as the XStream administrator.

   See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

2. Run the following query:

   COLUMN SERVER_NAME HEADING 'Outbound|Server|Name' FORMAT A10
   COLUMN CONNECT_USER HEADING 'Connect|User' FORMAT A10
   COLUMN CAPTURE_USER HEADING 'Capture|User' FORMAT A10
   COLUMN CAPTURE_NAME HEADING 'Capture|Process|Name' FORMAT A11
   COLUMN SOURCE_DATABASE HEADING 'Source|Database' FORMAT A11
   COLUMN QUEUE_OWNER HEADING 'Queue|Owner' FORMAT A10
   COLUMN QUEUE_NAME HEADING 'Queue|Name' FORMAT A10
   
   SELECT SERVER_NAME, 
          CONNECT_USER, 
          CAPTURE_USER, 
          CAPTURE_NAME,
          SOURCE_DATABASE,
          QUEUE_OWNER,
          QUEUE_NAME
     FROM DBA_XSTREAM_OUTBOUND;
   

Your output looks similar to the following:

Outbound                         Capture
Server     Connect    Capture    Process     Source      Queue      Queue
Name       User       User       Name        Database    Owner      Name
---------- ---------- ---------- ----------- ----------- ---------- ----------
XOUT       XSTRMADMIN XSTRMADMIN CAP$_XOUT_1 DB.EXAMPLE. XSTRMADMIN Q$_XOUT_2
                                             COM

Displaying Status and Error Information for an Outbound Server

You can monitor an outbound server using the same queries as you use to monitor an Oracle Streams apply process. See Oracle Streams Concepts and Administration for instructions.

The ALL_APPLY and DBA_APPLY views show XStream Out in the PURPOSE column for an apply process that is functioning as an outbound server.

To display detailed information about an outbound server: 

1. Connect to the database as the XStream administrator.

   See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

2. Run the following query:

   COLUMN APPLY_NAME HEADING 'Apply Name' FORMAT A10
   COLUMN STATUS HEADING 'Status' FORMAT A8
   COLUMN ERROR_NUMBER HEADING 'Error Number' FORMAT 9999999
   COLUMN ERROR_MESSAGE HEADING 'Error Message' FORMAT A40
   
   SELECT APPLY_NAME, 
          STATUS,
          ERROR_NUMBER,
          ERROR_MESSAGE
     FROM DBA_APPLY
     WHERE PURPOSE = 'XStream Out';
   

Your output looks similar to the following:

Apply Name Status   Error Number Error Message
---------- -------- ------------ ----------------------------------------
XOUT       ENABLED

This output shows that XOUT is an apply process that is functioning as an outbound server. Use the instructions in Oracle Streams Concepts and Administration to display detailed information about the outbound server.

Displaying Information About an Outbound Server's Current Transaction

The V$XSTREAM_OUTBOUND_SERVER view contains the following information about the transaction currently being processed by an XStream outbound server:

• The name of the outbound server

• The transaction ID of the transaction currently being processed

• Commit system change number (SCN) of the transaction currently being processed

• Commit position of the transaction currently being processed

• The position of the last LCR sent to the XStream client application

• The message number of the current LCR being processed by the outbound server

Run this query to determine how many LCRs an outbound server has processed in a specific transaction. You can query the TOTAL_MESSAGE_COUNT column in the V$XSTREAM_TRANSACTION view to determine the total number of LCRs in a transaction.

To display information about an outbound server's current transaction: 

1. Connect to the database as the XStream administrator.

   See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

2. Run the following query:

   COLUMN SERVER_NAME HEADING 'Outbound|Server|Name' FORMAT A10
   COLUMN 'Transaction ID' HEADING 'Transaction|ID' FORMAT A11
   COLUMN COMMITSCN HEADING 'Commit SCN' FORMAT 9999999999999
   COLUMN COMMIT_POSITION HEADING 'Commit Position' FORMAT A15
   COLUMN LAST_SENT_POSITION HEADING 'Last Sent|Position' FORMAT A15
   COLUMN MESSAGE_SEQUENCE HEADING 'Message|Number' FORMAT 999999999
    
   SELECT SERVER_NAME,
          XIDUSN ||'.'|| 
          XIDSLT ||'.'||
          XIDSQN "Transaction ID",
          COMMITSCN,
          COMMIT_POSITION,
          LAST_SENT_POSITION,
          MESSAGE_SEQUENCE
     FROM V$XSTREAM_OUTBOUND_SERVER;
   

Your output looks similar to the following:

Outbound
Server     Transaction                                Last Sent          Message
Name       ID              Commit SCN Commit Position Position            Number
---------- ----------- -------------- --------------- --------------- ----------
XOUT       17.23.59            645856 00000009DAE0000 00000009DAE0000          4
                                      000010000000100 000010000000100
                                      000009DAE000000 000009DAE000000
                                      0010000000101   0010000000101

Displaying Statistics for an Outbound Server

The V$XSTREAM_OUTBOUND_SERVER view contains the following statistics about the database changes processed by an XStream outbound server:

• The name of the outbound server

• The number of transactions sent from the outbound server to the XStream client application since the last time the client application attached to the outbound server

• The number of LCRs sent from the outbound server to the XStream client application since the last time the client application attached to the outbound server

• The number of megabytes sent from the outbound server to the XStream client application since the last time the client application attached to the outbound server

• The amount of time the outbound server spent sending LCRs to the XStream client application since the last time the client application attached to the outbound server

• The message number of the last LCR sent by the outbound server to the XStream client application

• Creation time at the source database of the last LCR sent by the outbound server to the client application

To display statistics for an outbound server: 

1. Connect to the database as the XStream administrator.

   See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

2. Run the following query:

   COLUMN SERVER_NAME HEADING 'Outbound|Server|Name' FORMAT A8
   COLUMN TOTAL_TRANSACTIONS_SENT HEADING 'Total|Trans|Sent' FORMAT 9999999
   COLUMN TOTAL_MESSAGES_SENT HEADING 'Total|LCRs|Sent' FORMAT 9999999999
   COLUMN BYTES_SENT HEADING 'Total|MB|Sent' FORMAT 99999999999999
   COLUMN ELAPSED_SEND_TIME HEADING 'Time|Sending|LCRs|(in seconds)' FORMAT 99999999
   COLUMN LAST_SENT_MESSAGE_NUMBER HEADING 'Last|Sent|Message|Number' FORMAT 99999999
   COLUMN LAST_SENT_MESSAGE_CREATE_TIME HEADING 'Last|Sent|Message|Creation|Time' FORMAT A10
    
   SELECT SERVER_NAME,
          TOTAL_TRANSACTIONS_SENT,
          TOTAL_MESSAGES_SENT,
          (BYTES_SENT/1024)/1024 BYTES_SENT,
          (ELAPSED_SEND_TIME/100) ELAPSED_SEND_TIME,
          LAST_SENT_MESSAGE_NUMBER,
          LAST_SENT_MESSAGE_CREATE_TIME
     FROM V$XSTREAM_OUTBOUND_SERVER;
   

Your output looks similar to the following:

Last
                                                      Time      Last Sent
Outbound    Total       Total           Total      Sending      Sent Message
Server      Trans        LCRs              MB         LCRs   Message Creation
Name         Sent        Sent            Sent (in seconds)    Number Time
-------- -------- ----------- --------------- ------------ --------- ----------
XOUT         2000      216000              56          291   9381070 4-AUG-10
                                                                     11:03 A.M.

Displaying the Processed Low Position for an Outbound Server

For an outbound server, the processed low position is the position below which all transactions have been committed and logged by the client application. The processed low position is important when the outbound server or the client application is restarted.

You can display the following information about the processed low position for an outbound server by running the query in this section:

• The outbound server name

• The name of the source database for the captured changes

• The processed low position, which indicates the low watermark position processed by the client application

• The time when the processed low position was last updated by the outbound server

To display the processed low position for an outbound server: 

1. Connect to the database as the XStream administrator.

   See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

2. Run the following query:

   COLUMN SERVER_NAME HEADING 'Outbound|Server|Name' FORMAT A10
   COLUMN SOURCE_DATABASE HEADING 'Source|Database' FORMAT A20
   COLUMN PROCESSED_LOW_POSITION HEADING 'Processed|Low LCR|Position' FORMAT A30
   COLUMN PROCESSED_LOW_TIME HEADING 'Processed|Low|Time' FORMAT A9
   
   SELECT SERVER_NAME,
          SOURCE_DATABASE,
          PROCESSED_LOW_POSITION,
          TO_CHAR(PROCESSED_LOW_TIME,'HH24:MI:SS MM/DD/YY') PROCESSED_LOW_TIME
   FROM DBA_XSTREAM_OUTBOUND_PROGRESS; 
   

Your output looks similar to the following:

Outbound                        Processed                      Processed
Server     Source               Low LCR                        Low
Name       Database             Position                       Time
---------- -------------------- ------------------------------ ---------
XOUT       DB.EXAMPLE.COM       00000008F17A000000000000000000 13:39:01
                                000008F17A000000000000000001   07/15/09

Determining the Process Information for an Outbound Server

An outbound server is an Oracle background process. This background process runs only when an XStream client application attaches to the outbound server. The V$XSTREAM_OUTBOUND_SERVER view contains information about this background process.

You can display the following information for an outbound server by running the query in this section:

• The outbound server name

• The session ID of the outbound server's session

• The serial number of the outbound server's session

• The process identification number of the operating-system process that sends LCRs to the client application

To display the process information for an outbound server: 

1. Connect to the database as the XStream administrator.

   See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

2. Run the following query:

   COLUMN SERVER_NAME HEADING 'Outbound Server Name' FORMAT A20
   COLUMN SID HEADING 'Session ID' FORMAT 9999999999
   COLUMN SERIAL# HEADING 'Serial Number' FORMAT 9999999999
   COLUMN SPID HEADING 'Operating-System Process' FORMAT A25
   
   SELECT SERVER_NAME, 
          SID, 
          SERIAL#, 
          SPID
     FROM V$XSTREAM_OUTBOUND_SERVER;
   

Your output looks similar to the following:

Outbound Server Name  Session ID Serial Number Operating-System Process
-------------------- ----------- ------------- -------------------------
XOUT                          53           406 25783

Displaying General Information About an Inbound Server

You can display the following information for an inbound server by running the query in this section:

• The inbound server name

• The owner of the queue used by the inbound server

• The name of the queue used by the inbound server

• The apply user for the inbound server

To display general information about an inbound server: 

1. Connect to the database as the XStream administrator.

   See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

2. Run the following query:

   COLUMN SERVER_NAME HEADING 'Inbound Server Name' FORMAT A20
   COLUMN QUEUE_OWNER HEADING 'Queue Owner' FORMAT A15
   COLUMN QUEUE_NAME HEADING 'Queue Name' FORMAT A15
   COLUMN APPLY_USER HEADING 'Apply User' FORMAT A15
    
   SELECT SERVER_NAME, 
          QUEUE_OWNER,
          QUEUE_NAME,
          APPLY_USER
     FROM DBA_XSTREAM_INBOUND;
   

Your output looks similar to the following:

Inbound Server Name  Queue Owner     Queue Name      Apply User
-------------------- --------------- --------------- ---------------
XIN                  XSTRMADMIN      XQUEUE          XSTRMADMIN

Displaying the Status and Error Information for an Inbound Server

You can monitor an inbound server using the same queries that you use to monitor an Oracle Streams apply process. See Oracle Streams Concepts and Administration for instructions.

The ALL_APPLY and DBA_APPLY views show XStream In in the PURPOSE column for an apply process that is functioning as an inbound server.

To display the status of an inbound server: 

1. Connect to the database as the XStream administrator.

   See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

2. Run the following query:

   COLUMN APPLY_NAME HEADING 'Apply Name' FORMAT A10
   COLUMN STATUS HEADING 'Status' FORMAT A8
   COLUMN ERROR_NUMBER HEADING 'Error Number' FORMAT 9999999
   COLUMN ERROR_MESSAGE HEADING 'Error Message' FORMAT A40
   
   SELECT APPLY_NAME, 
          STATUS,
          ERROR_NUMBER,
          ERROR_MESSAGE
     FROM DBA_APPLY
     WHERE PURPOSE = 'XStream In';
   

Your output looks similar to the following:

Apply Name Status   Error Number Error Message
---------- -------- ------------ ----------------------------------------
XIN        ENABLED

This output shows that XIN is an apply process that is functioning as an inbound server. Use the instructions in Oracle Streams Concepts and Administration to display detailed information about the inbound server.

Displaying the Position Information for an Inbound Server

For an inbound server, you can view position information by querying the DBA_XSTREAM_INBOUND_PROGRESS view. Specifically, you can display the following position information by running the query in this section:

• The inbound server name

• The applied low position for the inbound server

• The spill position for the inbound server

• The applied high position for the inbound server

• The processed low position for the inbound server

To display the position information for an inbound server: 

1. Connect to the database as the XStream administrator.

   See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

2. Run the following query:

   COLUMN SERVER_NAME HEADING 'Inbound|Server|Name' FORMAT A10
   COLUMN APPLIED_LOW_POSITION HEADING 'Applied Low|Position' FORMAT A15
   COLUMN SPILL_POSITION HEADING 'Spill Position' FORMAT A15
   COLUMN APPLIED_HIGH_POSITION HEADING 'Applied High|Position' FORMAT A15
   COLUMN PROCESSED_LOW_POSITION HEADING 'Processed Low|Position' FORMAT A15
    
   SELECT SERVER_NAME, 
          APPLIED_LOW_POSITION,
          SPILL_POSITION,
          APPLIED_HIGH_POSITION,
          PROCESSED_LOW_POSITION
     FROM DBA_XSTREAM_INBOUND_PROGRESS;
   

Your output looks similar to the following:

Inbound
Server     Applied Low                     Applied High    Processed Low
Name       Position        Spill Position  Position        Position
---------- --------------- --------------- --------------- ---------------
XIN        C10A            C11D            C10A            C11D

The values of the positions shown in the output were set by the client application that attaches to the inbound server. However, the inbound server determines which values are the current applied low position, spill position, applied high position, and processed low position.

XStream Components

The Oracle Streams Performance Advisor tracks the following types of components in an XStream environment:

• QUEUE

• CAPTURE

• PROPAGATION SENDER

• PROPAGATION RECEIVER

• APPLY

The preceding types are the same in an Oracle Streams environment and an XStream environment, except for APPLY. The APPLY component type can be an XStream outbound server or inbound server.

The following subcomponent types are possible for apply processes, outbound servers, and inbound servers:

• PROPAGATION SENDER+RECEIVER for sending LCRs from a capture process directly to an apply process or outbound server in a combined capture and apply optimization

• APPLY READER for a reader server

• APPLY COORDINATOR for a coordinator process

• APPLY SERVER for an apply server

In addition, the Oracle Streams Performance Advisor identifies a bottleneck component as the busiest component or the component with the least amount of idle time. In an XStream configuration, the XStream client application might be the bottleneck when EXTERNAL appears in the ACTION_NAME column of the DBA_STREAMS_TP_PATH_BOTTLENECK view.

Topology and Stream Paths

In the Oracle Streams topology, a stream path is a flow of messages from a source to a destination. A stream path begins where a capture process, synchronous capture, or application enqueues messages into a queue. A stream path ends where an apply process, outbound server, or inbound server dequeues the messages. The stream path might flow through multiple queues and propagations before it reaches an apply process, outbound server, or inbound server. Therefore, a single stream path can consist of multiple source/destination component pairs before it reaches last component.

The Oracle Streams topology only gathers information about a stream path if the stream path ends with an apply process, an outbound server, or an inbound server. The Oracle Streams topology does not track stream paths that end when a messaging client or an application that dequeues messages.

XStream and Component-Level Statistics

The Oracle Streams Performance Advisor tracks the following component-level statistics:

• The MESSAGE APPLY RATE is the average number of messages applied each second by the apply process, outbound server, or inbound server.

• The TRANSACTION APPLY RATE is the average number of transactions applied by the apply process, outbound server, or inbound server each second. Transactions typically include multiple messages.

An LCR can be applied in one of the following ways:

• An apply process or inbound server makes the change encapsulated in the LCR to a database object.

• An apply process or inbound server passes the LCR to an apply handler.

• If the LCR raises an error, then an apply process or inbound server sends the LCR to the error queue.

• An outbound server passes the LCR to an XStream client application. If the LCR raises an error, then the outbound server also reports the error to the client application.

Also, the Oracle Streams Performance Advisor tracks the LATENCY component-level statistics. LATENCY is defined in the following ways:

• For apply processes, the LATENCY is the amount of time between when the message was created at a source database and when the message was applied by the apply process at the destination database.

• For outbound servers, the LATENCY is amount of time between when the message was created at a source database and when the message was sent to the XStream client application.

• For inbound servers, the LATENCY is amount of time between when the message was created by the XStream client application and when the message was applied by the apply process.

When a capture process creates an LCR, the message creation time is the time when the redo entry for the database change was recorded. When an XStream client application creates an LCR, the message creation time is the time when the LCR was constructed.

The UTL_SPADV Package

The UTL_SPADV package provides subprograms to collect and analyze statistics for the XStream components in a distributed database environment. The package uses the Oracle Streams Performance Advisor to gather statistics, and the output is formatted so that it can be imported into a spreadsheet easily and analyzed.

The UTL_SPADV package works the same way for an Oracle Streams environment with apply processes and an XStream environment with outbound servers or inbound servers. However, there are some differences in the output for the SHOW_STATS procedure. This section describes the differences between the output for apply processes and the output for XStream outbound servers and inbound servers.

The following sections describe the output for the SHOW_STATS procedure for outbound servers and inbound servers:

• Sample Output When an Outbound Server Is the Last Component in a Path

• Sample Output When an Inbound Server Is the Last Component in a Path

OUTPUT                                                    
PATH 1 RUN_ID 2 RUN_TIME 2009-MAY-15 12:20:55 CCA Y
|<C> CAP$_XOUT_1 2733 2730 3392 LMR 8.3% 91.7% 0% "" LMP (1) 8.3% 91.7% 0% ""
LMB 8.3% 91.7% 0% "" CAP 8.3% 91.7% 0% "" |<Q> "XSTRMADMIN"."Q$_XOUT_2" 2730 0.01
4109 |<A> XOUT 2329 2.73 0 -1 PS+PR 8.3% 91.7% 0% "" APR 8.3% 91.7% 0% "" APC
100% 0% 0% "" APS (1) 8.3% 83.3% 8.3% "" |<B> "EXTERNAL"
.
.
.

OUTPUT                                                    
PATH 1 RUN_ID 2 RUN_TIME 2009-MAY-16 10:11:38 CCA N
|<PR> "clientcap"=> 75% 0% 8.3% "CPU + Wait for CPU" |<Q> "XSTRMADMIN"."QUEUE2"  467 0.01 1 
|<A> XIN 476 4.71 0 APR 100% 0% 0% "" APC 100% 0% 0% "" APS (4) 366.7% 0% 33.3% "CPU + Wait for CPU" 
|<B> "EXTERNAL"
.
.
.