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

FINISH_REPORT_CAPTURE Procedure

Ends the complete capture of SQL monitor data that was started with the START_REPORT_CAPTURE Procedure.

REPORT_REPOSITORY_DETAIL Function

Obtains the stored report for a given report ID

REPORT_REPOSITORY_DETAIL_XML Function

Obtains the stored XML report for a given report ID

REPORT_REPOSITORY_LIST_XML Function

Obtains an XML report of the list of SQL Monitor and Real-time ADDM data captured in AWR

START_REPORT_CAPTURE 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.

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;

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

rid

ID of the stored report which returned by the function

type

Desired format of the report. Values can be 'XML', 'TEXT', 'HTML', 'EM' or 'ACTIVE'. The last two options generate a report in the same format called active HTML. Default value is 'XML'.

base_path

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

rid

ID of the stored report which returned by the function

base_path

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

active_since

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.

active_upto

Same as active_since except that it is the end of the time range

snapshot_id

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.

dbid

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

inst_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.

con_dbid

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.

session_id

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.

session_serial

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 session_id parameter.

component_name

Can be 'sqlmonitor' for SQL Monitor data or 'rtaddm' for Real-time ADDM data. If a value is specified then data pertaining only to the specified component will be included in the list report. If no value is specified, no filtering is performed.

key1

Key value relevant to a component. For SQL Monitor, key1 is the SQL ID of the captured SQL statement. If a value is specified, only those data having specified value for key1 are included, else no filtering is performed on key1.

key2

Key value relevant to a component. For SQL Monitor, key2 is the SQL execution ID of the captured SQL statement. If a value is specified, only those data having specified value for key2 are included, else no filtering is performed on key2.

key3

Key value relevant to a component. For SQL Monitor, key3 is the SQL execution start time of the captured SQL statement. If a value is specified, then only those data having specified value for key3 are included, else no filtering is performed on key3.

report_level

Currently only 'TYPICAL' is used

base_path

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.