Oracle PRVT_AWR

Oracle PRVT_AWR_VIEWER Version 19c

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

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

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

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

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;

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

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

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