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

22 Monitoring the Oracle Streams Topology and Performance

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 Oracle Streams environment. The Oracle Streams topology includes information about the components in an Oracle Streams 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.

The following topics contain information about the Oracle Streams Performance Advisor:

About the Oracle Streams Topology

Oracle Streams enables you to send messages between multiple databases. An Oracle Streams environment can send the following types of messages:

The Oracle Streams topology is a representation of the databases in an Oracle Streams environment, the Oracle Streams components configured in these databases, and the flow of messages between these components.

The messages in the environment flow in separate stream paths. A stream path begins when a capture process, a synchronous capture, or an application generates messages and enqueues them. The messages can flow through one or more propagations and queues in its stream path. The stream path ends when the messages are dequeued by an apply process, a messaging client, or an application.

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

About 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. You can use the ANALYZE_CURRENT_PERFORMANCE procedure in the DBMS_STREAMS_ADVISOR_ADM package to gather information about the Oracle Streams topology and about the performance of the Oracle Streams components in the topology.

This section contains the following topics:

Oracle Streams Performance Advisor Data Dictionary Views

After information is gathered by the Oracle Streams Performance Advisor, you can view it by querying the following data dictionary views:

The topology information is stored permanently in the following data dictionary views: DBA_STREAMS_TP_DATABASE, DBA_STREAMS_TP_COMPONENT, and DBA_STREAMS_TP_COMPONENT_LINK.

The following views contain temporary information: DBA_STREAMS_TP_COMPONENT_STAT, DBA_STREAMS_TP_PATH_BOTTLENECK, and DBA_STREAMS_TP_PATH_STAT. Some of the data in these views is retained only for the user session that runs the ANALYZE_CURRENT_PERFORMANCE procedure. When this user session ends, this temporary information is purged.

Oracle Streams Components and Statistics

The DBMS_STREAMS_ADVISOR_ADM package gathers information about the following Oracle Streams components:

  • A QUEUE stores messages. The package gathers the following component-level statistics for queues:

    • ENQUEUE RATE

    • SPILL RATE

    • CURRENT QUEUE SIZE

  • A CAPTURE is a capture process. A capture process captures database changes in the redo log and enqueues the changes as logical change records (LCRs). Each capture process has the following subcomponents:

    • LOGMINER BUILDER is a builder server.

    • LOGMINER PREPARER is a preparer server.

    • LOGMINER READER is a reader server.

    • CAPTURE SESSION is the capture process session.

    The package gathers the following component-level statistics for each capture process (CAPTURE):

    • CAPTURE RATE

    • ENQUEUE RATE

    • LATENCY

    The package also gathers session-level statistics for capture process subcomponents.

  • A PROPAGATION SENDER sends messages from a source queue to a destination queue. The package gathers the following component-level statistics for propagation senders:

    • SEND RATE

    • BANDWIDTH

    • LATENCY

    The package also gathers session-level statistics for propagation senders.

  • A PROPAGATION RECEIVER enqueues messages sent by propagation senders into a destination queue. The package gathers session-level statistics for propagation receivers.

  • An APPLY is an apply process. An apply process either applies messages directly or sends messages to apply handlers. Each apply process has the following subcomponents:

    • APPLY READER is a reader server.

    • APPLY COORDINATOR is a coordinator process.

    • APPLY SERVER is an apply server.

    The package gathers the following component-level statistics for each apply process (APPLY):

    • MESSAGE APPLY RATE

    • TRANSACTION APPLY RATE

    • LATENCY

    The package also gathers session-level statistics for apply process subcomponents.

When the package gathers session-level statistics for a component or subcomponent, the session-level statistics include the following:

  • IDLE percentage

  • FLOW CONTROL percentage

  • EVENT percentage for wait events

Note:

Currently, the DBMS_STREAMS_ADVISOR_ADM package does not gather information about synchronous captures or messaging clients.

See Also:

About Stream Paths in an Oracle Streams Topology

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

The Oracle Streams topology assigns a number to each stream path so that you can monitor each one easily. The Oracle Streams topology also assigns a number to each link between two components in a stream path. The number specifies the position of the link in the overall stream path. Table 22-1 shows the position of each link in a sample stream path.

Table 22-1 Position of Each Link in a Sample Stream Path

Start Component End Component Position

Capture process

Queue

1

Queue

Propagation sender

2

Propagation sender

Propagation receiver

3

Propagation receiver

Queue

4

Queue

Apply process

5


When the Oracle Streams Performance Advisor gathers information about an Oracle Streams environment, it tracks stream paths by starting with each apply process and working backward to its source. When a capture process is the source, the Oracle Streams Performance Advisor tracks the path from the apply process back to the capture process. When a synchronous capture or an application that enqueues messages is the source, the Oracle Streams Performance Advisor tracks the path from the apply process back to the queue into which the messages are enqueued.

The following sections describe sample replication environments and the stream paths in each one:

See Also:

Oracle Streams Replication Administrator's Guide for information about best practices for Oracle Streams replication environments

Stream Paths in a Combined Capture and Apply Replication Environment

Consider an Oracle Streams replication environment with two databases. Each database captures changes made to the replicated database objects with a capture process and sends the changes to the other database, where they are applied by an apply process.

This type of Oracle Streams replication environment can use combined capture and apply. In a combined capture and apply path, the capture process acts as the propagation sender to improve efficiency.

Figure 22-1 shows an Oracle Streams replication environment that uses combined capture and apply in two separate stream paths.

Figure 22-1 Oracle Streams Topology with Two Separate Stream Paths

Description of Figure 22-1 follows
Description of "Figure 22-1 Oracle Streams Topology with Two Separate Stream Paths"

Notice that the Oracle Streams Performance Advisor assigns a component ID to each Oracle Streams component and a path ID to each path. The Oracle Streams topology in Figure 22-1 shows the following information:

  • There are twelve Oracle Streams components in the Oracle Streams environment.

  • There are two stream paths in the Oracle Streams environment.

  • Stream path 1 starts with component 1 and ends with component 6.

  • Stream path 2 starts with component 7 and ends with component 12.

Stream Paths in a Replication Environment That Does Not Use Combined Capture and Apply

When there are multiple apply processes that apply changes generated by a single source, a stream path splits into multiple stream paths. This type of configuration cannot use the combined capture and apply optimization.

Figure 22-2 shows this type of Oracle Streams environment.

Figure 22-2 Oracle Streams Topology with Multiple Apply Processes for a Single Source

Description of Figure 22-2 follows
Description of "Figure 22-2 Oracle Streams Topology with Multiple Apply Processes for a Single Source"

The Oracle Streams topology in Figure 22-2 shows the following information:

  • There are ten Oracle Streams components in the Oracle Streams environment.

  • There are two stream paths in the Oracle Streams environment.

  • Stream path 1 starts with component 1 and ends with component 7.

  • Stream path 2 starts with component 1 and ends with component 10.

  • The messages flowing between component 1 and component 2 are in both path 1 and path2.

About the Information Gathered With the Oracle Streams Performance Advisor

The ANALYZE_CURRENT_PERFORMANCE procedure in the DBMS_STREAMS_ADVISOR_ADM package gathers information about the Oracle Streams topology and the performance of Oracle Streams components. The procedure stores the information in a collection of data dictionary views. To use the Oracle Streams Performance Advisor effectively, it is important to understand how the procedure gathers information and calculates statistics.

The procedure takes snapshots of the Oracle Streams environment to gather information and calculate statistics. For some statistics, the information in a single snapshot is sufficient. For example, only one snapshot is needed to determine the current number of messages in a queue. However, to calculate other statistics, the procedure must compare two snapshots. These statistics include the rate, bandwidth, event, and flow control statistics. The first time the procedure is run in a user session, it takes two snapshots to calculate these statistics. In each subsequent run in the same user session, the procedure takes one snapshot and compares it with the snapshot taken during the previous run.

Table 22-2 illustrates how the procedure gathers information in each advisor run in a single user session.

Table 22-2 How the Oracle Streams Performance Advisor Gathers Information in a Session

Advisor Run Information Gathered

1

  1. Take snapshot of statistics.

  2. Wait at least five seconds.

  3. Take another snapshot of statistics.

  4. Compare data from the first snapshot with data from the second snapshot to calculate performance statistics.

2

  1. Take snapshot of statistics.

  2. Compare data from the last snapshot in advisor run 1 with the snapshot taken in advisor run 2 to calculate performance statistics.

3

  1. Take snapshot of statistics.

  2. Compare data from the snapshot in advisor run 2 with the snapshot taken in advisor run 3 to calculate performance statistics.


For the best results in an advisor run, meet the following criteria:

Gathering Information About the Oracle Streams Topology and Performance

To gather information about the Oracle Streams topology and Oracle Streams performance, complete the following steps:

  1. Identify the database that you will use to gather the information. An administrative user at this database must meet the following requirements:

    • The user must have access to a database link to each database that contains Oracle Streams components.

    • The user must have been granted privileges using the DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE procedure, and each database link must connect to a user at the remote database that has been granted privileges using the DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE procedure.

      If you configure an Oracle Streams administrator at each database with Oracle Streams components, then the Oracle Streams administrator has the necessary privileges. See "Configuring an Oracle Streams Administrator" for instructions.

    If no database in your environment meets these requirements, then choose a database, configure the necessary database links, and grant the necessary privileges to the users before proceeding.

  2. In SQL*Plus, connect to the database you identified in Step 1 as a user that meets the requirements listed in Step 1.

    For example, connect to the hub.example.com database as the Oracle Streams administrator.

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

  3. Run the ANALYZE_CURRENT_PERFORMANCE procedure in the DBMS_STREAMS_ADVISOR_ADM package:

    exec DBMS_STREAMS_ADVISOR_ADM.ANALYZE_CURRENT_PERFORMANCE;
    
  4. Optionally, rerun the ANALYZE_CURRENT_PERFORMANCE procedure one or more times in same session that ran the procedure in Step 3:

    exec DBMS_STREAMS_ADVISOR_ADM.ANALYZE_CURRENT_PERFORMANCE;
    
  5. Run the following query to identify the advisor run ID for the information gathered in Step 4:

    SELECT DISTINCT ADVISOR_RUN_ID FROM DBA_STREAMS_TP_COMPONENT_STAT
       ORDER BY ADVISOR_RUN_ID;
    

    Your output is similar to the following:

    ADVISOR_RUN_ID
    --------------
                 1
                 2
    

    The Oracle Streams Performance Advisor assigns an advisor run ID to the statistics for each run. Use the last value in the output for the advisor run ID in the queries in "Viewing Performance Statistics for Oracle Streams Components". In this example, use 2 for the advisor run ID in the queries.

    Remember that the Oracle Streams Performance Advisor purges some of the performance statistics that it gathered when a user session ends. Therefore, run the performance statistics queries in the same session that ran the ANALYZE_CURRENT_PERFORMANCE procedure.

Complete these steps whenever you want to monitor the current performance of your Oracle Streams environment.

You should also run the ANALYZE_CURRENT_PERFORMANCE procedure when new Oracle Streams components are added to any database in the Oracle Streams environment. Running the procedure updates the Oracle Streams topology with information about any new components.

See Also:

Viewing the Oracle Streams Topology and Analyzing Oracle Streams Performance

This section contains several queries that you can use to view your Oracle Streams topology and monitor the performance of your Oracle Streams components. The queries specify the views described in "About the Oracle Streams Topology".

The queries in this section can be run in any Oracle Stream environment. However, the output shown for these queries is based on the sample Oracle Streams replication environment shown in Figure 22-3.

Figure 22-3 Sample Oracle Streams Replication Environment

Description of Figure 22-3 follows
Description of "Figure 22-3 Sample Oracle Streams Replication Environment"

The Oracle Database 2 Day + Data Replication and Integration Guide contains instructions for configuring the Oracle Streams replication environment shown in Figure 22-3. This environment contains both of the following types of stream paths:

The output of the queries in this section illustrate the differences between these two types of stream paths.

This section contains the following topics:

Viewing the Oracle Streams Topology

To view the Oracle Streams topology, you must first gather information about the Oracle Streams environment using the DBMS_STREAMS_ADVISOR_ADM package. See "Gathering Information About the Oracle Streams Topology and Performance".

The following sections explain how to view different types of information in an Oracle Streams topology:

Viewing the Databases in the Oracle Streams Environment

You can view the following information about the databases in an Oracle Streams environment:

  • The global name of each database

  • The last time the Oracle Streams Performance Advisor was run at each database

  • The version number of each database

  • The compatibility level of each database

  • Whether each database has access to the Oracle Diagnostics Pack and Oracle Tuning Pack

To display this information, run the following query:

COLUMN GLOBAL_NAME HEADING 'Global Name' FORMAT A15
COLUMN LAST_QUERIED HEADING 'Last|Queried'
COLUMN VERSION HEADING 'Version' FORMAT A15
COLUMN COMPATIBILITY HEADING 'Compatibility' FORMAT A15
COLUMN MANAGEMENT_PACK_ACCESS HEADING 'Management Pack' FORMAT A20

SELECT GLOBAL_NAME, LAST_QUERIED, VERSION, COMPATIBILITY, MANAGEMENT_PACK_ACCESS
   FROM DBA_STREAMS_TP_DATABASE;

The following output shows the databases in the Oracle Streams replication environment described in "Viewing the Oracle Streams Topology and Analyzing Oracle Streams Performance":

                Last                                                           
Global Name     Queried   Version         Compatibility   Management Pack       
--------------- --------- --------------- --------------- --------------------  
HUB.EXAMPLE.COM 08-APR-08 11.1.0.7.0      11.1.0          DIAGNOSTIC+TUNING     
SPOKE1.EXAMPLE. 08-APR-08 11.1.0.7.0      11.1.0          DIAGNOSTIC+TUNING     
COM
SPOKE2.EXAMPLE. 08-APR-08 11.1.0.7.0      11.1.0          DIAGNOSTIC+TUNING     
COM

This output shows the following information about the databases in the Oracle Streams environment:

  • The Global Name column shows that the global names of the databases are hub.example.com, spoke1.example.com, and spoke2.example.com.

  • The Last Queried column shows that the Oracle Streams Performance Advisor was last run on April 8, 2008 at each database.

  • The Version column shows that version of each database is 11.1.0.7.0.

  • The Compatibility column shows that the compatibility level of each database is 11.1.0.

  • The Management Pack column shows that each database has access to the Oracle Diagnostics Pack and Oracle Tuning Pack.

See Also:

Oracle Database Upgrade Guide for information about database compatibility

Viewing the Oracle Streams Components at Each Database

You can view the following information about the components in an Oracle Streams environment:

  • The component ID for each Oracle Streams component. The Oracle Streams topology assigns an ID number to each component and uses the number to track information about the component and about the stream path that flows through the component.

  • The name of the Oracle Streams component. For capture processes and apply processes, the query lists the name of each process. For queues, the query lists the name of each queue. For propagations, two Oracle Streams components are tracked in the Oracle Streams topology:

    • The name of a propagation sender is the source queue of the propagation and the destination queue and database to which the propagation sends messages. For example, a propagation sender with the strmadmin.source_hns source queue that sends messages to the strmadmin.destination_spoke1 destination queue at the spoke1.example.com database is shown in the following way:

      "STRMADMIN"."SOURCE_HNS"=>"STRMADMIN"."DESTINATION_SPOKE1"
         @SPOKE1.EXAMPLE.COM
      
    • The name of a propagation receiver is the source queue and database from which the messages are sent and the destination queue for the propagation. For example, a propagation receiver that gets messages from the strmadmin.source_hns source queue at the hub.example.com database and enqueues them into the strmadmin.destination_spoke1 destination queue is shown in the following way:

      "STRMADMIN"."SOURCE_HNS"@HUB.EXAMPLE.COM=>"STRMADMIN".
         "DESTINATION_SPOKE1"
      
  • The type of the Oracle Streams component. The following types are possible:

    • CAPTURE for capture processes

    • QUEUE for queues

    • PROPAGATION SENDER for propagation senders

    • PROPAGATION RECEIVER for propagation receivers

    • APPLY for apply processes

  • The database that contains the component

To display this information, run the following query:

COLUMN COMPONENT_ID HEADING 'ID' FORMAT 999
COLUMN COMPONENT_NAME HEADING 'Name' FORMAT A43
COLUMN COMPONENT_TYPE HEADING 'Type' FORMAT A20
COLUMN COMPONENT_DB HEADING 'Database' FORMAT A10

SELECT COMPONENT_ID, COMPONENT_NAME, COMPONENT_TYPE, COMPONENT_DB
   FROM DBA_STREAMS_TP_COMPONENT
   ORDER BY COMPONENT_ID;

The following output shows the components in the Oracle Streams replication environment described in "Viewing the Oracle Streams Topology and Analyzing Oracle Streams Performance":

  ID Name                                        Type                 Database 
---- ------------------------------------------- -------------------- ----------
   1 "STRMADMIN"."DESTINATION_SPOKE1"            QUEUE                HUB.EXAMPL
                                                                      E.COM
   2 "STRMADMIN"."DESTINATION_SPOKE2"            QUEUE                HUB.EXAMPL
                                                                      E.COM
   3 "STRMADMIN"."SOURCE_HNS"                    QUEUE                HUB.EXAMPL
                                                                      E.COM
   4 "STRMADMIN"."SOURCE_HNS"=>"STRMADMIN"."DEST PROPAGATION SENDER   HUB.EXAMPL
     INATION_SPOKE1"@SPOKE1.EXAMPLE.COM                               E.COM
   5 "STRMADMIN"."SOURCE_HNS"=>"STRMADMIN"."DEST PROPAGATION SENDER   HUB.EXAMPL
     INATION_SPOKE2"@SPOKE2.EXAMPLE.COM                               E.COM
   6 "STRMADMIN"."SOURCE_HNS"@SPOKE1.EXAMPLE.COM PROPAGATION RECEIVER HUB.EXAMPL
     =>"STRMADMIN"."DESTINATION_SPOKE1"                               E.COM
   7 "STRMADMIN"."SOURCE_HNS"@SPOKE2.EXAMPLE.COM PROPAGATION RECEIVER HUB.EXAMPL
     =>"STRMADMIN"."DESTINATION_SPOKE2"                               E.COM
   8 APPLY_SPOKE1                                APPLY                HUB.EXAMPL
                                                                      E.COM
   9 APPLY_SPOKE2                                APPLY                HUB.EXAMPL
                                                                      E.COM
  10 CAPTURE_HNS                                 CAPTURE              HUB.EXAMPL
                                                                      E.COM
  11 "STRMADMIN"."DESTINATION_SPOKE1"            QUEUE                SPOKE1.EXA
                                                                      MPLE.COM
  12 "STRMADMIN"."SOURCE_HNS"                    QUEUE                SPOKE1.EXA
                                                                      MPLE.COM
  13 "STRMADMIN"."SOURCE_HNS"=>"STRMADMIN"."DEST PROPAGATION SENDER   SPOKE1.EXA
     INATION_SPOKE1"@HUB.EXAMPLE.COM                                  MPLE.COM  
  14 "STRMADMIN"."SOURCE_HNS"@HUB.EXAMPLE.COM=>" PROPAGATION RECEIVER SPOKE1.EXA
     STRMADMIN"."DESTINATION_SPOKE1"                                  MPLE.COM
  15 APPLY_SPOKE1                                APPLY                SPOKE1.EXA
                                                                      MPLE.COM
  16 CAPTURE_HNS                                 CAPTURE              SPOKE1.EXA
                                                                      MPLE.COM
  17 "STRMADMIN"."DESTINATION_SPOKE2"            QUEUE                SPOKE2.EXA
                                                                      MPLE.COM
  18 "STRMADMIN"."SOURCE_HNS"                    QUEUE                SPOKE2.EXA
                                                                      MPLE.COM
  19 "STRMADMIN"."SOURCE_HNS"=>"STRMADMIN"."DEST PROPAGATION SENDER   SPOKE2.EXA
     INATION_SPOKE2"@HUB.EXAMPLE.COM                                  MPLE.COM  
  20 "STRMADMIN"."SOURCE_HNS"@HUB.EXAMPLE.COM=>" PROPAGATION RECEIVER SPOKE2.EXA
     STRMADMIN"."DESTINATION_SPOKE2"                                  MPLE.COM  
  21 APPLY_SPOKE2                                APPLY                SPOKE2.EXA
                                                                      MPLE.COM
  22 CAPTURE_HNS                                 CAPTURE              SPOKE2.EXA
                                                                      MPLE.COM

See Also:

Viewing Each Stream Path in an Oracle Streams Topology

You can view the following information about the stream paths in an Oracle Streams topology:

  • The path ID. The Oracle Streams topology assigns an ID number to each stream path it identifies. The path ID is associated with each link in the path. For example, a single path ID can be associated with the following component links:

    • Capture process to queue

    • Queue to propagation sender

    • Propagation sender to propagation receiver

    • Propagation receiver to queue

    • Queue to apply process

  • The source component ID. A source component is a component from which messages flow to another component.

  • The name of the source component. See "Viewing the Oracle Streams Components at Each Database" for information about how components are named in the query output.

  • The destination component ID. A destination component receives messages from another component.

  • The name of the destination component.

  • The position in the stream path shows the location of a particular link in a path. For example, a position might be the first link in a path, the second link in a path, and so on.

To display this information, run the following query:

COLUMN PATH_ID HEADING 'Path|ID' FORMAT 9999
COLUMN SOURCE_COMPONENT_ID HEADING 'Source|Component|ID' FORMAT 9999
COLUMN SOURCE_COMPONENT_NAME HEADING 'Source|Component|Name' FORMAT A20
COLUMN DESTINATION_COMPONENT_ID HEADING 'Dest|Component|ID' FORMAT 9999
COLUMN DESTINATION_COMPONENT_NAME HEADING 'Dest|Component|Name' FORMAT A15
COLUMN POSITION HEADING 'Position' FORMAT 9999
 
SELECT PATH_ID, 
       SOURCE_COMPONENT_ID, 
       SOURCE_COMPONENT_NAME,
       DESTINATION_COMPONENT_ID, 
       DESTINATION_COMPONENT_NAME,
       POSITION 
  FROM DBA_STREAMS_TP_COMPONENT_LINK
  ORDER BY PATH_ID, POSITION;

The following output shows the paths in the Oracle Streams topology for the components listed in "Viewing the Oracle Streams Components at Each Database":

         Source Source                    Dest Dest                            
 Path Component Component            Component Component                        
   ID        ID Name                        ID Name            Position         
----- --------- -------------------- --------- --------------- --------         
    1        16 CAPTURE_HNS                 12 "STRMADMIN"."SO        1         
                                               URCE_HNS"                        
    1        12 "STRMADMIN"."SOURCE_        13 "STRMADMIN"."SO        2         
                HNS"                           URCE_HNS"=>"STR                  
                                               MADMIN"."DESTIN                  
                                               ATION_SPOKE1"@H                  
                                               UB.EXAMPLE.COM                   
    1        13 "STRMADMIN"."SOURCE_         6 "STRMADMIN"."SO        3         
                HNS"=>"STRMADMIN"."D           URCE_HNS"@SPOKE                  
                ESTINATION_SPOKE1"@H           1.EXAMPLE.COM=>                  
                UB.EXAMPLE.COM                 "STRMADMIN"."DES                 
                                               TINATION_SPOKE1"                 
    1         6 "STRMADMIN"."SOURCE_         1 "STRMADMIN"."DE        4         
                HNS"@SPOKE1.EXAMPLE.           STINATION_SPOKE                  
                COM=>"STRMADMIN"."DE           1"                               
                STINATION_SPOKE1"                                               
    1         1 "STRMADMIN"."DESTINA         8 APPLY_SPOKE1           5         
                TION_SPOKE1"                                                    
    2        22 CAPTURE_HNS                 18 "STRMADMIN"."SO        1         
                                               URCE_HNS"                        
    2        18 "STRMADMIN"."SOURCE_        19 "STRMADMIN"."SO        2         
                HNS"                           URCE_HNS"=>"STR                  
                                               MADMIN"."DESTIN                  
                                               ATION_SPOKE2"@H                  
                                               UB.EXAMPLE.COM                   
    2        19 "STRMADMIN"."SOURCE_         7 "STRMADMIN"."SO        3         
                HNS"=>"STRMADMIN"."D           URCE_HNS"@SPOKE                  
                ESTINATION_SPOKE2"@H           2.EXAMPLE.COM=>                  
                UB.EXAMPLE.COM                 "STRMADMIN"."DES                 
                                               TINATION_SPOKE2"                 
    2         7 "STRMADMIN"."SOURCE_         2 "STRMADMIN"."DE        4         
                HNS"@SPOKE2.EXAMPLE.           STINATION_SPOKE                  
                COM=>"STRMADMIN"."DE           2"                               
                STINATION_SPOKE2"                                               
    2         2 "STRMADMIN"."DESTINA         9 APPLY_SPOKE2           5         
                TION_SPOKE2"                                                    
    3        10 CAPTURE_HNS                  3 "STRMADMIN"."SO        1         
                                               URCE_HNS"                        
    3         3 "STRMADMIN"."SOURCE_         4 "STRMADMIN"."SO        2         
                HNS"                           URCE_HNS"=>"STR                  
                                               MADMIN"."DESTIN                  
                                               ATION_SPOKE1"@S                  
                                               POKE1.EXAMPLE.CO                 
                                               M                                
    3         4 "STRMADMIN"."SOURCE_        14 "STRMADMIN"."SO        3         
                HNS"=>"STRMADMIN"."D           URCE_HNS"@HUB.N                  
                ESTINATION_SPOKE1"@S           ET=>"STRMADMIN"                  
                POKE1.EXAMPLE.COM              ."DESTINATION_S                  
                                               POKE1"                           
    3        14 "STRMADMIN"."SOURCE_        11 "STRMADMIN"."DE        4         
                HNS"@HUB.EXAMPLE.COM           STINATION_SPOKE                  
                =>"STRMADMIN"."DESTI           1"                               
                NATION_SPOKE1"                                                  
    3        11 "STRMADMIN"."DESTINA        15 APPLY_SPOKE1           5         
                TION_SPOKE1"                                                    
    4        10 CAPTURE_HNS                  3 "STRMADMIN"."SO        1         
                                               URCE_HNS"                        
    4         3 "STRMADMIN"."SOURCE_         5 "STRMADMIN"."SO        2         
                HNS"                           URCE_HNS"=>"STR                  
                                               MADMIN"."DESTIN                  
                                               ATION_SPOKE2"@S                  
                                               POKE2.EXAMPLE.C                  
                                               OM                               
    4         5 "STRMADMIN"."SOURCE_        20 "STRMADMIN"."SO        3         
                HNS"=>"STRMADMIN"."D           URCE_HNS"@HUB.N                  
                ESTINATION_SPOKE2"@S           ET=>"STRMADMIN"                  
                POKE2.EXAMPLE.COM              ."DESTINATION_S                  
                                               POKE2"                           
    4        20 "STRMADMIN"."SOURCE_        17 "STRMADMIN"."DE        4         
                HNS"@HUB.EXAMPLE.COM           STINATION_SPOKE                  
                =>"STRMADMIN"."DESTI           2"                               
                NATION_SPOKE2"                                                  
    4        17 "STRMADMIN"."DESTINA        21 APPLY_SPOKE2           5         
                TION_SPOKE2"                                                    

Viewing Performance Statistics for Oracle Streams Components

The DBMS_STREAMS_ADVISOR_ADM package and the Oracle Streams topology views comprise the Oracle Streams Performance Advisor. The Oracle Streams topology views enable you to display and analyze performance statistics for the Oracle Streams components in your environment.

To view performance statistics for Oracle Streams components, you must first gather information about the Oracle Streams environment using the DBMS_STREAMS_ADVISOR_ADM package. See "Gathering Information About the Oracle Streams Topology and Performance".

The following sections explain how to view performance statistics for Oracle Streams components:

Note:

The performance of Oracle Streams components depends on several factors, including the computer equipment used in the environment and the speed of the network.

Checking for Bottleneck Components in the Oracle Streams Topology

A bottleneck component is the busiest component or the component with the least amount of idle time. You can view the following information about the bottleneck components in an Oracle Streams environment:

  • The path ID of the path that includes the component.

  • The component ID for each Oracle Streams component. The Oracle Streams topology assigns an ID number to each component and uses the number to track information about the component and about the stream path that flows through the component.

  • The name of the Oracle Streams component. See "Viewing the Oracle Streams Components at Each Database" for information about how components are named in the query output.

  • The type of the Oracle Streams component. The following types are possible:

  • The database that contains the component

Run the following query to check for bottleneck components in your Oracle Streams environment:

COLUMN PATH_ID HEADING 'Path ID' FORMAT 999
COLUMN COMPONENT_ID HEADING 'Component ID' FORMAT 999
COLUMN COMPONENT_NAME HEADING 'Name' FORMAT A20
COLUMN COMPONENT_TYPE HEADING 'Type' FORMAT A20
COLUMN COMPONENT_DB HEADING 'Database' FORMAT A15
 
SELECT PATH_ID,
       COMPONENT_ID, 
       COMPONENT_NAME, 
       COMPONENT_TYPE, 
       COMPONENT_DB
   FROM DBA_STREAMS_TP_PATH_BOTTLENECK
   WHERE BOTTLENECK_IDENTIFIED='YES' AND
         ADVISOR_RUN_ID=2
   ORDER BY PATH_ID, COMPONENT_ID;

This example uses 2 for the ADVISOR_RUN_ID in the WHERE clause. Substitute the advisor run ID for the advisor run you want to query. See "Gathering Information About the Oracle Streams Topology and Performance" for information about determining the ADVISOR_RUN_ID.

The following output shows the bottleneck components for the components listed in "Viewing the Oracle Streams Components at Each Database":

Path ID Component ID Name                 Type                 Database        
------- ------------ -------------------- -------------------- ---------------  
      1            6 "STRMADMIN"."SOURCE_ PROPAGATION RECEIVER HUB.EXAMPLE.COM  
                     HNS"@SPOKE1.EXAMPLE.                                       
                     COM=>"STRMADMIN"."DE                                       
                     STINATION_SPOKE1"                                          
      3           10 CAPTURE_HNS          CAPTURE              HUB.EXAMPLE.COM  
      4           10 CAPTURE_HNS          CAPTURE              HUB.EXAMPLE.COM  

If this query returns no results, then the Oracle Streams Performance Advisor did not identify any bottleneck components in your environment. However, if this query returns one or more bottleneck components, then check the status of these components. If they are disabled, then you can enable them. If the components are enabled, then you can examine the components to see if they can be modified to perform better.

In some cases, the Oracle Streams Performance Advisor cannot determine whether a component is a bottleneck component. To view these components, set BOTTLENECK_IDENTIFIED to 'NO' when you query the DBA_STREAMS_TP_PATH_BOTTLENECK view. The output for the ADVISOR_RUN_REASON column shows why the Oracle Streams Performance Advisor could not determine whether the component is a bottleneck component. The following reasons can be specified in the ADVISOR_RUN_REASON column output:

  • PRE-11.1 DATABASE EXISTS means that the component is in a stream path that includes a database prior to Oracle Database 11g Release 1. Bottleneck analysis is not performed on these components.

  • DIAGNOSTIC PACK REQUIRED means that the component is in a stream path that includes a database that does not have the Oracle Diagnostics Pack. Bottleneck analysis is not performed on the these components.

  • NO BOTTLENECK IDENTIFIED means that either no bottleneck was identified in a stream path or that there might be more than one bottleneck component in the stream path.

See Also:

Viewing Component-Level Statistics

You can view statistics for the Oracle Streams components in the Oracle Streams topology. The query in this section displays the following information for each component:

  • The ID of the path to which the component belongs

  • The name of the Oracle Streams component

  • The type of the Oracle Streams component. The following types are possible:

  • The statistic that was gathered for the component

  • The value and unit of the statistic. For example, a LATENCY statistic shows a number for the value and SECONDS for the unit. A TRANSACTION APPLY RATE statistic shows a number for the value and TRANSACTIONS PER SECOND for the unit.

The ANALYZE_CURRENT_PERFORMANCE procedure in the DBMS_STREAMS_ADVISOR_ADM package gathers the statistics returned by the query in this section. Therefore, the statistics returned by the query were the current statistics when the procedure was run. The statistics are not updated automatically.

Table 22-3 describes each of the statistics that can be returned by the query in this section:

Table 22-3 Component-Level Statistics for Oracle Streams Components

Component Type Statistic Unit Description

CAPTURE

CAPTURE RATE

MESSAGES PER SECOND

The average number of database changes in the redo log scanned by the capture process each second.

A capture process captures and enqueues the scanned changes that satisfy its rule sets.

CAPTURE

ENQUEUE RATE

MESSAGES PER SECOND

The average number of logical change records (LCRs) enqueued by the capture process each second.

CAPTURE

LATENCY

SECONDS

The amount of time between when the last redo entry became available for the capture process and the time when the last redo entry scanned by the capture process was recorded in the redo log.

The purpose of the statistic is to show the amount of time between when a change is recorded in the redo log is and when the redo record is scanned by the capture process.

The capture process might or might not enqueue a scanned change. A capture process only enqueues a change if the change satisfies its rule sets.

PROPAGATION SENDER

SEND RATE

MESSAGES PER SECOND

The average number of messages sent each second by the propagation sender.

PROPAGATION SENDER

BANDWIDTH

BYTES PER SECOND

The average number of bytes sent each second by the propagation sender.

PROPAGATION SENDER

LATENCY

SECONDS

The amount of time between when a message was created at the source database and when the message was sent to the destination queue by the propagation sender.

The value shown is for a single message that was sent from the source queue to the destination queue by the propagation sender. This message was the last message sent by the propagation sender when the ANALYZE_CURRENT_PERFORMANCE procedure was run.

Depending on the type of message sent by the propagation, message creation time is one of the following:

APPLY

MESSAGE APPLY RATE

MESSAGES PER SECOND

The average number of messages applied by the apply process each second.

A captured LCR or persistent LCR can be applied in one of the following ways:

  • The apply process makes the change encapsulated in the LCR to a database object.

  • The apply process passes the LCR to an apply handler.

  • The apply process sends the LCR to the error queue.

A persistent user message can be applied in one of the following ways:

  • The apply process sends the message to a message handler.

  • The apply process sends the message to the error queue.

APPLY

TRANSACTION APPLY RATE

TRANSACTIONS PER SECOND

The average number of transactions applied by the apply process each second. Transactions typically include multiple messages.

A transaction that includes captured LCRs or persistent LCRs can be applied in one of the following ways:

  • The apply process makes all of the changes in the transaction and commits the transaction.

  • The apply process passes all of the LCRs in the transaction to an apply handler.

  • The apply process sends the transaction and all of the LCRs in the transaction to the error queue.

A transaction that includes persistent user messages can be applied in one of the following ways:

  • The apply process passes all of the messages in the transaction to a message handler.

  • The apply process sends all of the messages in the transaction to the error queue.

APPLY

LATENCY

SECONDS

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.

The value shown is for a single message that was applied by the apply process. This message was the last message applied by the apply process when the ANALYZE_CURRENT_PERFORMANCE procedure was run.

Depending on the type of message applied, message creation time is one of the following:

  • For captured LCRs, the time when the redo entry for the database change was recorded

  • For persistent LCRs, the time when the LCR was constructed

  • For user messages, the time when the message was enqueued

QUEUE

ENQUEUE RATE

MESSAGES PER SECOND

The average number of messages enqueued into the queue each second.

QUEUE

SPILL RATE

MESSAGES PER SECOND

The average number of messages that spilled from the buffered queue to the queue table each second.

QUEUE

CURRENT QUEUE SIZE

NUMBER OF MESSAGES

The number of messages in the queue when the ANALYZE_CURRENT_PERFORMANCE procedure was run.

CAPTURE, PROPAGATION SENDER, PROPAGATION RECEIVER, and APPLY

EVENT (Top wait event)

PERCENT

The percentage of time that the Oracle Streams component spent waiting because of a wait event.

The Oracle Streams Performance Advisor only gathers information about the top three events for each component.

For example, a capture process might wait for a redo log file to become available.


The following are general considerations for these performance statistics:

  • Regarding rate, bandwidth, and event statistics, the time period is calculated as the time difference between the two snapshots used by the ANALYZE_CURRENT_PERFORMANCE procedure in the same user session. See "About the Information Gathered With the Oracle Streams Performance Advisor" for information about the snapshots. When a user session ends, the rate, bandwidth, and event statistics are purged.

  • When a latency statistic is -1 seconds, the ANALYZE_CURRENT_PERFORMANCE procedure could not gather statistics for the component when it was run. In most cases, this result indicates that the component was disabled when the procedure was run. For example, if the LATENCY statistic for an apply process is -1, then the apply process was probably disabled when the ANALYZE_CURRENT_PERFORMANCE procedure was run.

To display performance statistics for the components in an Oracle Streams topology, run the following query:

COLUMN PATH_ID HEADING 'Path|ID' FORMAT 999
COLUMN COMPONENT_ID HEADING 'Componenet|ID' FORMAT 999
COLUMN COMPONENT_NAME HEADING 'Name' FORMAT A20
COLUMN COMPONENT_TYPE HEADING 'Type' FORMAT A12
COLUMN STATISTIC_NAME HEADING 'Statistic' FORMAT A15
COLUMN STATISTIC_VALUE HEADING 'Value' FORMAT 99999999999.99
COLUMN STATISTIC_UNIT HEADING 'Unit' FORMAT A15 

SELECT DISTINCT
       cp.PATH_ID,
       cs.COMPONENT_ID,
       cs.COMPONENT_NAME,
       cs.COMPONENT_TYPE,
       cs.STATISTIC_NAME,
       cs.STATISTIC_VALUE,
       cs.STATISTIC_UNIT
   FROM DBA_STREAMS_TP_COMPONENT_STAT  cs,
        (SELECT PATH_ID, SOURCE_COMPONENT_ID AS COMPONENT_ID
        FROM DBA_STREAMS_TP_COMPONENT_LINK
        UNION
        SELECT PATH_ID, DESTINATION_COMPONENT_ID AS COMPONENT_ID
        FROM DBA_STREAMS_TP_COMPONENT_LINK) cp
   WHERE cs.ADVISOR_RUN_ID = 2 AND
         cs.SESSION_ID IS NULL AND
         cs.SESSION_SERIAL# IS NULL AND
         cs.COMPONENT_ID = cp.COMPONENT_ID
   ORDER BY PATH_ID, COMPONENT_ID, COMPONENT_NAME, COMPONENT_TYPE, STATISTIC_NAME;

This example uses 2 for the ADVISOR_RUN_ID in the WHERE clause. Substitute the advisor run ID for the advisor run you want to query. See "Gathering Information About the Oracle Streams Topology and Performance" for information about determining the ADVISOR_RUN_ID.

The following output shows a partial list of the performance statistics for the components listed in "Viewing the Oracle Streams Components at Each Database". Specifically, the following output shows performance statistics for the components in stream path 1 and stream path 3:

Path Componenet                                                                                 
  ID         ID Name                 Type         Statistic                 Value Unit           
---- ---------- -------------------- ------------ --------------- --------------- ---------------
   1          1 "STRMADMIN"."DESTINA QUEUE        CURRENT QUEUE S             .00 NUMBER OF MESSA
                TION_SPOKE1"                      IZE                             GES            
   1          1 "STRMADMIN"."DESTINA QUEUE        ENQUEUE RATE            2573.21 MESSAGES PER SE
                TION_SPOKE1"                                                      COND           
   1          1 "STRMADMIN"."DESTINA QUEUE        SPILL RATE                  .00 MESSAGES PER SE
                TION_SPOKE1"                                                      COND           
   1          6 "STRMADMIN"."SOURCE_ PROPAGATION  EVENT: CPU + Wa           32.55 PERCENT        
                HNS"@SPOKE1.EXAMPLE. RECEIVER     it for CPU                                     
                COM=>"STRMADMIN"."DE                                                             
                STINATION_SPOKE1"                                                                
   1          6 "STRMADMIN"."SOURCE_ PROPAGATION  EVENT: SQL*Net            23.62 PERCENT        
                HNS"@SPOKE1.EXAMPLE. RECEIVER     more data from                                 
                COM=>"STRMADMIN"."DE              client                                         
                STINATION_SPOKE1"                                                                
   1          6 "STRMADMIN"."SOURCE_ PROPAGATION  EVENT: latch: r            2.10 PERCENT        
                HNS"@SPOKE1.EXAMPLE. RECEIVER     ow cache object                                
                COM=>"STRMADMIN"."DE              s                                              
                STINATION_SPOKE1"                                                                
   1          8 APPLY_SPOKE1         APPLY        EVENT: CPU + Wa           23.10 PERCENT        
                                                  it for CPU                                     
   1          8 APPLY_SPOKE1         APPLY        EVENT: latch: r            1.31 PERCENT        
                                                  ow cache object                                
                                                  s                                              
   1          8 APPLY_SPOKE1         APPLY        EVENT: latch: s            1.57 PERCENT        
                                                  hared pool                                     
   1          8 APPLY_SPOKE1         APPLY        LATENCY                    2.13 SECONDS        
   1          8 APPLY_SPOKE1         APPLY        MESSAGE APPLY R        10004.00 MESSAGES PER SE
                                                  ATE                             COND           
   1          8 APPLY_SPOKE1         APPLY        TRANSACTION APP          100.00 TRANSACTIONS PE
                                                  LY RATE                         R SECOND       
   1         12 "STRMADMIN"."SOURCE_ QUEUE        CURRENT QUEUE S             .00 NUMBER OF MESSA
                HNS"                              IZE                             GES            
   1         12 "STRMADMIN"."SOURCE_ QUEUE        ENQUEUE RATE            9932.00 MESSAGES PER SE
                HNS"                                                              COND           
                                                                                                 
   1         12 "STRMADMIN"."SOURCE_ QUEUE        SPILL RATE                  .00 MESSAGES PER SE
                HNS"                                                              COND           
   1         13 "STRMADMIN"."SOURCE_ PROPAGATION  BANDWIDTH              32992.96 BYTES PER SECON
                HNS"=>"STRMADMIN"."D SENDER                                       D              
                ESTINATION_SPOKE1"@H                                                             
                UB.EXAMPLE.COM                                                                   
   1         13 "STRMADMIN"."SOURCE_ PROPAGATION  EVENT: CPU + Wa           35.96 PERCENT        
                HNS"=>"STRMADMIN"."D SENDER       it for CPU                                     
                ESTINATION_SPOKE1"@H                                                             
                UB.EXAMPLE.COM                                                                   
   1         13 "STRMADMIN"."SOURCE_ PROPAGATION  EVENT: SQL*Net              .26 PERCENT        
                HNS"=>"STRMADMIN"."D SENDER       message to dbli                                
                ESTINATION_SPOKE1"@H              nk                                             
                UB.EXAMPLE.COM                                                                   
   1         13 "STRMADMIN"."SOURCE_ PROPAGATION  EVENT: latch: r             .26 PERCENT        
                HNS"=>"STRMADMIN"."D SENDER       ow cache object                                
                ESTINATION_SPOKE1"@H              s                                              
                UB.EXAMPLE.COM                                                                   
   1         13 "STRMADMIN"."SOURCE_ PROPAGATION  LATENCY                    4.00 SECONDS        
                HNS"=>"STRMADMIN"."D SENDER                                                      
                ESTINATION_SPOKE1"@H                                                             
                UB.EXAMPLE.COM                                                                   
   1         13 "STRMADMIN"."SOURCE_ PROPAGATION  SEND RATE               2568.00 MESSAGES PER SE
                HNS"=>"STRMADMIN"."D SENDER                                       COND           
                ESTINATION_SPOKE1"@H                                                             
                UB.EXAMPLE.COM                                                                   
   1         16 CAPTURE_HNS          CAPTURE      CAPTURE RATE           10464.00 MESSAGES PER SE
                                                                                  COND           
   1         16 CAPTURE_HNS          CAPTURE      ENQUEUE RATE           10002.00 MESSAGES PER SE
                                                                                  COND           
   1         16 CAPTURE_HNS          CAPTURE      EVENT: CPU + Wa           11.02 PERCENT        
                                                  it for CPU                                     
   1         16 CAPTURE_HNS          CAPTURE      EVENT: CPU + Wa           35.96 PERCENT        
                                                  it for CPU                                     
   1         16 CAPTURE_HNS          CAPTURE      EVENT: SQL*Net             5.51 PERCENT        
                                                  message from db                                
                                                  link                                           
   1         16 CAPTURE_HNS          CAPTURE      LATENCY                    2.65 SECONDS        
.
.
.
   3          3 "STRMADMIN"."SOURCE_ QUEUE        CURRENT QUEUE S        42580.00 NUMBER OF MESSA
                HNS"                              IZE                             GES            
   3          3 "STRMADMIN"."SOURCE_ QUEUE        ENQUEUE RATE            2624.17 MESSAGES PER SE
                HNS"                                                              COND           
   3          3 "STRMADMIN"."SOURCE_ QUEUE        SPILL RATE                15.48 MESSAGES PER SE
                HNS"                                                              COND           
   3          4 "STRMADMIN"."SOURCE_ PROPAGATION  EVENT: CPU + Wait         17.65 PERCENT        
                HNS"=>"STRMADMIN"."D SENDER        for CPU                                       
                ESTINATION_SPOKE1"@S                                                             
                POKE1.EXAMPLE.COM                                                                
   3          4 "STRMADMIN"."SOURCE_ PROPAGATION  EVENT: latch free             .45 PERCENT      
                HNS"=>"STRMADMIN"."D SENDER                                                      
                ESTINATION_SPOKE1"@S                                                             
                POKE1.EXAMPLE.COM                                                                
   3          4 "STRMADMIN"."SOURCE_ PROPAGATION  EVENT: latch: row            4.90 PERCENT      
                HNS"=>"STRMADMIN"."D SENDER        cache objects                                 
                ESTINATION_SPOKE1"@S                                                             
                POKE1.EXAMPLE.COM                                                                
   3          4 "STRMADMIN"."SOURCE_ PROPAGATION  BANDWIDTH              30265.44 BYTES PER SECON
                HNS"=>"STRMADMIN"."D SENDER                                       D              
                ESTINATION_SPOKE1"@S                                                             
                POKE1.EXAMPLE.COM                                                                
   3          4 "STRMADMIN"."SOURCE_ PROPAGATION  LATENCY                    3.60 SECONDS        
                HNS"=>"STRMADMIN"."D SENDER                                                      
                ESTINATION_SPOKE1"@S                                                             
                POKE1.EXAMPLE.COM                                                                
   3          4 "STRMADMIN"."SOURCE_ PROPAGATION  SEND RATE               2572.00 MESSAGES PER SE
                HNS"=>"STRMADMIN"."D SENDER                                       COND           
                ESTINATION_SPOKE1"@S                                                             
                POKE1.EXAMPLE.COM                                                                
   3         10 CAPTURE_HNS          CAPTURE      CAPTURE RATE            2595.29 MESSAGES PER SE
                                                                                  COND           
   3         10 CAPTURE_HNS          CAPTURE      ENQUEUE RATE            2573.21 MESSAGES PER SE
                                                                                  COND           
   3         10 CAPTURE_HNS          CAPTURE      EVENT: ARCH wai           52.23 PERCENT        
                                                  t for archivelo                                
                                                  g lock                                         
   3         10 CAPTURE_HNS          CAPTURE      EVENT: CPU + Wa            7.35 PERCENT        
                                                  it for CPU                                     
   3         10 CAPTURE_HNS          CAPTURE      EVENT: control              .52 PERCENT        
                                                  file sequential                                
                                                   read                                          
   3         10 CAPTURE_HNS          CAPTURE      LATENCY                    3.65 SECONDS        
   3         11 "STRMADMIN"."DESTINA QUEUE        CURRENT QUEUE S        40779.00 NUMBER OF MESSA
                TION_SPOKE1"                      IZE                             GES            
   3         11 "STRMADMIN"."DESTINA QUEUE        ENQUEUE RATE            2573.21 MESSAGES PER SE
                TION_SPOKE1"                                                      COND           
   3         11 "STRMADMIN"."DESTINA QUEUE        SPILL RATE                  .00 MESSAGES PER SE
                TION_SPOKE1"                                                      COND           
   3         14 "STRMADMIN"."SOURCE_ PROPAGATION  EVENT: CPU + Wait            6.92 PERCENT      
                HNS"@HUB.EXAMPLE.COM RECEIVER      for CPU                                       
                =>"STRMADMIN"."DESTI                                                             
                NATION_SPOKE1"                                                                   
   3         14 "STRMADMIN"."SOURCE_ PROPAGATION  EVENT: latch: row            2.23 PERCENT      
                HNS"@HUB.EXAMPLE.COM RECEIVER      cache objects                                 
                =>"STRMADMIN"."DESTI                                                             
                NATION_SPOKE1"                                                                   
   3         14 "STRMADMIN"."SOURCE_ PROPAGATION  EVENT: library ca            3.79 PERCENT      
                HNS"@HUB.EXAMPLE.COM RECEIVER     che: mutex X                                   
                =>"STRMADMIN"."DESTI                                                             
                NATION_SPOKE1"                                                                   
   3         15 APPLY_SPOKE1         APPLY        EVENT: latch: r            4.20 PERCENT        
                                                  ow cache object                                
                                                  s                                              
   3         15 APPLY_SPOKE1         APPLY        EVENT: latch: s             .26 PERCENT        
                                                  hared pool                                     
   3         15 APPLY_SPOKE1         APPLY        EVENT: latch: s             .52 PERCENT        
                                                  hared pool                                     
   3         15 APPLY_SPOKE1         APPLY        LATENCY                    3.64 SECONDS        
   3         15 APPLY_SPOKE1         APPLY        MESSAGE APPLY R         2580.05 MESSAGES PER SE
                                                  ATE                             COND           
   3         15 APPLY_SPOKE1         APPLY        TRANSACTION APP           25.68 TRANSACTIONS PE
                                                  LY RATE                         R SECOND       
.
.
.

Note:

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

You can analyze this output along with the output for the queries in "Viewing the Oracle Streams Components at Each Database" and "Viewing Each Stream Path in an Oracle Streams Topology".

For example, performance is better for components that are part of a combined capture and apply stream path than it is for components that are not. Compare the statistics for the apply_spoke1 apply process at hub.example.com (component ID 8) with the statistics for the apply_spoke1 apply process at spoke1.example.com (component ID 15).

The apply_spoke1 apply process at hub.example.com is part of a combined capture and apply stream path. The apply_spoke1 apply process at spoke1.example.com is not part of a combined capture and apply stream path.

The following statistics illustrate the performance differences between these apply processes:

  • The LATENCY statistic for the apply_spoke1 apply process at hub.example.com (component ID 8) is 2.13 seconds. The LATENCY statistic for the apply_spoke1 apply process at spoke1.example.com (component ID 15) is 3.64 seconds.

  • The MESSAGE APPLY RATE statistic for the apply_spoke1 apply process at hub.example.com is 10004.00 messages per second. The MESSAGE APPLY RATE statistic for the apply_spoke1 apply process at spoke1.example.com is 2580.05 messages per second.

  • The TRANSACTION APPLY RATE statistic for the apply_spoke1 apply process at hub.example.com is 100.00 transactions per second. The TRANSACTION APPLY RATE statistic for the apply_spoke1 apply process at spoke1.example.com is 25.68 transactions per second.

See Also:

Viewing Session-Level Statistics

You can view session-level statistics for the Oracle Streams components. The query in this section displays the following information for each session-level statistic:

  • The name of the Oracle Streams component

  • The type of the Oracle Streams component. The following types are possible:

  • The type of the subcomponent. Only capture processes and apply processes have subcomponents.

    The following capture process subcomponent types are possible:

    • LOGMINER READER for a builder server of a capture process

    • LOGMINER PREPARER for a preparer server of a capture process

    • LOGMINER BUILDER for a reader server of a capture process

    • CAPTURE SESSION for a capture process session

    The following apply process subcomponent types are possible:

    • PROPAGATION SENDER+RECEIVER for sending LCRs from a capture process directly to an apply process in a combined capture and apply configuration

    • APPLY READER for a reader server of an apply process

    • APPLY COORDINATOR for a coordinator process of an apply process

    • APPLY SERVER for a reader server of an apply process

  • The statistic that was gathered for the component

  • The value and unit of the statistic. Session-level statistics show PERCENT for the unit. The value is the percentage of time spent either IDLE, paused for FLOW CONTROL, or waiting for an EVENT.

The ANALYZE_CURRENT_PERFORMANCE procedure in the DBMS_STREAMS_ADVISOR_ADM package gathers the statistics returned by the query in this section. Therefore, the statistics returned by the query were the current statistics when the procedure was run. The statistics are not updated automatically.

Table 22-4 describes each of the statistics that can be returned by the query in this section:

Table 22-4 Session-Level Statistics for Oracle Streams Components

Statistic Unit Description

IDLE

PERCENT

The percentage of time that the session spent idle. When a session is idle, it is not performing any work.

FLOW CONTROL

PERCENT

The percentage of time that the session was paused for flow control. See "Capture Process States" for information about flow control.

EVENT (Top wait event)

PERCENT

The percentage of time that the session spent waiting because of a wait event.

The Oracle Streams Performance Advisor only gathers information about the top three events for each session.

For example, an apply server might wait for a dependent transaction to be applied before applying its transaction.


Regarding flow control and event statistics, the time period is calculated as the time difference between the two snapshots used by the ANALYZE_CURRENT_PERFORMANCE procedure in the same user session. See "About the Information Gathered With the Oracle Streams Performance Advisor" for information about the snapshots. When a user session ends, the flow control and event statistics are purged.

To display session-level performance statistics for the components in an Oracle Streams topology, run the following query:

COLUMN PATH_ID HEADING 'Path|ID' FORMAT 999
COLUMN COMPONENT_ID HEADING 'Component|ID' FORMAT 999
COLUMN COMPONENT_NAME HEADING 'Component|Name' FORMAT A20
COLUMN COMPONENT_TYPE HEADING 'Component|Type' FORMAT A10
COLUMN SUB_COMPONENT_TYPE HEADING 'Subcomponent|Type' FORMAT A17
COLUMN STATISTIC_NAME HEADING 'Statistic' FORMAT A15
COLUMN STATISTIC_VALUE HEADING 'Value' FORMAT 999.99
COLUMN STATISTIC_UNIT HEADING 'Unit' FORMAT A7
 
SELECT DISTINCT
       cp.PATH_ID,
       cs.COMPONENT_ID,
       cs.COMPONENT_NAME,
       cs.COMPONENT_TYPE,
       cs.SUB_COMPONENT_TYPE,
       cs.STATISTIC_NAME,
       cs.STATISTIC_VALUE,
       cs.STATISTIC_UNIT
   FROM DBA_STREAMS_TP_COMPONENT_STAT  cs,
        (SELECT PATH_ID, SOURCE_COMPONENT_ID AS COMPONENT_ID
        FROM DBA_STREAMS_TP_COMPONENT_LINK
        UNION
        SELECT PATH_ID, DESTINATION_COMPONENT_ID AS COMPONENT_ID
        FROM DBA_STREAMS_TP_COMPONENT_LINK) cp
   WHERE cs.ADVISOR_RUN_ID=2 AND
         cs.SESSION_ID IS NOT NULL AND
         cs.SESSION_SERIAL# IS NOT NULL AND
         cs.COMPONENT_ID = cp.COMPONENT_ID
   ORDER BY PATH_ID, COMPONENT_ID, COMPONENT_NAME, COMPONENT_TYPE, STATISTIC_NAME;

This example uses 2 for the ADVISOR_RUN_ID in the WHERE clause. Substitute the advisor run ID for the advisor run you want to query. See "Gathering Information About the Oracle Streams Topology and Performance" for information about determining the ADVISOR_RUN_ID.

The following output shows a partial list of the session-level performance statistics for the components listed in "Viewing the Oracle Streams Components at Each Database". Specifically, the following output shows session-level performance statistics for the components in stream path 1 and stream path 3:

Path Component Component            Component  Subcomponent                                     
  ID        ID Name                 Type       Type              Statistic         Value Unit    
---- --------- -------------------- ---------- ----------------- --------------- ------- ------- 
   1         6 "STRMADMIN"."SOURCE_ PROPAGATIO                   EVENT: CPU + Wa   32.55 PERCENT 
               HNS"@SPOKE1.EXAMPLE. N RECEIVER                   it for CPU                      
               COM=>"STRMADMIN"."DE                                                              
               STINATIO N_SPOKE1"                                                                
   1         6 "STRMADMIN"."SOURCE_ PROPAGATIO                   EVENT: SQL*Net    23.62 PERCENT 
               HNS"@SPOKE1.EXAMPLE. N RECEIVER                   more data from                  
               COM=>"STRMADMIN"."DE                              client                          
               STINATION_SPOKE1"                                                                 
   1         6 "STRMADMIN"."SOURCE_ PROPAGATIO                   EVENT: latch: r    2.10 PERCENT 
               HNS"@SPOKE1.EXAMPLE. N RECEIVER                   ow cache object                 
               COM=>"STRMADMIN"."DE                              s                               
               STINATION_SPOKE1"                                                                 
   1         6 "STRMADMIN"."SOURCE_ PROPAGATIO                   FLOW CONTROL        .89 PERCENT 
               HNS"@SPOKE1.EXAMPLE. N RECEIVER                                                   
               COM=>"STRMADMIN"."DE                                                              
               STINATION_SPOKE1"                                                                 
   1         6 "STRMADMIN"."SOURCE_ PROPAGATIO                   IDLE              36.61 PERCENT 
               HNS"@SPOKE1.EXAMPLE. N RECEIVER                                                   
               COM=>"STRMADMIN"."DE                                                              
               STINATION_SPOKE1"                                                                 
   1         8 APPLY_SPOKE1         APPLY      APPLY READER      EVENT: CPU + Wa     .26 PERCENT 
                                                                 it for CPU                      
   1         8 APPLY_SPOKE1         APPLY      APPLY SERVER      EVENT: CPU + Wa   23.10 PERCENT 
                                                                 it for CPU                      
   1         8 APPLY_SPOKE1         APPLY      APPLY SERVER      EVENT: latch: r    1.31 PERCENT 
                                                                 ow cache object                 
                                                                 s                               
   1         8 APPLY_SPOKE1         APPLY      APPLY READER      EVENT: latch: s     .26 PERCENT 
                                                                 hared pool                      
   1         8 APPLY_SPOKE1         APPLY      APPLY SERVER      EVENT: latch: s    1.57 PERCENT 
                                                                 hared pool                      
   1         8 APPLY_SPOKE1         APPLY      APPLY COORDINATOR FLOW CONTROL        .00 PERCENT 
   1         8 APPLY_SPOKE1         APPLY      APPLY READER      FLOW CONTROL      10.76 PERCENT 
   1         8 APPLY_SPOKE1         APPLY      APPLY SERVER      FLOW CONTROL        .00 PERCENT 
   1         8 APPLY_SPOKE1         APPLY      APPLY COORDINATOR IDLE               6.21 PERCENT 
   1         8 APPLY_SPOKE1         APPLY      APPLY READER      IDLE               9.24 PERCENT 
   1         8 APPLY_SPOKE1         APPLY      APPLY SERVER      IDLE               8.53 PERCENT 
   1        13 "STRMADMIN"."SOURCE_ PROPAGATIO                   EVENT: CPU + Wa   21.65 PERCENT 
               HNS"=>"STRMADMIN"."D N SENDER                     it for CPU                      
               ESTINATION_SPOKE1"@H                                                              
               UB.EXAMPLE.COM                                                                    
   1        13 "STRMADMIN"."SOURCE_ PROPAGATIO                   EVENT: SQL*Net      .26 PERCENT 
               HNS"=>"STRMADMIN"."D N SENDER                     message to dbli                 
               ESTINATION_SPOKE1"@H                              nk                              
               UB.EXAMPLE.COM                                                                    
   1        13 "STRMADMIN"."SOURCE_ PROPAGATIO                   EVENT: latch: r     .26 PERCENT 
               HNS"=>"STRMADMIN"."D N SENDER                     ow cache object                 
               ESTINATION_SPOKE1"@H                              s                               
               UB.EXAMPLE.COM                                                                    
   1        13 "STRMADMIN"."SOURCE_ PROPAGATIO                   EVENT: latch: s     .26 PERCENT 
               HNS"=>"STRMADMIN"."D N SENDER                     hared pool                      
               ESTINATION_SPOKE1"@H                                                              
               UB.EXAMPLE.COM                                                                    
   1        13 "STRMADMIN"."SOURCE_ PROPAGATIO                   FLOW CONTROL       7.37 PERCENT 
               HNS"=>"STRMADMIN"."D N SENDER                                                     
               ESTINATION_SPOKE1"@H                                                              
               UB.EXAMPLE.COM                                                                    
   1        13 "STRMADMIN"."SOURCE_ PROPAGATIO                   IDLE              67.41 PERCENT 
               HNS"=>"STRMADMIN"."D N SENDER                                                     
               ESTINATION_SPOKE1"@H                                                              
               UB.EXAMPLE.COM                                                                    
   1        16 CAPTURE_HNS          CAPTURE    LOGMINER READER   EVENT: ARCH wai     .26 PERCENT 
                                                                 t on c/f tx acq                 
                                                                 uire 2                          
   1        16 CAPTURE_HNS          CAPTURE    CAPTURE SESSION   EVENT: CPU + Wa   35.96 PERCENT 
                                                                 it for CPU                      
   1        16 CAPTURE_HNS          CAPTURE    LOGMINER BUILDER  EVENT: CPU + Wa     .26 PERCENT 
                                                                 it for CPU                      
   1        16 CAPTURE_HNS          CAPTURE    LOGMINER PREPARER EVENT: CPU + Wa   11.02 PERCENT 
                                                                 it for CPU                      
   1        16 CAPTURE_HNS          CAPTURE    LOGMINER READER   EVENT: CPU + Wa     .26 PERCENT 
                                                                 it for CPU                      
   1        16 CAPTURE_HNS          CAPTURE    CAPTURE SESSION   EVENT: SQL*Net     5.51 PERCENT 
                                                                 message from db                 
                                                                 link                            
   1        16 CAPTURE_HNS          CAPTURE    CAPTURE SESSION   EVENT: SQL*Net      .26 PERCENT 
                                                                 message to dbli                 
                                                                 nk                              
   1        16 CAPTURE_HNS          CAPTURE    CAPTURE SESSION   EVENT: latch: r     .26 PERCENT 
                                                                 ow cache object                 
                                                                 s                               
   1        16 CAPTURE_HNS          CAPTURE    LOGMINER BUILDER  EVENT: latch: r    1.84 PERCENT 
                                                                 ow cache object                 
                                                                 s                               
   1        16 CAPTURE_HNS          CAPTURE    LOGMINER PREPARER EVENT: latch: r     .79 PERCENT 
                                                                 ow cache object                 
                                                                 s                               
   1        16 CAPTURE_HNS          CAPTURE    CAPTURE SESSION   EVENT: latch: s     .26 PERCENT 
                                                                 hared pool                      
   1        16 CAPTURE_HNS          CAPTURE    LOGMINER READER   EVENT: latch: s     .79 PERCENT 
                                                                 hared pool                      
   1        16 CAPTURE_HNS          CAPTURE    CAPTURE SESSION   FLOW CONTROL      16.27 PERCENT 
   1        16 CAPTURE_HNS          CAPTURE    LOGMINER BUILDER  FLOW CONTROL        .00 PERCENT 
   1        16 CAPTURE_HNS          CAPTURE    LOGMINER PREPARER FLOW CONTROL        .00 PERCENT 
   1        16 CAPTURE_HNS          CAPTURE    LOGMINER READER   FLOW CONTROL        .00 PERCENT 
   1        16 CAPTURE_HNS          CAPTURE    CAPTURE SESSION   IDLE              41.47 PERCENT 
   1        16 CAPTURE_HNS          CAPTURE    LOGMINER BUILDER  IDLE              97.90 PERCENT 
   1        16 CAPTURE_HNS          CAPTURE    LOGMINER PREPARER IDLE              88.19 PERCENT 
   1        16 CAPTURE_HNS          CAPTURE    LOGMINER READER   IDLE              98.69 PERCENT 
.
.
.
   3         4 "STRMADMIN"."SOURCE_ PROPAGATIO                   FLOW CONTROL       6.50 PERCENT 
               HNS"=>"STRMADMIN"."D N SENDER                                                     
               ESTINATION_SPOKE1"@S                                                              
               POKE1.EXAMPLE.COM                                                                 
   3         4 "STRMADMIN"."SOURCE_ PROPAGATIO                   IDLE              70.50 PERCENT 
               HNS"=>"STRMADMIN"."D N SENDER                                                     
               ESTINATION_SPOKE1"@S                                                              
               POKE1.EXAMPLE.COM                                                                 
   3        10 CAPTURE_HNS          CAPTURE    CAPTURE SESSION   EVENT: ARCH wai   52.23 PERCENT 
                                                                 t for archivelo                 
                                                                 g lock                          
   3        10 CAPTURE_HNS          CAPTURE    CAPTURE SESSION   EVENT: CPU + Wa    7.35 PERCENT 
                                                                 it for CPU                      
   3        10 CAPTURE_HNS          CAPTURE    CAPTURE SESSION   EVENT: control      .52 PERCENT 
                                                                 file sequential                 
                                                                  read                           
   3        10 CAPTURE_HNS          CAPTURE    CAPTURE SESSION   FLOW CONTROL       4.24 PERCENT 
   3        10 CAPTURE_HNS          CAPTURE    CAPTURE SESSION   IDLE               2.23 PERCENT 
   3        14 "STRMADMIN"."SOURCE_ PROPAGATIO                   EVENT: CPU + Wa    6.92 PERCENT 
               HNS"@HUB.EXAMPLE.COM N RECEIVER                   it for CPU                      
               =>"STRMADMIN"."DESTI                                                              
               NATION_SPOKE1"                                                                    
   3        14 "STRMADMIN"."SOURCE_ PROPAGATIO                   EVENT: latch: r    2.23 PERCENT 
               HNS"@HUB.EXAMPLE.COM N RECEIVER                   ow cache object                 
               =>"STRMADMIN"."DESTI                              s                               
               NATION_SPOKE1"                                                                    
   3        14 "STRMADMIN"."SOURCE_ PROPAGATIO                   EVENT: library     3.79 PERCENT 
               HNS"@HUB.EXAMPLE.COM N RECEIVER                   cache: mutex X                  
               =>"STRMADMIN"."DESTI                                                              
               NATION_SPOKE1"                                                                    
   3        14 "STRMADMIN"."SOURCE_ PROPAGATIO                   FLOW CONTROL        .67 PERCENT 
               HNS"@HUB.EXAMPLE.COM N RECEIVER                                                   
               =>"STRMADMIN"."DESTI                                                              
               NATION_SPOKE1"                                                                    
   3        14 "STRMADMIN"."SOURCE_ PROPAGATIO                   IDLE              85.04 PERCENT 
               HNS"@HUB.EXAMPLE.COM N RECEIVER                                                   
               =>"STRMADMIN"."DESTI                                                              
               NATION_SPOKE1"                                                                    
   3        15 APPLY_SPOKE1         APPLY      APPLY COORDINATOR EVENT: latch: r    4.20 PERCENT 
                                                                 ow cache object                 
                                                                 s                               
   3        15 APPLY_SPOKE1         APPLY      APPLY COORDINATOR EVENT: latch: s     .52 PERCENT 
                                                                 hared pool                      
   3        15 APPLY_SPOKE1         APPLY      APPLY READER      EVENT: latch: s     .26 PERCENT 
                                                                 hared pool                      
   3        15 APPLY_SPOKE1         APPLY      APPLY COORDINATOR FLOW CONTROL        .00 PERCENT 
   3        15 APPLY_SPOKE1         APPLY      APPLY READER      FLOW CONTROL       1.56 PERCENT 
   3        15 APPLY_SPOKE1         APPLY      APPLY SERVER      FLOW CONTROL        .00 PERCENT 
   3        15 APPLY_SPOKE1         APPLY      APPLY COORDINATOR IDLE              87.28 PERCENT 
   3        15 APPLY_SPOKE1         APPLY      APPLY READER      IDLE              96.88 PERCENT 
   3        15 APPLY_SPOKE1         APPLY      APPLY SERVER      IDLE              91.29 PERCENT 

Note:

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

  • You can view the session ID and serial number for each session by adding the SESSION_ID and SESSION_SERIAL# columns to the query on the DBA_STREAMS_TP_COMPONENT_STAT view.

See Also:

Viewing Statistics for the Stream Paths in an Oracle Streams Environment

The query in this section shows the following information for each stream path in the Oracle Streams topology:

  • Whether optimization mode for Oracle Streams is used for the path. When the OPTIMIZATION_MODE statistic is greater than 0 (zero) for a path, the path uses the combined capture and apply optimization. When the OPTIMIZATION_MODE statistic is 0 (zero) for a path, the path does not use the combined capture and apply optimization.

  • The MESSAGE RATE value is the average number of messages sent each second from the start of the path to the end of the path.

  • The TRANSACTION RATE value is the average number of transactions sent each second from the start of the path to the end of the path.

The time period for these statistics is calculated as the time difference between the two snapshots used by the ANALYZE_CURRENT_PERFORMANCE procedure in the same user session. See "About the Information Gathered With the Oracle Streams Performance Advisor" for information about the snapshots. When a user session ends, these statistics are purged.

To display this information, run the following query:

COLUMN PATH_ID HEADING 'Path ID' FORMAT 999
COLUMN STATISTIC_NAME HEADING 'Statistic' FORMAT A25
COLUMN STATISTIC_VALUE HEADING 'Value' FORMAT 99999999.99
COLUMN STATISTIC_UNIT HEADING 'Unit' FORMAT A25

SELECT PATH_ID,
       STATISTIC_NAME,
       STATISTIC_VALUE,
       STATISTIC_UNIT
   FROM DBA_STREAMS_TP_PATH_STAT
   WHERE ADVISOR_RUN_ID=2
   ORDER BY PATH_ID, STATISTIC_NAME;

This example uses 2 for the ADVISOR_RUN_ID in the WHERE clause. Substitute the advisor run ID for the advisor run you want to query. See "Gathering Information About the Oracle Streams Topology and Performance" for information about determining the ADVISOR_RUN_ID.

The following output shows the path statistics for the stream paths listed in "Viewing Each Stream Path in an Oracle Streams Topology":

Path ID Statistic                        Value Unit
------- ------------------------- ------------ -------------------------
      1 OPTIMIZATION_MODE                 1.00 NUMBER
      1 MESSAGE RATE                  10004.00 MESSAGES PER SECOND
      1 TRANSACTION RATE                100.00 TRANSACTIONS PER SECOND
      2 OPTIMIZATION_MODE                 1.00 NUMBER
      2 MESSAGE RATE                  10028.25 MESSAGES PER SECOND
      2 TRANSACTION RATE                100.37 TRANSACTIONS PER SECOND
      3 OPTIMIZATION_MODE                  .00 NUMBER
      3 MESSAGE RATE                   2623.20 MESSAGES PER SECOND
      3 TRANSACTION RATE                 26.10 TRANSACTIONS PER SECOND
      4 OPTIMIZATION_MODE                  .00 NUMBER
      4 MESSAGE RATE                   2580.05 MESSAGES PER SECOND
      4 TRANSACTION RATE                 25.68 TRANSACTIONS PER SECOND

Note:

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

Stream paths 1 and 2 use combined capture and apply, while stream paths 3 and 4 do not. Notice that performance is better for the stream paths that use combined capture and apply.

Using the UTL_SPADV Package

The UTL_SPADV package provides subprograms to collect and analyze statistics for the Oracle Streams components in a distributed database environment. The package uses the Oracle Streams Performance Advisor to gather statistics.

The package includes the following procedures:

These procedures collect the same statistics as the Oracle Streams Performance Advisor. These statistics are described in Table 22-3, "Component-Level Statistics for Oracle Streams Components" and Table 22-4, "Session-Level Statistics for Oracle Streams Components".

To collect and show statistics using the UTL_SPADV package, complete the following steps:

  1. Identify the database that you will use to gather the information. An administrative user at this database must meet the following requirements:

    • The user must have access to a database link to each database that contains Oracle Streams components.

    • The user must have been granted privileges using the DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE procedure, and each database link must connect to a user at the remote database that has been granted privileges using the DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE procedure.

      If you configure an Oracle Streams administrator at each database with Oracle Streams components, then the Oracle Streams administrator has the necessary privileges. See "Configuring an Oracle Streams Administrator" for instructions.

    If no database in your environment meets these requirements, then choose a database, configure the necessary database links, and grant the necessary privileges to the users before proceeding.

  2. In SQL*Plus, connect to the database you identified in Step 1 as a user that meets the requirements listed in Step 1.

    For example, connect to the hub.example.com database as the Oracle Streams administrator.

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

  3. Run the utlspadv.sql script in the rdbms/admin directory to load the UTL_SPADV package. For example:

    @utlspadv.sql
    
  4. Run the COLLECT_STATS procedure:

    exec UTL_SPADV.COLLECT_STATS
    
  5. Run the SHOW_STATS procedure:

    SET SERVEROUTPUT ON SIZE 50000
    exec UTL_SPADV.SHOW_STATS
    

Your output is similar to the following:

LEGEND                                                                                            
<statistics>= <capture> [ <queue> <psender> <preceiver> <queue> ] <apply> <bottleneck>            
<capture>   = '|<C>' <name> <msgs captured/sec> <msgs enqueued/sec> <latency>
                    'LMR' <idl%> <flwctrl%> <topevt%> <topevt>                                           
                    'LMP' (<parallelism>) <idl%> <flwctrl%> <topevt%> <topevt>                           
                    'LMB' <idl%> <flwctrl%> <topevt%> <topevt>                                           
                    'CAP' <idl%> <flwctrl%> <topevt%> <topevt>                                           
                    'CAP+PS' <msgs sent/sec> <bytes sent/sec> <latency> <idl%> <flwctrl%> <topevt%>      
<topevt>                                                                                          
<apply>     = '|<A>' <name> <msgs applied/sec> <txns applied/sec> <latency>                       
                    'PS+PR' <idl%> <flwctrl%> <topevt%> <topevt>                                         
                    'APR' <idl%> <flwctrl%> <topevt%> <topevt>                                           
                    'APC' <idl%> <flwctrl%> <topevt%> <topevt>                                           
                    'APS' (<parallelism>) <idl%> <flwctrl%> <topevt%> <topevt>                           
<queue>     = '|<Q>' <name> <msgs enqueued/sec> <msgs spilled/sec> <msgs in queue>                
<psender>   = '|<PS>' <name> <msgs sent/sec> <bytes sent/sec> <latency> <idl%> <flwctrl%>         
<topevt%> <topevt>                                                                                
<preceiver> = '|<PR>' <name> <idl%> <flwctrl%> <topevt%> <topevt>                                 
<bottleneck>= '|<B>' <name> <sub_name> <sessionid> <serial#> <topevt%> <topevt>                   
 
                                                                                                 
OUTPUT                                                                                            
PATH 1 RUN_ID 3 RUN_TIME 2008-MAR-20 10:52:22 CCA Y
|<C> CAPTURE_HNS=>HUB.EXAMPLE.COM 10267 10040 3 LMR 95% 0% 3.3% "" LMP (1) 86.7% 0% 11.7% "" 
LMB 86.7% 0% 11.7% "" CAP+PS 10040 1.46E+06 3 16.7% 71.7% 11.7% "" 
|<PR> SPOKE1.EXAMPLE.COM=> 98.5% 0% 0.6% "" |<Q> "STRMADMIN"."DESTINATION_SPOKE1" 25 0 0 
|<A> APPLY_SPOKE1 10042 100 4 APR 93.3% 0% 6.7% "" APC 98.1% 0% 1.8% "" 
APS (1) 953 272 76.7% "CPU + Wait for CPU" |<B> "NO BOTTLENECK IDENTIFIED"
 
PATH 1 RUN_ID 4 RUN_TIME 2008-MAR-20 10:53:32 CCA Y
|<C> CAPTURE_HNS=>HUB.EXAMPLE.COM 10464 10002 3 LMR 95% 0% 1.7% "" LMP (1) 83.3% 0% 16.7% "" 
LMB 85% 0% 15% "" CAP+PS 10002 1.41E+06 2 62.9% 0% 35.7% "CPU + Wait for CPU" 
|<PR> SPOKE1.EXAMPLE.COM=> 0% 83.3% 13.3% "" |<Q> "STRMADMIN"."DESTINATION_SPOKE1" 127 0 0 
|<A> APPLY_SPOKE1 10004 100 3 APR 42.9% 57.1% 0% "" APC 90% 0% 10% "" 
APS (1) 11.7% 0% 75% "CPU + Wait for CPU" |<B> APPLY_SPOKE1 APS 953 272 65.% "CPU + Wait for CPU"
.
.
.

Note:

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

The following table describes the abbreviations used in the legend:

Abbreviation Description
A Apply process
APC Coordinator process used by an apply process
APR Reader server used by an apply process
APS Apply server used by an apply process
B Bottleneck
C or CAP Capture process
CAP+PS Capture process session and propagation sender in a combined capture and apply configuration
CCA Combined capture and apply (Y indicates that it is used for the path; N indicates that it is not used for the path.)
flwctrl Flow control
idl Idle
LMB Builder server used by a capture process (LogMiner builder)
LMP Preparer server used by a capture process (LogMiner preparer)
LMR Reader server used by a capture process (LogMiner reader)
msgs Messages
preceiver or PR Propagation receiver
psender or PS Propagation sender
PS+PR Propagation sender and propagation receiver in a combined capture and apply configuration
Q Queue
serial# Session serial number
sec Second
sid Session identifier
sub_name Subcomponent name
topevt Top event

Use the legend and the abbreviations to determine the statistics in the output. For example, the following output is for capture_hns capture process in path 1, run ID 4:

|<C> CAPTURE_HNS=>HUB.EXAMPLE.COM 10464 10002 3 LMR 95% 0% 1.7% "" LMP (1) 83.3% 0% 16.7% "" 
LMB 85% 0% 15% "" CAP+PS 10002 1.41E+06 2 62.9% 0% 35.7% "CPU + Wait for CPU" 

This output shows the following statistics:

See Also:

Oracle Database PL/SQL Packages and Types Reference for more information about the UTL_SPADV package