SAP for Oracle Performance Tuning

SAP for Oracle backups and restores have 2 components that use the input and output buffer.

  1. Reading and writing the input.
  2. Copying and validating input data blocks to the output buffer.

The slowest of these components can decrease the efficiency of the backup or restore job. You must understand the input and output buffering in your hardware configuration to determine the slowest component. Once you have determined the slowest component, you can adjust RMAN performance tuning parameters to increase the efficiency of backup and restore jobs.

Consider the following factors when determining how to increase the performance of backup and restore jobs.

  • The multiplexing level
  • The restore type (partial or whole database)

Use the above factors, and the information about the input and output buffering in your hardware configuration, to modify the following RMAN performance tuning parameters.

  • FILESPERSET - the maximum number of datafiles and archive files in an RMAN backup set. The default is 32.
  • MAXOPENFILES - the maximum number of files RMAN can simultaneously read. The default is 8.

Multiplexing

Multiplexing indicates how many files the SAP for Oracle channel reads at one time. By default there is no multiplexing, which can result in slower backup times.

The multiplexing level is the lower value of the following parameters.

  • FILESPERSET - the maximum number of datafiles and archive files in an RMAN backup set. The default is 32. When setting this value, there are 2 additional parameters to se.

    Data Files per BFS - the maximum number of data files for each RMAN backup set.

    Archive Files per BFS - the maximum number of archive files for each RMAN backup set.
  • MAXOPENFILES - the maximum number of files RMAN can simultaneously read. You can disable multiplexing by setting this parameter to 1.

A smaller MAXOPENFILES setting results in faster performance on most systems. It should be used in conjunction with the Data Files per BFS or Archive Files per BFS parameters to achieve the most efficient RMAN multiplexing level for optimizing disk buffer allocation. The goal is to set the number of files read simultaneously to fully utilize the output device.

Multiplexing too many files can decrease restore performance depending on the hardware configuration.