Oracle PRVT_AWR_VIEWER
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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 21c
What's New In 23c

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