Using Automatic Workload Repository for Applications

Application-specific AWR is a transformative feature for Oracle performance management, especially in complex, multi-application environments, because it gives a targeted lens on performance.

Application-specific AWR (Automatic Workload Repository) capture and analyze performance data scoped to individual applications. Instead of sifting through global AWR reports, you can pinpoint performance issues per application (app), improving root cause analysis and tuning efforts. Also, 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.

AWR has a snapshot for performance tracking for specified applications. It includes:

  • Top SQLs for the specified application
  • Time Model Statistics
  • Wait Events
  • Load Profile

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

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; 
/

This step only needs to be done once per application.

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;
/

This step only needs to be done once per application.

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: Acquire a AWR Snapshot

Get AWR Snapshot either manually by DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT or automatically by waiting for database background process to create snapshot for you. This step can be done as many times as users requested.

Once AWR Snapshot starts tracking the specified application, all snapshots created later will contain performance data specific to the application.

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.