Oracle | DBMS

Oracle DBMS_PERF Version 21c

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 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_SQL	PRVT_AWRV_METADATA
AWR_PDB_DATABASE_INSTANCE	DBMS_SQLTUNE	PRVT_AWR_VIEWER
AWR_PDB_PDB_INSTANCE	DBMS_SQLTUNE_UTIL0	PRVT_HDM
AWR_PDB_SNAPSHOT	DBMS_SQLTUNE_UTIL2	PRVT_REPORT_TAGS
AWR_ROOT_CELL_CONFIG_DETAIL	DBMS_SWRF_REPORT_INTERNAL	PRVT_RTADDM
AWR_ROOT_SNAPSHOT	DBMS_SYS_ERROR	PRVT_SMGUTIL
DBA_HIST_DATABASE_INSTANCE	DUAL	V$DATABASE
DBA_HIST_SNAPSHOT	PRVTEMX_CELL	V$INSTANCE
DBMS_ASH_INTERNAL	PRVTEMX_PERF	V$SESSION
DBMS_AUTO_REPORT	PRVT_ASH_OMX	WRI$_REPT_PERF
DBMS_MANAGEMENT_PACKS	PRVT_AWRV_METADATA	XMLTYPE
DBMS_REPORT	PRVT_ASH_OMX	XQSEQUENCE

Documented Yes: Packages and Types Reference

First Available 12.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	REPORT_SESSION	REPORT_SQL
REPORT_PERFHUB	REPORT_SESSION_XML	REPORT_SQL_XML
REPORT_PERFHUB_XML

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;