Oracle | DBMS

Oracle DBMS_PERF Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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;