Oracle PRVT_AWR_VIEWER
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. 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.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Undocumented
AUTHID CURRENT_USER
Dependencies
DBA_HIST_DATABASE_INSTANCE DUAL PRVT_HDM
DBA_HIST_SNAPSHOT GV$INSTANCE PRVT_REPORT_TAGS
DBMS_ASH_INTERNAL GV$SESSION PRVT_SMGUTIL
DBMS_LOB 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 WRI$_REPT_PERF
DBMS_STANDARD PRVT_AWRV_METADATA XMLAGG
DBMS_SYS_ERROR PRVT_AWRV_VARCHAR64TAB XMLTYPE
DBMS_WORKLOAD_REPOSITORY PRVT_EMX XQSEQUENCE
Documented No
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_RAC_XML
GET_MAPPING_TYPE REPORT_AWRREP_XML REPORT_TIMEPICKER_XML
GET_VERSION REPORT_AWR_XML REPORT_TOPSQL_XML
INTERVAL_TO_SECOND REPORT_METRICS_XML SET_MAPPING_TYPE
 
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,
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_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

Related Topics
DBMS_PERF
DBMS_REPORT
DBMS_SQLTUNE
Packages
XMLTYPE

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved