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

To display this general information about an outbound server, 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       STRMADMIN  STRMADMIN  CAP$_XOUT_1 DB.EXAMPLE. STRMADMIN  Q$_XOUT_2
                                             COM

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 in this package, 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.

Displaying Detailed Information About 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. For example, run the following query:

COLUMN APPLY_NAME HEADING 'Apply Name' FORMAT A10
COLUMN STATUS HEADING 'Status' FORMAT A8
COLUMN PURPOSE HEADING 'Purpose' FORMAT A20
COLUMN ERROR_NUMBER HEADING 'Error Number' FORMAT 9999999
COLUMN ERROR_MESSAGE HEADING 'Error Message' FORMAT A20

SELECT APPLY_NAME, 
       STATUS,
       PURPOSE,
       ERROR_NUMBER,
       ERROR_MESSAGE
  FROM DBA_APPLY;

Your output looks similar to the following:

Apply Name Status   Purpose              Error Number Error Message
---------- -------- -------------------- ------------ --------------------
XOUT       ENABLED  XSTREAM OUT

This output shows that XOUT is an apply process that is functioning as an outbound server.

Displaying the Processed Low Position for an Outbound Server

For an outbound server, the processed low position is a 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

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

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 Oracle Process ID and Operating System ID of an Outbound Server

An outbound server is an Oracle background process. When a client application attaches to an outbound server, the Oracle process ID and operating system process ID of the outbound server process is recorded in the alert log. A message similar to the following records these IDs:

XStream Outbound Server for XOUT attached with pid=30 OS id=12873

In this example, the Oracle process ID is 30 and the operating system process ID is 12873.

Monitoring XStream Out Components Using General Oracle Streams Views

With XStream Out, an Oracle Streams apply process functions as an outbound server. Therefore, you can use the data dictionary views for apply processes to monitor outbound servers. In addition, an XStream Out environment includes capture processes and queues, and might include other Oracle Streams components, such as propagations, rules, and rule-based transformations.

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 this general information about an inbound server, 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                  STRMADMIN       XQUEUE          STRMADMIN

Displaying Detailed Information About an Inbound Server

You can monitor an inbound 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 In" in the PURPOSE column for an apply process that is functioning as an inbound server. For example, run the following query:

COLUMN APPLY_NAME HEADING 'Apply Name' FORMAT A10
COLUMN STATUS HEADING 'Status' FORMAT A8
COLUMN PURPOSE HEADING 'Purpose' FORMAT A20
COLUMN ERROR_NUMBER HEADING 'Error Number' FORMAT 9999999
COLUMN ERROR_MESSAGE HEADING 'Error Message' FORMAT A20

SELECT APPLY_NAME, 
       STATUS,
       PURPOSE,
       ERROR_NUMBER,
       ERROR_MESSAGE
  FROM DBA_APPLY;

Your output looks similar to the following:

Apply Name Status   Purpose              Error Number Error Message
---------- -------- -------------------- ------------ --------------------
XIN        ENABLED  XSTREAM IN

This output shows that XIN is an apply process that is functioning as an 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

Run the following query to view this information:

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.

Determining the Process ID and Operating System ID of a Propagation Receiver

A propagation receiver gets the logical change records (LCRs) sent by a client application and passes the LCRs to an inbound server. When a client application attaches to an inbound server, the process ID and operating system ID of the propagation receiver is recorded in the alert log. A message similar to the following records the process ID and operating system ID:

Propagation Receiver (CCA) for XStream In From_XOUT and Apply XIN with pid=23,
OS id=1980, objnum=0 started.

In this example, the process ID is 23 and the operating system ID is 1980.

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 following types are the same in an Oracle Streams environment and an XStream environment: QUEUE, CAPTURE, PROPAGATION SENDER, and PROPAGATION RECEIVER.

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 in a combined capture and apply optimization

• APPLY READER for a reader server

• APPLY COORDINATOR for a coordinator process

• APPLY SERVER for a reader 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 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 each second. Transactions typically include multiple messages.

A logical change record (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.

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

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

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 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 at the destination database.

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.

The following sections describe the output for the SHOW_STATS procedure:

• 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> "STRMADMIN"."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> "STRMADMIN"."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"
.
.
.