43 DBMS_AWRAPP

DBMS_AWRAPP contains subprograms for generating an application specific AWR report.

43.1 Using DBMS_AWRAPP

Application-specific AWR (Automatic Workload Repository) capture and analyze performance data scoped to individual applications.

Application-level high-usage SQL statements may not appear in the standard AWR report if they do not rank among the top SQLs across the entire database. Therefore, generating an application-specific AWR report gives a targeted lens on performance.

Follow the steps to setup your database to generate Application-Specific AWR report:

Step 1: Enable Application-Specific AWR Snapshots

The ENABLE_AWRAPP_SNAPSHOT procedure turns on the collection of application-specific data in AWR snapshots. This must be executed by a user with appropriate privileges (typically a DBA). 

begin 
DBMS_WORKLOAD_REPOSITORY.ENABLE_AWRAPP_SNAPSHOT;
end; 
/

Step 2: Specify Applications to Track

After enabling the feature, define the applications you want to track by inserting their names into a designated table or configuration. Use the DBMS_AWRAPP_ADMIN.CREATE_APPMAP procedure to define an application identifier and associate it with specific application programs. The application identifier acts as a logical group for related programs. This step ensures that AWR collects data only for the specified applications. 

/* define MY_APPMAP_1 with module like '%payroll%', action like '%payroll%', client_info like '%payroll%', and client_identifier like '%payroll%'. */
begin
   DBMS_AWRAPP_ADMIN.CREATE_APPMAP(
        appmap_name           =>  'MY_APPMAP_1' ,
        module_pat            =>  '%payroll%' ,
        action_pat            =>  '%payroll%' ,
        client_info_pat       =>  '%payroll%' ,
        client_identifier_pat =>  '%payroll%' );
end;
/

Step 3: Start tracking the Applications

To start tracking application performance for a specified APPMAP, use DBMS_WORKLOAD_REPOSITORY.INCLUDE_APPMAP. This procedure ensures that AWR begins collecting performance data for the application, and the data is included in regular system-wide AWR snapshots. 

begin
   DBMS_WORKLOAD_REPOSITORY.INCLUDE_APPMAP(APPMAP_NAME =>  'MY_APPMAP_1' );
   DBMS_WORKLOAD_REPOSITORY.INCLUDE_APPMAP(APPMAP_NAME =>  'MY_APPMAP_2' );
end;
/

After tracking the application, generate the application-specific AWR report. The AWR snapshots used in Step 4 should be collected after completing Steps 1 through 3.

Step 4: Generate the report

An AWR application report is generated using DBMS_AWRAPP.GENERATE_AWRAPP_REPORT. 

spool awr_application_report.html
select dbms_awrapp.generate_awrapp_report(2171, 4, 5, '1', 'html') from dual;
spool off

Step 5: Analyze the report

The report captures application-specific SQL performance data collected over a selected time interval.

The following data dictionary views can be used to retrieve the performance data of the application snapshots:

  • DBA_AWRAPP_APPMAP_CONTROL - Displays the control information (application performance tracking status) for the Workload Repository.
  • DBA_AWRAPP_APPMAP_DEF - Displays information about all application maps in the database.
  • DBA_AWRAPP_INFO (or AWR_PDB_AWRAPP_INFO) - Displays the mapping between an AWR application snapshot and its application map in the Workload Repository.
  • DBA_AWRAPP_SQLSTAT (or AWR_PDB_AWRAPP_SQLSTAT) - Displays information about SQL statistics specific to applications captured in the Workload Repository.
  • DBMS_AWRAPP_ADMIN.DROP_AWRAPP - Drops a specified APPMAP definition from the database
  • DBMS_WORKLOAD_REPOSITORY.EXCLUDE_APPMAP - Stops performance tracking for the specified application in the AWR.

43.2 Summary of DBMS_AWRAPP Subprograms

DBMS_AWRAPP contains the following functions.

Table 43-1 DBMS_AWRAPP Package Subprograms

Subprogram Description

GENERATE_AWRAPP_REPORT Function

Generates an AWR that is specific to the database over a snapshot range.

43.2.1 GENERATE_AWRAPP_REPORT Function

This function generates an AWR that is specific to the database over a snapshot range.

Syntax

DBMS_AWRAPP.GENERATE_AWRAPP_REPORT(
         dbid        IN NUMBER,
         beg_snap    IN NUMBER,
         end_snap    IN NUMBER,
         inst_val    IN VARCHAR2 DEFAULT 'ALL',
         format      IN VARCHAR2 DEFAULT 'JSON',
         options     IN CLOB     DEFAULT NULL)
RETURN CLOB;

Note:

Users with DB_DEVELOPER_ROLE have privilege to call this function.

The application report contains the performance data specific to the tracked applications within a range of time. The report has the following sections

  • Report Header
  • Application Information
  • Top SQL for the specified application.
  • Top SQL Details

Parameters

Table 43-2 GENERATE_AWRAPP_REPORT Function Parameters

Parameter Description

dbid

Database identifier.

beg_snap

Identifier for the starting snapshot.

end_snap

Identifier for the ending snapshot.

inst_val

Insance number

format

The format for the output can be either text or html.

options

Options for the CLOB output.

Example 43-1 Generating an application specific report from AWR.

DBMS_AWRAPP.GENERATE_AWRAPP_REPORT(
    dbid        =>   111111,
    beg_snap    =>   1,
    end_snap    =>   3,
    inst_val    => '1',
    format      =>   'html');