Monitoring with the AWR Report

Edit online

The information in an Automatic Workload Repository (AWR) report needs to be considered in relation to Sterling B2B Integrator performance.

Important: This information is not meaningful in isolation. You should monitor AWR over a period of time to establish your steady state performance baseline. You should monitor AWR even if Sterling B2B Integrator is performing to your satisfaction; doing so could uncover issues before they become noticeable.
The AWR is used to collect performance statistics, including:
  • Wait events used to identify performance problems
  • Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views
  • Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view
  • Selected system and session statistics from the V$SYSSTAT and V$SESSTAT views
  • Object usage statistics
  • Resource-intensive SQL statements
The repository is a source of information for several other Oracle database features, including:
  • Automatic Database Diagnostic Monitor
  • SQL Tuning Advisor
  • Undo Advisor
  • Segment Advisor

By default, AWR snapshots of the relevant data are taken every hour and retained for seven days. It is recommended to set shorter intervals, for example, 30 minutes, because at longer intervals, the issues are less noticeable. Default values for these settings can be altered with statements such as:

BEGIN

DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(

retention => 43200, -- Minutes (= 30 Days).

Current value retained if NULL.

interval => 30); -- Minutes. Current value retained if NULL.

END;

/

A typical Sterling B2B Integrator deployment contains characteristics of both interactive and batch data processing systems, including a high volume of small transactions that rapidly update the database and periodic long-running transactions that touch many records.

The key areas to inspect in an AWR report are:
  • Top SQL (by CPU or I/O)
  • Top wait events
  • Buffer cache hit ratio (refer to the topic "Instance Efficiency Percentages")
  • I/O contention (hot spots) on disks (refer to the topic "Tablespace IO Stats and File IO Stats")

Instance Efficiency Percentages

When viewing the Instance Efficiency Percentages area, focus on Buffer Hit % and Buffer Nowait % as shown in the following figure. These are the key performance indicators on the Oracle database server. The goal is to keep these values at 95% or greater, which can typically increase efficiency by increasing buffer cache available to the DB server.

Note: When Sterling B2B Integrator performs internal maintenance, these values may be affected.

The following figure shows the buffer pool statistics.

Tablespace IO Stats and File IO Stats

Review the IO Stats area to obtain information about hotspots or drive contention. Analysis of information such as Buffer Wait and Average Buffer Waits can help you determine whether you are getting the best performance from your drives, and identify potential bottlenecks. Separation of objects into their own unique tablespaces can contribute to performance tuning of specific areas.

Wait Events

The AWR report for Sterling B2B Integrator shows typical wait events as displayed in the following figure. To see if the wait events are problematic, compare it to a baseline in your environment. The following figures are an example of what you can see normally for Sterling B2B Integrator.

The SQL statements that should be inspected are SQL ordered by Gets and SQL ordered by Reads as shown in the following figure. Again, compare these with the baseline of the system to determine if any SQL statement is behaving oddly. If yes, refer to the query plans to determine if the query plan is appropriate.

Occasionally, you may observe full table scans on some tables. It is not a cause for concern unless the overall system performance is slow or business level SLAs are not met.