Loading...

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 (BFS) - the maximum number of data files for each RMAN backup set.

    Archive Files (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 (BFS) or Archive Files (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.

Incremental Backups

Best Practice: If you perform an incremental backup on an Oracle database that is version 10G or later, enable the change tracking feature to enhance the backup performance. Add the following to the RMAN script before you run the backup.

Alter database enable block change tracking using file

Best Practices

Oracle Parameters

Use the Oracle recommendations for the  following parameters.

This learn more about the values to use, see  the "Tuning RMAN Performance" section of the Oracle Database and Recover User's Guide on the Oracle documentation site.

Parameter Recommendation
LARGE_POOL_SIZE Set the LARGE_POOL_SIZE parameter by using the following formula:

LARGE_POOL_SIZE=number of allocated channels * (16 MB + ( 4 *  size of tape buffer))

If you do not set the LARGE_POOL_SIZE parameter, the Oracle software allocates the buffer from the shared pool.

TAPE_IO_SLAVES Set the TAPE_IO_SLAVES parameter to true so that RMAN uses the System Global Area (SGA) for the buffer allocation, which prevents resource contention.

SnapProtect Parameters

Use client side deduplication with compression on the client (ON).

Use 128K for the deduplication block size, but if the data rate changes, you can increase the size to 512K.