1. Database PL/SQL Packages and Types Reference
  2. DBMS_STREAMS_ADVISOR_ADM

163 DBMS_STREAMS_ADVISOR_ADM

The DBMS_STREAMS_ADVISOR_ADM package, one of a set of Oracle Streams packages, provides an interface to gather information about an Oracle Streams environment and advise database administrators based on the information gathered.

This package is part of the Oracle Streams Performance Advisor.

This chapter contains the following topics:

See Also:

Oracle Streams Concepts and Administration for instructions about using this package

163.1 DBMS_STREAMS_ADVISOR_ADM 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

See Also:

Oracle Streams Concepts and Administration for instructions about using this package

163.2 DBMS_STREAMS_ADVISOR_ADM Security Model

Security on this package can be controlled by either granting EXECUTE on this package to selected users or roles, or 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.

See Also:

Oracle Streams Replication Administrator's Guide for information about configuring an Oracle Streams administrator

163.3 DBMS_STREAMS_ADVISOR_ADM 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.

Table 163-1 DBMS_STREAMS_ADVISOR_ADM Parameters With Enumerated Constants

Parameter Option Type Description

component_type

  • CAPTURE_TYPE

  • PROPAGATION_SENDER_TYPE

  • PROPAGATION_RECEIVER_TYPE

  • APPLY_TYPE

  • QUEUE_TYPE

NUMBER

CAPTURE_TYPE indicates that the Oracle Streams component is a capture process. The constant number for this option is 1.

PROPAGATION_SENDER_TYPE indicates that the Oracle Streams component is a propagation sender. The constant number for this option is 2.

PROPAGATION_RECEIVER_TYPE indicates that the Oracle Streams component is a propagation receiver. The constant number for this option is 3.

APPLY_TYPE indicates that the Oracle Streams component is an apply process. The constant number for this option is 4.

QUEUE_TYPE indicates that the Oracle Streams component is a queue. The constant number for this option is 5.

163.4 DBMS_STREAMS_ADVISOR_ADM Views

The views in the package contain information about Oracle Streams components and performance.

DBMS_STREAMS_ADVISOR_ADM uses the following 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.

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.

See Also:

163.5 DBMS_STREAMS_ADVISOR_ADM Operational Notes

This section contains operational notes for the DBMS_STREAMS_ADVISOR_ADM package.

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 previously, 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 these steps.

Note:

When you exit the user session, the rate, bandwidth, event, and flow control statistics are purged from the data dictionary views.

163.6 Summary of DBMS_STREAMS_ADVISOR_ADM Subprograms

This table lists the DBMS_STREAMS_ADVISOR_ADM subprograms and briefly describes them.

Table 163-2 DBMS_STREAMS_ADVISOR_ADM Package Subprograms

Subprogram Description

ANALYZE_CURRENT_PERFORMANCE 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

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

Note:

The parameters in this procedure must all be either non-NULL or NULL.

See Also:

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

Table 163-3 ANALYZE_CURRENT_PERFORMANCE Procedure Parameters

Parameter Description

component_name

The name of the Oracle Streams component to analyze. For example, to analyze a capture process named capture01, then specify capture01.

If NULL, then all of the Oracle Streams components are analyzed, and the other two parameters must also be NULL.

component_db

The global name of the database that contains the component specified in the component_name parameter. For example, if the db.net database contains the component, then specify db.net.

If NULL, then all of the Oracle Streams components are analyzed, and the other two parameters must also be NULL.

component_type

The type of the component specified in the component_name parameter. If the component_name parameter is non-NULL, then specify one of the following:

  • DBMS_STREAMS_ADVISOR_ADM.CAPTURE_TYPE

  • DBMS_STREAMS_ADVISOR_ADM.PROPAGATION_SENDER_TYPE

  • DBMS_STREAMS_ADVISOR_ADM.PROPAGATION_RECEIVER_TYPE

  • DBMS_STREAMS_ADVISOR_ADM.APPLY_TYPE

  • DBMS_STREAMS_ADVISOR_ADM.QUEUE_TYPE

See "Constants" for information about these constants.

If NULL, then all of the Oracle Streams components are analyzed, and the other two parameters must also be NULL.