218 UTL_SPADV

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.

Summary of UTL_SPADV Subprograms

Table 218-1 DBMS_STREAMS Package Subprograms

Subprogram	Description
COLLECT_STATS Procedure	Uses the Oracle Streams Performance Advisor to gather statistics about the Oracle Streams components and subcomponents in a distributed database environment.
SHOW_STATS Procedure	Generates output that includes the statistics gathered by the `COLLECT_STATS` procedure.

COLLECT_STATS Procedure

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 Advisor

Syntax

UTL_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);

Parameters

Table 218-2 COLLECT_STATS Procedure Parameters

Parameter	Description
`interval`	The time period, in seconds, between two consecutive Oracle Streams Performance Advisor runs.
`num_runs`	The number of times that the Oracle Streams Performance Advisor is run by the procedure.
`comp_stat_table`	The name of the table that stores the statistics collected for Oracle Streams components and subcomponents. Specify the table name as `[schema_name.]object_name`. If the schema is not specified, then the current user is the default. The procedure creates the specified table if it does not exist. Oracle recommends that you use the default table `STREAMS$_ADVISOR_COMP_STAT`. See "Usage Notes" for more information about this parameter.
`path_stat_table`	The name of the table that stores the statistics collected for stream paths. Specify the table name as `[schema_name.]object_name`. If the schema is not specified, then the current user is the default. The procedure creates the specified table if it does not exist. Oracle recommends that you use the default table `STREAMS$_ADVISOR_PATH_STAT`. See "Usage Notes" for more information about this parameter.
`top_event_threshold`	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 `15` is specified, then only wait events with a value larger than 15% are collected.
`bottleneck_idle_threshold`	A percentage that determines whether or not an Oracle Streams component session is eligible for bottleneck analysis based on its `IDLE` percentage. The `IDLE` percentage must be less than or equal to the value specified in this parameter for the Oracle Streams component session to be eligible for bottleneck analysis. For example, if `50` is specified, then only components that are idle 50% of the time or less are eligible for bottleneck analysis.
`bottleneck_flowctrl_threshold`	A percentage that determines whether or not an Oracle Streams component session is eligible for bottleneck analysis based on its `FLOW` `CONTROL` percentage. The `FLOW` `CONTROL` percentage must be less than or equal to the value specified in this parameter for the Oracle Streams component session to be eligible for bottleneck analysis. For example, if `50` is specified, then only components that are paused for flow control 50% of the time or less are eligible for bottleneck analysis.

Usage Notes

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.

SHOW_STATS Procedure

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 Advisor

Syntax

UTL_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_setting    IN BOOLEAN   DEFAULT FALSE,
   show_stat       IN BOOLEAN   DEFAULT TRUE,
   show_sess       IN BOOLEAN   DEFAULT FALSE,
   show_legend     IN BOOLEAN   DEFAULT TRUE);

Parameters

Table 218-3 SHOW_STATS Procedure Parameters

Parameter	Description
`path_stat_table`	The name of the table that contains the stream path statistics. This table is specified in the `path_stat_table` parameter in the `COLLECT_STATS` procedure. Specify the table name as `[schema_name.]object_name`. If the schema is not specified, then the current user is the default.
`path_id`	A stream path ID. If non-`NULL`, then the procedure shows output for the specified stream path only. If `NULL`, then the procedure shows output for all active stream paths.
`bgn_run_id`	The first Oracle Streams Performance Advisor run ID to show in the range of runs. See "Usage Notes" for more information about this parameter.
`end_run_id`	The last Oracle Streams Performance Advisor run ID to show in the range of runs. See "Usage Notes" for more information about this parameter.
`show_path_id`	If `TRUE`, then the path ID for each stream path is included in the output. If `FALSE`, then the path ID for each stream path is not included in the output.
`show_run_id`	If `TRUE`, then the Oracle Streams Performance Advisor run ID is included in the output. If `FALSE`, then the Oracle Streams Performance Advisor run ID is not included in the output.
`show_run_time`	If `TRUE`, then the Oracle Streams Performance Advisor run time is included in the output. If `FALSE`, then the Oracle Streams Performance Advisor run time is not included in the output.
`show_setting`	If `TRUE`, then the settings for the threshold parameters are included in the output. The threshold parameters are the `top_event_threshold`, `bottleneck_idle_threshold`, and `bottleneck_flowctrl_threshold` parameters in the `COLLECT_STATS` procedure. If `FALSE`, then the settings for the threshold parameters are not included in the output.
`show_stat`	If `TRUE`, then the component-level and subcomponent-level statistics are included in the output. These components include capture processes, queues, propagation senders, propagation receivers, and apply processes. The subcomponents are the subcomponents for capture processes and apply processes. If `FALSE`, then the component-level and subcomponent-level statistics are not included in the output.
`show_sess`	If `TRUE`, then the session-level statistics are included in the output. Session-level statistics include `IDLE`, `FLOW` `CONTROL`, and `EVENT` statistics. If `FALSE`, then the session-level statistics are not included in the output.
`show_legend`	If `TRUE`, then the legend is included in the output. The legend describes the abbreviations used in the output. If `FALSE`, then the legend is not included in the output.

Usage Notes

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.