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
This section contains topics which relate to using 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
DBMS_STREAMS_ADVISOR_ADM package defines several enumerated constants that should be used for specifying parameter values. Enumerated constants must be prefixed with the package name. For example,
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:
However, the following views contain temporary information:
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.
This section contains the following operational notes for the
DBMS_STREAMS_ADVISOR_ADM analyzes the following Oracle Streams components at the specified databases:
DBMS_STREAMS_ADVISOR_ADM package does not analyze the following Oracle Streams components:
To use the
DBMS_STREAMS_ADVISOR_ADM package, complete the following general steps:
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
Typically, 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.
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.
Optionally, allow messages to flow in the environment for some time.
Query the data dictionary views listed in "Views" to analyze the Oracle Streams environment.
Note:When you exit the user session, the rate, bandwidth, event, and flow control statistics are purged from the data dictionary views.
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
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-
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);
The name of the Oracle Streams component to analyze. For example, to analyze a capture process named
The global name of the database that contains the component specified in the
The type of the component specified in the
See "Constants" for information about these constants.