Oracle PRVT_AWR_VIEWER
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Purpose AWR Support
AUTHID CURRENT_USER
Dependencies
AWR_PDB_DATABASE_INSTANCE DBMS_STANDARD PRVT_EMX
AWR_PDB_SNAPSHOT DBMS_SYS_ERROR PRVT_HDM
AWR_PDB_STAT_NAME DBMS_WORKLOAD_REPOSITORY PRVT_REPORT_TAGS
AWR_ROOT_DATABASE_INSTANCE DUAL PRVT_SMGUTIL
AWR_ROOT_SNAPSHOT GV$INSTANCE SYS_IXMLAGG
AWR_ROOT_STAT_NAME GV$SESSION V$PDBS
DBMS_ASH_INTERNAL PLITBLM_ WRI$_REPT_ADDM
DBMS_LOB PRVTEMX_DBHOME WRI$_REPT_AWRV
DBMS_MANAGEMENT_PACKS PRVTEMX_MEMORY WRI$_REPT_DBHOME
DBMS_PERF PRVTEMX_RSRCMGR WRI$_REPT_EMX_PERF
DBMS_REPORT PRVT_AWRV_INSTTAB WRI$_REPT_PERF
DBMS_SQLTUNE PRVT_AWRV_MAP XMLAGG
DBMS_SQLTUNE_UTIL0 PRVT_AWRV_MAPTAB XMLTYPE
DBMS_SQLTUNE_UTIL1 PRVT_AWRV_METADATA XQSEQUENCE
DBMS_SQLTUNE_UTIL2 PRVT_AWRV_VARCHAR64TAB  
Documented No
First Available 12.1.0.1
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/prvsawrv.plb
Subprograms
 
ADJUST_REALTIME_INPUT_TIMES (new 18.1)
Undocumented prvt_awr_viewer.adjust_realtime_input_times(
p_start_time IN OUT DATE,
p_end_time   IN OUT DATE,
p_source        OUT VARCHAR2,
p_duration   IN     NUMBER);
SQL> DECLARE
  2   pstart DATE := SYSDATE;
  3   pend DATE := SYSDATE+1;
  4   psrc VARCHAR2(30);
  5  BEGIN
  6    prvt_awr_viewer.adjust_realtime_input_times(pstart, pend, psrc, 10);
  7    dbms_output.put_line(psrc);
  8  END;
  9  /
history

PL/SQL procedure successfully completed.
 
AWR_RMMETRICS_XML (new 18.1)
Undocumented prvt_awr_viewer.awr_rmmetrics_xml(
p_plan_name       IN     VARCHAR2,
p_awr_period      IN OUT PRVT_AWRV_METADATA,
p_bucket_map      IN OUT PRVT_AWRV_MAPTAB,
p_bucket_interval IN     NUMBER,
p_show_sql        IN     NUMBER,
p_idname_map      IN OUT PRVT_AWRV_INSTTAB,
output_xml        IN OUT XMLTYPE);
TBD
 
AWR_SYSMETRICS_XML (new 18.1)
Undocumented prvt_awr_viewer.awr_sysmetrics_xml(
awr_period         IN OUT PRVT_AWRV_METADATA,
bucket_map         IN OUT PRVT_AWRV_MAPTAB,
p_bucket_max_count IN     NUMBER,
p_bucket_interval  IN     NUMBER,
p_metric_type      IN     VARCHAR2,
p_show_sql         IN     NUMBER,
output_xml            OUT XMLTYPE);
TBD
 
AWR_SYSSTAT_TOTALSTAT_XML (new 18.1)
Undocumented prvt_awr_viewer.awr_sysstat_totalstat_xml(
awr_period         IN OUT PRVT_AWRV_METADATA,
bucket_map         IN OUT PRVT_AWRV_MAPTAB,
p_bucket_max_count IN     NUMBER,
p_bucket_interval  IN     NUMBER,
p_show_sql         IN     NUMBER,
p_stat_desc        IN     VARCHAR2,
output_xml            OUT XMLTYPE);
TBD
 
CALCULATE_BUCKETS (new 18.1)
Undocumented prvt_awr_viewer.calculate_buckets(
p_bucket_count    IN OUT NUMBER,
p_bucket_interval IN OUT NUMBER,
p_duration        IN     NUMBER,
p_source          IN     VARCHAR2);
TBD
 
CREATE_BUCKET_SNAP_MAP (new 18.1)
Undocumented prvt_awr_viewer.create_bucket_snap_map(
p_awr_period           IN OUT PRVT_AWRV_METADATA,
p_bucket_map           IN OUT PRVT_AWRV_MAPTAB,
p_bucket_max_count     IN OUT NUMBER,
p_bucket_calc_interval    OUT NUMBER);
TBD
 
CREATE_CONTAINER_MAP (new 18.1)
Undocumented prvt_awr_viewer.create_container_map(
p_awr_period IN OUT PRVT_AWRV_METADATA,
p_con_map    IN OUT PRVT_AWRV_INSTTAB);
TBD
 
CREATE_INSTANCE_MAP (new 18.1)
Undocumented prvt_awr_viewer.create_instance_map(
p_awr_period IN OUT PRVT_AWRV_METADATA,
p_inst_map   IN OUT PRVT_AWRV_INSTTAB);
TBD
 
DELTA_STR (new 18.1)
Undocumented prvt_awr_viewer.delta_str(
s        IN VARCHAR2,
t        IN VARCHAR2,
col_id   IN VARCHAR2,
col_name IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
ERROR_XML (new 18.1)
Undocumented prvt_awr_viewer.error_xml(
function_call IN VARCHAR2,
sqlc          IN NUMBER,
sqle          IN VARCHAR2,
addl_info     IN VARCHAR2)
RETURN XMLTYPE;
TBD
 
GENERATE_BUCKETID_TAG (new 18.1)
Undocumented prvt_awr_viewer.generate_bucketid_tag(
p_stat_xml   IN VARCHAR2,
p_alias_name IN VARCHAR2,
p_addl_attr  IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
GENERATE_BUCKETS_TAG2 (new 18.1)
Undocumented prvt_awr_viewer.generate_buckets_tag2(p_buckets IN VARCHAR2)
RETURN VARCHAR2;
SQL> SELECT prvt_awr_viewer.generate_buckets_tag2('Testing')
  2  FROM dual;

PRVT_AWR_VIEWER.GENERATE_BUCKETS_TAG2('TESTING')
---------------------------------------------------------------------------------
nvl2(Testing, xmlelement("buckets", xmlattributes( :p_bucket_interval as "bucket_interval", :p_bucket_count as "bucket_count", :b_min_time as "start_time", :b_max_time as "end_time", :b_duration as "duration"),Testing), null)
 
GET_BUCKET_MAP
Undocumented prvt_awr_viewer.get_bucket_map(
p_start_time       IN     DATE,
p_end_time         IN     DATE,
p_inst_id          IN     NUMBER,
p_dbid             IN     NUMBER,
p_bucket_max_count IN OUT NUMBER,
p_bucket_interval     OUT NUMBER,
p_awr_period          OUT prvt_awrv_metadata;
p_bucket_map       IN OUT prvt_awrv_maptab);
SELECT dbid
FROM v$database;

DECLARE
 a DATE := SYSDATE-1;
 b DATE := SYSDATE;
 c NUMBER := 1;
 d NUMBER := 428676178;
 e NUMBER := 5;
 f NUMBER;
 g prvt_awrv_metadata;
 h prvt_awrv_maptab;
BEGIN
  prvt_awr_viewer.get_bucket_map(a,b,c,d,e,f,g,h);

  dbms_output.put_line(e);
  dbms_output.put_line(f);
END;
/
 
GET_MAPPING_TYPE
Returns the mapping type if set: Otherwise NULL prvt_awr_viewer.get_mapping_type RETURN VARCHAR2;
SELECT prvt_awr_viewer.get_mapping_type
FROM dual;
 
GET_VERSION
Undocumented prvt_awr_viewer.get_version RETURN NUMBER;
SELECT prvt_awr_viewer.get_version
FROM dual;
 
INTERVAL_TO_SECOND
Undocumented prvt_awr_viewer.interval_to_second(v IN INTERVAL DAY TO SECOND) RETURN NUMBER;
SQL> SELECT prvt_awr_viewer.interval_to_second(TO_DSINTERVAL('100 10:00:00'))
  2  FROM dual;

PRVT_AWR_VIEWER.INTERVAL_TO_SECOND(TO_DSINTERVAL('10010:00:00'))
----------------------------------------------------------------
                                                         8676000
Overload 2 prvt_awr_viewer.interval_to_second(
e IN TIMESTAMP,
b IN TIMESTAMP)
RETURN NUMBER;
SQL> SELECT prvt_awr_viewer.interval_to_second(SYSTIMESTAMP, SYSTIMESTAMP-2)
  2  FROM dual;

PRVT_AWR_VIEWER.INTERVAL_TO_SECOND(SYSTIMESTAMP,SYSTIMESTAMP-2)
---------------------------------------------------------------
                                                         172800
 
REPORT_ADDM_XML
Undocumented prvt_awr_viewer.report_addm_xml(
start_time   IN DATE,
end_time     IN DATE,
num_days     IN NUMBER,
owner        IN VARCHAR2,
task_name    IN VARCHAR2,
section      IN VARCHAR2,
spotrep      IN VARCHAR2,
spotlist     IN VARCHAR2,
inst_id      IN NUMBER,
db_id        IN NUMBER,
show_sql     IN NUMBER,
top_n_detail IN NUMBER,
compress_xml IN NUMBER)
RETURN XMLTYPE;
TBD
 
REPORT_AWRREP_XML
Undocumented prvt_awr_viewer.report_awrrep_xml(
selected_start_time IN DATE,
selected_end_time   IN DATE,
inst_id             IN NUMBER,
dbid                IN NUMBER,
compress_xml        IN BINARY_INTEGER,
report_reference    IN VARCHAR2)
RETURN XMLTYPE;
TBD
 
REPORT_AWR_XML
Undocumented prvt_awr_viewer.report_awr_xml(
start_time       IN DATE,
end_time         IN DATE,
instance_list    IN VARCHAR2,
dbid             IN NUMBER,
member_id        IN NUMBER,
bucket_max_count IN NUMBER,
time_model       IN VARCHAR2,
wait_class       IN VARCHAR2,
wiat_event       IN VARCHAR2,
event_class      IN VARCHAR2,
sysstat          IN VARCHAR2,
sqlstat          IN VARCHAR2,
osstat           IN VARCHAR2,
iostat           IN VARCHAR2,
memory           IN VARCHAR2,
space            IN VARCHAR2,
key_statistics   IN VARCHAR2,
summary          IN VARCHAR2,
inst_detail      IN VARCHAR2,
members          IN VARCHAR2,
timepicker_start IN DATE,
timepicker_end   IN DATE,
report_level     IN VARCHAR2,
show_sql         IN NUMBER)
RETURN XMLTYPE;
TBD
 
REPORT_CONTAINERS_XML (new 18.1)
Undocumented prvt_awr_viewer.report_containers_xml(
p_start_time        IN DATE,
p_end_time          IN DATE,
p_last_refresh_time IN DATE,
p_inst_id           IN NUMBER,
p_dbid              IN NUMBER,
p_IS_realtime       IN NUMBER,
p_top_n_count       IN NUMBER,
p_top_n_rankby      IN VARCHAR2,
p_show_sql          IN NUMBER)
RETURN XMLTYPE;
TBD
 
REPORT_METRICS_XML
Undocumented prvt_awr_viewer.report_metrics_xml(
start_time       IN DATE,
end_time         IN DATE,
inst_id          IN NUMBER,
dbid             IN NUMBER,
bucket_max_count IN NUMBER,
bucket_interval  IN NUMBER,
inst_detail      IN VARCHAR2,
report_level     IN VARCHAR2,
show_sql         IN NUMBER)
RETURN XMLTYPE;
CREATE TABLE t (testcol XMLTYPE);

SELECT sql_id, sid, serial#
FROM v$sqlarea
WHERE executions = (
  SELECT MAX(executions) FROM v$sqlarea);

SELECT dbid
FROM v$database;

DECLARE
 a DATE := SYSDATE-1;
 b DATE := SYSDATE;
 c NUMBER := 1;
 d NUMBER := 428676178;
 e NUMBER := 5;
 f NUMBER := 1;
 g VARCHAR2(30); -- inst_detail
 h VARCHAR2(10) := 'TYPICAL';
 i NUMBER := 1; -- show_sql
 x XMLTYPE;
BEGIN
  x := prvt_awr_viewer.report_metrics_xml(a,b,c,d,e,f,g,h,i);

  INSERT INTO t VALUES (x);
  COMMIT;
END;
/

SELECT * FROM t;
 
REPORT_RAC_XML
Undocumented prvt_awr_viewer.report_rac_xml(
start_time        IN DATE,
end_time          IN DATE,
last_refresh_time IN DATE,
inst_id           IN NUMBER,
dbid              IN NUMBER,
is_realtime       IN NUMBER,
show_sql          IN NUMBER)
RETURN XMLTYPE;
CREATE TABLE t (testcol XMLTYPE);

SELECT sql_id, sid, serial#
FROM v$sqlarea
WHERE executions = (
  SELECT MAX(executions) FROM v$sqlarea);

SELECT dbid
FROM v$database;

DECLARE
 a DATE := SYSDATE-1;
 b DATE := SYSDATE;
 c DATE := SYSDATE;
 d NUMBER := 1;
 e NUMBER := 428676178;
 f NUMBER := 1;
 g NUMBER := 1;
 x XMLTYPE;
BEGIN
  x := prvt_awr_viewer.report_rac_xml(a,b,c,d,e,f,g);

  INSERT INTO t VALUES (x);
  COMMIT;
END;
/

SELECT * FROM t;
 
REPORT_TIMEPICKER_XML
Undocumented prvt_awr_viewer.report_timepicker_xml(
start_time  IN DATE,
end_time    IN DATE,
duration    IN NUMBER,
inst_id     IN NUMBER,
dbid        IN NUMBER,
is_realtime IN NUMBER,
sql_id      IN VARCHAR2,
sid         IN NUMBER,
serial      IN NUMBER,
show_sql    IN NUMBER)
RETURN XMLTYPE;
CREATE TABLE t (testcol XMLTYPE);

SELECT sql_id, sid, serial#
FROM v$sqlarea
WHERE executions = (
  SELECT MAX(executions) FROM v$sqlarea);

SELECT dbid
FROM v$database;

DECLARE
 a DATE := SYSDATE-1;
 b DATE := SYSDATE;
 c NUMBER := 1;
 d NUMBER := 1;
 e NUMBER := 428676178;
 f NUMBER := 1;
 g VARCHAR2(13) := '96g93hntrzjtr';
 h NUMBER := NULL;
 i NUMBER := NULL;
 j NUMBER := 1;
 x XMLTYPE;
BEGIN
  x := prvt_awr_viewer.report_timepicker_xml(a,b,c,d,e,f,g,h,i,j);

  INSERT INTO t VALUES (x);
  COMMIT;
END;
/

SELECT * FROM t;
 
REPORT_TOPSQL_XML
Undocumented prvt_awr_viewer.report_topsql_xml(
start_time        IN DATE,
end_time          IN DATE,
last_refresh_time IN DATE,
inst_id           IN NUMBER,
dbid              IN NUMBER,
is_realtime       IN NUMBER,
top_n_detail      IN NUMBER,
outer_start_time  IN DATE,
outer_end_time    IN DATE,
compress_xml      IN BINARY_INTEGER,
show_sql          IN NUMBER)
RETURN XMLTYPE
conn / as sysdba

CREATE TABLE t (testcol XMLTYPE);

SELECT dbid
FROM v$database;

DECLARE
 a DATE := SYSDATE-1;
 b DATE := SYSDATE;
 c DATE := SYSDATE-1;
 d NUMBER := 1;
 e NUMBER := 428676178;
 f NUMBER := 1;  -- is_realtime
 g NUMBER := 10; -- top_n
 h DATE;
 i DATE;
 j NUMBER := 1;  -- compress
 k NUMBER := 1;  -- show SQL
 x XMLTYPE;
BEGIN
  x := prvt_awr_viewer.report_topsql_xml(a,b,c,d,e,f,g,h,i,j,k);

  INSERT INTO t VALUES (x);
  COMMIT;
END;
/

SELECT * FROM t;
 
RMMETRICS_DATA_XML (new 18.1)
Undocumented prvt_awr_viewer.rmmetrics_data_xml(
p_start_time      IN DATE,
p_end_time        IN DATE,
p_inst_id_low     IN NUMBER,
p_inst_id_high    IN NUMBER,
p_dbid            IN NUMBER,
p_bucket_count    IN NUMBER,
p_bucket_interval IN NUMBER,
p_inst_detail     IN NUMBER,
p_is_rac          IN NUMBER,
p_show_sql        IN NUMBER,
p_plan_name       IN VARCHAR2)
RETURN XMLTYPE;
TBD
 
SET_MAPPING_TYPE
Undocumented prvt_awr_viewer.set_mapping_type(bucket_mapping_type IN VARCHAR2);
TBD

Related Topics
Built-in Functions
Built-in Packages
DBMS_PERF
DBMS_REPORT
DBMS_SQLTUNE
XMLTYPE
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
  DBSecWorx