Oracle DBMS_PERF
Version 12.2.0.1

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

Do you remember when mainframes were replaced by client-server? When client-server was replaced by n-tier architecture? The "Cloud" is a distraction ... DevOps is not. Prepare for the future.
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
AWR_PDB_CELL_CONFIG_DETAIL DBMS_PICKLER PRVT_AWR_VIEWER
AWR_PDB_DATABASE_INSTANCE DBMS_REPORT PRVT_REPORT_TAGS
AWR_PDB_PDB_INSTANCE DBMS_SQL PRVT_RTADDM
AWR_PDB_SNAPSHOT DBMS_SQLTUNE PRVT_SMGUTIL
AWR_ROOT_CELL_CONFIG_DETAIL DBMS_SQLTUNE_UTIL0 V$DATABASE
AWR_ROOT_SNAPSHOT DBMS_SQLTUNE_UTIL2 V$INSTANCE
DBA_HIST_DATABASE_INSTANCE DBMS_SYS_ERROR V$SESSION
DBA_HIST_SNAPSHOT PRIVATE_JDBC WRI$_REPT_PERF
DBMS_ASH_INTERNAL PRVTEMX_CELL XMLTYPE
DBMS_AUTO_REPORT PRVTEMX_PERF XQSEQUENCE
DBMS_MANAGEMENT_PACKS PRVT_AWRV_METADATA  
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
Generates a composite active performance report of the entire database system for a specified time period 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,
report_reference    IN VARCHAR2 DEFAULT NULL,
report_level        IN VARCHAR2 DEFAULT NULL,
type                IN VARCHAR2 DEFAULT 'ACTIVE',
base_path           IN VARCHAR2 DEFAULT NULL)
RETURN CLOB;
SELECT dbms_perf.report_perfhub
FROM dual;
 
REPORT_PERFHUB_XML
Generates a composite active performance report of the entire database system for a specified time period in XML format 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,
base_path           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',
base_path           IN VARCHAR2 DEFAULT NULL)
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,
base_path           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',
base_path           IN VARCHAR2 DEFAULT NULL)
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,
base_path           IN VARCHAR2       DEFAULT NULL)
RETURN XMLTYPE;
SELECT dbms_perf.report_sql_xml
FROM dual;

Related Topics
AWR Report
AutoTrace
Built-in Functions
Built-in Packages
DBMS_AUTO_SQLTUNE
DBMS_HPROF
DBMS_PROFILER
DBMS_SQLTUNE
DBMS_SQLTUNE_UTIL0
DBMS_SQLTUNE_UTIL1
DBMS_SQLTUNE_UTIL2
DBMS_STATS
Explain Plan
TKPROF & Tracing
Tuning
What's New In 12cR1
What's New In 12cR2

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-2017 Daniel A. Morgan All Rights Reserved