Oracle UTL_SPADV
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose

The package is not installed by default during database creation. 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).
Source {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
conn sys@pdbdev as sysdba

SQL> @/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


conn uwclass/uwclass@pdbdev

SQL> @/rdbms/admin/utlspadv.sql

-- ignore the exception and warning messages raised by the script

SQL> SELECT object_name, object_type FROM dba_objects
2 where created > sysdate-1/24;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ --------------------
SYS_LOB0000011196C00016$$      LOB PARTITION
SYS_IL0000011196C00016$$       INDEX PARTITION
WRI$_OPTSTAT_HISTHEAD_HISTORY  TABLE PARTITION
WRI$_OPTSTAT_HISTGRM_HISTORY   TABLE PARTITION
STREAMS$_PA_MONITORING         TABLE
STREAMS$_PA_DATABASE           TABLE
STREAMS$_PA_DATABASE_PROP      TABLE
STREAMS$_PA_COMPONENT          TABLE
STREAMS$_PA_COMPONENT_PROP     TABLE
STREAMS$_PA_COMPONENT_LINK     TABLE
STREAMS$_PA_CONTROL            TABLE
STREAMS$_PA_COMPONENT_STAT     TABLE
STREAMS$_PA_PATH_STAT          TABLE
STREAMS$_PA_PATH_BOTTLENECK    TABLE
STREAMS$_PA_SHOW_COMP_STAT     TABLE
COMP_STAT_PKEY                 INDEX
STREAMS$_PA_SHOW_PATH_STAT     TABLE
UTL_SPADV                      PACKAGE
UTL_SPADV                      PACKAGE BODY


19 rows selected.
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
DBMS_STREAMS
DBMS_STREAMS_ADM
Directory Objects
Packages

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-2014 Daniel A. Morgan All Rights Reserved