Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
11g Release 1 (11.1)

B28419-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

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

See Also:

Oracle Streams Concepts and Administration for more information about this package and the Oracle Streams Performance Advisor

This chapter contains the following topic:


Summary of UTL_SPADV Subprograms

Table 217-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 217-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_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);

Parameters

Table 217-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_cca_mode

If TRUE, then path output includes information pertaining to the combined capture and apply optimization.

If FALSE, then path output does not include information pertaining to the combined capture and apply optimization.

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.

See Also:

Oracle Streams Concepts and Administration for information about the combined capture and apply optimization