Improving Performance Through Parallelism
Problem
You want to improve RMAN performance by utilizing multiple I/O channels.
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;
How It Works
An easy way to improve performance is to allocate multiple channels for backup and restore operations. If your database has datafiles that physically exist across different disks, then using multiple channels can significantly reduce the time required to back up and restore your database.
The default degree of parallelism for a channel is 1 (and can be up to 254).
If you change the degree of parallelism, RMAN will start the number of server sessions to match the degree of parallelism that you specify.
For example,
if you specify a degree of parallelism of 4, then RMAN will start four server sessions for that channel. A good rule of thumb to follow is to have the degree of parallelism match the number of physical devices.
For example,
if you have datafiles distributed over four physical drives, then
a parallelism degree of 4 would be appropriate. If you have only one physical drive, then set-
ting the degree of parallelism to a higher value generally does not help improve performance.
To view the degree of parallelism for the default device type, use the show device type
command as shown here:
RMAN> show device type;
RMAN configuration parameters are:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;CHAPTER 16 ■ TUNING RMAN
You can configure channel devices to back up to different locations on disk as shown
here:
RMAN>
RMAN>
RMAN>
RMAN>
configure
configure
configure
configure
channel
channel
channel
channel
1
2
3
4
device
device
device
device
type
type
type
type
disk
disk
disk
disk
format
format
format
format
'/ora01/backup/rman%U.bak';
'/ora02/backup/rman%U.bak';
'/ora03/backup/rman%U.bak';
'/ora04/backup/rman%U.bak';
In this case, if you configure the degree of parallelism to 4, then RMAN will spread the backup pieces across the four configured channels. If you set the degree of parallelism to 1,
then RMAN will back up only to the first channel device defined (even though you have con-figured four channels).
You can view the channel configuration information as shown here:
RMAN> show channel;
16-6. Maximizing Throughput to Backup Device
Problem
You suspect that your backup device is a bottleneck for backup operations. You want to adjust
the throughput to the 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.
How It Works
Multiplexing backup sets is RMAN’s ability to read multiple datafiles simultaneously and write
them to the same physical backup piece file. The level of multiplexing is defined by the num-
ber of files read simultaneously.
As of Oracle Database 10g, RMAN will automatically tune the level of multiplexing of your
backup sets. RMAN will automatically divide the files being read during a backup across the
available channels. Therefore, under most conditions you will not be required to tune
throughput. If you’re working with tape devices, you may need to adjust the parameters
described in this recipe to ensure that writes to tape are continuously streaming. Setting
filesperset high and maxopenfiles low may increase the efficiency of writing to your tape
device.
You can alter the default levels of multiplexing by using the filesperset and maxopenfiles
parameters. 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)CHAPTER 16 ■ TUNING RMAN
For example, if you had 12 datafiles in your database and allocated two channels, then the
number of files in each backup piece would be 6.
The default value of maxopenfiles is as follows:
MIN(8, # files being backed up)
This places a limit on the number of files that RMAN can read in parallel. For example, if
you set maxopenfiles for a channel to 2, then only two datafiles would be read and then writ-
ten to the backup piece at a time (for that channel).
Oracle determines the
number of parallel processes to spawn for media recovery from the initialization parameter
cpu_count. This parameter is set by default to the number of CPUs on your database server.
For example, if your server has two CPUS, then by default cpu_count will be set to 2 when
you create your database. For this server, Oracle will spawn two processes to apply redo any-
time you issue a recover command (from either RMAN or SQL*Plus).
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 recov-
ery. 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.
The following subsections provide more detail on this procedure.
After your database has experienced a normal amount of activity, you can query
V$MTTR_TARGET_ADVICE as follows:
SQL>
2
3
4
SELECT
mttr_target_for_estimate, advice_status, estd_cache_writes, estd_total_ios
from v$mttr_target_advice
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
------------------------
52
130
209
288
377
ADVIC ESTD_CACHE_WRITES ESTD_TOTAL_IOS
----- ----------------- --------------
ON
1811
11030
ON
1575
10794
ON
1575
10794
ON
1575
10794
ON
1575
10794
Problem
You want to improve RMAN performance by utilizing multiple I/O channels.
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;
How It Works
An easy way to improve performance is to allocate multiple channels for backup and restore operations. If your database has datafiles that physically exist across different disks, then using multiple channels can significantly reduce the time required to back up and restore your database.
The default degree of parallelism for a channel is 1 (and can be up to 254).
If you change the degree of parallelism, RMAN will start the number of server sessions to match the degree of parallelism that you specify.
For example,
if you specify a degree of parallelism of 4, then RMAN will start four server sessions for that channel. A good rule of thumb to follow is to have the degree of parallelism match the number of physical devices.
For example,
if you have datafiles distributed over four physical drives, then
a parallelism degree of 4 would be appropriate. If you have only one physical drive, then set-
ting the degree of parallelism to a higher value generally does not help improve performance.
To view the degree of parallelism for the default device type, use the show device type
command as shown here:
RMAN> show device type;
RMAN configuration parameters are:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;CHAPTER 16 ■ TUNING RMAN
You can configure channel devices to back up to different locations on disk as shown
here:
RMAN>
RMAN>
RMAN>
RMAN>
configure
configure
configure
configure
channel
channel
channel
channel
1
2
3
4
device
device
device
device
type
type
type
type
disk
disk
disk
disk
format
format
format
format
'/ora01/backup/rman%U.bak';
'/ora02/backup/rman%U.bak';
'/ora03/backup/rman%U.bak';
'/ora04/backup/rman%U.bak';
In this case, if you configure the degree of parallelism to 4, then RMAN will spread the backup pieces across the four configured channels. If you set the degree of parallelism to 1,
then RMAN will back up only to the first channel device defined (even though you have con-figured four channels).
You can view the channel configuration information as shown here:
RMAN> show channel;
16-6. Maximizing Throughput to Backup Device
Problem
You suspect that your backup device is a bottleneck for backup operations. You want to adjust
the throughput to the 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.
How It Works
Multiplexing backup sets is RMAN’s ability to read multiple datafiles simultaneously and write
them to the same physical backup piece file. The level of multiplexing is defined by the num-
ber of files read simultaneously.
As of Oracle Database 10g, RMAN will automatically tune the level of multiplexing of your
backup sets. RMAN will automatically divide the files being read during a backup across the
available channels. Therefore, under most conditions you will not be required to tune
throughput. If you’re working with tape devices, you may need to adjust the parameters
described in this recipe to ensure that writes to tape are continuously streaming. Setting
filesperset high and maxopenfiles low may increase the efficiency of writing to your tape
device.
You can alter the default levels of multiplexing by using the filesperset and maxopenfiles
parameters. 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)CHAPTER 16 ■ TUNING RMAN
For example, if you had 12 datafiles in your database and allocated two channels, then the
number of files in each backup piece would be 6.
The default value of maxopenfiles is as follows:
MIN(8, # files being backed up)
This places a limit on the number of files that RMAN can read in parallel. For example, if
you set maxopenfiles for a channel to 2, then only two datafiles would be read and then writ-
ten to the backup piece at a time (for that channel).
Oracle determines the
number of parallel processes to spawn for media recovery from the initialization parameter
cpu_count. This parameter is set by default to the number of CPUs on your database server.
For example, if your server has two CPUS, then by default cpu_count will be set to 2 when
you create your database. For this server, Oracle will spawn two processes to apply redo any-
time you issue a recover command (from either RMAN or SQL*Plus).
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 recov-
ery. 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.
The following subsections provide more detail on this procedure.
After your database has experienced a normal amount of activity, you can query
V$MTTR_TARGET_ADVICE as follows:
SQL>
2
3
4
SELECT
mttr_target_for_estimate, advice_status, estd_cache_writes, estd_total_ios
from v$mttr_target_advice
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
------------------------
52
130
209
288
377
ADVIC ESTD_CACHE_WRITES ESTD_TOTAL_IOS
----- ----------------- --------------
ON
1811
11030
ON
1575
10794
ON
1575
10794
ON
1575
10794
ON
1575
10794
No comments:
Post a Comment