Oracle DBMS_PERF
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 The build script states DBMS_PERF stands for 'DBMS Performance Reports." The output of these functions is not directly human readable but rather intended for Oracle's new Enterprise Manager Express.
AUTHID CURRENT_USER
Dependencies
DBA_HIST_SNAPSHOT DUAL V$DATABASE
DBMS_ASH_INTERNAL GV$INSTANCE V$INSTANCE
DBMS_AUTO_REPORT PRVTEMX_PERF V$SESSION
DBMS_MANAGEMENT_PACKS PRVT_AWRV_METADATA WRI$_REPT_PERF
DBMS_REPORT PRVT_AWR_VIEWER XMLSEQUENCETYPE
DBMS_SQLTUNE PRVT_REPORT_TAGS XMLTYPE
DBMS_SQLTUNE_UTIL0 PRVT_RTADDM XQSEQUENCE
DBMS_SYS_ERROR PRVT_SMGUTIL  
Documented No
First Available 12.1.0.1
Security Model Owned by SYS with EXECUTE granted to the DBA and EM_EXPRESS_BASIC roles
Source {ORACLE_HOME}/rdbms/admin/dbmsperf.sql
Subprograms
 
REPORT_ADDM_WATCHDOG_XML
Retrieves addm_watchdog_xml from the reporting framework repository dbms_perf.report_addm_watchdog_xml(report_id IN NUMBER RETURN XMLTYPE;
TBD
 
REPORT_PERFHUB
Retrieves perfhub XML for all tabs should be used for generating active report dbms_perf.report_perfhub_xml (
is_realtime         IN NUMBER DEFAULT NULL,
outer_start_time    IN DATE DEFAULT NULL,
outer_end_time      IN DATE DEFAULT NULL,
selected_start_time IN DATE DEFAULT NULL,
selected_end_time   IN DATE DEFAULT NULL,
inst_id             IN NUMBER DEFAULT NULL,
dbid                IN NUMBER DEFAULT NULL,
monitor_list_detail IN NUMBER DEFAULT NULL,
workload_sql_detail IN NUMBER DEFAULT NULL,
addm_task_detail    IN NUMBER DEFAULT NULL,
compress_xml        IN BINARY_INTEGER DEFAULT NULL,
report_reference    IN VARCHAR2 DEFAULT NULL,
report_level        IN VARCHAR2 DEFAULT NULL)
RETURN XMLTYPE;
SELECT dbms_perf.report_perfhub
FROM dual;
 
REPORT_PERFHUB_XML
Retrieves perfhub XML for all tabs should be used for generating active report dbms_perf.report_perfhub_xml (
is_realtime         IN NUMBER         DEFAULT NULL,
outer_start_time    IN DATE           DEFAULT NULL,
outer_end_time      IN DATE           DEFAULT NULL,
selected_start_time IN DATE           DEFAULT NULL,
selected_end_time   IN DATE           DEFAULT NULL,
inst_id             IN NUMBER         DEFAULT NULL,
dbid                IN NUMBER         DEFAULT NULL,
monitor_list_detail IN NUMBER         DEFAULT NULL,
workload_sql_detail IN NUMBER         DEFAULT NULL,
addm_task_detail    IN NUMBER         DEFAULT NULL,
compress_xml        IN BINARY_INTEGER DEFAULT NULL,
report_reference    IN VARCHAR2       DEFAULT NULL,
report_level        IN VARCHAR2       DEFAULT NULL)
RETURN XMLTYPE;
SELECT dbms_perf.report_perfhub_xml
FROM dual;
 
REPORT_SESSION
Retrieves Session Details for all tabs for generating active report dbms_perf.report_session(
inst_id             IN NUMBER DEFAULT NULL,
sid                 IN NUMBER DEFAULT NULL,
serial              IN NUMBER DEFAULT NULL,
is_realtime         IN NUMBER DEFAULT NULL,
outer_start_time    IN DATE DEFAULT NULL,
outer_end_time      IN DATE DEFAULT NULL,
selected_start_time IN DATE DEFAULT NULL,
selected_end_time   IN DATE DEFAULT NULL,
dbid                IN NUMBER DEFAULT NULL,
monitor_list_detail IN NUMBER DEFAULT NULL,
report_reference    IN VARCHAR2 DEFAULT NULL,
report_level        IN VARCHAR2 DEFAULT NULL,
type                IN VARCHAR2 DEFAULT 'ACTIVE')
RETURN CLOB;
SELECT dbms_perf.report_session
FROM dual;
 
REPORT_SESSION_XML
Retrieves Session Details XML for all tabs for generating active report dbms_perf.report_session_xml (
inst_id             IN NUMBER         DEFAULT NULL,
sid                 IN NUMBER         DEFAULT NULL,
serial              IN NUMBER         DEFAULT NULL,
is_realtime         IN NUMBER         DEFAULT NULL,
outer_start_time    IN DATE           DEFAULT NULL,
outer_end_time      IN DATE           DEFAULT NULL,
selected_start_time IN DATE           DEFAULT NULL,
selected_end_time   IN DATE           DEFAULT NULL,
dbid                IN NUMBER         DEFAULT NULL,
monitor_list_detail IN NUMBER         DEFAULT NULL,
compress_xml        IN BINARY_INTEGER DEFAULT NULL,
report_reference    IN VARCHAR2       DEFAULT NULL,
report_level        IN VARCHAR2       DEFAULT NULL)
RETURN XMLTYPE;
SELECT dbms_perf.report_session_xml
FROM dual;
 
REPORT_SQL
Retrieves SQL Details text for all tabs for generating active report report_sql(
sql_id              IN VARCHAR2 DEFAULT NULL,
is_realtime         IN NUMBER   DEFAULT NULL,
outer_start_time    IN DATE     DEFAULT NULL,
outer_end_time      IN DATE     DEFAULT NULL,
selected_start_time IN DATE     DEFAULT NULL,
selected_end_time   IN DATE     DEFAULT NULL,
inst_id             IN NUMBER   DEFAULT NULL,
dbid                IN NUMBER   DEFAULT NULL,
monitor_list_detail IN NUMBER   DEFAULT NULL,
report_reference    IN VARCHAR2 DEFAULT NULL,
report_level        IN VARCHAR2 DEFAULT NULL,
type                IN VARCHAR2 DEFAULT 'ACTIVE')
RETURN CLOB;
SELECT dbms_perf.report_sql
FROM dual;
 
REPORT_SQL_XML
Retrieves SQL Details XML for all tabs for generating active report dbms_perf.report_sql_xml (
sql_id              IN VARCHAR2       DEFAULT NULL,
is_realtime         IN NUMBER         DEFAULT NULL,
outer_start_time    IN DATE           DEFAULT NULL,
outer_end_time      IN DATE           DEFAULT NULL,
selected_start_time IN DATE           DEFAULT NULL,
selected_end_time   IN DATE           DEFAULT NULL,
inst_id             IN NUMBER         DEFAULT NULL,
dbid                IN NUMBER         DEFAULT NULL,
monitor_list_detail IN NUMBER         DEFAULT NULL,
compress_xml        IN BINARY_INTEGER DEFAULT NULL,
report_reference    IN VARCHAR2       DEFAULT NULL,
report_level        IN VARCHAR2       DEFAULT NULL)
RETURN XMLTYPE;
SELECT dbms_perf.report_sql_xml
FROM dual;

Related Topics
AWR Report
AutoTrace
DBMS_HPROF
DBMS_PROFILER
DBMS_SQLTUNE
DBMS_STATS
Explain Plan
Packages
TKPROF & Tracing
Tuning

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