The UTL_SPADV
package, one of a set of Oracle Streams packages, provides subprograms to collect and analyze statistics for the Oracle Streams components in a distributed database environment. This package uses the Oracle Streams Performance Advisor to gather statistics.
See Also:
Oracle Streams Concepts and Administration for more information about this package and the Oracle Streams Performance AdvisorThis chapter contains the following topic:
Table 217-1 DBMS_STREAMS Package Subprograms
Subprogram | Description |
---|---|
Uses the Oracle Streams Performance Advisor to gather statistics about the Oracle Streams components and subcomponents in a distributed database environment. |
|
Generates output that includes the statistics gathered by the |
This procedure uses the Oracle Streams Performance Advisor to gather statistics about the Oracle Streams components and subcomponents in a distributed database environment.
Note:
This procedure commits.See Also:
Oracle Streams Concepts and Administration for more information about the Oracle Streams Performance AdvisorUTL_SPADV.COLLECT_STATS( interval IN NUMBER DEFAULT 60, num_runs IN NUMBER DEFAULT 10, comp_stat_table IN VARCHAR2 DEFAULT 'STREAMS$_ADVISOR_COMP_STAT', path_stat_table IN VARCHAR2 DEFAULT 'STREAMS$_ADVISOR_PATH_STAT', top_event_threshold IN NUMBER DEFAULT 15, bottleneck_idle_threshold IN NUMBER DEFAULT 50, bottleneck_flowctrl_threshold IN NUMBER DEFAULT 50);
Table 217-2 COLLECT_STATS Procedure Parameters
Parameter | Description |
---|---|
|
The time period, in seconds, between two consecutive Oracle Streams Performance Advisor runs. |
|
The number of times that the Oracle Streams Performance Advisor is run by the procedure. |
|
The name of the table that stores the statistics collected for Oracle Streams components and subcomponents. Specify the table name as The procedure creates the specified table if it does not exist. Oracle recommends that you use the default table See "Usage Notes" for more information about this parameter. |
|
The name of the table that stores the statistics collected for stream paths. Specify the table name as The procedure creates the specified table if it does not exist. Oracle recommends that you use the default table See "Usage Notes" for more information about this parameter. |
|
A percentage that determines whether or not a top wait event statistic is collected. The percentage for a wait event must be greater than the value specified in this parameter for the procedure to collect the wait event statistic. For example, if |
|
A percentage that determines whether or not an Oracle Streams component session is eligible for bottleneck analysis based on its The |
|
A percentage that determines whether or not an Oracle Streams component session is eligible for bottleneck analysis based on its The |
The table specified in the path_stat_table
parameter stores stream path statistics. This table also concatenates the component and subcomponent statistics stored in the table specified in the comp_stat_table
parameter. The SHOW_STATS
procedure in this package shows only the statistics stored in the table specified in the path_stat_table
parameter.
This 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 for analysis.
Note:
This procedure does not commit.See Also:
Oracle Streams Concepts and Administration for more information about the Oracle Streams Performance AdvisorUTL_SPADV.SHOW_STATS( path_stat_table IN VARCHAR2 DEFAULT 'STREAMS$_ADVISOR_PATH_STAT', path_id IN NUMBER DEFAULT NULL, bgn_run_id IN NUMBER DEFAULT -1, end_run_id IN NUMBER DEFAULT -10, show_path_id IN BOOLEAN DEFAULT TRUE, show_run_id IN BOOLEAN DEFAULT TRUE, show_run_time IN BOOLEAN DEFAULT TRUE, show_cca_mode IN BOOLEAN DEFAULT TRUE, show_setting IN BOOLEAN DEFAULT FALSE, show_stat IN BOOLEAN DEFAULT TRUE, show_sess IN BOOLEAN DEFAULT FALSE, show_legend IN BOOLEAN DEFAULT TRUE);
Table 217-3 SHOW_STATS Procedure Parameters
Parameter | Description |
---|---|
|
The name of the table that contains the stream path statistics. This table is specified in the |
|
A stream path ID. If non- If |
|
The first Oracle Streams Performance Advisor run ID to show in the range of runs. See "Usage Notes" for more information about this parameter. |
|
The last Oracle Streams Performance Advisor run ID to show in the range of runs. See "Usage Notes" for more information about this parameter. |
|
If If |
|
If If |
|
If If |
|
If If |
|
If If |
|
If If |
|
If If |
|
If If |
Use the bgn_run_id
and end_run_id
together to specify the range of Oracle Streams Performance Advisor runs that you want to display. Positive numbers show statistics from the an earlier run forward. Negative numbers show statistics from the a a later run backward.
For example, if bgn_run_id
is set to 1
and end_run_id
is set to 10
, then the procedure shows statistics for the first ten Oracle Streams Performance Advisor runs.
However, if bgn_run_id
is set to -1
and end_run_id
is set to -10
, then the procedure shows statistics for the last ten Oracle Streams Performance Advisor runs.
See Also:
Oracle Streams Concepts and Administration for information about the combined capture and apply optimization