Oracle UTL_RPADV
Version 19.3

General Information
Library Note Morgan's Library Page Header
For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.
Purpose The UTL_RPADV package provides subprograms to collect and analyze statistics for the Oracle Replication components in a distributed database environment. This package uses the Oracle Replication Performance Advisor to gather statistics.
AUTHID DEFINER
Constants
Name Data Type Value
Package Header Constants
version VARCHAR2(30) '2.0'
Package Body Constants
minutes_per_day NUMBER 1440
seconds_per_day NUMBER 86400
seconds_per_hour NUMBER 3600
Dependencies
STREAMS$_PA_COMPONENT_LINK STREAMS$_PA_DATABASE STREAMS$_PA_PATH_STAT
STREAMS$_PA_COMPONENT_PROP STREAMS$_PA_DATABASE_PROP STREAMS$_PA_SHOW_COMP_STAT
STREAMS$_PA_COMPONENT_STAT STREAMS$_PA_MONITORING STREAMS$_PA_SHOW_PATH_STAT
STREAMS$_PA_CONTROL STREAMS$_PA_PATH_BOTTLENECK  
Documented Yes
First Available 19c
Security Model Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE role.
Source {ORACLE_HOME}/rdbms/admin/utlrpadv.sql
Subprograms
 
ALTER_MONITORING (new 19c)
Alters monitoring of replication performance utl_rpadv.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);
 
 
COLLECT_STATS (new 19c)
Collect statistics for all active replication paths utl_rpadv.collect_stats(
interval                      IN NUMBER   DEFAULT 6,
num_runs                      IN NUMBER   DEFAULT 2,
comp_stat_table               IN VARCHAR2 DEFAULT 'REPL$_ADVISOR_COMP_STAT',
path_stat_table               IN VARCHAR2 DEFAULT 'REPL$_ADVISOR_PATH_STAT',
top_event_threshold           IN NUMBER   DEFAULT 15,
bottleneck_idle_threshold     IN NUMBER   DEFAULT 50,
bottleneck_flowctrl_threshold IN NUMBER   DEFAULT 50);
 
 
IS_MONITORING (new 19c)
Returns TRUE if a client has submitted a monitoring job utl_rpadv.is_monitoring(
job_name    IN VARCHAR2 DEFAULT 'REPL$_MONITORING_JOB',
client_name IN VARCHAR2 DEFAULT NULL)
RETURN BOOLEAN;
BEGIN
  IF utl_rpadv.is_monitoring; THEN
    dbms_output.put_line('Monitoring Job Submitted');
  ELSE
    dbms_output.put_line('No Monitoring Job');
  END IF;
END;
/
 
SHOW_STATS (new 19c)
Print statistics for a replication path utl_rpadv.show_stats(
path_stat_table   IN VARCHAR2 DEFAULT 'REPL$_ADVISOR_PATH_STAT',
path_id           IN NUMBER DEFAULT NULL, -- show all replication paths
bgn_run_id        IN NUMBER DEFAULT -1,   -- show the last 10 runs
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);
exec utl_rpadv.show_stats(show_setting => TRUE);
 
SHOW_STATS_HTML (new 19c)
Generates a html report of the replication performance statistics collected using collect_stats utl_rpadv.show_stats(
directory       IN VARCHAR2,
reportName      IN VARCHAR2 DEFAULT 'SPADVREPORT.HTML',
comp_stat_table IN VARCHAR2 DEFAULT 'REPL$_ADVISOR_COMP_STAT',
path_id         IN NUMBER   DEFAULT NULL,    -- show all replication 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_rpadv.show_stats('ORACLE_HOME', 'SPADVRPT_' || TO_CHAR(TRUNC(sysdate)));
 
START_MONITORING (new 19c)
Begins persistent monitoring of replication performance. Allows (1) at most one monitoring job per schema, and (2) at most one EM monitoring job per database. utl_rpadv.start_monitoring(
job_name                      IN VARCHAR2 DEFAULT 'REPL$_MONITORING_JOB',
client_name                   IN VARCHAR2 DEFAULT NULL,
query_user_name               IN VARCHAR2 DEFAULT NULL,
interval                      IN NUMBER DEFAULT 10,
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);
exec utl_rpadv.start_monitoring(top_event_threshold => 20);
 
STOP_MONITORING (new 19c)
Stops persistent monitoring of replication performance utl_rpadv.stop_monitoring(purge IN BOOLEAN DEFAULT FALSE);
exec utl_rpadv.stop_monitoring(TRUE);

Related Topics
Built-in Functions
Built-in Packages
What's New In 18cR3
What's New In 19cR3

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