120 DBMS_PERF
The DBMS_PERF package provides and interface to generate active reports for monitoring database performance
See Also:
Oracle Database PL/SQL Language Reference for more information about "Avoiding SQL Injection in PL/SQL"
This chapter contains the following topics:
120.1 DBMS_PERF Overview
The DBMS_PERF package provides an interface for generating database performance reports. All subprograms return an active report and these reports can be generated at the system level, session level or at SQL level.
120.3 Summary of DBMS_PERF Subprograms
This table lists the DBMS_PERF
subprograms and briefly describes them.
Table 120-1 DBMS_PERF Package Subprograms
Subprogram | Description |
---|---|
Generates a composite active performance report of the entire database system for a specified time period |
|
Generates a performance report for a specific database session where a session is identified by |
|
Generates an active performance report for a particular SQL statement identified by its |
120.3.1 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;
Parameters
Table 120-2 REPORT_PERFHUB Function Parameters
Parameter | Description |
---|---|
|
If 1, then real-time. If |
|
Start time of outer period shown in the time selector. If
|
|
End time of outer period shown in the time selector. If
|
|
Start time period of selection. If
|
|
End time period of selection. If
|
|
Instance ID to for which to retrieve data
|
|
|
|
Top N in SQL monitor list for which to retrieve SQL monitor details.
|
|
Top N in Workload Top SQL list to retrieve monitor details,
|
|
Maximum N latest ADDM tasks to retrieve
|
|
Must be |
|
|
|
Report type:
|
|
URL path for HTML resources since flex HTML requires access to external files. This is only valid for type=' |
Usage Notes
-
Once a time period is selected, the performance information is collected and presented based on performance subject areas.
-
The time period can be real-time or historical.
-
When real-time data is selected, more granular data is presented because data points are available every minute.
-
When historical data is selected, more detailed data (broken down by different metrics) is presented, but the data points are averaged out to the Automatic Workload Repository (AWR) interval (usually an hour).
-
Different tabs are available in the Performance Hub, depending on whether is_real-time is 1 for real time mode or 0 for historical mode.
120.3.2 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;
Parameters
Table 120-3 REPORT_SESSION Function Parameters
Parameter | Description |
---|---|
|
Instance ID to for which to retrieve data. If |
|
Session ID for which to retrieve performance. If |
|
Serial# of session. If |
|
If 1, then real-time. If |
|
Start time of outer period shown in the time selector. If
|
|
End time of outer period shown in the time selector. If
|
|
Start time period of selection. If
|
|
End time period of selection. If
|
|
|
|
Top N in SQL monitor list for which to retrieve SQL monitor details.
|
|
Must be |
|
|
|
Report type:
|
|
URL path for HTML resources since flex HTML requires access to external files |
120.3.3 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;
Parameters
Table 120-4 REPORT_SQL Function Parameters
Parameter | Description |
---|---|
|
|
|
If 1, then real-time. If |
|
Start time of outer period shown in the time selector. If
|
|
End time of outer period shown in the time selector. If
|
|
Start time period of selection. If
|
|
End time period of selection. If
|
|
Instance ID to for which to retrieve data. If |
|
|
|
Top N in SQL monitor list for which to retrieve SQL monitor details.
|
|
Must be |
|
|
|
Report type:
|
|
URL path for HTML resources since flex HTML requires access to external files |