8 Oracle Business Activity Monitoring Performance

To achieve the highest performance for Oracle Business Activity Monitoring, you should maintain a database on its own hardware dedicated to the Oracle Business Activity Monitoring system. Additionally, following certain database administration practices maximizes the throughput of incoming Oracle Business Activity Monitoring transactions. These practices are based on specific testing and observations of the Oracle Business Activity Monitoring system. General database administration practices, as described in the Database Performance Tuning Guide, also apply to a database dedicated to Oracle Business Activity Monitoring.

This chapter includes the following sections:

Managing the Redo Log Files
Avoiding Frequent Log Switches and Checkpoints
Tuning the System Global Area
Database Re-Organization in the Presence of Deletion Activity

8.1 Managing the Redo Log Files

When Oracle Business Activity Monitoring receives input data at a high rate, the Oracle Business Activity Monitoring Active Data Cache (ADC) sends the data it receives to the database with little or no loss of input bandwidth incurred in the ADC server. Thus, the limiting factor for input data throughput is the rate at which the database can capture data it receives from the ADC server. At high data rates, the database throughput is limited by the ability of the database to write redo log records to disk, also called a log file sync. When an application, in this case the ADC server, commits data at a rate that is faster than the rate at which redo log data can be written to disk, subsequent requests to commit or rollback a transaction must wait.

The overall goal of redo log file tuning is to reduce or eliminate log file sync waits since they limit insertion throughput. There are two main strategies to reduce the number of log file sync waits in an Oracle database:

You can increase the I/O bandwidth available to redo log sync activity. To do this, move the redo log files onto their own physical disk so they do not have to compete with other I/O activity in the system.
You can modify the database insert workload to increase the number of rows inserted into the database per transaction committed. This strategy involves modifying the application to issue fewer commits. By batching a larger number of operations in each transaction, the demand for commit processing is reduced. The downside of such an approach, however, is that when there is a system failure, all data input since the previous commit is lost. With frequent commits, a small amount of data is lost when there is a system failure. With less frequent commits, a larger amount of recently captured data is lost. This tradeoff between performance and reliability implies that selecting a larger number of input operations per transaction must be consistent with the reliability requirements of the application. In the case of Oracle BPEL Process Manager sending data to Oracle Business Activity Monitoring, batching is an option that can be used and even if higher numbers are used for batching, there is no data loss in the case of Oracle Business Activity Monitoring failure, because Oracle BPEL Process Manager can retry the operations.

Redo log sync bandwidth may also be increased by striping the redo log files across multiple disks, using either a RAID or operating system based striping mechanism. Because log sync activity involves sequential writing, a large stripe depth such as 32K or 64K provides the best performance.

Note:

Avoid RAID-5 usage for redo logs because it is known that redo log sync activity performs poorly when the redo logs are stored on a RAID-5 storage system. If you must use RAID-5, consider removing one or more disks from the RAID-5 configuration on which you store the redo log groups.

8.2 Avoiding Frequent Log Switches and Checkpoints

An Oracle database must have at least two redo log files so that when one file is full, the database can switch to another and the space in the file can be reclaimed asynchronously. Because these log switches have a significant negative impact on database performance, the redo log files should be sized to reduce the frequency of the switches to an acceptably low rate. A good rule of thumb is to have no more than 1 log switch every 20 minutes.

You can determine the rate of log switches by setting the database initialization parameter LOG_CHECKPOINTS_TO_ALERT to TRUE. This writes log switch events to the database alert log with a timestamp, so that you can monitor the alert log for an excessive frequency of log switches.

Incremental checkpoints also degrade performance. In order to limit the amount of the redo log that must be processed during database crash recovery (and thus limit the time to startup a database after a system failure), the database checkpoints the redo log at regular intervals. These checkpoint events are written to the alert log when the LOG_CHECKPOINTS_TO_ALERT parameter is set to TRUE.

Normally, the frequency of incremental checkpoints is set implicitly. When you set the FAST_START_MTTR_TARGET initialization parameter, this specifies the target instance startup time for crash recovery after a crash. Setting a large value for FAST_START_MTTR_TARGET allows for infrequent incremental checkpoints, which benefits runtime performance (this is at the expense of crash recovery time during instance startup). Setting a small value for FAST_START_MTTR_TARGET causes frequent incremental checkpoints that can decrease the required crash recovery time during instance startup, at the expense of runtime performance. Application requirements should dictate the analysis of this tradeoff. When runtime performance is the dominant consideration, setting the initialization parameter, FAST_START_MTTR_TARGET to be large enough to completely eliminate incremental checkpoints is desirable. In this case, checkpoints are made at the time of a log switch.

8.3 Tuning the System Global Area

Oracle Business Activity Monitoring database performance is sensitive to the size specified for the db_cache and log_buffer in the database System Global Area (SGA), as well as the overall SGA size (the size specified should be relative to the size of the objects managed within the SGA).

Note:

The optimal SGA size is dependent on the complete database workload; thus, these guidelines for SGA tuning for an Oracle Business Activity Monitoring database only apply if the database is dedicated to Oracle Business Activity Monitoring data.

Configure a dedicated Oracle Business Activity Monitoring database with a minimum SGA size of 1024MB. Set the SGA size using the SGA_MAX_SIZE initialization parameter. Use either SGA autotuning to set the values of memory pools within the SGA, or use fixed sized pools. To specify autotuning, set the SGA_TARGET initialization parameter to the same value as SGA_MAX_SIZE.

You may obtain better performance by manually setting the internal SGA memory pools. Autotuning of the SGA is disabled by setting the SGA_TARGET initialization parameter to 0. For an installation with an SGA maximum size set to 1024MB, the settings shown in Example 8-1 are a good starting point, when autotuning is disabled.

Example 8-1 Sample SGA Initialization with Autotuning Disabled

db_cache_size = 820M
java_pool_size = 16M
large_pool_size = 16M
streams_pool_size = 16M
shared_pool_size = 144M
log_buffer = 10485760

The database cache or buffer pool holds recently accessed data from the database, to avoid repeated reading of the same data pages. In the case of Oracle Business Activity Monitoring database, database insert performance depends on fast primary key constraint checking, which is accomplished by virtue of the indexes created for each primary key being held in the database cache. The buffer pool hit ratio is a good indicator of a database cache that is too small. In general, the hit ratio should be above 95%. For an Oracle Business Activity Monitoring database, you should try to maintain a hit ratio at 99% or higher; this is generally is feasible with workloads that primarily insert incoming data to the database. With workloads having substantial report querying, 95% is a good target for the buffer pool hit ratio. You can obtain the buffer pool hit ratio using the following query:

SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,
   1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) 
   "Hit Ratio" FROM V$BUFFER_POOL_STATISTICS;

Oracle Business Activity Monitoring is also sensitive to the log buffer size. When the database has a log file sync wait, the log buffer holds any pending requests for commit processing. When there are a large number of log file sync waits, the log buffer may become full. When a request for the log buffer is unable to be fulfilled, the requester continues to make the request after short delay, called a log buffer retry.

Log buffer retries have a significant negative impact on performance. For workloads with a data input batch size in the thousands or for any workload with a significant number of log buffer retries, the log buffer size might need to be increased.

Use the following query to monitor the buffer retries in the database:

SELECT NAME, VALUE FROM V$SYSSTAT
  WHERE NAME = 'redo buffer allocation retries';

This metric is normally measured by collecting the value at the start of a workload, and again at the end of a workload, and then the difference between the two values is the number of log buffer retries required for running the workload. If the database is restarted at the beginning of a test workload, then the beginning value is 0, and only the end value is needed. Over about a 10 minute interval, the number of log buffer retries should be under 100 (in the ideal case, the value would be 0). Values in the 100-300 range only moderately impact performance, but larger values can have a sizable impact.

It is important to note that whenever you increase the size of an SGA component, such as the log buffer or the database cache, it is also important to increase the total SGA size by the same amount (the same amount increase as the component is increased). Otherwise, the increased component takes the memory away from other objects in the SGA.

Note:

The SGA size is rounded up to the next higher multiple of 4MB at database startup.

8.4 Database Re-Organization in the Presence of Deletion Activity

When Oracle Business Activity Monitoring is installed, the Oracle Business Activity Monitoring tablespace, ORACLEBAM, is created to hold datasets and metadata. When a Oracle Business Activity Monitoring user creates a dataset, an Oracle Database table is create to store objects in the dataset. This table has a system-generated primary key, named SysIterID. When a Oracle Business Activity Monitoring user creates Oracle Business Activity Monitoring indexes on columns in a Oracle Business Activity Monitoring dataset, corresponding indexes are created on the corresponding table in the database.The Oracle Business Activity Monitoring tablespace is created with automatic segment space management enabled. This helps to manage free space in segments mapped to Oracle Business Activity Monitoring tables, and enables database re-organization to take place without taking down the system. However, it does not completely eliminate the need for manual re-organization of the database, particularly after a significant accumulation of deletion activity

After a significant amount of deletion activity, the segments mapped to a Oracle Business Activity Monitoring dataset table may become sparsely filled with data, this condition is usually called internal fragmentation and leads to a very significant increase in the amount of I/O required to perform operations on the table. When there is internal fragmentation, any indexes created on the table, including the index for the primary key, will not be structured in an optimal, balanced configuration after a significant number of deletion operations. Thus, after a significant number of deletion operations, you should perform a database re-organize to improve the structure of the indexes and better pack the rows of the table in a smaller number of data pages. You could also write a script to delete the obsolete Oracle Business Activity Monitoring data and perform the reorganization as part of the deletion job.

The procedure for re-organizing segments for a Oracle Business Activity Monitoring dataset table is to drop the primary key for the table, drop indexes on the table, enable row movement for the table, shrink space on the table, deallocate unused space in the table, re-create the primary key for the table, re-create indexes for the table, and disable row movement for the table. Example 8-2 shows a sample script that re-organizes segments for two Oracle Business Activity Monitoring datasets, Call_Detail, and Call_Agg that are stored in database tables _Call_Detail and _Call_Agg.

Example 8-2 Sample Oracle Business Activity Monitoring Table Reorganization

alter table "ORABAM"."_Call_Detail" drop primary key;
alter table "ORABAM"."_Call_Agg" drop primary key;
<drop indexes on "ORABAM"."_Call_Detail">
<drop indexes on "ORABAM"."_Call_AggÓ>
alter table "ORABAM"."_Call_Detail" enable row movement;
alter table "ORABAM"."_Call_Agg" enable row movement;

< Note: include deletion activity here, if it is added as part of the script>

alter table "ORABAM"."_Call_Detail" shrink space;
alter table "ORABAM"."_Call_Agg" shrink space;
alter table "ORABAM"."_Call_Detail" deallocate unused;
alter table "ORABAM"."_Call_Agg" deallocate unused;
alter table "ORABAM"."_Call_Detail" disable row movement;
alter table "ORABAM"."_Call_Agg" disable row movement;
alter table "ORABAM"."_Call_Detail" add primary key ("SysIterID");
alter table "ORABAM"."_Call_Agg" add primary key ("SysIterID");

<rebuild indexes on "ORABAM"."_Call_Detail">
<rebuild indexes on "ORABAM"."_Call_Detail">

Note:

If your Oracle Business Activity Monitoring tables are configured to run with row movement enabled, then you need to change the script shown in Example 8-2 to remove the disable row movement commands.

This reorganization procedure could be scheduled at regular intervals, such as once a week or once a month, or after significant deletion activity, or with deletion included in the script as noted.