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

This chapter contains the following topic:

See Also:

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

Using UTL_SPADV

Overview

This package enables you to collect and analyze statistics about the performance or Oracle Streams components. You can either collect statistics on demand or you can create a monitoring job that continually monitors Oracle Streams performance.

When this package is used on an Oracle Database 11g Release 2 (11.2) database, it can monitor Oracle Database 10g Release 2 (10.2) and later databases. It cannot monitor databases before release 10.2.

Security Model

Security on this package can be controlled in either of the following ways:

  • Granting EXECUTE on this package to selected users or roles.

  • 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

Operational Notes

To use this package, you must connect to an Oracle database as an Oracle Streams administrator and run the utlspadv.sql script in the rdbms/admin directory in ORACLE_HOME.

The utlspadv.sql script creates the following tables:

The Oracle Streams Performance Advisor populates these tables when it is run.

STREAMS$_PA_COMPONENT Table

The STREAMS$_PA_COMPONENT table displays information about the Oracle Streams components at each database.

Table 264-1 STREAMS$_PA_COMPONENT Table

Column Datatype NULL Description

COMPONENT_ID

NUMBER

NOT NULL

Identification number assigned to the component by the Oracle Streams Performance Advisor

COMPONENT_NAME

VARCHAR2(194)

 

Name of the component

COMPONENT_DB

VARCHAR2(128)

 

Name of the database that contains the component

COMPONENT_TYPE

VARCHAR2(20)

 

Type of the component

The following types are possible:

  • CAPTURE for a capture process

  • PROPAGATION SENDER for a propagation sender

  • PROPAGATION RECEIVER for a propagation receiver

  • APPLY for an apply process

  • QUEUE for a queue

COMPONENT_CHANGED_TIME

DATE

 

Time when the component was last changed


STREAMS$_PA_COMPONENT_LINK Table

The STREAMS$_PA_COMPONENT_LINK table displays information about how information flows between Oracle Streams components.

Table 264-2 STREAMS$_PA_COMPONENT_LINK Table

Column Datatype NULL Description

PATH_ID

NUMBER

NOT NULL

Identification number assigned to the path by the Oracle Streams Performance Advisor

PATH_KEY

VARCHAR2(4000)

 

Unique key assigned to the path by the Oracle Streams Performance Advisor

SOURCE_COMPONENT_ID

NUMBER

NOT NULL

Source component ID for the path

The path starts with this component.

DESTINATION_COMPONENT_ID

NUMBER

NOT NULL

Destination component ID for the path

The path ends with this component.

POSITION

NUMBER

 

Position of the component in the path


STREAMS$_PA_COMPONENT_PROP Table

The STREAMS$_PA_COMPONENT_PROP table displays information about capture processes and apply processes necessary for analysis by the Streams Performance Advisor.

Table 264-3 STREAMS$_PA_COMPONENT_PROP Table

Column Datatype NULL Description

COMPONENT_ID

NUMBER

NOT NULL

Identification number assigned to the component by the Oracle Streams Performance Advisor

PROP_NAME

VARCHAR2(30)

 

Property name

For a capture process, the component properties include the following:

  • SOURCE_DATABASE - The source database for the changes captured by the capture process

  • PARALLELISM - The setting for the parallelism capture process parameter

  • OPTIMIZATION_MODE - Indicates whether the capture process uses combined capture and apply (greater than zero) or does not use combined capture and apply (0)

For an apply process, the component properties include the following:

  • SOURCE_DATABASE - The source database for the messages applied by the apply process

  • PARALLELISM - The setting for the parallelism apply process parameter

  • APPLY_CAPTURED - Indicates whether the apply process applies captured messages (YES) persistent messages (NO)

  • MESSAGE_DELIVERY_MODE - Either buffered or persistent

PROP_VALUE

VARCHAR2(30)

 

Property value


STREAMS$_PA_COMPONENT_STAT Table

The STREAMS$_PA_COMPONENT_STAT table displays performance statistics and session statistics about each Oracle Streams component.

Table 264-4 STREAMS$_PA_COMPONENT_STAT Table

Column Datatype NULL Description

ADVISOR_RUN_ID

NUMBER

 

Identification number of the Oracle Streams Performance Advisor run

ADVISOR_RUN_TIME

DATE

 

Time when the Oracle Streams Performance Advisor was run for the advisor run ID

COMPONENT_ID

NUMBER

 

Identification number assigned to the component by the Oracle Streams Performance Advisor

STATISTIC_TIME

DATE

 

Time when the statistic was recorded

STATISTIC_NAME

VARCHAR2(64)

 

Name of the statistic

STATISTIC_VALUE

NUMBER

 

Value recorded for the statistic

STATISTIC_UNIT

VARCHAR2(64)

 

Unit of measurement for the statistic

SUB_COMPONENT_TYPE

VARCHAR2(64)

 

Type of the subcomponent

Only capture processes and apply processes have subcomponents.

The following capture process subcomponent types are possible:

  • LOGMINER READER for a builder server of a capture process

  • LOGMINER PREPARER for a preparer server of a capture process

  • LOGMINER BUILDER for a reader server of a capture process

  • CAPTURE SESSION for a capture process session

The following apply process subcomponent types are possible:

  • PROPAGATION SENDER+RECEIVER for sending LCRs from a capture process directly to an apply process in a combined capture and apply configuration in which both the capture process and apply process run on a single database

  • APPLY READER for a reader server of an apply process

  • APPLY COORDINATOR for a coordinator process of an apply process

  • APPLY SERVER for a reader server of an apply process

SESSION_ID

NUMBER

 

Identification number of the session for the component. Query the V$SESSION view for information about the session.

SESSION_SERIAL#

NUMBER

 

Session serial number of the session for the component. Query the V$SESSION view for information about the session.


STREAMS$_PA_CONTROL Table

The STREAMS$_PA_CONTROL table displays the parameters set for the COLLECT_STATS procedure in this package. The parameters control the monitoring behavior.

Table 264-5 STREAMS$_PA_CONTROL Table

Column Datatype NULL Description

ADVISOR_RUN_ID

NUMBER

 

Identification number of the Oracle Streams Performance Advisor run

ADVISOR_RUN_TIME

DATE

 

Time when the Oracle Streams Performance Advisor was last run

PARAM_NAME

VARCHAR2(30)

 

The name of the parameter

PARAM_VALUE

VARCHAR2(4000)

 

The value set for the parameter

PARAM_UNIT

VARCHAR2(30)

 

The unit of the parameter


STREAMS$_PA_DATABASE Table

The STREAMS$_PA_DATABASE table displays information about each database that contains Oracle Streams components.

Table 264-6 STREAMS$_PA_DATABASE Table

Column Datatype NULL Description

GLOBAL_NAME

VARCHAR2(128)

NOT NULL

Global name of the database analyzed by the Oracle Streams Performance Advisor

LAST_QUERIED

DATE

 

The time when the Performance Advisor successfully collected information from a database in its last run

ERROR_NUMBER

NUMBER

 

The error number of the error encountered when the database was last queried

ERROR_MESSAGE

VARCHAR2(4000)

 

The error message of the error encountered when the database was last queried


STREAMS$_PA_DATABASE_PROP Table

The STREAMS$_PA_DATABASE_PROP table displays Oracle Streams database property information necessary for analysis by the Streams Performance Advisor.

Table 264-7 STREAMS$_PA_DATABASE_PROP Table

Column Datatype NULL Description

GLOBAL_NAME

VARCHAR2(128)

NOT NULL

Global name of the database analyzed by the Oracle Streams Performance Advisor

PROP_NAME

VARCHAR2(30)

 

Property name

The database properties include the following:

  • VERSION

  • COMPATIBILITY

  • MANAGEMENT_PACK_ACCESS

  • DB_UNIQUE_NAME

PROP_VALUE

VARCHAR2(30)

 

Property value


STREAMS$_PA_MONITORING Table

The STREAMS$_PA_MONITORING table displays information about each monitoring job running in a database.

Table 264-8 STREAMS$_PA_MONITORING Table

Column Datatype NULL Description

JOB_NAME

VARCHAR2(30)

NOT NULL

Name of the monitoring job

CLIENT_NAME

VARCHAR2(30)

 

Name of the client that submitted the job

See Also: "Full Monitoring Job Names"

QUERY_USER_NAME

VARCHAR2(30)

 

User granted privileges to view the monitoring results

SHOW_STATS_TABLE

VARCHAR2(30)

 

Name of the table used by the SHOW_STATS procedure to display statistics

STARTED_TIME

TIMESTAMP

 

Time the monitoring job started

STOPPED_TIME

TIMESTAMP

 

Time the monitoring job last stopped

ALTERED_TIME

TIMESTAMP

 

Time the monitoring job was last altered

STATE

VARCHAR2(30)

 

State of the monitoring job, either ENABLED or STOPPED


STREAMS$_PA_PATH_BOTTLENECK Table

The STREAMS$_PA_PATH_BOTTLENECK table displays information about Oracle Streams components that might be slowing down the flow of messages.

Table 264-9 STREAMS$_PA_PATH_BOTTLENECK Table

Column Datatype NULL Description

ADVISOR_RUN_ID

NUMBER

 

Identification number of the Oracle Streams Performance Advisor run

ADVISOR_RUN_TIME

DATE

 

Time when the Oracle Streams Performance Advisor was last run

ADVISOR_RUN_REASON

VARCHAR2(4000)

 

Reason for the bottleneck

PATH_ID

NUMBER

 

Identification number assigned to the path by the Oracle Streams Performance Advisor

PATH_KEY

VARCHAR2(4000)

 

Unique key assigned to the path by the Oracle Streams Performance Advisor

COMPONENT_ID

NUMBER

 

Identification number assigned to the component by the Oracle Streams Performance Advisor

TOP_SESSION_ID

NUMBER

 

Session ID of the top component. Query the V$SESSION view for information about the session.

TOP_SESSION_SERIAL#

NUMBER

 

Session serial number of the top component. Query the V$SESSION view for information about the session.

ACTION_NAME

VARCHAR2(32)

 

Action name for the top session

BOTTLENECK_IDENTIFIED

VARCHAR2(30)

 

Whether a bottleneck was identified


STREAMS$_PA_PATH_STAT Table

The STREAMS$_PA_PATH_STAT table displays performance statistics about each stream path.

Table 264-10 STREAMS$_PA_PATH_STAT Table

Column Datatype NULL Description

ADVISOR_RUN_ID

NUMBER

 

Identification number of the Oracle Streams Performance Advisor run

ADVISOR_RUN_TIME

DATE

 

Time when the Oracle Streams Performance Advisor was run for the advisor run ID

PATH_ID

NUMBER

 

Identification number assigned to the path by the Oracle Streams Performance Advisor

PATH_KEY

VARCHAR2(4000)

 

Unique key assigned to the path by the Oracle Streams Performance Advisor

STATISTIC_TIME

DATE

 

Time when the statistic was recorded

STATISTIC_NAME

VARCHAR2(64)

 

Name of the statistic

STATISTIC_VALUE

NUMBER

 

Value recorded for the statistic

STATISTIC_UNIT

VARCHAR2(64)

 

Unit of measurement for the statistic


STREAMS$_PA_SHOW_COMP_STAT Table

The STREAMS$_PA_SHOW_COMP_STAT table displays statistics for Oracle Streams components.

Table 264-11 STREAMS$_PA_SHOW_COMP_STAT Table

Column Datatype NULL Description

ADVISOR_RUN_ID

NUMBER

 

Identification number of the Oracle Streams Performance Advisor run

ADVISOR_RUN_TIME

DATE

 

Time when the Oracle Streams Performance Advisor was last run

PATH_ID

NUMBER

 

Identification number assigned to the path by the Oracle Streams Performance Advisor

POSITION

NUMBER

 

Position of the component in the path

COMPONENT_ID

NUMBER

 

Identification number assigned to the component by the Oracle Streams Performance Advisor

COMPONENT_NAME

VARCHAR2(194)

 

Name of the component

COMPONENT_TYPE

VARCHAR2(30)

 

Type of the component

The following types are possible:

  • CAPTURE for a capture process

  • PROPAGATION SENDER for a propagation sender

  • PROPAGATION RECEIVER for a propagation receiver

  • APPLY for an apply process

  • QUEUE for a queue

SUB_COMPONENT_TYPE

VARCHAR2(30)

 

Type of the subcomponent

Only capture processes and apply processes have subcomponents.

The following capture process subcomponent types are possible:

  • LOGMINER READER for a builder server of a capture process

  • LOGMINER PREPARER for a preparer server of a capture process

  • LOGMINER BUILDER for a reader server of a capture process

  • CAPTURE SESSION for a capture process session

The following apply process subcomponent types are possible:

  • PROPAGATION SENDER+RECEIVER for sending LCRs from a capture process directly to an apply process in a combined capture and apply configuration in which both the capture process and apply process run on a single database

  • APPLY READER for a reader server of an apply process

  • APPLY COORDINATOR for a coordinator process of an apply process

  • APPLY SERVER for a reader server of an apply process

SESSION_ID

NUMBER

 

Identification number of the session for the component. Query the V$SESSION view for information about the session.

SESSION_SERIAL#

NUMBER

 

Session serial number of the session for the component. Query the V$SESSION view for information about the session.

STATISTIC_ALIAS

VARCHAR2(30)

 

Name of the statistic

STATISTIC_NAME

VARCHAR2(128)

 

Name of the statistic

STATISTIC_VALUE

NUMBER

 

Value recorded for the statistic

STATISTIC_UNIT

VARCHAR2(128)

 

Unit of measurement for the statistic


STREAMS$_PA_SHOW_PATH_STAT Table

The STREAMS$_PA_SHOW_PATH_STAT table displays statistics for the stream paths in an Oracle Streams configuration. A monitoring job uses this table as the default table for the statistics collected for stream paths.

Table 264-12 STREAMS$_PA_SHOW_PATH_STAT Table

Column Datatype NULL Description

PATH_ID

NUMBER

 

Identification number assigned to the path by the Oracle Streams Performance Advisor

ADVISOR_RUN_ID

NUMBER

 

Identification number of the Oracle Streams Performance Advisor run

ADVISOR_RUN_TIME

DATE

 

Time when the Oracle Streams Performance Advisor was last run

SETTING

VARCHAR2(2000)

 

Setting for the Oracle Streams Performance Advisor Run

STATISTICS

VARCHAR2(4000)

 

Component-level statistics

SESSION_STATISTICS

VARCHAR2(4000)

 

Session-level statistics

OPTIMIZATION

NUMBER

 

Whether the path uses the combined capture and apply optimization

0 (zero) means that the path does not use the combined capture and apply optimization.

1 means that the path uses the combined capture and apply optimization.


Summary of UTL_SPADV Subprograms

Table 264-13 UTL_SPADV Package Subprograms

Subprogram Description

ALTER_MONITORING Procedure

Alters the monitoring job submitted by the current user.

COLLECT_STATS Procedure

Uses the Oracle Streams Performance Advisor to gather statistics about the Oracle Streams components and subcomponents in a distributed database environment.

IS_MONITORING Function

Checks whether a monitoring job is currently running.

SHOW_STATS Procedure

Generates output that includes the statistics gathered by the COLLECT_STATS procedure.

SHOW_STATS_HTML Procedure

Generates HTML output that includes the statistics gathered by the COLLECT_STATS procedure.

START_MONITORING Procedure

Starts a monitoring job.

STOP_MONITORING Procedure

Stops a monitoring job.


ALTER_MONITORING Procedure

This procedure alters the monitoring job submitted by the current user.

Syntax

UTL_SPADV.ALTER_MONITORING(
   interval                      IN NUMBER  DEFAULT NULL,
   top_event_threshold           IN NUMBER  DEFAULT NULL,
   bottleneck_idle_threshold     IN NUMBER  DEFAULT NULL,
   bottleneck_flowctrl_threshold IN NUMBER  DEFAULT NULL,
   retention_time                IN NUMBER  DEFAULT NULL);

Parameters

Table 264-14 ALTER_MONITORING Procedure Parameters

Parameter Description

interval

The amount of time, in seconds, between each Performance Advisor run. The maximum is 3600 seconds.

If NULL, then the current value is not changed.

top_event_threshold

A percentage that determines whether 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.

If NULL, then the current value is not changed.

bottleneck_idle_threshold

A percentage that determines whether 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.

If NULL, then the current value is not changed.

bottleneck_flowctrl_threshold

A percentage that determines whether 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.

If NULL, then the current value is not changed.

retention_time

The number of hours to retain monitoring results.

If NULL, then the current value is not changed.


Exceptions

Table 264-15 ALTER_MONITORING Procedure Exceptions

Exception Description

ORA-20113

no active monitoring job found


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 264-16 COLLECT_STATS Procedure Parameters

Parameter Description

interval

The amount of time, in seconds, between each Performance Advisor run. The maximum is 3600 seconds.

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

IS_MONITORING Function

This function checks whether a monitoring job is currently running. This function either returns TRUE if a monitoring job is currently running or FALSE if a monitoring job is not currently running.

A monitoring job is submitted using the START_MONITORING procedure.

Syntax

UTL_SPADV.IS_MONITORING(
   job_name    IN VARCHAR2  DEFAULT 'STREAMS$_MONITORING_JOB',
   client_name IN VARCHAR2  DEFAULT NULL) 
RETURN BOOLEAN;

Parameters

Table 264-17 IS_MONITORING Function Parameters

Parameter Description

job_name

The name of the job for which to check.

client_name

The name of the client that submitted the job.


SHOW_STATS Procedure

This procedure generates output that includes the statistics gathered by the COLLECT_STATS and START_MONITORING procedures.

The output is formatted so that it can be imported into a spreadsheet for analysis.

Note:

This procedure does not commit.

See Also:

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_optimization 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 264-18 SHOW_STATS Procedure Parameters

Parameter Description

path_stat_table

The name of the table that contains the stream path statistics. Specify the table name as [schema_name.]object_name. If the schema is not specified, then the current user is the default.

When you gather statistics using the COLLECT_STATS procedure, this table is specified in the path_stat_table parameter in the COLLECT_STATS procedure. The default table is STREAMS$_ADVISOR_PATH_STAT.

When you gather statistics using the START_MONITORING procedure, you can determine the name for this table by querying the SHOW_STATS_TABLE column in the STREAMS$_PA_MONITORING view. The default table for a monitoring job is STREAMS$_PA_SHOW_PATH_STAT.

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_optimization

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 to display. Positive numbers show statistics from an earlier run forward. Negative numbers show statistics from 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

SHOW_STATS_HTML Procedure

This procedure generates HTML output that includes the statistics gathered by the COLLECT_STATS and START_MONITORING procedures.

Note:

This procedure does not commit.

See Also:

Syntax

UTL_SPADV.SHOW_STATS_HTML(
   directory       IN VARCHAR2,
   reportname      IN VARCHAR2  DEFAULT 'SPADVREPORT.HTML',
   comp_stat_table IN VARCHAR2  DEFAULT 'STREAMS$_ADVISOR_COMP_STAT',
   path_id         IN NUMBER    DEFAULT NULL,
   bgn_run_id      IN NUMBER    DEFAULT -1,
   end_run_id      IN NUMBER    DEFAULT -10,
   detailed        IN BOOLEAN   DEFAULT TRUE);

Parameters

Table 264-19 SHOW_STATS_HTML Procedure Parameters

Parameter Description

directory

The directory object for the directory on the local computer system into which the generated HTML report is placed

The specified directory object must be created using the SQL statement CREATE DIRECTORY, and the user who invokes the procedure must have READ and WRITE privilege on each one.

reportname

The name of the HTML report

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.

When you gather statistics using the COLLECT_STATS procedure, this table is specified in the comp_stat_table parameter in the COLLECT_STATS procedure. The default table is STREAMS$_ADVISOR_COMP_STAT.

When you gather statistics using the START_MONITORING procedure, you can determine the name for this table by querying the SHOW_STATS_TABLE column in the STREAMS$_PA_MONITORING view. The default table for a monitoring job is STREAMS$_PA_SHOW_PATH_STAT.

Oracle recommends that you start a monitoring job with the START_MONITORING procedure in this package and use the appropriate the STREAMS$_PA_SHOW_PATH_STAT table.

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.

detailed

If TRUE, then the procedure generates component-level statistics.

If FALSE, then the procedure does not generate component-level statistics.


Usage Notes

Use the bgn_run_id and end_run_id together to specify the range of Oracle Streams Performance Advisor runs to display. Positive numbers show statistics from an earlier run forward. Negative numbers show statistics from 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.

START_MONITORING Procedure

This procedure starts a monitoring job.

This procedure runs the COLLECT_STATS procedure to gather statistics about the Oracle Streams components and subcomponents in a distributed database environment.

Note:

This procedure commits.

See Also:

Syntax

UTL_SPADV.START_MONITORING(
   job_name                      IN VARCHAR2  DEFAULT 'STREAMS$_MONITORING_JOB',
   client_name                   IN VARCHAR2  DEFAULT NULL,
   query_user_name               IN VARCHAR2  DEFAULT NULL,
   interval                      IN NUMBER    DEFAULT 60,
   top_event_threshold           IN NUMBER    DEFAULT 15,
   bottleneck_idle_threshold     IN NUMBER    DEFAULT 50,
   bottleneck_flowctrl_threshold IN NUMBER    DEFAULT 50,
   retention_time                IN NUMBER    DEFAULT 24);

Parameters

Table 264-20 START_MONITORING Procedure Parameters

Parameter Description

job_name

The name of the monitoring job to create.

client_name

The name of the client.

query_user_name

The user who will query the result tables.

This procedure grants privileges to the specified user to enable the user to query the result tables.

interval

The amount of time, in seconds, between each Performance Advisor run. The maximum is 3600 seconds.

The specified interval is used for the interval parameter in the COLLECT_STATS procedure.

top_event_threshold

A percentage that determines whether 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 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 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.

retention_time

The number of hours to retain monitoring results.


Exceptions

Table 264-21 START_MONITORING Procedure Exceptions

Exception Description

ORA-20111

cannot start monitoring due to active EM monitoring job

Stop the Oracle Enterprise Manager (EM) monitoring job, and run the START_MONITORING procedure again.

ORA-20112

cannot start monitoring due to active Streams monitoring job

Stop the Streams monitoring job, and run the START_MONITORING procedure again.


Usage Notes

The following are usage notes for the START_MONITORING procedure:

Requirements for the User Running the Procedure

The user who runs the START_MONITORING procedure 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.

Full Monitoring Job Names

When you submit a monitoring job, the client name and job name are concatenated to form the full monitoring job name. You specify the client name using the client_name parameter and the job name using the job_name parameter when you run the START_MONITORING procedure. The client name for a monitoring job submitted by Oracle Enterprise Manager is always EM.

The following table show examples of full monitoring job names:

Setting for client_name Parameter Setting for job_name parameter Full Monitoring Job Name
NULL STREAMS$_MONITORING_JOB STREAMS$_MONITORING_JOB
EM STREAMS$_MONITORING_JOB EMSTREAMS$_MONITORING_JOB
strm STREAMS$_MONITORING_JOB strmSTREAMS$_MONITORING_JOB
strm mjob1 strmmjob1

Restrictions on Monitoring Jobs

The following restrictions apply to monitoring jobs:

  • The limit for the length of the full monitoring job name is 30 bytes.

  • Two monitoring jobs cannot have the same full monitoring job name, even if the monitoring jobs were submitted by different schemas. The name check is not case-sensitive. For example, strmSTREAMS$_MONITORING_JOB and STRMSTREAMS$_MONITORING_JOB are considered to be the same name.

  • Oracle Enterprise Manager can have at most one monitoring job for each database.

  • Each schema can have at most one monitoring job.

STOP_MONITORING Procedure

This procedure stops a monitoring job that was submitted by the current user.

Syntax

UTL_SPADV.STOP_MONITORING(
   purge IN BOOLEAN  DEFAULT FALSE);

Parameters

Table 264-22 STOP_MONITORING Procedure Parameters

Parameter Description

purge

If TRUE, then the procedure purges information about the monitoring job from the result tables.

If FALSE, then the procedure retains information about the monitoring job in the result tables.