Instructor's Notes

(If applicable)

• Lesson 12 – Tuning Oracle RMAN Jobs
• Author: Dave Anderson, SkillBuilders
• www.skillbuilders.com
• Last Update: October 17, 2007

Instructor's Notes

(If applicable)

• This lesson provides a solid introduction to tuning RMAN backup and recovery operations. You will learn:
• How to set reasonable goals.
• How fast RMAN can go – by reviewing statistics from Oracle Corp’s benchmark tests.
• What factors impact the speed of the operations.
• How to capture statistics to monitor the performance of the operations. (i.e. What tools to use.)
• How to check the status of an RMAN operation – including determining if a job is waiting for a resource.
• Most of the theories presented herein are validated with tests that show the improvement of a change.

Instructor's Notes

(If applicable)

• How fast will your backups go? Well, that depends on factors such as the transfer rates of your disk and tape devices (the connection from the tape/disk device to the computer can also be a limiting factor). You will not be able to make an RMAN operation go faster than the cumulative speed of your disk and/or tape devices.

Instructor's Notes

(If applicable)

• Oracle conducted a performance test a the Sun Customer Benchmark Center in October of 2001. The results are documented in the white paper “Oracle Recovery Manager: Performance Testing at Sun Customer Benchmark Center” (An Oracle White Paper October 2001). This white paper is available at http://technet.oracle.com. (Search using the document title.)
• Oracle used the “Stripe and Mirror Everything” (S.A.M.E.) volume management as described in the white paper “Optimal Storage Management Made Easy” by Juan Loaiza. Refer to this white paper (also available at http://technet.oracle.com) and the section on S.A.M.E. later in this lesson.

Instructor's Notes

(If applicable)

• In this laboratory environment, Oracle was able to achieve backup speeds of approximately 530 GB per hour using 20 output tape drives. Adding 20 additional tape drives increased the throughput to approximately 1TB per hour (approximately doubling the throughput), showing near linear scalability with the addition of tape drives.

Instructor's Notes

(If applicable)

• Let's discuss the factors that will impact the performance of your RMAN operations. I will list them here (with a brief note on each), then look at each in more detail and incorporate these factors in case studies throughout the remainder of this lesson.
• The performance factors include:
• Disk configuration – The number of disks you have containing the Oracle data to be backed up will have a definite impact on RMAN performance. Using an extreme example, imagine the performance bottleneck if only a single device was available to hold all your Oracle data. The backup or restore operation could not go any faster that the transfer rate of that one device. The SAME document summarizes the issue as follows: “stripe data to equalize the workload across disks and eliminate hotspots” and “stripe data to enable many disks to serve requests for any subset of data”. Striping will have a positive impact on performance. Device transfer rate will limit performance. The SAME methodology also finds that, for availability, mirroring will perform better that RAID-5.



• Notes for this slide continue on the next page…

Instructor's Notes

(If applicable)

• Output device configuration – Whether you use disk or tape for your output device, the number of available devices will have a large impact on RMAN performance. Of course, the specifications of the output devices will have an impact. You will not be able to backup or recover faster than the cumulative transfer rate of these devices.
• RMAN settings – Several RMAN settings such as the degree of parallelism and multiplexing will have a performance impact. Of course, we will study them in this lesson.
• Database settings – We will need to tune memory areas and allocate I/O slaves if we use synchronous devices such as tapes (to implement asynchronous operations to those devices).
• Processor speed – How fast can your processor issue I/O requests and verify Oracle data? Though the I/O factors are usually the problem, CPU speed can be a factor.
• Existing load on server – Of course, the existing load on your server will impact how long an RMAN job will take. Usually there’s not a lot we can do about that.

Instructor's Notes

(If applicable)

• Oracle 10g provides a new view call V$RMAN_BACKUP_JOB_DETAILS. This view contains elapsed time and read rate for RMAN jobs and should make the PL/SQL package runstats_pkg presented later in this chapter unnecessary.
• I will present several test cases in this lesson. The tools we will use to capture RMAN runtime statistics are:
• V$BACKUP_SYNC_IO – This view contains statistics on “ongoing and recently completed” RMAN synchronous I/O operations. We will see that we actually do not want to see statistics here because we will prefer asynchronous I/O. So we will just query this view to insure there is nothing here – confirming that we are using asynchronous I/O.
• V$BACKUP_ASYNC_IO - This view contains statistics on “ongoing and recently completed” RMAN asynchronous I/O operations.
• RUNSTATS.SQL – This script (supplied with the course book) is derived from a Tom Kyte script (see http://asktom.oracle.com). This script uses V$MYSTAT to measure the total overall elapsed time for the RMAN job.
• Refer to the Oracle Database Reference manual for detailed information on these V$ views.

Instructor's Notes

(If applicable)

• RMAN reports its activity to the V$SESSION_LONGOPS view. Therefore we can query this view to determine the status of a job and also determine if a job is hung (perhaps waiting for a tape mount). The example shown above displays activity that is not complete (sofar <> totalwork). It also shows the LAST_UPDATE. If a job is taking longer than you think it should, query again after a couple of minutes. If the SOFAR value or the LAST_UPDATE value has not changed, try to find a reason that the session is hung. The V$SESSION_WAIT view can assist here (though it will not show a tape media manager event).
• A more detailed version of this query – including a query of V$SESSION_WAIT – is supplied in the script file V$SESSION_LONGOPS.SQL.

Instructor's Notes

(If applicable)

• We see in this example the Oracle reports the wait event as “sbtbackup”. I have been waiting for 1000 seconds (16 minutes) for a tape to be mounted.
• Chapter 12 of the Oracle 10g Backup and Recovery Advanced User’s Guide and Chapter 15 of the Oracle9i Recovery Manager User’s Guide lists all the possible tape-related events:
• sbtinit sbtopen sbtread sbtwrite sbtbackup.
• See the supplied script V$SESSION_LONGOPS.SQL for a working sample of this code.

Instructor's Notes

(If applicable)

• Though the cost of very large disk devices (e.g. 80GB) is always dropping, and thus it is easy to place more of your data on fewer disks, this is not recommended for best overall Oracle performance. The Oracle benchmark referenced earlier in this lesson showed that the “stripe and mirror everything” (S.A.M.E.) configuration provided the best overall performance. The white paper states “The SAME configuration produces close to optimal performance for all workloads: OLTP, Warehouse and Batch.”
• The S.A.M.E. methodology calls for:
• Striping all Oracle files over many disks (the more the better) and using a 1MB stripe size. Note that the 1MB stripe size has to do with disk transfer speed and seek rate and could change as these characteristics change with advancement in disk technology.
• Placing high use application files and system files (e.g. redo logs) on the outer half of the disk device (because the transfer rate on the outer half of disk devices is faster than the inner half).
• Using disk mirroring rather than RAID-5 (for availability) because RAID-5 introduces unnecessary overhead.
• Volume managers such as the “Veritas Volume Manager” should be used because they provide tools for easy striping of files across many disks (up to hundreds of disks).

Author’s Note

Unlike subsequent theories discussed later in this lesson, the limitations of my lab environment did not permit actual testing of the theories in SAME. I am relying on the tests conducted by Oracle and documented in the white paper “Oracle Recovery Manager: Performance Testing at Sun Customer Benchmark Center” for the conclusions described on this page.

Instructor's Notes

(If applicable)

• When using RMAN, the number of allocated channels (manually or configured) determines the degree of parallelization. Parallelism in RMAN is the number of server processes (i.e. channels) that are reading and writing Oracle data. If one channel is allocated, the degree of parallelization is one; if two channels are allocated the degree of parallelization is two, etc.
• The general guideline is to allocate one channel per output device. This is because allocating more channels than output devices will often just create a bottleneck by sending more data (issuing more I/O requests) than the device can handle, thus the additional channels/processes will wait. While one channel per output device is common, this is not always the case. Backup to disk devices that have high transfer rates might warrant more channels. The best advice is to test. See the next page for an example of how to test the performance of an RMAN operation.
• A point to remember is that each channel allocated to a backup creates a backup set. Too many backup sets can become a management headache.

Instructor's Notes

(If applicable)

• This test invokes a backup to two disk devices using two channels. Because Oracle9i RMAN did not have the ability to show elapsed time by RMAN job, I execute the routines “RS_START” and “RS_STOP” to capture performance statistics (see the statistics on the following page). These scripts are derived from a script I found on the Tom Kyte web site, http://asktom.oracle.com/~tkyte/runstats.html. The scripts are provided with this course book.
• To run similar tests on your server, review, modify where necessary and execute the following supplied script and log files:
• MORE_CHANNELS_THAN_DISKS.SQL
• TWO_CHANNELS.RMAN
• TEN_CHANNELS.RMAN
• TWO_CHANNELS.LOG
• TEN_CHANNELS.LOG
• The scripts required to capture and display elapsed time are:
• RUNSTATS.SQL
• DISPLAY_ELAPSED_TIME.SQL


• NOTE: Oracle 10g Release 2 provides a new view V$RMAN_BACKUP_JOB_DETAILS which should negate the need for the technique demonstrated here.

Instructor's Notes

(If applicable)

• My tests showed that adding more channels than available devices increased elapsed time. The first test (script “TWO_CHANNELS.RMAN”) allocated two channels for two output disk devices. The elapsed time for the backup is 84 seconds (rounded). Backing up the same exact data using ten channels required 99 seconds, an 18% increase in elapsed time.
• Note that these tests were run on a server dedicated to RMAN testing; no other processes where active. The specifications of the my test environment are:
• Dell Poweredge 2400, 1 Pentium 733 Mhz CPU
• Linux OS (Red Hat, 8.0)
• Oracle9i (9.2.0.4)
• The input devices are three 9 GB Seagate Cheetah disk drives, tied with RAID 0, 40MB/second transfer rate
• The output devices are two 36 GB Seagate Cheetah disk drives, 40MB/second transfer rate
• One Quantum (Benchmark) DLT1 tape drive, 40GB cap, 80GB compressed, sustained transfer rate 3MB / second (6MB compressed), burst transfer rate 20MB / second.

Instructor's Notes

(If applicable)

• A major performance benefit is the ability of most operating systems support for asynchronous I/O disk devices. This means that a channel can issue an I/O to write a block to the disk, then, while that operation is taking place, move on to the next operation. You will definitely need asynchronous I/O for best performance. To check if you are achieving asynchronous I/O for your disk devices, query the V$BACKUP_SYNC_IO view after running a backup. If it contains data related to the backup operations channels, you are using synchronous I/O. THIS IS AN ISSUE YOU WILL WANT TO ADDRESS. If this is the case, set initialization parameter DBWR_IO_SLAVES to a non-zero value and allocate additional memory to the large pool. This will cause RMAN to spawn four I/O slaves for backup and restore operations, simulating asynchronous I/O. Refer to Chapter 14 of the Oracle9i Recovery Manager User’s Guide for more information on this issue. (Initialization parameter DISK_ASYNCH_IO should be set to TRUE if your system supports asynchronous I/O to disk.)
• Tape devices, on the other hand, are synchronous devices. Thus, if your backing up or restoring from tapes, you will want to enable support for asynchronous I/O with the BACKUP_TAPE_IO_SLAVES=TRUE initialization parameter. See the next page for an example.

Instructor's Notes

(If applicable)

• This example shows how to enable asynchronous I/O to tape device(s). This is accomplished with the BACKUP_TAPE_IO_SLAVES parameter. The Oracle9i Database Reference says this about this parameter: “BACKUP_TAPE_IO_SLAVES specifies whether I/O server processes (also called slaves) are used by the Recovery Manager to back up, copy, or restore data to tape. When the value is set to true, Oracle uses an I/O server process to write to or read from a tape device.”
• The BACKUP_TAPE_IO_SLAVES parameter cannot be changed for the current instance. You must restart the database to put the change into effect.
• Note that it is also important to allocate memory to the large pool for the tape buffers; otherwise the memory is taken from the shared pool, which can cause unnecessary contention in for shared pool resources. Chapter 14 of the Oracle9i Recovery Manager User’s Guide contains this formula for calculating the space required in the shared pool for tape buffers:
• RMAN large pool requirement* =
• number_of_allocated_channels * (16 MB + ( 4 * size_of_tape_buffer ) )
• * Add the result of this calculation to your existing large pool allocation.

Instructor's Notes

(If applicable)

• One of the methods of monitoring performance of RMAN operations is the V$BACKUP_ASYNC_IO view. Each row containing TYPE “AGGREGATE” provides statistics on a channel operation, including elapsed time and data transfer speed (“effective bytes per second”). To test the performance gain of enabling asynchronous I/O to tape, first I ran a backup using synchronous I/O.
• The supplied script V$BACKUP_SYNC_IO.SQL generated the first report shown in the slide:
• sys@TEST> select to_char(close_time, 'mm/dd hh:mi') as time, type,
• 2 elapsed_time as et,effective_bytes_per_second as eb
• 3 from v$backup_sync_io
• 4 where close_time > sysdate - (1/24 / 4) /* within last .25 hour


• The supplied script V$BACKUP_ASYNC_IO.SQL revealed the statistics for the backup using asynchronous I/O:
• -- Display performance data for a channel:
• select to_char(close_time, 'mm/dd hh:mi') as time, type,
• elapsed_time as et, effective_bytes_per_second as eb
• from v$backup_async_io
• where close_time > sysdate - (1/24 / 4)
• and type = 'AGGREGATE';

Instructor's Notes

(If applicable)

• To help identify RMAN processes, use the SET COMMAND ID command to populate the CLIENT_INFO column of the V$SESSION view. By default, the processes might look like this:


• However, we can see that SID 18 process is not clearly marked. This was the tape IO slave started because I had the parameter BACKUP_TAPE_IO_SLAVE set to TRUE to enable asynchronous writes to the tape device.
• See the supplied script SET_COMMAND_ID.RMAN for this code.
• Warning: On my client, placing the SET command before the ALLOCATE command caused a fatal RMAN error.

Instructor's Notes

(If applicable)

• Tape device data compression can have a very good impact on RMAN performance.
• The PARMS parameter of the ALLOCATE CHANNEL command is used to send a command to the tape media manager. In my environment I use the Legato Single Server Edition, which supports the NSR_COMPRESSION environment variable to enable compression. Refer to the lesson Getting Started with Tapes for more information on tape manager environment variables.

Instructor's Notes

(If applicable)

• My tests show that enabling compression generates excellent improvement both in bytes per second (+36%) and elapsed time (-27%).
• Next, let’s look at the effect of multiplexing (MAXOPENFILES and FILESPERSET parameters).

Instructor's Notes

(If applicable)

• Multiplexing has an effect on RMAN performance. RMAN multiplexing is the simultaneous read of multiple datafiles and the writing of commingled blocks (i.e. blocks from different datafiles) to the same backup piece (file). Since a restore will have to read the entire backup piece, it makes since to multiplex only those files likely to be restored together, if possible.
• RMAN multiplexing is controlled with either the BACKUP command FILESPERSET parameter or the ALLOCATE/CONFIGURE CHANNEL command MAXOPENFILES parameter.

Instructor's Notes

(If applicable)

• A benefit of multiplexing can be realized when the output device is not receiving enough data to keep it streaming. If the tape device is continually starting, stopping, rewinding and starting again, it is not streaming. THIS IS AN ISSUE YOU WILL WANT TO ADDRESS. This can happen, for example, if we are conducting an incremental backup but only a few blocks have changed. RMAN still has to read the entire input file(s), but only writes a few blocks – thus the output device is often found idle, waiting for data. Multiplexing files together increases the chances that the incremental backup will find changed blocks to write out – and keep the output device streaming.
• Multiplexing has a dark side. Too much multiplexing, especially in environments where the data is already striped across many disks (preferably in 1MB stripes), will decrease throughput. Additionally, if a backup piece has 10 datafiles multiplexed within it, yet only 1 needs to be restored, your forcing RMAN to read entirely through a large file containing all 10 files during the restore operation.

Instructor's Notes

(If applicable)

• The level of multiplexing has an effect on the size and number of memory buffers allocated. When less than or equal to 4, RMAN creates 16 1MB buffers, which work really nicely with the 1MB stripes recommended in the SAME methodology.
• This memory is usually allocated from the PGA (because disks usually support asynchronous I/O by default and the DBWR_IO_SLAVES parameter is set to 0). Using the Oracle9i automatic PGA tuning feature will help here. This feature is enabled by setting initialization parameters WORKAREA_SIZE_POLICY=AUTO and PGA_AGGREGATE_TARGET=“n”, where n is a number, in bytes, that you want the database to set as the upper limit for all combined work areas. Oracle will dynamically allocate memory to active queries, attempting to keep the total at or below the upper limit. Use the PGA advisory to tune the PGA_AGGREGATE_TARGET parameter.

Instructor's Notes

(If applicable)

• When your data is striped (according to the SAME methodology) it is best to disable or severely limit RMAN multiplexing. The reason is that the striping causes RMAN to automatically allocate some number of buffers, equal to the number of stripes, and issue the same number of read I/O requests. Thus, in essence, we get a sort-of multiplexing: RMAN issues parallel reads to different disk devices, just not to different datafiles.

Instructor's Notes

(If applicable)

• I did not have a striped disk configuration to test with, so I will demonstrate only the effect of multiplexing in a non-striped environment. Here we see three tests, using multiplexing levels of 4, 8 and 16. We see that the multiplexing level of 8 was best in my environment: a 5% reduction in elapsed time (see the previous “tape compression” test, elapsed time 8100, multiplexing level 1) and a 5% increase in bytes per second.


• The following is supplied script V$BACKUP_ASYNC_IO.SQL, which created the result shown in the slide:
• select to_char(close_time, 'mm/dd hh:mi') as time, type,
• maxopenfiles,
• elapsed_time as et,effective_bytes_per_second as eb
• from v$backup_async_io
• where close_time > sysdate - (1/24 / 4) /* within last .25 hour */
• and type = 'AGGREGATE'
• /

Instructor's Notes

(If applicable)

• Increasing the tape buffer size can help keep a tape device streaming – so if your device is doing lots of starts and stops, this could help (so could increasing the level of multiplexing).
• The Oracle manual recommends setting BLKSIZE to “a little less” than the block size of the tape. (See chapter 14 of the Oracle9i Recovery Manager User’s Guide for more information. ) However, note the following error I received when the block size was not a multiple of the physical block size:
• RMAN> run { allocate channel t1 type sbt MAXOPENFILES 1
• 2> PARMS="BLKSIZE=1000000";
• 3> backup force tag='pgatest' tablespace ts5; }


• RMAN-00571: ==================================================
• RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ====
• RMAN-00571: =================================================
• RMAN-03009: failure of allocate command on t1 channel at 11/29/2003
• ORA-19554: error allocating device, device type: SBT_TAPE, device na
• ORA-27018: BLKSIZE is not a multiple of the minimum physical block size
• Additional information: 1000000
• Additional information: 16384
• Note that adjusting the tape buffer size did not help in my environment (I tested 512K and 1M buffers). That could have been because the media manager in use – Legato LSSV – did not allow me to adjust the physical tape block size.

Instructor's Notes

(If applicable)

• RMAN can consume too much of your servers resources and slow down other critical applications. In this situation, you will want to take your foot of the RMAN accelerator. This is accomplished with the RATE parameter of the ALLOCATE CHANNEL command. The Oracle9i Recovery Manager Reference says this about the RATE parameter: “This parameter sets an upper limit for bytes read so that RMAN does not consume too much disk bandwidth and degrade performance.”
• My tests showed the slowing affect of the RATE parameter. Note the difference between the two runs:
• sys@TEST> @v$backup_async_io

Instructor's Notes

(If applicable)

• In Oracle 10g, the DURATION parameter of the BACKUP command can be used in place of the RATE parameter. The DURATION parameter will do one of two things for you:
• If MINIMIZE LOAD is specified, RMAN will reduce resource consumption so that the job requires the amount of time specified. This is like the RATE parameter, but perhaps more intuitive.
• IF MINIMIZE LOAD is not specified, the time specified is the maximum time the BACKUP can execute. RMAN will kill the job when it reaches the specified time limit.

Instructor's Notes

(If applicable)

• If you configure backup optimization on (the default is off), RMAN will skip datafiles, archive logs or backup sets under certain situations. For example, if the DBID and SCN of a datafile match the DBID and SCN of a backup of a datafile, RMAN “might” skip it (i.e. omit it from the backup). I say that it might skip it because it also takes into account the configured retention policy. For example, if your retention policy is redundancy 2, RMAN skips the datafile only if 3 backups already exist. There are many factors involved in backup optimization. Refer to Chapter 5 of the Oracle9i Recovery Manager User’s Guide for complete details.
• Note that backup optimization can be overridden with the FORCE parameter of the BACKUP command.

Instructor's Notes

(If applicable)

• In this lesson we learned that the performance of RMAN jobs is affected by
• Disk layout
• Parallelism
• Asynchronous I/O
• Compression
• Multiplexing
• Memory
• Tape Buffer Size
• Slowing RMAN Down
• Backup Optimization
• CPU speed
• Server load
• We also learned that asynchronous IO, parallelism and compression are often the big positive factors. Tuning RMAN jobs involves using V$ views to view statistics, testing, tweaking RMAN parameters and re-testing.

Instructor's Notes

(If applicable)

• Tuning RMAN Workshop
  1. Gather performance data (elapsed time / effective bytes/second) on the backup operation supplied in WORKSHOP_TUNE_DISK_SETUP.SQL (if you have disks for output devices) or WORKSHOP_TUNE_TAPE_SETUP.SQL (if you use tape for output devices).
  2. Use the techniques learned in this lesson to increase the performance of the operation.