Oracle PRVT_AWR

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	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