Oracle 18cR3 PRVT_AWR

Oracle PRVT_AWR_VIEWER Version 18.3.0.1

General Information

Library Note

Morgan's Library Page Header

For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.

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	ERROR_XML	REPORT_AWR_XML
AWR_RMMETRICS_XML	GENERATE_BUCKETID_TAG	REPORT_CONTAINERS_XML
AWR_SYSMETRICS_XML	GENERATE_BUCKETS_TAG2	REPORT_METRICS_XML
AWR_SYSSTAT_TOTALSTAT_XML	GET_BUCKET_MAP	REPORT_RAC_XML
CALCULATE_BUCKETS	GET_MAPPING_TYPE	REPORT_TIMEPICKER_XML
CREATE_BUCKET_SNAP_MAP	GET_VERSION	REPORT_TOPSQL_XML
CREATE_CONTAINER_MAP	INTERVAL_TO_SECOND	RMMETRICS_DATA_XML
CREATE_INSTANCE_MAP	REPORT_ADDM_XML	SET_MAPPING_TYPE
DELTA_STR	REPORT_AWRREP_XML

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