Oracle UTL_SPADV
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
Purpose The package is not installed by default during database creation and it can not be installed as SYS or SYSTEM. Note also that this package's body code is not wrapped.
AUTHID CURRENT_USER
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 DBMS_SQL STREAMS$_PA_DATABASE
DBA_DB_LINKS DBMS_STANDARD STREAMS$_PA_DATABASE_PROP
DBA_SCHEDULER_JOBS DBMS_STREAMS_ADVISOR_ADM STREAMS$_PA_MONITORING
DBA_STREAMS_TP_COMPONENT DBMS_UTILITY STREAMS$_PA_PATH_BOTTLENECK
DBA_STREAMS_TP_COMPONENT_LINK DUAL STREAMS$_PA_PATH_STAT
DBA_STREAMS_TP_COMPONENT_STAT GLOBAL_NAME STREAMS$_PA_SHOW_COMP_STAT
DBA_STREAMS_TP_DATABASE PLITBLM STREAMS$_PA_SHOW_PATH_STAT
DBA_STREAMS_TP_PATH_BOTTLENECK STREAMS$_PA_COMPONENT USER_SCHEDULER_JOBS
DBA_STREAMS_TP_PATH_STAT STREAMS$_PA_COMPONENT_LINK USER_TABLES
DBMS_ASSERT STREAMS$_PA_COMPONENT_PROP USER_TAB_COLUMNS
DBMS_LOCK STREAMS$_PA_COMPONENT_STAT UTL_FILE
DBMS_OUTPUT STREAMS$_PA_CONTROL _DBA_STREAMS_TP_COMPONENT_PROP
DBMS_SCHEDULER    
Documented No
Exceptions
Error Code Reason
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
First Available 11gR1
Security Model Should be installed in a Streams Admin schema ... not into a privileged schema such as SYS or SYSTEM (see below).
No privileges are granted during package installation.
Source SQL> conn / as sysdba

{ORACLE_HOME}/rdbms/admin/utlspadv.sql

ERROR at line 1:
ORA-20100: The package UTL_SPADV should be loaded into a Streams administration schema: current user is SYS.
ORA-06512: at line 9
SQL> conn c##uwclass
Password:
Connected.

SQL> @?/rdbms/admin/utlspadv.sql
drop table streams$_pa_monitoring
*
ERROR at line 1:
ORA-00942: table or view does not exist

Table created.

drop table streams$_pa_database
*
ERROR at line 1:
ORA-00942: table or view does not exist

Table created.

drop table streams$_pa_database_prop
*
ERROR at line 1:
ORA-00942: table or view does not exist

Table created.

drop table streams$_pa_component
*
ERROR at line 1:
ORA-00942: table or view does not exist

Table created.

drop table streams$_pa_component_prop
*
ERROR at line 1:
ORA-00942: table or view does not exist

Table created.

drop table streams$_pa_component_link
*
ERROR at line 1:
ORA-00942: table or view does not exist

Table created.

drop table streams$_pa_control
*
ERROR at line 1:
ORA-00942: table or view does not exist

Table created.

drop table streams$_pa_component_stat
*
ERROR at line 1:
ORA-00942: table or view does not exist

Table created.

drop table streams$_pa_path_stat
*
ERROR at line 1:
ORA-00942: table or view does not exist

Table created.

drop table streams$_pa_path_bottleneck
*
ERROR at line 1:
ORA-00942: table or view does not exist

Table created.

drop table streams$_pa_show_comp_stat
*
ERROR at line 1:
ORA-00942: table or view does not exist

Table created.

Index created.

drop table streams$_pa_show_path_stat
*
ERROR at line 1:
ORA-00942: table or view does not exist

Table created.

SP2-0808: Package created with compilation warnings

Errors for PACKAGE UTL_SPADV:

LINE/COL ERROR
-------- -----------------------------------------------------------------
28/5 PLW-06010: keyword "INTERVAL" used as a defined name
87/5 PLW-06010: keyword "INTERVAL" used as a defined name
113/5 PLW-06010: keyword "INTERVAL" used as a defined name
131/29 PLW-06010: keyword "PURGE" used as a defined name

Warning: Package Body created with compilation errors.

Errors for PACKAGE BODY UTL_SPADV:

LINE/COL ERROR
-------- -----------------------------------------------------------------
433/10 PL/SQL: Item ignored
433/10 PLS-00201: identifier 'DBA_STREAMS_TP_COMPONENT_STAT' must be declared

437/9 PL/SQL: SQL Statement ignored
440/19 PL/SQL: ORA-00942: table or view does not exist
456/9 PL/SQL: Statement ignored
456/35 PLS-00364: loop index variable 'BOTT' use is invalid
467/9 PL/SQL: SQL Statement ignored
468/14 PL/SQL: ORA-00942: table or view does not exist
477/11 PL/SQL: SQL Statement ignored
478/16 PL/SQL: ORA-00942: table or view does not exist
1421/16 PL/SQL: Item ignored
1421/16 PLS-00201: identifier 'DBA_STREAMS_TP_COMPONENT_STAT' must be
declared

1422/16 PL/SQL: Item ignored
1422/16 PLS-00201: identifier 'DBA_STREAMS_TP_COMPONENT_STAT' must be
declared

1442/7 PL/SQL: SQL Statement ignored
1442/27 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

1452/5 PL/SQL: Statement ignored
1452/21 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

1455/5 PL/SQL: Statement ignored
1456/23 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

PL/SQL procedure successfully completed.
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
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);
-- creation of the CTEMP directory object can be found through the link at page bottom
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);

Related Topics
Built-in Functions
Built-in Packages
DBMS_STREAMS
DBMS_STREAMS_ADM
Directory Objects
What's New In 12cR2
What's New In 18cR3

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved