| General Information |
| Note: Package body code is not wrapped |
| Source |
{ORACLE_HOME}/rdbms/admin/utlspadv.sql |
| First Availability |
11.1 |
| Constants |
| Name |
Data Type |
Value |
| minutes_per_day |
NUMBER |
1440 |
| monitoring_altered_time |
TIMESTAMP |
NULL |
| monitoring_client_name |
VARCHAR2(30) |
NULL |
| monitoring_job_name |
VARCHAR2(30) |
NULL |
| monitoring_query_user_name |
VARCHAR2(30) |
NULL |
| monitoring_started_time |
TIMESTAMP |
NULL |
| param_bot_flowctrl_threshold |
NUMBER |
50 |
| param_bot_idle_threshold |
NUMBER |
50 |
| param_interval (in seconds) |
NUMBER |
60 |
| param_retention_time (in hours) |
NUMBER |
24 |
| param_top_event_threshold |
NUMBER |
15 |
| seconds_per_day |
NUMBER |
86400 |
| seconds_per_hour |
NUMBER |
3600 |
| total_param_cnt |
NUMBER |
5 |
| version |
VARCHAR2(30) |
'2.0' |
|
| Dependencies |
| ALL_DIRECTORIES |
PLITBLM |
| DBA_DB_LINKS |
STREAMS$_PA_COMPONENT |
| DBA_SCHEDULER_JOBS |
STREAMS$_PA_COMPONENT_LINK |
| DBA_STREAMS_TP_COMPONENT |
STREAMS$_PA_COMPONENT_PROP |
| DBA_STREAMS_TP_COMPONENT_LINK |
STREAMS$_PA_COMPONENT_STAT |
| DBA_STREAMS_TP_COMPONENT_STAT |
STREAMS$_PA_CONTROL |
| DBA_STREAMS_TP_DATABASE |
STREAMS$_PA_DATABASE |
| DBA_STREAMS_TP_PATH_BOTTLENECK |
STREAMS$_PA_DATABASE_PROP |
| DBA_STREAMS_TP_PATH_STAT |
STREAMS$_PA_MONITORING |
| DBMS_ASSERT |
STREAMS$_PA_PATH_BOTTLENECK |
| DBMS_LOCK |
STREAMS$_PA_PATH_STAT |
| DBMS_OUTPUT |
STREAMS$_PA_SHOW_COMP_STAT |
| DBMS_SCHEDULER |
STREAMS$_PA_SHOW_PATH_STAT |
| DBMS_SQL |
USER_SCHEDULER_JOBS |
| DBMS_STREAMS_ADVISOR_ADM |
USER_TABLES |
| DBMS_UTILITY |
USER_TAB_COLUMNS |
| DUAL |
UTL_FILE |
| GLOBAL_NAME |
_DBA_STREAMS_TP_COMPONENT_PROP |
|
| Exceptions |
| Exception |
Description |
| ORA-20110 |
Invalid top_event_threshold
Invalid bottleneck_idle_threshold
Invalid bottleneck_flowctrl_threshold
Invalid retention_time, too small
Combined length of job_name and client_name must be less than 30 |
| ORA-20111 |
Cannot start monitoring due to active EM monitoring job |
| ORA-20112 |
Cannot start monitoring due to active Streams monitoring job |
| ORA-20113 |
No active monitoring job found |
|
| Security Model |
None: The package is not installed by default during database creation.
Is created with AUTHID CURRENT_USER. If you try to create it as SYS or SYSTEM you will receive the following error and the package will not be created.
ERROR at line 1:
ORA-20100: The package UTL_SPADV should be loaded into a Streams administration schema: current user
ORA-06512: at line 9 |
| Subprograms |
|
| |
| ALTER_MONITORING |
| Alters monitoring of Streams performance |
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); |
| TBD |
| |
| COLLECT_STATS |
| Uses the Oracle Streams Performance Advisor to gather statistics about the Oracle Streams components and subcomponents
in a distributed database environment from all active streams paths |
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); |
| exec utl_spadv.collect_stats(num_runs=>2); |
| |
| IS_MONITORING |
| Checks if a client has submitted a monitoring job |
utl_spadv.is_monitoring(
job_name IN VARCHAR2 DEFAULT 'STREAMS$_MONITORING_JOB',
client_name IN VARCHAR2 DEFAULT NULL)
RETURN BOOLEAN; |
| TBD |
| |
| SHOW_STATS |
| Generates output that includes the statistics gathered by the COLLECT_STATS procedure. |
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); |
set serveroutput on
exec utl_spadv.show_stats; |
| |
SHOW_STATS_HTML (new 11.2.0.2)  |
| Generates a html report of the streams performance statistics collected using collect_stats |
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, -- show all stream paths
bgn_run_id IN NUMBER DEFAULT -1, -- show the last 10 runs
end_run_id IN NUMBER DEFAULT -10,
detailed IN BOOLEAN DEFAULT TRUE); |
| exec utl_spadv.show_stats_html('CTEMP'); |
| |
| START_MONITORING |
| Begins persistent monitoring of Streams performance |
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); |
| TBD |
| |
| STOP_MONITORING |
| Stops persistent monitoring of Streams performance |
utl_spadv.stop_monitoring(purge IN BOOLEAN DEFAULT FALSE); |
| exec utl_spadv.stop_monitoring(TRUE); |
