Oracle PRVT_AWR_VIEWER
Version 21c

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_SYS_ERROR PRVT_REPORT_TAGS
AWR_PDB_SNAPSHOT DBMS_WORKLOAD_REPOSITORY PRVT_SMGUTIL
AWR_PDB_STAT_NAME DUAL SYS_IXMLAGG
AWR_ROOT_DATABASE_INSTANCE GV$INSTANCE V$INSTANCE
AWR_ROOT_SNAPSHOT GV$SESSION V$OSSTAT
AWR_ROOT_STAT_NAME PLITBLM V$PDBS
DBMS_ASH_INTERNAL PRVTEMX_DBHOME V_$SYSTEM_PARAMETER2
DBMS_LOB PRVTEMX_MEMORY WRI$_REPT_ADDM
DBMS_MANAGEMENT_PACKS PRVTEMX_RSRCMGR WRI$_REPT_AWRV
DBMS_PERF PRVT_AWRV_INSTTAB WRI$_REPT_DBHOME
DBMS_REPORT PRVT_AWRV_MAP WRI$_REPT_EMX_PERF
DBMS_SQLTUNE PRVT_AWRV_MAPTAB WRI$_REPT_PERF
DBMS_SQLTUNE_UTIL0 PRVT_AWRV_METADATA XMLAGG
DBMS_SQLTUNE_UTIL1 PRVT_AWRV_VARCHAR64TAB XMLTYPE
DBMS_SQLTUNE_UTIL2 PRVT_EMX XQSEQUENCE
DBMS_STANDARD PRVT_HDM  
Documented No
First Available 12.1
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/prvsawrv.plb
Subprograms
 
ADJUST_REALTIME_INPUT_TIMES
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);
DECLARE
 pstart DATE := SYSDATE;
 pend DATE := SYSDATE+1;
 psrc VARCHAR2(30);
BEGIN
  prvt_awr_viewer.adjust_realtime_input_times(pstart, pend, psrc, 10);
  dbms_output.put_line(psrc);
END;
/
history

PL/SQL procedure successfully completed.
 
AWR_RMMETRICS_XML
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
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
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
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
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_CONSUMER_GROUP_MAP (new 21c)
Undocumented prvt_awr_viewer.create_consumer_group_map(
p_con_id     IN     NUMBER,
P_awr_period IN OUT sys.prvt_awrv_metadata,
p_cgrp_map   IN OUT sys.prvt_awrv_insttab);
TBD
 
CREATE_CONTAINER_MAP
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
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
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
Undocumented prvt_awr_viewer.error_xml(
function_call IN VARCHAR2,
sqlc          IN NUMBER,
sqle          IN VARCHAR2,
addl_info     IN VARCHAR2)
RETURN XMLTYPE;
TBD
 
FETCH_TOPSQL_XML (new 21c)
Undocumented prvt_awr_viewer.fet_topsql_xml(
start_time       IN DATE,
end_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,
db_tz            IN VARCHAR2,
is_omx           IN NUMBER)
RETURN XMLTYPE;
TBD
 
GENERATE_BUCKETID_TAG
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
Undocumented prvt_awr_viewer.generate_buckets_tag2(p_buckets IN VARCHAR2)
RETURN VARCHAR2;
SELECT prvt_awr_viewer.generate_buckets_tag2('Testing')
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;

      DBID
----------
2140826538


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;
/
1
90000

PL/SQL procedure successfully completed.
 
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_MAPPING_TYPE
-----------------
 
 
GET_RMMETRICS_XML (new 21c)
Undocumented prvt_awr_viewer.get_rmmetrics_xml(
is_realtime     IN NUMBER,
start_time      IN DATE,
end_time        IN DATE,
bucket_count    IN NUMBER,
bucket_interval IN NUMBER,
inst_id_low     IN NUMBER,
inst_id_high    IN NUMBER,
awr_period      IN sys.prvt_awrv_metadata,
show_sql        IN NUMBER)
RETURN XMLTYPE;
TBD
 
GET_SESSION_METRICS_XML (new 21c)
Undocumented prvt_awr_viewer.get_session_metrics_xml(
inst_id_low   IN NUMBER,
inst_id_high IN NUMBER,
show_sql     IN NUMBER)
RETURN XMLTYPE;
TBD
 
GET_SQL_N_PARSE_TIME_XML (new 21c)
Undocumented prvt_awr_viewer.get_sql_n_parse_time_xml(
is_realtime     IN NUMBER,
start_time_UTC  IN DATE,
end_time_UTC    IN DATE,
bucket_count    IN NUMBER,
bucket_interval IN NUMBER,
inst_id_low     IN NUMBER,
inst_id_high    IN NUMBER,
awr_period      IN sys.prvt_awrv_metadata,
show_sql        IN NUMBER)
RETURN XMLTYPE;
TBD
 
GET_STORAGE_METRICS_XML (new 21c)
Undocumented prvt_awr_viewer.get_storage_metrics_xml(
service_type IN VARCHAR2,
inst_id_low  IN NUMBER,
inst_id_high IN NUMBER,
show_sql     IN NUMBER)
RETURN XMLTYPE;
TBD
 
GET_SYSMETRICS_XML (new 21c)
Undocumented prvt_awr_viewer.get_sysmetrics_xml(
is_realtime     IN NUMBER,
start_time_utc  IN DATE,
end_time_utc    IN DATE,
bucket_count    IN NUMBER,
bucket_interval IN NUMBER,
inst_id_low     IN NUMBER,
inst_id_high    IN NUMBER,
awr_period      IN sys.prvt_awrv_metadata,
show_sql        IN NUMBER)
RETURN XMLTYPE;
TBD
 
GET_VERSION
Undocumented prvt_awr_viewer.get_version RETURN NUMBER;
SELECT prvt_awr_viewer.get_version
FROM dual;

GET_VERSION
-----------
          1
 
GET_WAIT_TIME_XML (new 21c)
Undocumented prvt_awr_viewer.get_wait_time_xml(
is_realtime     IN NUMBER,
start_time_utc  IN DATE,
end_time_utc    IN DATE,
bucket_count    IN NUMBER,
bucket_interval IN NUMBER,
inst_id_low     IN NUMBER,
inst_id_high    IN NUMBER,
awr_period      IN sys.prvt_awrv_metadata,
show_sql        IN NUMBER)
RETURN XMLTYPE;
TBD
 
INTERVAL_TO_SECOND
Undocumented prvt_awr_viewer.interval_to_second(v IN INTERVAL DAY TO SECOND) RETURN NUMBER;
SELECT prvt_awr_viewer.interval_to_second(TO_DSINTERVAL('100 10:00:00'))
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;
SELECT prvt_awr_viewer.interval_to_second(SYSTIMESTAMP, SYSTIMESTAMP-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
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);

Table created.

SELECT dbid
FROM v$database;

      DBID
----------
2140826538


DECLARE
 a DATE := SYSDATE-1;
 b DATE := SYSDATE;
 c NUMBER := 1;
 d NUMBER := 2140826538;
 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;
/

PL/SQL procedure successfully completed.

SELECT * FROM t;

TESTCOL
----------------------------------------------------------------------------
<report db_version="21.0.0.0.0" elapsed_time="0.09" cpu_time="0.07" cpu_cores="1" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="0" packs="2" service_type="0">
  <report_id>
    <![CDATA[/orarep/perfpage/main%3fdbid%3d2140826538%26end_time%
3d06%3a20%3a2021%2019%3a20%3a24%
26inst_id%3d1%26report_level%3dTYPICAL%26start_time%3d06%3a19%3a2021%2019%
3a20%3a24]]>
  </report_id>
  <report_parameters>
    <start_time>06/19/2021 19:20:24</start_time>
    <end_time>06/20/2021 19:20:24</end_time>
    <inst_id>1</inst_id>
    <dbid>2140826538</dbid>
    <bucket_max_count>5</bucket_max_count>
    <bucket_interval>1</bucket_interval>
    <report_level>TYPICAL</report_level>
    <show_sql>1</show_sql>
  </report_parameters>
...
             select nvl2(buckets,
               xmlelement("stattype",
               xmlattributes('allochist' as "name"),
               xmltype(:l_total_mem),
               nvl2(stat_id,
               xmlelement("stat_info",stat_id),
               null),
               xmlelement("buckets",
               xmlattributes(:l_bucket_interval as "bucket_interval",
               :l_bucket_count as "bucket_count",
               :l_min_time as "start_time",
               :l_max_time as "end_time",
               :l_duration as "duration"),
               buckets)),
               null)
               from (
                 select xmlagg(
                 xmlelement("bucket",
                 xmlattributes(bucket_id as "bucket_id"),stats)
                 order by bucket_id) buckets,
                 xmlagg(stat_id) stat_id
                 from (
                   select bucket_id,
                   xmlagg(stat_id order by id) stat_id,
                   xmlagg(xmlelement("stat",
                   xmlattributes(id as "id",
                   round(bytes,2) as "value"))
                   order by id) stats
                   from (
                     select bucket_id, id, bytes,
                     nvl2(component,
                     xmlelement("stat",
                     xmlattributes(id as "id",
                     component as "name",
                     alloc as "alloc",
                     'bytes' as "unit",
                     case when alloc != 'paging'
                     then '1048576'
                     else null
                     end as "factor")),
                     null) stat_id
                     from alloc_hist)
                   group by bucket_id))]]></script>
</report>
 
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 dbid
FROM v$database;

      DBID
----------
2140826538


DECLARE
 a DATE := SYSDATE-1;
 b DATE := SYSDATE;
 c DATE := SYSDATE;
 d NUMBER := 1;
 e NUMBER := 2140826538;
 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;

      DBID
----------
2140826538


DECLARE
 a DATE := SYSDATE-1;
 b DATE := SYSDATE;
 c NUMBER := 1;
 d NUMBER := 1;
 e NUMBER := 2140826538;
 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;

      DBID
----------
2140826538


DECLARE
 a DATE := SYSDATE-1;
 b DATE := SYSDATE;
 c DATE := SYSDATE-1;
 d NUMBER := 1;
 e NUMBER := 2140826538;
 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;
 
REPORT_WORKLOAD (new 21c)
Undocumented prvt_awr_viewer.report_workload(
service_type IN VARCHAR2,
is_realtime  IN NUMBER
dbid         IN NUMBER,
inst_id      IN NUMBER,
start_time   IN DATE,
end_time     IN DATE,
report_level IN VARCHAR2,
db_tz        IN VARCHAR2,
show_sql     IN NUMBER)
RETURN XMLTYPE;
TBD
 
RMMETRICS_DATA_XML
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 19c
What's New In 20c-21c

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