DBMS_PERF

The DBMS_PERF package provides and interface to generate active reports for monitoring database performance

REPORT_PERFHUB Function	Generates a composite active performance report of the entire database system for a specified time period
REPORT_SESSION Function	Generates a performance report for a specific database session where a session is identified by inst_id, sid, and serial_num.
REPORT_SQL Function	Generates an active performance report for a particular SQL statement identified by its sql_id.

REPORT_PERFHUB Function

This function generates a composite active performance report of the entire database system for a specified time period.

Syntax

DBMS_PERF.REPORT_PERFHUB (

   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;

REPORT_SESSION Function

This function produces a performance report for a specific database session where a session is identified by inst_id, sid, andserial_num.
If any of those parameters are missing, then the report is for the current session.
The session-level performance report contains the following tabs:

·       Summary - This tab contains key identifiers and attributes of the session along with a summary of its activity data. It also contains a list of SQLs, PLSQL blocks and Database Operations (DBOP) executed by that session that were monitored by Real-time SQL Monitoring.

·       Activity - This tab shows activity broken down by wait classes for this session. The data used for this chart is fetched from Active Session History (ASH).

·       Metrics - This tab shows charts for certain key metrics for the selected session over time and is only available in historical mode. Some of the metrics shown are CPU usage, PGA usage, IO Throughput and IO Requests.

Syntax

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;

REPORT_SQL Function

This function generates an active performance report for a particular SQL statement identified by its sql_id.
The SQL-level performance report contains the following tabs:

·       Summary - This tab contains an overview of the SQL statement with key attributes like the SQL text, user name, sessions executing it, and related information. It also contains a Plans tab which shows statistics and activity for each distinct plan for this SQL statement found in memory and in the AWR.

·       Activity - This tab shows activity broken down by wait classes for this SQL statement. The data used for this chart is fetched from Active Session History (ASH).

·       Execution Statistics - This tab shows statistics and activity for each distinct plan for this statement along with a graphical and tabular representation of the plan.

·       Monitored SQL - All executions of this SQL statement that were monitored by Real-time SQL Monitoring are listed in this tab.

·       Plan Control - This tab shows information about SQL Profiles and SQL Plan Baselines if they exist for this SQL statement.

·       Historical Statistics - This tab is available only in Historical mode. It contains statistics, such as number of executions, number of I/Os, rows processed, and other information produced over time for different execution plans. This information is retrieved from AWR.

Syntax

DBMS_PERF.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;