| Oracle® Streams Concepts and Administration 11g Release 1 (11.1) Part Number B28321-01 |
|
|
View PDF |
| Oracle® Streams Concepts and Administration 11g Release 1 (11.1) Part Number B28321-01 |
|
|
View PDF |
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 Information Gathered With the Oracle Streams Performance Advisor
Gathering Information About the Oracle Streams Topology and Performance
Viewing the Oracle Streams Topology and Analyzing Oracle Streams Performance
Oracle Streams enables you to send messages between multiple databases. An Oracle Streams environment can send the following types of messages:
Logical change records (LCRs) that contain database changes
User messages that contain custom information based on user-defined types
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.
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:
After information is gathered by the Oracle Streams Performance Advisor, you can view it by querying the following data dictionary views:
DBA_STREAMS_TP_COMPONENT contains information about each Oracle Streams component at each database.
DBA_STREAMS_TP_COMPONENT_LINK contains information about how messages flow between Oracle Streams components.
DBA_STREAMS_TP_COMPONENT_STAT contains temporary performance statistics and session statistics about each Oracle Streams component.
DBA_STREAMS_TP_DATABASE contains information about each database that contains Oracle Streams components.
DBA_STREAMS_TP_PATH_BOTTLENECK contains temporary information about Oracle Streams components that might be slowing down the flow of messages in a stream path.
DBA_STREAMS_TP_PATH_STAT contains temporary performance statistics about each stream path that exists in the Oracle Streams topology.
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.
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 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
BYTES SENT VIA SQL*NET TO DBLINK
LATENCY
SEND RATE TO APPLY
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 NETWORK RECEIVER receives LCRs directly from a capture process in a combined capture and apply configuration.
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:
"Viewing Component-Level Statistics" for detailed information about component-level statistics
"Viewing Session-Level Statistics" for detailed information about session-level statistics
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 24-1 shows the position of each link in a sample stream path.
Table 24-1 Position of Each Link in a Sample Stream Path
| Beginning 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:
Stream Paths in a Sample Combined Capture and Apply Replication Environment
Stream Paths in a Sample Replication Environment That Propagates Messages
See Also:
Oracle Streams Replication Administrator's Guide for information about best practices for Oracle Streams replication environmentsConsider 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. Although queues and propagations must be configured in a combined capture and apply environment, they are not used. Instead, the capture process sends messages directly to the apply process to achieve the best performance possible.
Figure 24-1 shows an Oracle Streams replication environment that uses combined capture and apply in two separate stream paths.
Figure 24-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 24-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.
The queues and propagations appear in the Oracle Streams topology and in the stream paths. However, because the environment uses combined capture and apply, the Oracle Streams Performance Advisor does not record performance statistics for the queues and propagations.
When there are multiple apply process that apply changes generated by a single source, a stream path splits into multiple stream paths. If this type environment is an Oracle Streams replication environment, then it cannot use combined capture and apply. Therefore, the queues store the messages, and propagations send the messages from one queue to another.
Figure 24-2 shows this type of Oracle Streams environment.
Figure 24-2 Oracle Streams Topology with Multiple Apply Processes for a Single Source
The Oracle Streams topology in Figure 24-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.
In an environment that sends messages from queue to queue with propagations, the Oracle Streams Performance Advisor records performance statistics for the queues and propagations.
See Also:
"Message Propagation Between Queues"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 24-2 illustrates how the procedure gathers information in each advisor run in a single user session.
Table 24-2 How the Oracle Streams Performance Advisor Gathers Information in a Session
| Advisor Run | Information Gathered |
|---|---|
|
1 |
|
|
2 |
|
|
3 |
|
For the best results in an advisor run, meet the following criteria:
Ensure that as many Oracle Streams components as possible are enabled during the time period between the two snapshots used in the advisor run. Specifically, capture processes, propagations, and apply processes should be enabled, queues should be started, and database links should be active.
If data is replicated in the Oracle Streams environment, then ensure that the replicated database objects are experiencing an average, or near average, number of changes during the time period between the two snapshots used in the advisor run. The Oracle Streams Performance Advisor gathers more accurate statistics if it is run when the Oracle Streams replication environment is experiencing typical replication activity.
If messages are sent by applications in the Oracle Streams environment, then ensure that the applications are sending an average, or near average, number of messages during the time period between the two snapshots used in the advisor run. The Oracle Streams Performance Advisor gathers more accurate statistics if it is run when the Oracle Streams messaging environment is sending a typical number of messages.
To gather information about the Oracle Streams topology and Oracle Streams performance, complete the following steps:
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 the SELECT_CATALOG_ROLE system privilege at the local database, and each database link must connect to a user at the remote database that has the SELECT_CATALOG_ROLE system privilege. If you configure an Oracle Streams administrator at each database with Oracle Streams components, then the Oracle Streams administrator has the necessary privilege. 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.
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, if the Oracle Streams administrator strmadmin at the hub.net database meets the requirements, then connect to the database as follows:
CONNECT strmadmin/user-password@hub.net
Run the ANALYZE_CURRENT_PERFORMANCE procedure in the DBMS_STREAMS_ADVISOR_ADM package:
exec DBMS_STREAMS_ADVISOR_ADM.ANALYZE_CURRENT_PERFORMANCE;
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;
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:
Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_STREAMS_ADVISOR_ADM package
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 Oracle Streams replication environment shown in Figure 24-3.
Figure 24-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 24-3. This environment contains both of the following types of stream paths:
A combined capture and apply stream path flows from the spoke1.net database to the hub.net database and from the spoke2.net database to the hub.net database. This type of stream path is described in "Stream Paths in a Sample Combined Capture and Apply Replication Environment".
Two stream paths that use propagations to send messages flow from the hub.net database to the spoke1.net and spoke2.net databases. This type of stream path is described in "Stream Paths in a Sample Replication Environment That Propagates Messages".
The output of the queries in this section illustrate the differences between these two types of stream paths.
This section contains the following topics:
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:
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
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.NET 15-JUN-07 11.1.0.5.0 11.1.0 DIAGNOSTIC+TUNING SPOKE1.NET 15-JUN-07 11.1.0.5.0 11.1.0 DIAGNOSTIC+TUNING SPOKE2.NET 15-JUN-07 11.1.0.5.0 11.1.0 DIAGNOSTIC+TUNING
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.net, spoke1.net, and spoke2.net.
The Last Queried column shows that the Oracle Streams Performance Advisor was last run on June 15, 2007.
The Version column shows that version of each database is 11.1.0.5.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 compatibilityYou 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 database to which the propagation sends messages. For example, a propagation sender with the strmadmin.source_hns source queue that sends messages to the spoke1.net database is shown in the following way:
"STRMADMIN"."SOURCE_HNS"=>SPOKE1.NET
The name of a propagation receiver is the database from which the messages are sent and the destination queue for the propagation. For example, a propagation receiver that gets messages from the hub.net database and enqueues them into the strmadmin.destination_spoke1 destination queue is shown in the following way:
HUB.NET=>"STRMADMIN"."DESTINATION_SPOKE1"
The type of the Oracle Streams component. The following types are possible:
CAPTURE for capture processes
PROPAGATION SENDER for propagation senders
PROPAGATION RECEIVER for propagation receivers
APPLY for apply processes
QUEUE for queues
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.NET
2 "STRMADMIN"."DESTINATION_SPOKE2" QUEUE HUB.NET
3 "STRMADMIN"."SOURCE_HNS" QUEUE HUB.NET
4 "STRMADMIN"."SOURCE_HNS"=>SPOKE1.NET PROPAGATION SENDER HUB.NET
5 "STRMADMIN"."SOURCE_HNS"=>SPOKE2.NET PROPAGATION SENDER HUB.NET
6 APPLY_SPOKE1 APPLY HUB.NET
7 APPLY_SPOKE2 APPLY HUB.NET
8 CAPTURE_HNS CAPTURE HUB.NET
9 SPOKE1.NET=>"STRMADMIN"."DESTINATION_SPOKE1 PROPAGATION RECEIVER HUB.NET
"
10 SPOKE2.NET=>"STRMADMIN"."DESTINATION_SPOKE2 PROPAGATION RECEIVER HUB.NET
"
11 "STRMADMIN"."DESTINATION_SPOKE1" QUEUE SPOKE1.NET
12 "STRMADMIN"."SOURCE_HNS" QUEUE SPOKE1.NET
13 "STRMADMIN"."SOURCE_HNS"=>HUB.NET PROPAGATION SENDER SPOKE1.NET
14 APPLY_SPOKE1 APPLY SPOKE1.NET
15 CAPTURE_HNS CAPTURE SPOKE1.NET
16 HUB.NET=>"STRMADMIN"."DESTINATION_SPOKE1" PROPAGATION RECEIVER SPOKE1.NET
17 "STRMADMIN"."DESTINATION_SPOKE2" QUEUE SPOKE2.NET
18 "STRMADMIN"."SOURCE_HNS" QUEUE SPOKE2.NET
19 "STRMADMIN"."SOURCE_HNS"=>HUB.NET PROPAGATION SENDER SPOKE2.NET
20 APPLY_SPOKE2 APPLY SPOKE2.NET
21 CAPTURE_HNS CAPTURE SPOKE2.NET
22 HUB.NET=>"STRMADMIN"."DESTINATION_SPOKE2" PROPAGATION RECEIVER SPOKE2.NET
See Also:
"Viewing Component-Level Statistics" for a query that shows performance statistics for each Oracle Streams component
Oracle Streams Replication Administrator's Guide for information about the n-way replication environment shown in the output
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.
Whether or not the path is active. If a path is active, then messages can flow through the path. If a path is not active, then messages cannot flow through it.
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
COLUMN ACTIVE HEADING 'Active?' FORMAT A7
SELECT PATH_ID,
SOURCE_COMPONENT_ID,
SOURCE_COMPONENT_NAME,
DESTINATION_COMPONENT_ID,
DESTINATION_COMPONENT_NAME,
POSITION,
ACTIVE
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 Active?
----- --------- -------------------- --------- --------------- -------- -------
1 8 CAPTURE_HNS 3 "STRMADMIN"."SO 1 YES
URCE_HNS"
1 3 "STRMADMIN"."SOURCE_ 4 "STRMADMIN"."SO 2 YES
HNS" URCE_HNS"=>SPOK
E1.NET
1 4 "STRMADMIN"."SOURCE_ 16 HUB.NET=>"STRMA 3 YES
HNS"=>SPOKE1.NET DMIN"."DESTINAT
ION_SPOKE1"
1 16 HUB.NET=>"STRMADMIN" 11 "STRMADMIN"."DE 4 YES
."DESTINATION_SPOKE1 STINATION_SPOKE
" 1"
1 11 "STRMADMIN"."DESTINA 14 APPLY_SPOKE1 5 YES
TION_SPOKE1"
2 8 CAPTURE_HNS 3 "STRMADMIN"."SO 1 YES
URCE_HNS"
2 3 "STRMADMIN"."SOURCE_ 5 "STRMADMIN"."SO 2 YES
HNS" URCE_HNS"=>SPOK
E2.NET
2 5 "STRMADMIN"."SOURCE_ 22 HUB.NET=>"STRMA 3 YES
HNS"=>SPOKE2.NET DMIN"."DESTINAT
ION_SPOKE2"
2 22 HUB.NET=>"STRMADMIN" 17 "STRMADMIN"."DE 4 YES
."DESTINATION_SPOKE2 STINATION_SPOKE
" 2"
2 17 "STRMADMIN"."DESTINA 20 APPLY_SPOKE2 5 YES
TION_SPOKE2"
3 15 CAPTURE_HNS 6 APPLY_SPOKE1 1 YES
4 15 CAPTURE_HNS 12 "STRMADMIN"."SO 1 NO
URCE_HNS"
4 12 "STRMADMIN"."SOURCE_ 13 "STRMADMIN"."SO 2 NO
HNS" URCE_HNS"=>HUB.
NET
4 13 "STRMADMIN"."SOURCE_ 9 SPOKE1.NET=>"ST 3 NO
HNS"=>HUB.NET RMADMIN"."DESTI
NATION_SPOKE1"
4 9 SPOKE1.NET=>"STRMADM 1 "STRMADMIN"."DE 4 NO
IN"."DESTINATION_SPO STINATION_SPOKE
KE1" 1"
4 1 "STRMADMIN"."DESTINA 6 APPLY_SPOKE1 5 NO
TION_SPOKE1"
5 21 CAPTURE_HNS 7 APPLY_SPOKE2 1 YES
6 21 CAPTURE_HNS 18 "STRMADMIN"."SO 1 NO
URCE_HNS"
6 18 "STRMADMIN"."SOURCE_ 19 "STRMADMIN"."SO 2 NO
HNS" URCE_HNS"=>HUB.
NET
6 19 "STRMADMIN"."SOURCE_ 10 SPOKE2.NET=>"ST 3 NO
HNS"=>HUB.NET RMADMIN"."DESTI
NATION_SPOKE2"
6 10 SPOKE2.NET=>"STRMADM 2 "STRMADMIN"."DE 4 NO
IN"."DESTINATION_SPO STINATION_SPOKE
KE2" 2"
6 2 "STRMADMIN"."DESTINA 7 APPLY_SPOKE2 5 NO
TION_SPOKE2"
This output shows the following information about the stream paths in this sample environment:
Stream paths 1 and 2 use propagations to send messages, and all of the components in these paths are active.
Stream path 3 includes only one link from the capture_hns capture process at spoke1.net to the apply_spoke1 apply process at hub.net. When a stream path has only one link from a capture process to an apply process, it means that the stream path uses combined capture and apply.
None of the components are active in stream path 4, and the capture process and apply process are the same as the ones identified in stream path 3. This means that stream path 4 identifies the original path for stream path 3. Stream path 4 includes all of the components that are configured to enable combined capture and apply in stream path 3. No performance statistics are recorded for the queue, propagation sender, and propagation receiver in stream path 4.
Stream path 5 includes only one link from the capture_hns capture process at spoke2.net to the apply_spoke2 apply process at hub.net. This stream path uses combined capture and apply.
The relationship between stream paths 5 and 6 is the same as the relationship between stream paths 3 and 4. Stream path 6 identifies the original path for stream path 5. No performance statistics are recorded for the queue, propagation sender, and propagation receiver in stream path 6.
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:
Checking for Bottleneck Components in the Oracle Streams Topology
Viewing Statistics for the Stream Paths in an Oracle Streams Environment
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.A bottleneck component is one that might be performing poorly or one that is disabled. 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:
CAPTURE for capture processes
PROPAGATION SENDER for propagation senders
PROPAGATION RECEIVER for propagation receivers
APPLY for apply processes
QUEUE for queues
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 8 CAPTURE_HNS CAPTURE HUB.NET
2 8 CAPTURE_HNS CAPTURE HUB.NET
3 6 APPLY_SPOKE1 APPLY HUB.NET
5 7 APPLY_SPOKE2 APPLY HUB.NET
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 try to enable them. If the components are enabled, then you can troubleshoot the environment to determine the cause of the bottleneck.
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. Therefore, these components might or might not be performing poorly.
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. Therefore, these components might or might not be performing poorly.
NO BOTTLENECK IDENTIFIED means that bottleneck analysis identified the stream path that contains the component as performing poorly, but bottleneck analysis could not identify a specific component that caused the poor performance. In this case, it is possible that more than one component in the stream path is performing poorly.
See Also:
Oracle Streams Replication Administrator's Guide for more information about troubleshooting Oracle Streams replication environments
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 name of the Oracle Streams component
The type of the Oracle Streams component. The following types are possible:
CAPTURE for capture processes
PROPAGATION SENDER for propagation senders
PROPAGATION RECEIVER for propagation receivers
APPLY for apply processes
QUEUE for queues
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 24-3 describes each of the statistics that can be returned by the query in this section:
Table 24-3 Component-Level Statistics for Oracle Streams Components
| Component Type | Statistic | Unit | Description |
|---|---|---|---|
|
|
|
|
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. |
|
|
|
|
The average number of logical change records (LCRs) enqueued by the capture process each second. |
|
|
|
|
The number of bytes sent by the capture process to destination databases since the capture process was last started. This statistic is recorded only if the stream path for the capture process uses combined capture and apply. |
|
|
|
|
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 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. |
|
|
|
|
The average number of bytes sent each second directly from the capture process to the apply process. This statistic is populated only if combined capture and apply is used to send LCRs directly from the capture process to the apply process. |
|
|
|
|
The average number of messages sent each second by the propagation sender. |
|
|
|
|
The average number of bytes sent each second by the propagation sender. |
|
|
|
|
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. 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 Depending on the type of message sent by the propagation, message creation time is one of the following:
|
|
|
|
|
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:
A persistent user message can be applied in one of the following ways:
|
|
|
|
|
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:
A transaction that includes persistent user messages can be applied in one of the following ways:
|
|
|
|
|
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 Depending on the type of message applied, message creation time is one of the following:
|
|
|
|
|
The average number of messages enqueued into the queue each second. |
|
|
|
|
The average number of messages that spilled from the buffered queue to the queue table each second. |
|
|
|
|
The number of messages in the queue when the |
|
|
|
|
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.
When a stream path uses combined capture and apply, the queue, propagation sender, and propagation receiver statistics for the stream path are not recorded. Therefore, these statistics do not appear in the output when you query the DBA_STREAMS_TP_COMPONENT_STAT view. See "Combined Capture and Apply Optimization".
To display performance statistics for the components in an Oracle Streams topology, run the following query:
COLUMN COMPONENT_ID HEADING 'ID' FORMAT 999
COLUMN COMPONENT_NAME HEADING 'Name' FORMAT A12
COLUMN COMPONENT_TYPE HEADING 'Type' FORMAT A12
COLUMN STATISTIC_NAME HEADING 'Statistic' FORMAT A25
COLUMN STATISTIC_VALUE HEADING 'Value' FORMAT 99999999999.99
COLUMN STATISTIC_UNIT HEADING 'Unit' FORMAT A20
SELECT COMPONENT_ID,
COMPONENT_NAME,
COMPONENT_TYPE,
STATISTIC_NAME,
CASE WHEN cs.STATISTIC_UNIT='BYTES' THEN cs.STATISTIC_VALUE/1048576
ELSE cs.STATISTIC_VALUE END STATISTIC_VALUE,
CASE WHEN cs.STATISTIC_UNIT='BYTES' THEN 'MEGABYTES'
ELSE cs.STATISTIC_UNIT END STATISTIC_UNIT
FROM DBA_STREAMS_TP_COMPONENT_STAT cs
WHERE ADVISOR_RUN_ID=2 AND SESSION_ID IS NULL AND SESSION_SERIAL# IS NULL
ORDER BY 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.
In addition, the query returns converts statistics with the unit BYTES into MEGABYTES. This conversion makes it easier to read the value when the number of bytes is very large.
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 2 and stream path 3:
ID Name Type Statistic Value Unit
---- ------------ ------------ ------------------------- --------------- --------------------
3 "STRMADMIN". QUEUE CURRENT QUEUE SIZE 44222.00 NUMBER OF MESSAGES
"SOURCE_HNS"
3 "STRMADMIN". QUEUE ENQUEUE RATE 2573.21 MESSAGES PER SECOND
"SOURCE_HNS"
3 "STRMADMIN". QUEUE SPILL RATE .00 MESSAGES PER SECOND
"SOURCE_HNS"
5 "STRMADMIN". PROPAGATION BANDWIDTH .00 BYTES PER SECOND
"SOURCE_HNS" SENDER
=>SPOKE2.NET
5 "STRMADMIN". PROPAGATION EVENT: CPU + Wait for CPU 45.00 PERCENT
"SOURCE_HNS" SENDER
=>SPOKE2.NET
5 "STRMADMIN". PROPAGATION LATENCY 3.64 SECONDS
"SOURCE_HNS" SENDER
=>SPOKE2.NET
5 "STRMADMIN". PROPAGATION SEND RATE 2568.00 MESSAGES PER SECOND
"SOURCE_HNS" SENDER
=>SPOKE2.NET
6 APPLY_SPOKE1 APPLY EVENT: CPU + Wait for CPU 65.00 PERCENT
6 APPLY_SPOKE1 APPLY EVENT: CPU + Wait for CPU 75.00 PERCENT
6 APPLY_SPOKE1 APPLY EVENT: SQL*Net more data 13.30 PERCENT
from client
6 APPLY_SPOKE1 APPLY LATENCY 2.13 SECONDS
6 APPLY_SPOKE1 APPLY MESSAGE APPLY RATE 10004.00 MESSAGES PER SECOND
6 APPLY_SPOKE1 APPLY TRANSACTION APPLY RATE 100.00 TRANSACTIONS PER SEC
OND
8 CAPTURE_HNS CAPTURE BYTES SENT VIA SQL*NET TO .00 MEGABYTES
DBLINK
8 CAPTURE_HNS CAPTURE CAPTURE RATE 2595.29 MESSAGES PER SECOND
8 CAPTURE_HNS CAPTURE ENQUEUE RATE 2573.21 MESSAGES PER SECOND
8 CAPTURE_HNS CAPTURE EVENT: CPU + Wait for CPU 13.33 PERCENT
8 CAPTURE_HNS CAPTURE EVENT: CPU + Wait for CPU 21.33 PERCENT
8 CAPTURE_HNS CAPTURE EVENT: CPU + Wait for CPU 11.67 PERCENT
8 CAPTURE_HNS CAPTURE LATENCY 3.65 SECONDS
8 CAPTURE_HNS CAPTURE SEND RATE TO APPLY .00 BYTES PER SECOND
15 CAPTURE_HNS CAPTURE BYTES SENT VIA SQL*NET TO 173.21 MEGABYTES
DBLINK
15 CAPTURE_HNS CAPTURE CAPTURE RATE 10064.00 MESSAGES PER SECOND
15 CAPTURE_HNS CAPTURE ENQUEUE RATE 10002.00 MESSAGES PER SECOND
15 CAPTURE_HNS CAPTURE EVENT: CPU + Wait for CPU 15.00 PERCENT
15 CAPTURE_HNS CAPTURE EVENT: CPU + Wait for CPU 16.70 PERCENT
15 CAPTURE_HNS CAPTURE EVENT: CPU + Wait for CPU 18.30 PERCENT
15 CAPTURE_HNS CAPTURE LATENCY 2.65 SECONDS
15 CAPTURE_HNS CAPTURE SEND RATE TO APPLY 1414386.00 BYTES PER SECOND
20 APPLY_SPOKE2 APPLY EVENT: CPU + Wait for CPU 11.67 PERCENT
20 APPLY_SPOKE2 APPLY EVENT: CPU + Wait for CPU 13.33 PERCENT
20 APPLY_SPOKE2 APPLY EVENT: CPU + Wait for CPU 10.00 PERCENT
20 APPLY_SPOKE2 APPLY EVENT: buffer busy waits 3.33 PERCENT
20 APPLY_SPOKE2 APPLY EVENT: buffer busy waits 3.33 PERCENT
20 APPLY_SPOKE2 APPLY LATENCY 3.64 SECONDS
20 APPLY_SPOKE2 APPLY MESSAGE APPLY RATE 2580.05 MESSAGES PER SECOND
20 APPLY_SPOKE2 APPLY TRANSACTION APPLY RATE 25.68 TRANSACTIONS PER SEC
22 HUB.NET=>"ST PROPAGATION EVENT: CPU + Wait for CPU 13.33 PERCENT
RMADMIN"."DE RECEIVER
STINATION_SP
OKE2"
.
.
.
Note:
This output is for illustrative purposes only and does not necessarily reflect realistic performance or a required benchmark. Actual performance characteristics will vary depending on individual client 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, here is some of the information contained in the statistics for this example:
The Oracle Streams Performance Advisor did not record statistics for the following components:
The destination_spoke1 queue at hub.net (component ID 1)
The propagation receiver at hub.net that receives messages from spoke1.net (component ID 9)
The source_hns queue at spoke1.net (component ID 12)
The propagation sender at spoke1.net that sends messages to hub.net (component 13)
Each of these components is part of a stream path that uses combined capture and apply. Therefore, the Oracle Streams Performance Advisor does not record queue, propagation sender, and propagation receiver statistics for these components. See "Viewing Each Stream Path in an Oracle Streams Topology" for more information about the stream paths that use combined capture and apply in this example.
Performance is better for components that are part of a combined capture and apply stream path than it is for components that are part of a stream path that sends messages with a propagation. For example, compare the statistics for the apply_spoke1 apply process at hub.net (component ID 6) with the statistics for the apply_spoke2 apply process at spoke2.net (component ID 20).
The apply_spoke1 apply process at hub.net is part of a combined capture and apply stream path, and the capture process sends messages directly to the apply process.
The apply_spoke2 apply process at spoke2.net is part of a stream path that sends messages using a propagation. The components in this type of stream path must spend time enqueuing messages, propagating them, and dequeuing them.
The following statistics illustrate the performance differences between these apply processes:
The LATENCY statistic for the apply_spoke1 apply process at hub.net (component ID 6) is 2.13 seconds. The LATENCY statistic for the apply_spoke2 apply process at spoke2.net (component ID 20) is 3.64 seconds.
The MESSAGE APPLY RATE statistic for the apply_spoke1 apply process at hub.net is 10004.00 messages per second. The MESSAGE APPLY RATE statistic for the apply_spoke2 apply process at spoke2.net is 2580.05 messages per second.
The TRANSACTION APPLY RATE statistic for the apply_spoke1 apply process at hub.net is 100.00 transactions per second. The TRANSACTION APPLY RATE statistic for the apply_spoke2 apply process at spoke2.net is 25.68 transactions per second.
The BYTES SENT VIA SQL*NET TO DBLINK statistic for the capture_hns capture process at the hub.net database is .00. This capture process is component ID 8. The Oracle Streams Performance Advisor does not record this statistic for this capture process because multiple propagations send the messages captured by the capture process to different destinations. The Oracle Streams Performance Advisor records this statistic only if the stream path for the capture process uses combined capture and apply.
The SEND RATE TO APPLY statistic is populated for the capture process that uses combined capture and apply (component ID 15). This statistic is not populated for the capture process that does not use combined capture and apply (component ID 8).
See Also:
"Gathering Information About the Oracle Streams Topology and Performance" for information about running the ANALYZE_CURRENT_PERFORMANCE procedure to gather statistics
"Message Processing Options for an Apply Process" for information about apply handlers
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:
CAPTURE for capture processes
PROPAGATION SENDER for propagation senders
PROPAGATION RECEIVER for propagation receivers
APPLY for apply processes
The type of the subcomponent. Only capture processes and apply processes have subcomponents. The following 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
APPLY NETWORK RECEIVER for an apply process network receiver that receives LCRs directly from a capture 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 24-4 describes each of the statistics that can be returned by the query in this section:
Table 24-4 Session-Level Statistics for Oracle Streams Components
| Statistic | Unit | Description |
|---|---|---|
|
|
|
The percentage of time that the session spent idle. When a session is idle, it is not performing any work. |
|
|
|
The percentage of time that the session was paused for flow control. See "Capture Process States" for information about flow control. |
|
|
|
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 COMPONENT_ID HEADING 'ID' FORMAT 999
COLUMN COMPONENT_NAME HEADING 'Component|Name' FORMAT A15
COLUMN COMPONENT_TYPE HEADING 'Component|Type' FORMAT A10
COLUMN SUB_COMPONENT_TYPE HEADING 'Subcomponent|Type' FORMAT A22
COLUMN STATISTIC_NAME HEADING 'Statistic' FORMAT A25
COLUMN STATISTIC_VALUE HEADING 'Value' FORMAT 999.99
COLUMN STATISTIC_UNIT HEADING 'Unit' FORMAT A7
SELECT COMPONENT_ID,
COMPONENT_NAME,
COMPONENT_TYPE,
SUB_COMPONENT_TYPE,
STATISTIC_NAME,
STATISTIC_VALUE,
STATISTIC_UNIT
FROM DBA_STREAMS_TP_COMPONENT_STAT
WHERE ADVISOR_RUN_ID=2 AND SESSION_ID IS NOT NULL AND SESSION_SERIAL# IS NOT NULL
ORDER BY 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 2 and stream path 3:
Component Component Subcomponent ID Name Type Type Statistic Value Unit --- --------------- ---------- ---------------------- ------------------------- ------- ------- 5 "STRMADMIN"."SO PROPAGATIO EVENT: CPU + Wait for CPU 45.00 PERCENT URCE_HNS"=>SPOK N SENDER E2.NET 5 "STRMADMIN"."SO PROPAGATIO FLOW CONTROL 55.00 PERCENT URCE_HNS"=>SPOK N SENDER E2.NET 5 "STRMADMIN"."SO PROPAGATIO IDLE .00 PERCENT URCE_HNS"=>SPOK N SENDER E2.NET 6 APPLY_SPOKE1 APPLY APPLY COORDINATOR FLOW CONTROL .00 PERCENT 6 APPLY_SPOKE1 APPLY APPLY COORDINATOR IDLE 90.00 PERCENT 6 APPLY_SPOKE1 APPLY APPLY NETWORK RECEIVER EVENT: CPU + Wait for CPU 3.33 PERCENT 6 APPLY_SPOKE1 APPLY APPLY NETWORK RECEIVER FLOW CONTROL 83.33 PERCENT 6 APPLY_SPOKE1 APPLY APPLY NETWORK RECEIVER IDLE 0.00 PERCENT 6 APPLY_SPOKE1 APPLY APPLY READER EVENT: CPU + Wait for CPU 11.67 PERCENT 6 APPLY_SPOKE1 APPLY APPLY READER FLOW CONTROL .00 PERCENT 6 APPLY_SPOKE1 APPLY APPLY READER IDLE 88.33 PERCENT 6 APPLY_SPOKE1 APPLY APPLY SERVER EVENT: CPU + Wait for CPU 65.00 PERCENT 6 APPLY_SPOKE1 APPLY APPLY SERVER EVENT: CPU + Wait for CPU 75.00 PERCENT 6 APPLY_SPOKE1 APPLY APPLY SERVER FLOW CONTROL .00 PERCENT 6 APPLY_SPOKE1 APPLY APPLY SERVER FLOW CONTROL .00 PERCENT 6 APPLY_SPOKE1 APPLY APPLY SERVER IDLE 3.33 PERCENT 6 APPLY_SPOKE1 APPLY APPLY SERVER IDLE 8.33 PERCENT 8 CAPTURE_HNS CAPTURE CAPTURE SESSION EVENT: CPU + Wait for CPU 11.67 PERCENT 8 CAPTURE_HNS CAPTURE CAPTURE SESSION FLOW CONTROL 83.33 PERCENT 8 CAPTURE_HNS CAPTURE CAPTURE SESSION IDLE 5.00 PERCENT 8 CAPTURE_HNS CAPTURE LOGMINER BUILDER EVENT: CPU + Wait for CPU 1.67 PERCENT 8 CAPTURE_HNS CAPTURE LOGMINER BUILDER FLOW CONTROL .00 PERCENT 8 CAPTURE_HNS CAPTURE LOGMINER BUILDER IDLE 98.33 PERCENT 8 CAPTURE_HNS CAPTURE LOGMINER PREPARER EVENT: CPU + Wait for CPU 21.33 PERCENT 8 CAPTURE_HNS CAPTURE LOGMINER PREPARER FLOW CONTROL .00 PERCENT 8 CAPTURE_HNS CAPTURE LOGMINER PREPARER IDLE 78.67 PERCENT 8 CAPTURE_HNS CAPTURE LOGMINER READER EVENT: CPU + Wait for CPU 13.33 PERCENT 8 CAPTURE_HNS CAPTURE LOGMINER READER FLOW CONTROL .00 PERCENT 8 CAPTURE_HNS CAPTURE LOGMINER READER IDLE 86.67 PERCENT 15 CAPTURE_HNS CAPTURE CAPTURE SESSION EVENT: CPU + Wait for CPU 18.33 PERCENT 15 CAPTURE_HNS CAPTURE CAPTURE SESSION FLOW CONTROL 71.67 PERCENT 15 CAPTURE_HNS CAPTURE CAPTURE SESSION IDLE 10.00 PERCENT 15 CAPTURE_HNS CAPTURE LOGMINER BUILDER EVENT: CPU + Wait for CPU 15.00 PERCENT 15 CAPTURE_HNS CAPTURE LOGMINER BUILDER FLOW CONTROL .00 PERCENT 15 CAPTURE_HNS CAPTURE LOGMINER BUILDER IDLE 85.00 PERCENT 15 CAPTURE_HNS CAPTURE LOGMINER PREPARER EVENT: CPU + Wait for CPU 16.67 PERCENT 15 CAPTURE_HNS CAPTURE LOGMINER PREPARER FLOW CONTROL .00 PERCENT 15 CAPTURE_HNS CAPTURE LOGMINER PREPARER IDLE 83.33 PERCENT 15 CAPTURE_HNS CAPTURE LOGMINER READER EVENT: CPU + Wait for CPU 1.67 PERCENT 15 CAPTURE_HNS CAPTURE LOGMINER READER FLOW CONTROL .00 PERCENT 15 CAPTURE_HNS CAPTURE LOGMINER READER IDLE 95.00 PERCENT 20 APPLY_SPOKE2 APPLY APPLY COORDINATOR FLOW CONTROL .00 PERCENT 20 APPLY_SPOKE2 APPLY APPLY COORDINATOR IDLE 100.00 PERCENT 20 APPLY_SPOKE2 APPLY APPLY READER EVENT: CPU + Wait for CPU 13.33 PERCENT 20 APPLY_SPOKE2 APPLY APPLY READER FLOW CONTROL .00 PERCENT 20 APPLY_SPOKE2 APPLY APPLY READER IDLE 86.67 PERCENT 20 APPLY_SPOKE2 APPLY APPLY SERVER EVENT: CPU + Wait for CPU 11.67 PERCENT 20 APPLY_SPOKE2 APPLY APPLY SERVER EVENT: CPU + Wait for CPU 10.00 PERCENT 20 APPLY_SPOKE2 APPLY APPLY SERVER FLOW CONTROL .00 PERCENT 20 APPLY_SPOKE2 APPLY APPLY SERVER FLOW CONTROL .00 PERCENT 20 APPLY_SPOKE2 APPLY APPLY SERVER IDLE 83.33 PERCENT 20 APPLY_SPOKE2 APPLY APPLY SERVER IDLE 83.33 PERCENT 22 HUB.NET=>"STRMA PROPAGATIO EVENT: CPU + Wait for CPU 13.33 PERCENT DMIN"."DESTINAT N RECEIVER ION_SPOKE2" 22 HUB.NET=>"STRMA PROPAGATIO FLOW CONTROL 81.67 PERCENT DMIN"."DESTINAT N RECEIVER ION_SPOKE2" 22 HUB.NET=>"STRMA PROPAGATIO IDLE 5.00 PERCENT DMIN"."DESTINAT N RECEIVER ION_SPOKE2" . . .
Note:
This output is for illustrative purposes only and does not necessarily reflect realistic performance or a required benchmark. Actual performance characteristics will vary depending on individual client 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:
"Capture Process Components" for more information about capture process subcomponents
"Apply Process Components" for more information about apply process subcomponents
The query in this section shows the following information for each stream path in the Oracle Streams topology:
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 MESSAGE RATE 2623.20 MESSAGES PER SECOND
1 TRANSACTION RATE 26.10 TRANSACTIONS PER SECOND
2 MESSAGE RATE 2580.05 MESSAGES PER SECOND
2 TRANSACTION RATE 25.68 TRANSACTIONS PER SECOND
3 MESSAGE RATE 10004.00 MESSAGES PER SECOND
3 TRANSACTION RATE 100.00 TRANSACTIONS PER SECOND
5 MESSAGE RATE 10028.25 MESSAGES PER SECOND
5 TRANSACTION RATE 100.37 TRANSACTIONS PER SECOND
Note:
This output is for illustrative purposes only and does not necessarily reflect realistic performance or a required benchmark. Actual performance characteristics will vary depending on individual client configurations and conditions.Stream paths 1 and 2 use propagations to send messages, while stream paths 3 and 5 use combined capture and apply. Notice that performance is better for the stream paths that use combined capture and apply.
Also, notice that no statistics were recorded for stream paths 4 and 6. These two paths identify the components used in the combined capture and apply stream paths. Specifically, stream path 4 identifies the Oracle Streams components in stream path 3, and stream path 6 identifies the Oracle Streams components in stream path 5.
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:
The COLLECT_STATS procedure uses the Oracle Streams Performance Advisor to gather statistics about the Oracle Streams components and subcomponents in a distributed database environment.
The SHOW_STATS procedure generates output that includes the statistics gathered by the COLLECT_STATS procedure. The output is formatted so that it can be imported into a spreadsheet easily and analyze it.
These procedures collect the same statistics as the Oracle Streams Performance Advisor. These statistics are described in Table 24-3, "Component-Level Statistics for Oracle Streams Components" and Table 24-4, "Session-Level Statistics for Oracle Streams Components".
To collect and show statistics using the UTL_SPADV package, complete the following steps:
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 the SELECT_CATALOG_ROLE system privilege at the local database, and each database link must connect to a user at the remote database that has the SELECT_CATALOG_ROLE system privilege. If you configure an Oracle Streams administrator at each database with Oracle Streams components, then the Oracle Streams administrator has the necessary privilege. 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.
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, if the Oracle Streams administrator strmadmin at the hub.net database meets the requirements, then connect to the database as follows:
CONNECT strmadmin/user-password@hub.net
Run the COLLECT_STATS procedure:
exec UTL_SPADV.COLLECT_STATS
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> <bytes to apply/sec>
'LMR' <idl%> <flwctrl%> <topevt%> <topevt>
'LMP' (<parallelism>) <idl%> <flwctrl%> <topevt%> <topevt>
'LMB' <idl%> <flwctrl%> <topevt%> <topevt>
'CAP' <idl%> <flwctrl%> <topevt%> <topevt>
<apply> = '|<A>' <name> <msgs applied/sec> <txns applied/sec> <latency>
'ANR' <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> <sid> <serial#> <topevt%> <topevt>
OUTPUT
PATH 3 RUN_ID 1 RUN_TIME 2007-JUN-13 12:02:12
|<C> CAPTURE_HNS 10023 10040 3 1.46E+06 LMR 95% 0% 3.3% "" LMP (1) 86.7% 0% 11.7% "" LMB 86.7% 0% 11.7% ""
CAP 16.7% 71.7% 11.7% "" |<A> APPLY_SPOKE1 10042 100 4 ANR 0% 80% 15% "" APR 93.3% 0% 6.7% "" APC 96.7%
0% 3.3% "" APS (2) 8.3% 0% 76.7% "CPU + Wait for CPU" |<B> APPLY_SPOKE1 APS 953 272 76.7% "CPU + Wait for CPU"
PATH 3 RUN_ID 2 RUN_TIME 2007-JUN-13 12:03:12
|<C> CAPTURE_HNS 10064 10002 3 1.41E+06 LMR 95% 0% 1.7% "" LMP (1) 83.3% 0% 16.7% "" LMB 85% 0% 15% "" CAP
10% 81.7% 8.3% "" |<A> APPLY_SPOKE1 10004 100 3 ANR 0% 83.3% 13.3% "" APR 88.3% 0% 11.7% "" APC 90% 0%
10% "" APS (2) 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 and does not necessarily reflect realistic performance or a required benchmark. Actual performance characteristics will vary depending on individual client configurations and conditions.The following table describes the abbreviations used in the legend:
| Abbreviation | Description |
|---|---|
A |
Apply process |
ANR |
Apply network receiver used by an apply process in a combined capture and apply configuration |
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 |
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 |
Q |
Queue |
serial# |
Session serial number |
sec |
Second |
sid |
Session identifier |
sub_name |
Subcomponent name |
topevt |
Top event |
See Also:
Oracle Database PL/SQL Packages and Types Reference for more information about theUTL_SPADV package