Oracle PRVT_AWR_VIEWER Demos

Oracle PRVT_AWR_VIEWER Version 12.1.0.1

General Information

Library Note

Morgan's Library Page Footer

The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Purpose

Undocumented

AUTHID

CURRENT_USER

Dependencies

DBA_HIST_DATABASE_INSTANCE	GV$INSTANCE	PRVT_REPORT_TAGS
DBA_HIST_SNAPSHOT	GV$SESSION	PRVT_SMGUTIL
DBMS_ASH_INTERNAL	PLITBLM	SYS_IXMLAGG
DBMS_MANAGEMENT_PACKS	PRVTEMX_DBHOME	V$DATABASE
DBMS_PERF	PRVTEMX_MEMORY	WRI$_REPT_ADDM
DBMS_REPORT	PRVT_AWRV_INSTTAB	WRI$_REPT_AWRV
DBMS_SQLTUNE	PRVT_AWRV_MAP	WRI$_REPT_EMX_PERF
DBMS_SQLTUNE_UTIL0	PRVT_AWRV_MAPTAB	XMLAGG
DBMS_STANDARD	PRVT_AWRV_METADATA	XMLSEQUENCETYPE
DBMS_SYS_ERROR	PRVT_AWRV_VARCHAR64TAB	XMLTYPE
DUAL	PRVT_HDM	XQSEQUENCE

Documented

First Available

12.1.0.1

Security Model

Owned by SYS with

Source

{ORACLE_HOME}/rdbms/admin/prvsawrv.plb

Subprograms

GET_BUCKET_MAP	REPORT_ADDM_XML	REPORT_TIMEPICKER_XML
GET_MAPPING_TYPE	REPORT_AWR_XML	REPORT_TOPSQL_XML
GET_VERSION	REPORT_METRICS_XML	SET_MAPPING_TYPE
INTERVAL_TO_SECOND	REPORT_RAC_XML

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;

SELECT prvt_awr_viewer.interval_to_second(
FROM dual;

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-1)
FROM dual;

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,
comopess_xml IN NUMBER)
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_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;

SET_MAPPING_TYPE

Undocumented

prvt_awr_viewer.set_mapping_type(bucket_mapping_type IN VARCHAR2);

TBD