Oracle Performance Tuning

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.
  • MAXBACKUPSETSIZE - the maximum RMAN backup set size. The default is 0, which is no limit. If you want to set a limit, make sure the value is at least 2000KB.

Multiplexing

Multiplexing indicates how many files the 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 set.

    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.

Partial or Whole Database Restore

Adjust partial or whole database restore performance by modifying the MAXBACKUPSETSIZE parameters. The proper setting depends on whether faster whole database restores or partial restores are required. A smaller MAXBACKUPSETSIZE results in faster partial restores, however, whole database restores are slower. A larger will result in faster whole database restores, but may not be optimal for partial restores. It is generally recommended that you avoid entering too small a value for this setting, which should be at least 2000 KB.

Multisection Backups for Oracle 11g

You can use the section size for Oracle 11g multisection backups to divide large data files into multiple sections and simultaneously backup the data file across multiple channels.

If the section size is larger than the data file size, the multisection backup is not used for the file. If the section size is small enough to produce more than 256 sections, RMAN increases the section size such that 256 sections are created.