37 DBMS_AUTO_REPORT
The DBMS_AUTO_REPORT
package provides an interface to view SQL Monitoring and Real-time Automatic Database Diagnostic Monitor (ADDM) data that has been captured into Automatic Workload Repository (AWR). It also provides subprograms to control the behavior of how these data are captured to AWR.
See Also:
Oracle Database SQL Tuning Guide for more information about reporting database operations
This chapter contains the following topics:
37.1 DBMS_AUTO_REPORT Overview
This package provides an interface to view SQL Monitoring and Real-time ADDM data that has been captured into AWR. It also provides subprograms to control the behavior of how these data are captured to AWR.Captured data are stored in AWR and exposed via 2 views: DBA_HIST_REPORTS
and DBA_HIST_REPORTS_DETAILS
.
37.2 DBMS_AUTO_REPORT Security Model
This package is available to PUBLIC
and performs its own security checking.
37.3 Summary of DBMS_AUTO_REPORT Subprograms
This table describes the parameters of the DBMS_AUTO_REPORT
package subprograms.
Table 37-1 DBMS_AUTO_REPORT Package Subprograms
Subprogram | Description |
---|---|
Ends the complete capture of SQL monitor data that was started with the START_REPORT_CAPTURE Procedure. |
|
Obtains the stored report for a given report ID |
|
Obtains the stored XML report for a given report ID |
|
Obtains an XML report of the list of SQL Monitor and Real-time ADDM data captured in AWR |
|
Captures SQL monitor data of any newly monitored SQLs every minute since the last run of the capture cycle, and stores it in AWR. |
|
PURGE_ALL_REPORTS Function | Purges all SQL Monitor content from WRP$_ tables associated with a single DBID (if specified), or from all non-local DBIDs if no DBID is specified. |
PURGE_REPORTS_BY_RETENTION Function | Purges SQL Monitor reports older than the retention period from WRP$_ tables associated with a specified container DBID, or if no DBID is specified, then it purges old reports from all non-local containers. |
37.3.1 FINISH_REPORT_CAPTURE Procedure
This procedure ends the complete capture of SQL monitor data that was started with the START_REPORT_CAPTURE
procedure.
After calling this subprogram, capture of data continues every minute except that it is not captured for all active SQLs but only for those deemed important, namely the top 5 SQLs (by elapsed time, or elapsed time*DOP in case of PQ) whose monitoring has completed.
Syntax
DBMS_AUTO_REPORT.FINISH_REPORT_CAPTURE;
Related Topics
37.3.2 REPORT_REPOSITORY_DETAIL Function
This procedure obtains the stored report for a given report ID in the specified format such as XML or HTML.
Syntax
DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL ( rid IN NUMBER DEFAULT NULL, type IN VARCHAR2 DEFAULT 'XML', base_path IN VARCHAR2 DEFAULT NULL) RETURNS CLOB
Parameters
Table 37-2 REPORT_REPOSITORY_DETAIL Function Parameters
Parameter | Description |
---|---|
|
ID of the stored report which returned by the function |
|
Desired format of the report. Values can be |
|
Unused/Non-operative |
Return Values
The persisted report for the given record ID
37.3.3 REPORT_REPOSITORY_DETAIL_XML Function
This procedure obtains the stored XML report for a given report ID.
Syntax
DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL_XML ( rid IN NUMBER DEFAULT NULL, base_path IN VARCHAR2 DEFAULT NULL) RETURNS XMLTYPE
Parameters
Table 37-3 REPORT_REPOSITORY_DETAIL_XML Function Parameters
Parameter | Description |
---|---|
|
ID of the stored report which returned by the function |
|
Unused/Non-operative |
Return Values
The persisted XML report for the given record ID
37.3.4 REPORT_REPOSITORY_LIST_XML Function
This procedure obtains an XML report of the list of SQL Monitor and Real-time ADDM data captured in AWR.
The input parameters can be used to select and restrict which captured data will be included in the list report. All parameters are optional.
Syntax
DBMS_AUTO_REPORT.REPORT_REPOSITORY_LIST_XML ( active_since IN DATE DEFAULT NULL, active_upto IN DATE DEFAULT NULL, snapshot_id IN NUMBER DEFAULT NULL, dbid IN NUMBER DEFAULT NULL, inst_id IN NUMBER DEFAULT NULL, con_dbid IN NUMBER DEFAULT NULL, session_id IN NUMBER DEFAULT NULL, session_serial IN NUMBER DEFAULT NULL, component_name IN VARCHAR2 DEFAULT NULL, key1 IN VARCHAR2 DEFAULT NULL, key2 IN VARCHAR2 DEFAULT NULL, key3 IN VARCHAR2 DEFAULT NULL, report_level IN VARCHAR2 DEFAULT 'TYPICAL', base_path IN VARCHAR2 DEFAULT NULL) RETURNS XMLTYPE
Parameters
Table 37-4 REPORT_REPOSITORY_LIST_XML Function Parameters
Parameter | Description |
---|---|
|
Start of a time range used to select data. When a time range is specified, only those data are included in the list that were active during the time range. When no value is specified the time range is chosen as the last 24 hours ending at the current system time. |
|
Same as |
|
If a value is specified, only those data captured during the specified snapshot ID are included in the list report. If no value is specified, no filtering is performed on snapshot ID. |
|
If a value is specified, only those data captured for the specified database ID are included in the list report. If no value is specified, no filtering is performed on database ID |
|
If a value is specified, only those data captured on the specified instance number are included in the list report. If no value is specified, no filtering is performed on the instance ID. |
|
If a value is specified, only those data captured on the specified container DBID are included in the list report. If no value is specified, no filtering is performed on the container DBID. |
|
If a value is specified, only those data captured for the specified session ID are included in the list report. If no value is specified, no filtering is performed on session ID. |
|
If a value is specified, only those data captured for the specified session are included in the list report. If no value is specified, no filtering is performed on session serial number. This parameter should be used in conjunction with the |
|
Can be |
|
Key value relevant to a component. For SQL Monitor, |
|
Key value relevant to a component. For SQL Monitor, |
|
Key value relevant to a component. For SQL Monitor, |
|
Currently only |
|
Unused/Non-operative |
37.3.5 START_REPORT_CAPTURE Procedure
This procedure captures SQL monitor data of any newly monitored SQLs every minute since the last run of the capture cycle, and stores it in AWR.
Every capture cycle attempts to capture data for SQLs that are not currently executing or queued. This is a complete capture since data of all newly monitored SQLs is captured. It continues to run every minute until it is explicitly ended with the FINISH_REPORT_CAPTURE Procedure. In the case of a RAC system, the capture will start on each node of the cluster.
Syntax
DBMS_AUTO_REPORT.START_REPORT_CAPTURE;
37.3.6 PURGE_ALL_REPORTS Function
This function purges all SQL Monitor content from WRP$_ tables associated with a single DBID (if specified), or from all non-local DBIDs if no DBID is specified.
Syntax
DBMS_AUTO_REPORT.PURGE_ALL_REPORTS (
dbid IN NUMBER DEFAULT NULL,
);
This function does not return a value.
37.3.7 PURGE_REPORTS_BY_RETENTION Function
This function purges SQL Monitor reports older than the retention period from WRP$_ tables associated with a specified container DBID, or if no DBID is specified, then it purges old reports from all non-local containers.
Only the reports that are older than the retention period are purged.
Syntax
DBMS_AUTO_REPORT.PURGE_REPORTS_BY_RETENTION (
dbid IN NUMBER DEFAULT NULL,
);
This function does not return a value.