Oracle PRVT_AWR_VIEWER
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 Undocumented
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 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