Oracle | PRVT_AWR

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	GENERATE_BUCKETID_TAG	REPORT_ADDM_XML
AWR_RMMETRICS_XML	GENERATE_BUCKETS_TAG2	REPORT_AWRREP_XML
AWR_SYSMETRICS_XML	GET_BUCKET_MAP	REPORT_AWR_XML
AWR_SYSSTAT_TOTALSTAT_XML	GET_MAPPING_TYPE	REPORT_CONTAINERS_XML
CALCULATE_BUCKETS	GET_RMMETRICS_XML	REPORT_METRICS_XML
CREATE_BUCKET_SNAP_MAP	GET_SESSION_METRICS_XML	REPORT_RAC_XML
CREATE_CONSUMER_GROUP_MAP	GET_SQL_N_PARSE_TIME_XML	REPORT_TIMEPICKER_XML
CREATE_CONTAINER_MAP	GET_STORAGE_METRICS_XML	REPORT_TOPSQL_XML
CREATE_INSTANCE_MAP	GET_SYSMETRICS_XML	REPORT_WORKLOAD
DELTA_STR	GET_VERSION	RMMETRICS_DATA_XML
ERROR_XML	GET_WAIT_TIME_XML	SET_MAPPING_TYPE
FETCH_TOPSQL_XML	INTERVAL_TO_SECOND

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