145 DBMS_STREAMS_ADVISOR_ADM

Using DBMS_STREAMS_ADVISOR_ADM

This section contains topics which relate to using the DBMS_STREAMS_ADVISOR_ADM package.

• Overview

• Security Model

• Constants

• Views

Overview

The DBMS_STREAMS_ADVISOR_ADM package enables you to gather and analyze information about an Oracle Streams environment. You can use this information in the following ways:

• To populate data dictionary views with an Oracle Streams topology that contains information about the Oracle Streams components at one or more databases

• To examine the Oracle Streams components at one or more databases in your environment and the ways in which information flows through streams that include these components

• To analyze the performance of the Oracle Streams components in your environment

• To detect performance problems with Oracle Streams components and correct these problems

Security Model

Security on this package can be controlled in either of the following ways:

• Granting EXECUTE on this package to selected users or roles.

• Granting EXECUTE_CATALOG_ROLE to selected users or roles.

If subprograms in the package are run from within a stored procedure, then the user who runs the subprograms must be granted EXECUTE privilege on the package directly. It cannot be granted through a role.

To ensure that the user who runs the subprograms in this package has the necessary privileges, configure an Oracle Streams administrator and connect as the Oracle Streams administrator when using this package.

Constants

The DBMS_STREAMS_ADVISOR_ADM package defines several enumerated constants for specifying parameter values. Enumerated constants must be prefixed with the package name. For example, DBMS_DBMS_ADVISOR_ADM.CAPTURE_TYPE.

Views

The DBMS_STREAMS_ADVISOR_ADM package uses the following 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 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 a stream.

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

However, 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.

Operational Notes

This section contains the following operational notes for the DBMS_STREAMS_ADVISOR_ADM package:

• Oracle Streams Components Analyzed by the DBMS_STREAMS_ADVISOR_ADM Package

• General Steps for Running the Oracle Streams Performance Advisor and Analyzing the Information

Oracle Streams Components Analyzed by the DBMS_STREAMS_ADVISOR_ADM Package

The DBMS_STREAMS_ADVISOR_ADM analyzes the following Oracle Streams components at the specified databases:

• Capture processes

• Propagations

• Apply processes

• Queues

The DBMS_STREAMS_ADVISOR_ADM package does not analyze the following Oracle Streams components:

• Synchronous captures

• Messaging clients

General Steps for Running the Oracle Streams Performance Advisor and Analyzing the Information

To use the DBMS_STREAMS_ADVISOR_ADM package, complete the following general 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.

   In an Oracle Streams environment, the Oracle Streams administrator uses this package.

   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. Connect as the administrative user to the database you identified in Step 1, and remain connected to the session while you complete the remaining steps.

3. Run the ANALYZE_CURRENT_PERFORMACE procedure.

4. Optionally, allow messages to flow in the environment for some time.

5. Optionally, rerun the ANALYZE_CURRENT_PERFORMACE procedure one or more times.

6. Query the data dictionary views listed in "Views" to analyze the Oracle Streams environment.

7. If you want to update the information in the data dictionary views or if you add new Oracle Streams components to any database in the environment, repeat Steps 2-6.

Summary of DBMS_STREAMS_ADVISOR_ADM Subprograms

ANALYZE_CURRENT_PERFORMANCE Procedure

This procedure gathers information about the Oracle Streams components at one or more databases in your environment and analyzes Oracle Streams performance based on the information gathered.

The performance analyses includes:

• Calculating bottleneck components for each separate stream

• Calculating the throughput of each Oracle Streams component

• Calculating the latency of each Oracle Streams component

• Calculating the top wait event of each Oracle Streams component

• Calculating the message rate of each stream

• Calculating the transaction rate of each stream

The procedure places the gathered information in data dictionary views.

Syntax

DBMS_STREAMS_ADVISOR_ADM.ANALYZE_CURRENT_PERFORMANCE(
   component_name  IN  VARCHAR2  DEFAULT NULL,
   component_db    IN  VARCHAR2  DEFAULT NULL,
   component_type  IN  NUMBER    DEFAULT NULL);

Parameters