Skip Headers
Oracle® Database XStream Guide
11g Release 2 (11.2)

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

6 Monitoring XStream

This chapter provides instructions for monitoring XStream.

This chapter contains these topics:

About Monitoring XStream

This chapter describes monitoring an XStream Out configuration and an XStream In configuration. This chapter provides instructions for querying data dictionary views related to XStream. The queries provide information about XStream components and statistics related to XStream.

The main interface for monitoring XStream database components is SQL*Plus, although you can monitor some aspects of an XStream configuring using Oracle Enterprise Manager. For example, you can view information about capture processes, outbound servers, inbound servers, and rules in Enterprise Manager. Outbound servers and inbound servers appear as apply processes in Enterprise Manager.

This chapter also describes using the Oracle Streams Performance Advisor to monitor an XStream configuration. The Oracle Streams Performance Advisor consists of the DBMS_STREAMS_ADVISOR_ADM package and a collection of data dictionary views. The Oracle Streams Performance Advisor enables you to monitor the topology and performance of an XStream environment.

Monitoring Session Information About XStream Components

Note:

This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2).

The query in this section displays the following session information about each XStream component in a database:

  • The XStream component name

  • The session identifier

  • The serial number

  • The operating system process identification number

  • The XStream process number

This query is especially useful for determining the session information for specific XStream components when there are multiple XStream Out or XStream In components configured in a database.

To display this information for each XStream component in a database: 

  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 ACTION HEADING 'XStream Component' FORMAT A30
    COLUMN SID HEADING 'Session ID' FORMAT 99999
    COLUMN SERIAL# HEADING 'Session|Serial|Number' FORMAT 99999999
    COLUMN PROCESS HEADING 'Operating System|Process Number' FORMAT A17
    COLUMN PROCESS_NAME HEADING 'XStream|Process|Number' FORMAT A7
     
    SELECT /*+PARAM('_module_action_old_length',0)*/ ACTION,
           SID,
           SERIAL#,
           PROCESS,
           SUBSTR(PROGRAM,INSTR(PROGRAM,'(')+1,4) PROCESS_NAME
      FROM V$SESSION
      WHERE MODULE ='XStream';
    

Your output for an XStream Out configuration looks similar to the following:

                                            Session                   XStream
                                             Serial Operating System  Process
XStream Component              Session ID    Number Process Number    Number
------------------------------ ---------- --------- ----------------- -------
XOUT - Apply Server                    35        33 16386             TNS
XOUT - Apply Coordinator               41         1 14093             AP01
XOUT - Apply Reader                    43         1 14095             AS01
XOUT - Apply Server                    45         1 14097             AS02
XOUT - Propagation Send/Rcv            47        55 16401             CS01
CAP$_XOUT_1 - Capture                  48         7 16399             CP01

The row that shows TNS for the XStream process number contains information about the session for the XStream client application that is attached to the outbound server.

Your output for an XStream In configuration looks similar to the following:

                                            Session                   XStream
                                             Serial Operating System  Process
XStream Component              Session ID    Number Process Number    Number
------------------------------ ---------- --------- ----------------- -------
XIN - Propagation Receiver             32        21 16386             TNS
XIN - Apply Coordinator                38        23 16414             AP01
XIN - Apply Reader                     40         3 16418             AS01
XIN - Apply Server                     42         1 16420             AS02
XIN - Apply Server                     44         1 16422             AS03
XIN - Apply Server                     46         1 16424             AS04
XIN - Apply Server                     48         1 16426             AS05

The row that shows TNS for the XStream process number contains information about the session for the XStream client application that is attached to the inbound server.

See Also:

Oracle Database Reference for more information about the V$SESSION view

Monitoring XStream Out

This section provides sample queries that you can use to monitor XStream Out.

This section contains these topics:

With XStream Out, an Oracle Streams apply process functions as an outbound server. Therefore, you can also 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 components, such as propagations, rules, and rule-based transformations.

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.

See Also:

"ALL_APPLY"

Displaying Information About an Outbound Server's Current Transaction

Note:

This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2).

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

Note:

The COMMITSCN and COMMIT_POSITION values are populated only if the COMMITTED_DATA_ONLY value is YES in V$XSTREAM_OUTBOUND_SERVER.

Displaying Statistics for an Outbound Server

Note:

This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2).

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.

Note:

The TOTAL_TRANSACTIONS_SENT value is populated only if the COMMITTED_DATA_ONLY value is YES in V$XSTREAM_OUTBOUND_SERVER.

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

Note:

This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2).

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

Note:

The V$STREAMS_APPLY_SERVER view provides additional information about the outbound server process, and information about the apply server background processes used by the outbound server.

Monitoring XStream In

This section provides sample queries that you can use to monitor XStream In.

This section contains these topics:

With XStream In, an Oracle Streams apply process functions as an inbound server. Therefore, you can also use the data dictionary views for apply processes to monitor inbound servers.

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.

See Also:

"ALL_APPLY"

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.

Monitoring XStream Rules

The ALL_XSTREAM_RULES and DBA_XSTREAM_RULES views contain information about the rules used by outbound servers and inbound servers. If an outbound server was created using the CREATE_OUTBOUND procedure in the DBMS_XSTREAM_ADM package, then these views also contain information about the rules used by the capture process that sends changes to the outbound server. However, if an outbound server was created using the ADD_OUTBOUND procedure, then these views do not contain information about the capture process rules. Also, these views do not contain information about the rules used by any propagation in the stream from a capture process to an outbound server.

To display information about the rules used by XStream components: 

  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 STREAMS_NAME HEADING 'Oracle|Streams|Name' FORMAT A12
    COLUMN STREAMS_TYPE HEADING 'Oracle|Streams|Type' FORMAT A11
    COLUMN RULE_NAME HEADING 'Rule|Name' FORMAT A10
    COLUMN RULE_SET_TYPE HEADING 'Rule Set|Type' FORMAT A8
    COLUMN STREAMS_RULE_TYPE HEADING 'Oracle|Streams|Rule|Level' FORMAT A7
    COLUMN SCHEMA_NAME HEADING 'Schema|Name' FORMAT A6
    COLUMN OBJECT_NAME HEADING 'Object|Name' FORMAT A11
    COLUMN RULE_TYPE HEADING 'Rule|Type' FORMAT A4
    
    SELECT STREAMS_NAME, 
           STREAMS_TYPE,
           RULE_NAME,
           RULE_SET_TYPE,
           STREAMS_RULE_TYPE,
           SCHEMA_NAME,
           OBJECT_NAME,
           RULE_TYPE
      FROM DBA_XSTREAM_RULES;
    

Your output looks similar to the following:

Oracle       Oracle                          Streams
Streams      Streams     Rule       Rule Set Rule    Schema Object      Rule
Name         Type        Name       Type     Level   Name   Name        Type
------------ ----------- ---------- -------- ------- ------ ----------- ----
CAP$_XOUT_49 CAPTURE     DB52       POSITIVE GLOBAL                     DML
CAP$_XOUT_49 CAPTURE     DB53       POSITIVE GLOBAL                     DDL
XOUT         APPLY       DB55       POSITIVE GLOBAL                     DML
XOUT         APPLY       DB56       POSITIVE GLOBAL                     DDL

Notice that the STREAMS_TYPE is APPLY even though the rules are in the positive rule set for the outbound server xout. You can determine the purpose of an apply process by querying the PURPOSE column in the DBA_APPLY view.

To view information about the rules used by all components, including capture processes, propagations, apply processes, outbound servers, and inbound servers, you can query the ALL_STREAMS_RULES and DBA_STREAMS_RULES views. See Oracle Streams Concepts and Administration for sample queries that enable you to monitor rules.

XStream and the Oracle Streams Performance Advisor

The Oracle Streams Performance Advisor consists of the DBMS_STREAMS_ADVISOR_ADM PL/SQL package and a collection of data dictionary views. The Oracle Streams Performance Advisor enables you to monitor the topology and performance of an XStream environment. The XStream topology includes information about the components in an XStream environment, the links between the components, and the way information flows from capture to consumption. The Oracle Streams Performance Advisor also provides information about how Oracle Streams components are performing.

Apply processes function as XStream outbound servers and inbound servers. In general, the Oracle Streams Performance Advisor works the same way for an Oracle Streams environment with apply processes and an XStream environment with outbound servers or inbound servers. This section describes important considerations about using the Oracle Streams Performance Advisor in an XStream environment.

This section contains these topics:

See Also:

Oracle Streams Concepts and Administration for detailed information about using the Oracle Streams Performance Advisor

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.

See Also:

Oracle Streams Concepts and Administration for more information about component-level statistics

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.

Note:

The rest of this section assumes that you are familiar with the UTL_SPADV package and the SHOW_STATS output for apply processes. See Oracle Streams Concepts and Administration and Oracle Database PL/SQL Packages and Types Reference for detailed information about using the UTL_SPADV package.

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

The following is sample output for when an outbound 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"
.
.
.

Note:

This output is for illustrative purposes only. Actual performance characteristics vary depending on individual configurations and conditions.

In this output, the A component is the outbound server XOUT. The output for when an outbound server is the last component in a path is similar to the output for when an apply process is the last component in a path. However, the apply server (APS) is not the last component because the outbound server connects to a client application. Statistics are not collected for the client application.

In an XStream Out configuration, the output can indicate flow control for the network because "SQL*Net more data to client" for an apply server is considered as a flow control event. If the output indicates flow control for an apply server, then either the network or the client application is considered the bottleneck component. In the previous output, EXTERNAL indicates that either the network or the client application is the bottleneck.

Other than these differences, you can interpret the statistics in the same way that you would for a path that ends with an apply process. Use the legend and the abbreviations to determine the statistics in the output.

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

The following is sample output for when an inbound server is the last component in a path:

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

Note:

This output is for illustrative purposes only. Actual performance characteristics vary depending on individual configurations and conditions.

In this output, the A component is the inbound server XIN. When an inbound server is the last component in a path, the XStream client application connects to the inbound server, and the inbound server applies the changes in the LCRs. The client application is not shown in the output.

The propagation receiver receives the LCRs from the client application. So, the propagation receiver is the first component shown in the output. In the previous sample output, the propagation receiver is named clientcap. In this case, clientcap is the source name given by the client application when it attaches to the inbound server.

If the propagation receiver is idle for a significant percentage of time, then either the network or the client application is considered a bottleneck component. In the previous output, EXTERNAL indicates that either the network or the client application is the bottleneck.

Other than these differences, you can interpret the statistics in the same way that you would for a path that ends with an apply process. Use the legend and the abbreviations to determine the statistics in the output.