DBMS_AWRAPP

44 DBMS_AWRAPP_ADMIN

DBMS_AWRAPP_ADMIN contains subprograms for creating and dropping APPMAPs.

This chapter contains the following topics:

44.1 Using DBMS_AWRAPP_ADMIN

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 can be particularly useful to:

Detect and resolve issues faster through precise, top SQL stats of the application
Enhance collaboration with shared, context-rich performance data
Validate changes and monitor top SQL performance continuously using baselines
Support reporting and cost tracking with accurate resource attribution per application

Using application-specific AWR 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.

44.2 Summary of DBMS_AWRAPP_ADMIN Subprograms

DBMS_AWRAPP_ADMIN uses the CREATE_APPMAP and DROP_APPMAP procedures.

Table 44-1 DBMS_AWRAPP_ADMIN Package Subprograms

Subprogram	Description
CREATE_APPMAP Procedure	Creates an APPMAP.
DROP_APPMAP Procedure	Drops an APPMAP.

44.2.1 CREATE_APPMAP Procedure

This procedure creates an APPMAP.

Patterns in the parameter will be used to do the pattern matching as PL/SQL 'LIKE' operation. The character '\' is served as an escaped character. Wildcard characters ('%', '_') can be included in the patteren.

Syntax

DBMS_AWRAPP_ADMIN.CREATE_APPMAP(
   dbid                  IN NUMBER   DEFAULT NULL,
   appmap_name           IN VARCHAR2,
   module_pat            IN VARCHAR2 DEFAULT '%',
   action_pat            IN VARCHAR2 DEFAULT '%',
   client_info_pat       IN VARCHAR2 DEFAULT '%',
   client_identifier_pat IN VARCHAR2 DEFAULT '%');

Parameters

Table 44-2 CREATE_APPMAP Procedure Parameters

Parameter	Description
`db_id`	Database identifier, default to local DBID.
`appmap_name`	APPMAP name.
`module_pat`	Module pattern.
`action_pat`	Action pattern.
`client_info_pat`	Client information pattern.
`client_identifier_pat`	Client identifier pattern

44.2.2 DROP_APPMAP Procedure

This procedure drops an APPMAP.

Syntax

DBMS_AWRAPP_ADMIN.DROP_APPMAP(
   dbid                  IN NUMBER   DEFAULT NULL,
   appmap_name           IN VARCHAR2);

Parameters

Table 44-3 DROP_APPMAP Procedure Parameters

Parameter	Description
`db_id`	Database identifier, default to local DBID.
`appmap_name`	APPMAP name.