Views for RMAN Performance Tuning
V$RMAN_BACKUP_JOB_DETAILS Reports information about backup jobs
V$BACKUP_ASYNC_IO Displays RMAN asynchronous I/O performance information for currently running and recently completed backup and restore operations
V$BACKUP_SYNC_IO Displays RMAN synchronous I/O performance information for currently running and recently completed backup and restore operations
V$PROCESS Lists currently active processes
V$SESSION Displays session information for current sessions
V$SESSION_LONGOPS Shows progress of RMAN backup, restore, and
recovery operations
V$RECOVERY_PROGRESS Shows progress of RMAN or user-managed recovery operations
V$SESSION_WAIT Displays events or resources for which sessions are
currently waiting
Identifying RMAN Processes
SQL> SELECT b.sid, b.serial#, a.spid, b.client_info
2 FROM v$process a, v$session b
3 WHERE a.addr = b.paddr
4 AND b.client_info LIKE '%rman%';
This output shows that there is currently one RMAN channel allocated:
SID SERIAL# SPID CLIENT_INFO
---------- ---------- ------------ -------------------------
146 29 4376 rman channel=ORA_DISK_1
Measuring Backup Performance
SQL> SELECT session_recid, input_bytes_per_sec_display,
2 output_bytes_per_sec_display,
3 time_taken_display, end_time
4 FROM v$rman_backup_job_details
5 ORDER BY end_time;
Y
ou should see output similar to the following:
SESSION_RECID INPUT_BYTES_PER OUTPUT_BYTES_PE TIME_TAKEN_DISPLAY END_TIME
------------- --------------- --------------- -------------------- ---------
1096 8.60M 7.69M 00:14:25 20-DEC-06
1101 1.88M 1.78M 00:42:03 21-DEC-06
1110 9.59M 8.56M 00:14:56 22-DEC-06
1114 9.75M 8.71M 00:14:52 23-DEC-06
1116 10.73M 9.58M 00:14:31 24-DEC-06
View Name Recovery Catalog View
V$RMAN_BACKUP_SUBJOB_DETAILS RC_RMAN_BACKUP_SUBJOB_DETAILS
V$BACKUP_SET RC_BACKUP_SET
V$BACKUP_SET_DETAILS RC_BACKUP_SET_DETAILS
V$BACKUP_SET_SUMMARY RC_BACKUP_SET_SUMMARY
V$BACKUP_PIECE RC_BACKUP_PIECE
V$BACKUP_PIECE_DETAILS RC_BACKUP_PIECE_DETAILS
V$BACKUP_DATAFILE RC_BACKUP_DATAFILE
V$BACKUP_DATAFILE_DETAILS RC_BACKUP_DATAFILE_DETAILS
V$BACKUP_DATAFILE_SUMMARY RC_BACKUP_DATAFILE_SUMMARY
V$BACKUP_FILES RC_BACKUP_FILES
V$BACKUP_COPY_DETAILS RC_BACKUP_COPY_DETAILS
V$BACKUP_COPY_SUMMARY RC_BACKUP_COPY_SUMMARY
V$BACKUP_REDOLOG RC_BACKUP_REDOLOG
V$BACKUP_ARCHIVELOG_DETAILS RC_BACKUP_ARCHIVELOG_DETAILS
V$BACKUP_ARCHIVELOG_SUMMARY RC_BACKUP_ARCHIVELOG_SUMMARY
V$BACKUP_CONTROLFILE_DETAILS RC_BACKUP_CONTROLFILE_DETAILS
V$BACKUP_CONTROLFILE_SUMMARY RC_BACKUP_CONTROLFILE_SUMMARY
V$BACKUP_SPFILE RC_BACKUP_SPFILE
V$BACKUP_SPFILE_DETAILS RC_BACKUP_SPFILE_DETAILS
V$BACKUP_SPFILE_SUMMARY RC_BACKUP_SPFILE_SUMMARY
Monitoring RMAN Job Progress
SQL>gt; select sid, serial#, sofar, totalwork, opname,
2 round(sofar/totalwork*100,2) "% Complete"
3 from v$session_longops
4 where opname LIKE 'RMAN%'
5 and opname NOT LIKE '%aggregate%'
6 and totalwork != 0
7 and sofar <> totalwork;
SID SERIAL# SOFAR TOTALWORK OPNAME % Complete
---------- ---------- ---------- ---------- ---------------------------------------------------------------- ----------
2838 43105 902382 902956 RMAN: archived log backup 99,94
Oracle also provides a view, V$RECOVERY_PROGRESS, that reports on just the recovery
operations (either RMAN or user-managed). This view is a subview of the V$SESSION_
LONGOPS view. To report on the progress of a recover command, you can run a SQL
query as shown here:
SQL> select type, item, units, sofar, total from v$recovery_progress;
TYPE ITEM UNITS SOFAR TOTAL
--------------- ------------------------- ---------- ---------- ----------
Media Recovery Log Files Files 3 229
Media Recovery Active Apply Rate KB/sec 3425 0
Media Recovery Average Apply Rate KB/sec 3861 0
Media Recovery Redo Applied Megabytes 449 0
Media Recovery Last Applied Redo SCN+Time 1415101 0
Media Recovery Active Time Seconds 81 0
Media Recovery Apply Time per Log Seconds 0 0
Media Recovery Checkpoint Time per Log Seconds 0 0
Media Recovery Elapsed Time Seconds 119 0
The V$SESSION_LONGOPS view contains information about long-running jobs (SQL statements,RMAN operations, and so on) in your database. You can use this view to monitor the progress of RMAN backup, restore, and recovery operations.
select s.client_info,
sl.opname,
sl.message,
sl.sid, sl.serial#, p.spid,
sl.sofar, sl.totalwork,
round(sl.sofar/sl.totalwork*100,2) "% Complete"
from v$session_longops sl, v$session s, v$process p
where p.addr = s.paddr
and sl.sid=s.sid
and sl.serial#=s.serial#
and opname LIKE 'RMAN%'
and opname NOT LIKE '%aggregate%'
and totalwork != 0
and sofar <> totalwork;
If your backup or restore command is interacting with several backup sets, then you may want to report at an aggregate level. To report at the aggregate level, run a query similar to the one shown next:
SQL> select sid, serial#, sofar, totalwork,opname,
2 round(sofar/totalwork*100,2) "% Complete"
3 from v$session_longops
4 where opname LIKE 'RMAN%aggregate%'
5 and totalwork != 0
6 and sofar <> totalwork;
Identifying I/O Bottlenecks
SQL> SELECT sid, serial, filename, type, elapsed_time, effective_bytes_per_second
2 FROM v$backup_async_io
3 WHERE close_time > sysdate – 7;
If you have identified your SID and SERIAL number (see recipe 16-1), you can specifically query for records associated with your current session:
SQL> SELECT filename, sid, serial, close_time, long_waits/io_count as ratio
2 FROM v$backup_async_io
3 WHERE type != 'AGGREGATE'
4 AND SID = &SID
5 AND SERIAL = &SERIAL
6 ORDER BY ratio desc;
Improving Tape I/O Performance
We have two recommendations for improving RMAN’s I/O performance with tape devices:
• Use an incremental backup strategy with block change tracking.
• Adjust multiplexing of backup sets.
The block change tracking feature enables RMAN to quickly identify which blocks have
changed since the last incremental backup. This feature can significantly improve the performance of incremental backups.
Also, consider altering the default multiplexing behavior of RMAN. Setting filesperset high and maxopenfiles low may increase the efficiency of writing to your tape device. You can also use the diskratio parameter to instruct RMAN to balance the load if datafiles are distributed across several different disks. See recipe 16-6 for details on how to adjust the multiplexing of backup sets.
Maximizing Throughput to Backup Device
Solution
You can tune the throughput to backup devices by adjusting RMAN’s level of multiplexing. RMAN multiplexing is controlled by three parameters:
• filesperset
• maxopenfiles
• diskratio
Using filesperset Use the fileperset clause of the backup command to limit the number of datafiles in each backup set. For example, if you wanted to limit the number of files being written to a backup set to only two files, you would use filesperset, as shown here:
RMAN> backup database filesperset 2;
Using maxopenfiles Use the maxopenfiles clause of the configure channel command or the allocate channel command to limit the number of files that can be simultaneously open for reads during a backup. If you want to limit the number of files being read by a channel to two files, use maxopenfiles as follows:
RMAN> configure channel 1 device type disk maxopenfiles 2;
To reset the channel maxopenfiles back to the default setting, use the clear parameter as shown here:
RMAN> configure channel 1 device type disk clear;
Using diskratio The diskratio parameter of the backup command instructs RMAN to read datafiles from a specified number of disks. For example, if you wanted RMAN to include datafiles located on at least four different disks into one backup set, then use diskratio as follows:
RMAN> backup database diskratio 4;
If you specify filesperset, and not diskratio, then diskratio will default to the value of filesperset. The diskratio parameter works only on operating systems that can provide RMAN with information such as disk contention and node affinity.
The value of filesperset specifies the maximum number of files in each backup
set. The default value of filesperset is as follows:
MIN(64, # of files to be backed up divided by the numbers of channels allocated)
The default value of maxopenfiles is as follows:
MIN(8, # files being backed up)
Setting Large Pool Memory Size
Problem
You’re using I/O slaves, and you’re aware that RMAN can take advantage of memory in the large pool when using I/O slaves. You want to ensure that the large pool is sized correctly.
Solution
Use the Automatic Shared Memory Management (ASMM) feature to have Oracle automatically manage the allocation of memory pools. To enable ASMM, do the following:
1. Ensure that the initialization parameter statistics_level is set to TYPICAL (the
default) or ALL.
2. Set sga_target to an appropriate nonzero value for your database not exceeding the
sga_max_size.
3. Set the following initialization parameters to zero:
• shared_pool_size
• large_pool_size
• java_pool_size
• db_cache_size
• streams_pool_size
Once you enable ASMM, Oracle will automatically size and adjust those memory pools
for you. You can set any of these values manually to enforce a minimum amount of memory used for the specified pool.
If you want to manually set the large pool size, here is Oracle’s recommended formula for sizing it:
large_pool_size = num_of_allocated_channels * (16 MB + (4 * size_of_tape_buffer ))
Tuning Media Recovery
Problem
You want to manually adjust the degree of parallelism that Oracle uses for media recovery to match the number of CPUs on your database server.
Solution
Starting with Oracle Database 10g, when you issue an RMAN recover command from either
within RMAN or within SQL*Plus, Oracle’s default behavior is to automatically perform media recovery operations in parallel. However, if you want to override this default behavior, you can use the recover parallel or recover noparallel command. This next line of code instructs
Oracle to spawn four parallel processes to apply redo:
RMAN> recover database parallel 4;
If you don’t want Oracle to recover in parallel, then specify the noparallel clause as shown here:
RMAN> recover database noparallel;
Tuning Crash Recovery
Problem
You want to ensure that your database comes up as efficiently as possible after you issue a shutdown abort command or experience a hard crash. You want to specify a target duration time for any crash recovery that is needed as a result of an instance crash or a shutdown abort command.
Solution
The fast_start_mttr_target initialization parameter allows you to specify a target value in seconds that denotes the amount of time that you want Oracle to take to perform crash recovery.
To determine an appropriate value for this parameter, follow this procedure:
1. Disable the initialization parameters that interfere with fast_start_mttr_target.
2. Determine the lower bound for fast_start_mttr_target.
3. Determine the upper bound for fast_start_mttr_target.
4. Select a value within the upper and lower bounds.
5. Monitor and adjust.
Note Although you can specify a target duration using fast_start_mttr_target, be aware that the database software treats that target as a “best-effort” target. Depending upon the circumstances, the actual time to perform crash recovery might be more or less than the target.
Step 1:Disable Parameters
Ensure that fast_start_io_target, log_checkpoint_interval, and log_checkpoint_timeout are all set to 0. These parameters will interfere with fast_start_mttr_target.
Step 2: Determine the Lower Bound Set fast_start_mttr_target to a value of 1, and then stop and start your database. This example
assumes you are using an spfile; if you aren’t, then you will have to manually modify your init.ora file.
SQL> alter system set fast_start_mttr_target=1;
SQL> shutdown immediate;
SQL> startup;
Immediately query the TARGET_MTTR value from V$INSTANCE_RECOVERY, as shown
here:
SQL> select target_mttr from v$instance_recovery;
TARGET_MTTR
-----------
52
Step 3: Determine the Upper Bound
Set fast_start_mttr_target to a value of 3600, and then stop and start your database. This example assumes you are using an spfile; if you aren’t, then you will have to manually modify your init.ora file.
SQL> alter system set fast_start_mttr_target=3600;
SQL> shutdown immediate;
SQL> startup;
Immediately query the TARGET_MTTR value from V$INSTANCE_RECOVERY, as shown
here:
SQL> select target_mttr from v$instance_recovery;
After your database has experienced a normal amount of activity, you can query
V$MTTR_TARGET_ADVICE as follows:
SQL> SELECT
2 mttr_target_for_estimate, advice_status, estd_cache_writes, estd_total_ios
3 from v$mttr_target_advice
4 order by 1;
The following output shows values of writes and I/O for each estimated value of
fast_start_mttr_target:
MTTR_TARGET_FOR_ESTIMATE ADVIC ESTD_CACHE_WRITES ESTD_TOTAL_IOS
------------------------ ----- ----------------- --------------
52 ON 1811 11030
130 ON 1575 10794
209 ON 1575 10794
288 ON 1575 10794
377 ON 1575 10794
Problem
You’ve noticed that your application performance degrades when the RMAN backups are running. You want to reduce RMAN’s I/O rate so that it spreads out its impact on the system over a period of time.
Solution
Use one of the following to control RMAN’s I/O rate:
• The backup duration ... minimize load command
• The rate clause of the allocate channel or configure channel command
Using backup duration ... minimize load
Use the backup duration ... minimize load command to evenly distribute RMAN I/O
over a period of time. This example shows how to spread the I/O of an RMAN backup over a
45-minute period:
RMAN> backup duration 00:45 minimize load database;
RMAN will report the time taken for the backup operation in the output, as shown in this
snippet:
channel ORA_DISK_1: throttle time: 0:44:43
Finished backup at 10-MAR-07
BACKUP AS BACKUPSET DURATION 00:30 MINIMIZE LOAD PARTIAL DATABASE;
BACKUP AS BACKUPSET DURATION 00:15 MINIMIZE TIME DATABASE;
Using rate
You can also use the rate clause of the allocate channel command or the configure channel command to control RMAN’s I/O rate. This example configures channel 1 to have a maximum read rate of 5MB per second:
RMAN> configure channel 1 device type disk rate=5M;
RMAN {
2> backup duration 1:00 partial minimize load database;
3> backup archivelog all;
3> backup current controlfile;
If you want to force RMAN to include only one datafile per backup set, then you can use the filesperset parameter as shown here
RMAN> backup duration 01:00 minimize load database filesperset 1;
Improving Performance Through Parallelism
Solution
Use the parallel clause of the configure command to instruct RMAN to allocate multiple channels for backup and restore operations. The following command instructs RMAN to automatically allocate four channels for the default disk device:
RMAN> configure device type disk parallelism 4;
RMAN> show device type;
using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 2;
Managing Backup Performance with MINIMIZE TIME and MINIMIZE LOAD
When using DURATION you can run the backup with the maximum possible performance, or run as slowly as possible while still finishing within the allotted time, to minimize the performance impact of backup tasks. To maximize performance, use the MINIMIZE TIME option with DURATION, as shown in this example:
BACKUP DURATION 4:00 PARTIAL MINIMIZE TIME DATABASE FILESPERSET 1;
To extend the backup to use the full time available, use the MINIMIZE LOAD option, as in this example:
BACKUP DURATION 4:00 PARTIAL MINIMIZE LOAD DATABASE FILESPERSET 1;
RMAN monitors the progress of the running backup, and periodically estimates how long the backup will take to complete at its present rate. If RMAN estimates that the backup will finish before the end of the backup window, it slows down the rate of backup so that the full available duration will be used. This reduces the overhead on the database associated with the backup.
Configuring Backup Optimization
Run the CONFIGURE command to enable and disable backup optimization. Backup optimization skips the backup of files in certain circumstances if the identical file or an identical version of the file has already been backed up. Full details on the backup optimization algorithm are provided in "Backup Optimization".
Note that backup optimization applies only to the following commands:
•BACKUP DATABASE
•BACKUP ARCHIVELOG with ALL or LIKE options
•BACKUP BACKUPSET ALL
You can override optimization at any time by specifying the FORCE option on the BACKUP command. For example, you can run:
BACKUP DATABASE FORCE;
BACKUP ARCHIVELOG ALL FORCE;
By default, backup optimization is configured to OFF. To enable backup optimization, run the following command:
CONFIGURE BACKUP OPTIMIZATION ON;
To disable backup optimization, run the following command:
CONFIGURE BACKUP OPTIMIZATION OFF;
To clear the current backup optimization setting, that is, return backup optimization to its default setting of OFF, run this command:
CONFIGURE BACKUP OPTIMIZATION CLEAR;
Configuring Tablespaces for Exclusion from Whole Database Backups
You can run CONFIGURE EXCLUDE FOR TABLESPACE to exempt the specified tablespace from the BACKUP DATABASE command. The exclusion condition applies to any datafiles that you add to this tablespace in the future.
This tablespace exclusion feature is useful when you do not want to make a specified tablespace part of the regular backup schedule, as in these cases:
•A tablespace is easy to rebuild, so it is more cost-effective to rebuild it than back it up every day.
•A tablespace contains temporary or test data that you do not need to back up.
•A tablespace does not change often and therefore should be backed up on a different schedule from other backups.
For example, you can exclude testing tablespaces cwmlite and example from whole database backups as follows:
CONFIGURE EXCLUDE FOR TABLESPACE cwmlite;
CONFIGURE EXCLUDE FOR TABLESPACE example;
If you run the following command, then RMAN backs up all tablespaces in the database except cwmlite and example:
BACKUP DATABASE;
You can still back up the configured tablespaces by explicitly specifying them in a BACKUP command or by specifying the NOEXCLUDE option on a BACKUP DATABASE command. For example, you can enter one of the following commands:
# backs up the whole database, including cwmlite and example
BACKUP DATABASE NOEXCLUDE;
BACKUP TABLESPACE cwmlite, example; # backs up only cwmlite and example
You can disable the exclusion feature for cwmlite and example as follows:
CONFIGURE EXCLUDE FOR TABLESPACE cwmlite CLEAR;
CONFIGURE EXCLUDE FOR TABLESPACE example CLEAR;
RMAN includes these tablespaces in future whole database backups.
Showing the Tablespaces Excluded from Backups
SHOW EXCLUDE shows how you have used the CONFIGURE EXCLUDE command to exclude tablespaces from whole database backups.
After connecting to the target database and recovery catalog (if you use one), run the SHOW EXCLUDE command. For example, enter:
RMAN> SHOW EXCLUDE; # shows the CONFIGURE EXCLUDE setting
Sample output for SHOW EXCLUDE follows:
RMAN configuration parameters are:
CONFIGURE EXCLUDE FOR TABLESPACE 'OLD_ACCOUNTS';
