20
Tuning I/O

1. Create an intermediate table with the same columns as the existing table to hold the migrated and chained rows:

   CREATE TABLE int_order_hist
      AS SELECT *
         FROM order_hist
         WHERE ROWID IN
            (SELECT HEAD_ROWID
               FROM CHAINED_ROWS
               WHERE TABLE_NAME = 'ORDER_HIST');
   
   

2. Delete the migrated and chained rows from the existing table:

   DELETE FROM order_hist
      WHERE ROWID IN
         (SELECT HEAD_ROWID
            FROM CHAINED_ROWS
            WHERE TABLE_NAME = 'ORDER_HIST');
   
   

3. Insert the rows of the intermediate table into the existing table:

   INSERT INTO order_hist
   

   SELECT *
   FROM int_order_hist;
   

4. Drop the intermediate table:

   DROP TABLE int_order_history;
   
   

DELETE FROM CHAINED_ROWS

WHERE TABLE_NAME = 'ORDER_HIST';

Retrieval of migrated rows is resource intensive; therefore, all tables subject to UPDATE should have their distributed free space set to allow enough space within the block for the likely update.

Modifying the SQL.BSQ File

The SQL.BSQ file runs when you issue the CREATE DATABASE statement. This file contains the table definitions that make up the Oracle server. The views you use as a DBA are based on these tables. Oracle recommends that you strictly limit modifications to SQL.BSQ.

• If necessary, you can increase the value of the following storage parameters: INITIAL, NEXT, MINEXTENTS, MAXEXTENTS, PCTINCREASE, FREELISTS, FREELIST GROUPS, and OPTIMAL.

  See Also: For complete information about these parameters, see Oracle8i SQL Reference.

• With the exception of PCTINCREASE, do not decrease the setting of a storage parameter to a value below the default. (If the value of MAXEXTENTS is large, then you can lower the value for PCTINCREASE or even set it to zero.)

• No other changes to SQL.BSQ are supported. In particular, you should not add, drop, or rename a column.

  Note: Oracle may add, delete, or change internal data dictionary tables from release to release. For this reason, modifications you make are not carried forward when the dictionary is migrated to later releases.

Using Locally-Managed Tablespaces

A tablespace that manages its own extents maintains a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Each bit in the bitmap corresponds to a block or a group of blocks. When an extent is allocated or freed for reuse, Oracle changes the bitmap values to show the new status of the blocks. These changes do not generate rollback information, because they do not update tables in the data dictionary (except for special cases such as tablespace quota information).

Locally-managed tablespaces have the following advantages over dictionary-managed tablespaces:

• Local management of extents avoids recursive space management operations, which can occur in dictionary-managed tablespaces if consuming or releasing space in an extent results in another operation that consumes or releases space in a rollback segment or data dictionary table.

• Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents.

The sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally-managed tablespace.

Tuning Sorts

There is a trade-off between performance and memory usage. For best performance, most sorts should occur in memory; sorts written to disk adversely affect performance. If the sort area size is too large, then too much memory may be used. If the sort area size is too small, then sorts may need to be written to disk which, as, mentioned, can severely degrade performance.

This section describes:

• Sorting to Memory

• Sorting to Disk

• Optimizing Sort Performance with Temporary Tablespaces

• Using NOSORT to Create Indexes Without Sorting

• GROUP BY NOSORT

Sorting to Memory

The default sort area size is adequate to hold all the data for most sorts. However, if your application often performs large sorts on data that does not fit into the sort area, then you may want to increase the sort area size. Large sorts can be caused by any SQL statement that performs a sort on many rows.

Recognizing Large Sorts

Oracle collects statistics that reflect sort activity and stores them in the dynamic performance view V$SYSSTAT. By default, this view is available only to the user SYS and to users granted the SELECT ANY TABLE system privilege. These statistics reflect sort behavior:

SORTS(MEMORY)	The number of sorts small enough to be performed entirely in sort areas without I/O to temporary segments on disk.
SORTS(DISK)	The number of sorts too large to be performed entirely in the sort area, requiring I/O to temporary segments on disk.

Use the following query to monitor these statistics over time:

SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME IN ('SORTS (MEMORY)', 'SORTS (DISK)');

The output of this query might look like this:

NAME                                                         VALUE
------------------------------------------------------- ----------
SORTS(MEMORY)                                                  965
SORTS(DISK)                                                      8

The information in V$SYSSTAT can also be obtained through the Simple Network Management Protocol (SNMP).

Increasing SORT_AREA_SIZE to Avoid Sorting to Disk

SORT_AREA_SIZE is a dynamically modifiable initialization parameter that specifies the maximum amount of memory to use for each sort. If a significant number of sorts require disk I/O to temporary segments, then your application's performance may benefit from increasing the size of the sort area. In this case, increase the value of SORT_AREA_SIZE.

The maximum value of this parameter depends on your operating system. You need to determine what size SORT_AREA_SIZE makes sense. If you set SORT_AREA_SIZE to an adequately large value, then most sorts should not need to go to disk (unless, for example, you are sorting a 10-gigabyte table).

Performance Benefits of Large Sort Areas

As mentioned, increasing sort area size decreases the chances that sorts go to disk. Therefore, with a larger sort area, most sorts process quickly without I/O.

When Oracle writes sort operations to disk, it writes out partially sorted data in sorted runs. After all the data has been received by the sort, Oracle merges the runs to produce the final sorted output. If the sort area is not large enough to merge all the runs at once, then subsets of the runs are merged in several merge passes. If the sort area is larger, then there are fewer, longer runs produced. A larger sort area also means the sort can merge more runs in one merge pass.

Performance Trade-offs for Large Sort Areas

Increasing sort area size causes each Oracle sort process to allocate more memory. This increase reduces the amount of memory for private SQL and PL/SQL areas. It can also affect operating system memory allocation and may induce paging and swapping. Before increasing the size of the sort area, be sure enough free memory is available on your operating system to accommodate a larger sort area.

If you increase sort area size, then consider decreasing the value for the SORT_AREA_RETAINED_SIZE parameter. This parameter controls the lower limit to which Oracle reduces the size of the sort area when Oracle completes some or all of a sort process. That is, Oracle reduces the size of the sort area after the sort has started sending the sorted data to the user or to the next part of the query. A smaller retained sort area reduces memory usage but causes additional I/O to write and read data to and from temporary segments on disk.

Sorting to Disk

Sort writes to disk directly bypass the buffer cache. If you sort to disk, then make sure that PCTINCREASE is set to zero for the tablespace used for sorting. Also, INITIAL and NEXT should be the same size. This reduces fragmentation of the tablespaces used for sorting. You set these parameters using the STORAGE clause of ALTER TABLE.

Optimizing Sort Performance with Temporary Tablespaces

Optimize sort performance by performing sorts in temporary tablespaces. To create temporary tablespaces, use the CREATE TABLESPACE or ALTER TABLESPACE statements with the TEMPORARY keyword.

Normally, a sort may require many space allocation calls to allocate and deallocate temporary segments. If you specify a tablespace as TEMPORARY, then Oracle caches one sort segment in that tablespace for each instance requesting a sort operation. This scheme bypasses the normal space allocation mechanism and greatly improves performance of medium-sized sorts that cannot be done completely in memory.

You cannot use the TEMPORARY keyword with tablespaces containing permanent objects such as tables or rollback segments.

Striping Temporary Tablespaces

Stripe the temporary tablespace over many disks, preferably using an operating system striping tool. For example, if you only stripe the temporary tablespace over 2 disks with a maximum of 50 I/Os per second on each disk, then Oracle can only perform 100 I/Os per second. This restriction could lengthen the duration of sort operations.

For the previous example, you could accelerate sort processing fivefold if you striped the temporary tablespace over 10 disks. This would enable 500 I/Os per second.

Using SORT_MULTIBLOCK_READ_COUNT

Another way to improve sort performance using temporary tablespaces is to tune the parameter SORT_MULTIBLOCK_READ_COUNT. For temporary segments, SORT_MULTIBLOCK_READ_COUNT has nearly the same effect as the parameter DB_FILE_MULTIBLOCK_READ_COUNT.

Increasing the value of SORT_MULTIBLOCK_READ_COUNT forces the sort process to read a larger section of each sort run from disk to memory during each merge pass. This also forces the sort process to reduce the merge width, or number of runs, that can be merged in one merge pass. This may increase in the number of merge passes.

Because each merge pass produces a new sort run to disk, an increase in the number of merge passes causes an increase in the total amount of I/O performed during the sort. Carefully balance increases in I/O throughput obtained by increasing the SORT_MULTIBLOCK_READ_COUNT parameter with possible increases in the total amount of I/O performed.

Using NOSORT to Create Indexes Without Sorting

One cause of sorting is the creation of indexes. Creating an index for a table involves sorting all rows in the table based on the values of the indexed columns. Oracle also allows you to create indexes without sorting. If the rows in the table are loaded in ascending order, then you can create the index faster without sorting.

The NOSORT Clause

To create an index without sorting, load the rows into the table in ascending order of the indexed column values. Your operating system may provide a sorting utility to sort the rows before you load them. When you create the index, use the NOSORT clause on the CREATE INDEX statement. For example, this CREATE INDEX statement creates the index EMP_INDEX on the ENAME column of the emp table without sorting the rows in the EMP table:

CREATE INDEX emp_index
   ON emp(ename)
   NOSORT;

When to Use the NOSORT Clause

Presorting your data and loading it in order may not always be the fastest way to load a table.

• If you have a multiple-CPU computer, then you may be able to load data faster using multiple processors in parallel, each processor loading a different portion of the data. To take advantage of parallel processing, load the data without sorting it first. Then create the index without the NOSORT clause.

• If you have a single-CPU computer, then you should sort your data before loading, if possible. Then create the index with the NOSORT clause.

GROUP BY NOSORT

Sorting can be avoided when performing a GROUP BY operation when you know that the input data is already ordered, so that all rows in each group are clumped together. This may be the case if the rows are being retrieved from an index that matches the grouped columns, or if a sort-merge join produces the rows in the right order. ORDER BY sorts can be avoided in the same circumstances. When no sort takes place, the EXPLAIN PLAN output indicates GROUP BY NOSORT.

Tuning Checkpoint Activity

Checkpointing is an operation that Oracle performs automatically. This section explains the following:

• How Checkpoints Affect Performance

• Adjusting Checkpointing Activity

• Fast-Start Checkpointing

  See Also: For a complete discussion of checkpoints, see Oracle8i Concepts.

How Checkpoints Affect Performance

Aggressive checkpointing will write dirty buffers to the datafiles more quickly and can reduce instance recovery time in the event of an instance failure. If checkpointing is fairly aggressive, then replaying the redo records in the redo log between the current checkpoint position and the end of the log involves processing relatively few data blocks. This means that the roll-forward phase of recovery will be fairly short.

However, aggressive checkpointing can reduce run-time performance, because checkpointing causes DBWn processes to perform I/O. The overhead associated with checkpointing is usually small.

Adjusting Checkpointing Activity

Adjust your checkpointing activity based on your performance concerns. If you are more concerned with efficient run-time performance than recovery time, then set checkpointing to be less aggressive.

If you are more concerned with having fast instance recovery than with achieving optimal run-time performance, then increase the checkpointing interval.

Checkpointing behavior can be influenced by the following parameters:

• Set the value of the LOG_CHECKPOINT_INTERVAL initialization parameter (in multiples of physical block size) to be larger than the size of your largest redo log file.

• Set the value of the LOG_CHECKPOINT_TIMEOUT initialization parameter to zero. This value eliminates time-based checkpoints.

• Set the value of FAST_START_IO_TARGET to zero to disable fast-start checkpointing. This is described below under the heading, "Fast-Start Checkpointing".

In addition to setting these parameters, also consider the size of your log files. Maintaining small log files can increase checkpoint activity and reduce performance.

Fast-Start Checkpointing

The fast-start checkpointing feature limits the number of dirty buffers and thereby limits the amount of time required for instance recovery. If Oracle must process an excessive number of I/O operations to perform instance recovery, then performance can be adversely affected. You can control this overhead by setting an appropriate value for the parameter FAST_START_IO_TARGET.

FAST_START_IO_TARGET limits the number of I/O operations that Oracle should allow for instance recovery. If the number of operations required for recovery at any point in time exceeds this limit, then Oracle writes dirty buffers to disk until the number of I/O operations needed for instance recovery is reduced to the limit set by FAST_START_IO_TARGET.

You can control the duration of instance recovery, because the number of operations required to recover indicates how much time this process takes. Disable this aspect of checkpointing by setting FAST_START_IO_TARGET to zero (0).

Tuning LGWR and DBWR I/O

This section describes how to tune I/O for the log writer and database writer background processes.

Tuning LGWR I/O

Applications with many INSERTs or with LONG/RAW activity may benefit from tuning LGWR I/O. The size of each I/O write depends on the size of the log buffer which is set by the initialization parameter LOG_BUFFER. Therefore, it is important to choose the right log buffer size. LGWR starts writing if the buffer is one third full, or when it is posted by a foreground process such as a COMMIT. Too large a log buffer size might delay the writes. Too small a log buffer might also be inefficient, resulting in frequent, small I/Os.

If the average size of the I/O becomes quite large, then the log file could become a bottleneck. To avoid this problem, you can stripe the redo log files, going in parallel to several disks. You must use an operating system striping tool, because manual striping is not possible in this situation.

Stripe size is also important. You can figure an appropriate value by dividing the average redo I/O size by the number of disks over which you want to stripe the buffer.

Review V$SYSSTAT or the UTLBSTAT report for the following:

• Log buffer space: This is time spent waiting for space in the log buffer. This is an indication that the buffers are being filled up faster than LGWR is writing. This may also indicate disk I/O contention. If the count is very high, then increase LGWR buffers and investigate disk I/O contention where the redo logs reside.

• Log file space/switch: This is the time Oracle spent waiting for the space on disk for LGWR to complete the write of log buffers to the redo log. This may be an indication to increase log buffers.

For example:

SELECT a.VALUE / DECODE(b.VALUE, 0, 1, b.VALUE) 
FROM V$SYSSTAT a, V$SYSSTAT b 
WHERE a.NAME = 'redo size' AND b.NAME = 'user commits';

This provides the average number redo records per commit. Now you must determine the average number of commits per second and multiply it by the average number redo records per commit (calculated above). This provides the max log buffers to setup.

The following events uniquely identify the specific wait:

• Log buffer space

• Log file switch (checkpoint incomplete)

• Log file switch (archiving needed)

• Log file switch (clearing log file)

• Log file switch completion

• Switch logfile statement

Pre-Oracle 7.3 tuning required the following to tune log file switch counts:

• If log file/switch = log space free requests, then add more log_buffers

• If log file/switch is high and there is a significant difference between background checkpoints started and background checkpoints completed, then consider reviewing the checkpoint frequency and the log file sizes.

Tuning DBWR I/O

This section describes the following issues of tuning DBWR I/O:

• Multiple Database Writer (DBWR) Processes and Database Slaves

• Internal Write Batch Size

• LRU Latches with a Single Buffer Pool

• LRU Latches with Multiple Buffer Pools

Multiple Database Writer (DBWR) Processes and Database Slaves

Multiple database writer processes are useful when a buffer cache is so large that one DBWn process running full-time cannot keep up with the load. However, for large transaction rate systems that have many CPUs, you can enable multiple database writers to handle the load.

Using the DB_WRITER_PROCESSES initialization parameter, you can create multiple database writer processes (from DBW0 to DBW9). Database I/O slaves provide non-blocking, asynchronous requests to simulate asynchronous I/O.

I/O slaves for DBWR are allocated immediately following database open, when the first I/O request is made. The DBWR continues to do all the DBWR-related work (scanning LRU). When the DBWR process initiates the I/O, the DBWR I/O slave simply does the I/O on behalf of DBWR. The writing of the batch is parallelized between the I/O slaves.

The main DBWR process, which is I/O issuing process, looks for an idle I/O slave. If one is available, then that I/O slave gets a post. If there are no idle slaves, then the I/O issuer spawns one. If the allowed number of slaves have been spawned, then the issuer waits and tries again to find an idle slave.

If the asynchronous I/O code of the platform has bugs or is not efficient, then asynchronous I/O can be disabled on a device type. However, multiple I/O slaves only parallelize the writing of the batch between the DBWR I/O slaves. In contrast, you can parallelize the gathering as well as the writing of buffers with the multiple DBWR feature. Therefore, from the throughput standpoint, N DBWR processes should deliver more throughput than one DBWR process with the same number of I/O slaves.

Multiple writer processes (and I/O slaves) are advanced features that are intended for heavy OLTP processing. Implement this feature only if the database environment requires such I/O throughput. For example, if asynchronous I/O is available, then it may be wise to disable I/O slaves and run with a single DBWR in asynchronous I/O mode.

If it is determined that there is a need for multiple writer processes or slave processes, then determine which option to use. Although both implementations of DBWR processes may be beneficial, the general indicator rule on which option to use depends on the availability of asynchronous I/O (from the operating system) and the number of CPUs.

The number of CPUs is also indirectly related to the number LRU latch sets. To determine whether to use multiple DBWn processes or database slaves, follow these guidelines:

• First, determine if DBWR is keeping up the write requests. Review the V$SYSTEM_EVENT view for significant numbers of 'free buffer' waits. Large values may indicate that users wants to read a buffer, but they cannot because there are too many dirty buffers in the cache. If you do not see free buffer waits, then DBWR is not a problem.

• If DBWR is keeping up with the write requests, then use I/O slaves only if asynchronous I/O is not supported (or is working improperly) on the operating system for the types of files that you are using. Start with the number of I/O slaves equal to the average number of disks that a typical file spans.

• If you are using a RAID device with a large write back cache, then you can reduce your I/O slaves significantly, because I/Os to the RAID cache are much faster than I/Os to the disks.

• Use multiple DBWR (DB_WRITER_PROCESSES) when one DBWR cannot keep up. This is generally only necessary for large SMP systems with a lot of I/O activity. Typically, there should be no more than one DBWR for every 8 CPUs. Some systems can use multiple DBWR processes with asynchronous I/O enabled if they have very high transaction rates, are not CPU starved, and the operating system's async I/O works effectively.

  Note: Implementing DB_IO_SLAVES or multiple writer processes creates some overhead cost. Enabling these features requires that extra shared memory be allocated for I/O buffers and request queues.

Internal Write Batch Size

Database writer (DBWn) processes use the internal write batch size, which is set to the lowest of the following three values (A, B, or C):

• Value A is calculated as follows:

  
  

• Value B is the port-specific limit. (See your Oracle platform-specific documentation.)

• Value C is one-fourth the value of DB_BLOCK_BUFFERS.

Setting the internal write batch size too large may result in uneven response times.

For best results, you can influence the internal write batch size by changing the parameter values by which Value A is calculated. Take the following approach:

1. Determine the files to which you must write, and the number of disks on which those files reside.

2. Determine the number of I/Os you can perform against these disks.

3. Determine the number of writes that your transactions require.

4. Make sure that you have enough disks to sustain this rate.

LRU Latches with a Single Buffer Pool

When you have multiple database writer DBWn processes and only one buffer pool, the buffer cache is divided up among the processes by LRU (least recently used) latches; each LRU latch is for one LRU list.

The default value of the DB_BLOCK_LRU_LATCHES parameter is 50% of the number of CPUs in the system. You can adjust this value to be equal to, or a multiple of, the number of CPUs. The objective is to cause each DBWn process to have the same number of LRU lists, so that they have equivalent loads.

For example, if you have 2 database writer processes and 4 LRU lists (4 latches), then the DBWn processes obtain latches in a round-robin fashion. DBW0 obtains latch 1, DBW1 obtains latch 2, then DBW0 obtains latch 3 and DBW1 obtains latch 4. Similarly, if your system has 8 CPUs and 3 DBWn processes, then you should have 9 latches.

LRU Latches with Multiple Buffer Pools

If you are using multiple buffer pools and multiple database writer (DBWn) processes, then the number of latches in each pool (DEFAULT, KEEP, and RECYCLE) should be equal to, or a multiple of, the number of processes. This is recommended so that each DBWn process is equally loaded.

Consider the example in Figure 20-3 where there are 3 DBWn processes and 2 latches for each of the 3 buffer pools, for a total of 6 latches. Each buffer pool would obtain a latch in round robin fashion.

Figure 20-3 LRU Latches with Multiple Buffer Pools: Example 1

The DEFAULT buffer pool has 500 buffers for each LRU list. The RECYCLE buffer pool has 250 buffers for each LRU list. The KEEP buffer pool has 100 buffers for each LRU.

DBW0 gets latch 1 (500) and latch 4 (250) for 750.

DBW1 gets latch 2 (500) and latch 6 (100) for 600.

DBW2 gets latch 3 (250) and latch 5 (100) for 350.

Thus, the load carried by each of the DBWn processes differs, and performance suffers. If, however, there are 3 latches in each pool, then the DBWn processes have equal loads, and performance is optimized.

The different buffer pools have different rates of block replacement. Ordinarily, blocks are rarely modified in the KEEP pool and frequently modified in the RECYCLE pool. This means that you need to write out blocks more frequently from the RECYCLE pool than from the KEEP pool. As a result, owning 100 buffers from one pool is not the same as owning 100 buffers from the other pool. To be perfectly load balanced, each DBWn process should have the same number of LRU lists from each type of buffer pool.

A well-configured system might have 3 DBWn processes and 9 latches, with 3 latches in each buffer pool.

Figure 20-4 LRU Latches with Multiple Buffer Pools: Example 2

The DEFAULT buffer pool has 500 buffers for each LRU list. The RECYCLE buffer pool has 250 buffers for each LRU list. The KEEP buffer pool has 100 buffers for each LRU list.

DBW0 gets latch 1 (500) and latch 4 (250) and latch 7 (100) for 850.

DBW1 gets latch 2 (500) and latch 5 (250) and latch 8 (100) for 850.

DBW2 gets latch 3 (500) and latch 6 (250) and latch 9 (100) for 850.

Tuning Backup and Restore Operations

The primary goal of backup and restore tuning is to create an adequate flow of data between disk and storage device. Tuning backup and restore operations involve the following tasks:

• Locating the Source of Bottlenecks

• Using Recovery Manager

• Using Fixed Views to Monitor Bottlenecks

• Improving Backup Throughput

Locating the Source of Bottlenecks

Backups and restore operations have three distinct components:

• Read the input (disk or tape).

• Process data by validating blocks and copying them from the input to the output buffer.

• Write the output to tape or disk.

It is unlikely that all three of these perform at the same speed. Therefore, the slowest of these components is the bottleneck.

Types of I/O

Oracle backup and restore uses two types of I/O: disk and tape. When performing a backup, the input files are read using disk I/O, and the output backup file is written using either disk or tape I/O. When performing restores, these roles reverse. Both disk and tape I/O can be synchronous or asynchronous; each is independent of the other.

Measuring Synchronous and Asynchronous I/O Rates

When using synchronous I/O, you can easily determine how much time backup jobs require, because devices only perform one I/O task at a time. When using asynchronous I/O, it is more difficult to measure the bytes-per-second rate for the following reasons:

• Asynchronous processing implies that more than one task occurs at a time.

• Oracle I/O uses a polling, rather than an interrupt, mechanism to determine when each I/O request completes. Because the backup or restore process is not immediately notified of I/O completion by the operating system, you cannot determine the duration of each I/O.

Using Recovery Manager

Recovery Manager (RMAN) is an Oracle tool that allows you to back up, copy, restore, and recover datafiles, control files, and archived redo logs. You can invoke RMAN as a command-line utility from the operating system prompt or use the GUI-based Enterprise Manager Backup Manager.

RMAN automates many of the backup and recovery tasks that were formerly performed manually. For example, instead of requiring you to locate appropriate backups for each datafile, copy them to the correct place using operating system commands, and choose which archived logs to apply, RMAN manages all these tasks automatically.

RMAN provides several parameters that allow you to tune backup and recovery operations. These are discussed in the following sections.

Allocating Disk Buffers

There are two different buffers: disk buffers and tape buffers. They can be different sizes. When RMAN backs up from disk, it allocates four disk buffers for each input datafile. You cannot alter the number of buffers that RMAN allocates.

The size of the disk buffers is controlled by the DB_FILE_DIRECT_IO_COUNT initialization parameter. This is the number of blocks per buffer. The default is 64.

The size of each buffer is equal to the product of the following initialization parameters:

DB_BLOCK_SIZE * DB_FILE_DIRECT_IO_COUNT

For example, if DB_BLOCK_SIZE = 2048 and DB_FILE_DIRECT_IO_COUNT =64, then each disk buffer is 128K. In this example, the total size of the buffers for each datafile is 128K * 4, or 512K. There are 4 buffers allocated for each datafile in the backup set.

If you want to know the total size of the buffers allocated in your backup, then multiply this total by the number of datafiles being accessed by the channel, and then multiply by the number of channels. You should also add a little extra to account for the control structures.

You can reduce the size of the buffers by lowering DB_FILE_DIRECT_IO_COUNT, but the number of buffers remains at 4 per file.

Figure 20-5 Disk Buffer Allocation

Allocating Tape Buffers

Oracle allocates 4 buffers per channel for the tape writers (or reads if doing a restore). There are usually 64K each. Therefore, to size this, multiply by 4, and then multiply by the number of channels.

You can change the size of each tape buffer using the parms parameter of the ALLOCATE CHANNEL statement. Set blksize to the desired size of each buffer. For example:

allocate channel foo type 'sbt_tape' parms="blksize=16384"

RMAN allocates the tape buffers in the SGA or the PGA. If you set the initialization parameter BACKUP_TAPE_IO_SLAVES = true, then RMAN allocates them from the SGA. If you set the parameter to FALSE, then RMAN allocates the buffers in the PGA.

If you use I/O slaves, then you should use the LARGE_POOL_SIZE initialization parameter to set aside some SGA memory that is dedicated to holding these large memory allocations. Hence, the RMAN I/O buffers do not compete with the library cache for SGA memory.

Figure 20-6 Tape Buffer Allocation

Synchronous vs. Asynchronous I/O

When RMAN reads or writes data, the action is either synchronous or asynchronous. When the I/O is synchronous, a server process can perform only one task at a time. When the I/O is asynchronous, a server process can begin one task, and other processes can perform other tasks while the initial process waits for the task to complete.

You can set initialization parameters that determine the type of I/O. If you set BACKUP_TAPE_IO_SLAVES to true, then the I/O is asynchronous. Otherwise, the I/O is synchronous.

Figure 20-7 illustrates synchronous I/O in a backup to tape. The following steps occur:

1. A server process writes blocks to a tape buffer.

2. The tape process writes data to tape. While the tape process is writing, the server process must remain idle.

3. The tape process returns a message to the server process stating that it has completed writing.

4. The server process can initiate a new task.

Figure 20-7 Synchronous I/O

Figure 20-8 illustrates asynchronous I/O in a backup to tape. The following steps occur:

1. A server process writes blocks to a tape buffer.

2. The tape process writes data to tape. While the tape process is writing, other server processes are free to perform tasks.

3. Two spawned server processes write to tape buffers as the initial tape process writes to tape.

Figure 20-8 Asynchronous I/O

Allocating Channels

When you allocate a channel, RMAN lets you set various channel limit parameters that apply to operations performed by the allocated server session. You can use these parameters to do the following:

• Force RMAN to create multiple backup pieces.

• Prevent RMAN from consuming too much disk bandwidth.

• Prevent RMAN from opening too many input files at once.

You can specify the following parameters:

Parameter	Description
kbytes	Specifies the maximum size of a backup piece created on a channel. Use this parameter to force RMAN to create multiple backup pieces in a backup set. RMAN creates each backup piece with a size no larger than the value specified in the parameter.
readrate	Specifies the maximum number of buffers per second read from each input datafile. You can use this parameter to prevent RMAN from consuming too much bandwidth during backups. For example, set readrate to 12. If each input buffer is 128K, then the read rate for each input datafile is 12 * 128 or about 1.5 Mb per second. If each SCI drive delivers 3 Mb per second, then RMAN leaves some disk bandwidth available to the online system.
maxopenfiles	Determines the maximum number of input files that a backup or copy can have open at a given time. Set this parameter to prevent RMAN from attempting to open a number of files greater than the upper limit for your operating system.

Allocating Input Files

The BACKUP statement lets you set parameters that influence how RMAN selects files for input into backup sets. You may need to set these parameter to do the following:

• Prevent RMAN from write a single backup set to multiple volumes.

• Prevent RMAN from reading from too many disks at once.

• Keep a tape drive streaming during a backup.

You can specify the following parameters:

Parameter	Description
filesperset	Specifies the maximum number of files to place in a backup set. RMAN divides the number of input files by the number of channels to calculate the number of files per backup set. Use this parameter to force RMAN to create multiple backup sets. For example, if you have 50 input datafiles and 2 channels, you can set filesperset = 5 to create 10 backup sets. This action can prevent you from splitting a backup set among multiple tapes.
diskratio	Specifies the number of drives to include in the backup. For example, assume your datafiles are located on 5 disks, the disks supply data at 10 bytes/second, and the tape drive requires 20 bytes/second to set keep streaming. If you set diskratio to 2, then RMAN reads from 2 drives at a time, thereby spreading the backup load.

Using Incremental Backups

An incremental backup is an RMAN backup in which only modified blocks are backed up. Incremental backups are not necessarily faster than full backups, because Oracle still reads the entire datafile to take an incremental backup. If tape drives are locally attached, then incremental backups could be faster. You must consider how much bandwidth there is for reading the disks compared to the bandwidth for writing to the tapes. If tape bandwidth is limited compared to disk, then incremental backups could help.

In an incremental backup, if only a few blocks have changed, then you need to input many buffers from the datafile before you accumulate enough blocks to fill a buffer and write to tape. So, it is possible that the tape drive is not streaming. Streaming means that the tape drive is 100% busy. If a tape drive is not kept streaming, then it becomes inefficient, because after each write, the tape drive must stop and rewind a little bit.

If you use a large filesperset in the filesperset parameter, then you can scan many datafiles in parallel, the output buffers for the tape drive are filled quickly, and you can write them frequently enough to keep the tape drive streaming.

For an incremental backup, filesperset=50 might be a good number. But, for a full or incremental level=0 backup, filesperset should be smaller, such as 4 or 8.

RMAN Performance Tips

To get the best performance for a backup, follow these suggestions:

1. Do not set the readrate parameter. This is intended to slow down a backup, so that you can run it in the background during production hours.

2. Set the BACKUP_TAPE_IO_SLAVES initialization parameter to TRUE.

3. If you set BACKUP_TAPE_IO_SLAVES to TRUE, then the tape buffers are allocated from the SGA. Therefore, allocate a LARGE_POOL for them. You can control the buffer size with the parms clause on the allocate channel statement:

4. Increase the size of the disk reads by setting the DB_FILE_DIRECT_IO_COUNT initialization parameter. Use the maxopenfiles parameter to control how many datafiles are opened simultaneously on each channel.

5. If your datafiles are in a UNIX file system, then try setting BACKUP_DISK_IO_SLAVES to 4. This spawns processes to read datafiles in parallel, simulating asynchronous I/O. In this case, the datafile buffers are allocated from the SGA. The default value for this parameter is 0, meaning I/O slaves are not used at all, and the buffers come from the process local memory, not the SGA.

6. For incremental backups, use a higher filesperset than for full backups. Set it high enough so that the tape drive is kept streaming. Filesperset should be less than or equal to maxopenfiles. Try making FILESPERSET=MAXOPENFILES. Use a value of 10 to start, and raise this if the tape drive does not stream. BACKUP_DISK_IO_SLAVES may be necessary.

Using Fixed Views to Monitor Bottlenecks

Use the V$BACKUP_SYNC_IO and V$BACKUP_ASYNC_IO views to determine the source of backup or restore bottlenecks and to determine the progress of backup jobs.

V$BACKUP_SYNC_IO contains rows when the I/O is synchronous to the process (or thread on some platforms) performing the backup. V$BACKUP_ASYNC_IO contains rows when the I/O is asynchronous. Asynchronous I/O is obtained either with I/O processes or because it is supported by the underlying operating system.

Identifying Bottlenecks with Synchronous I/O

With synchronous I/O, it is difficult to identify specific bottlenecks, because all synchronous I/O is a bottleneck to the process. The only way to tune synchronous I/O is to compare the bytes-per-second rate with the device's maximum throughput rate. If the bytes-per-second rate is lower than that device specifies, then consider tuning that part of the backup/restore process. Use the DISCRETE_BYTES_PER_SECOND column in the V$BACKUP_SYNC_IO view to see the I/O rate.

Identifying Bottlenecks with Asynchronous I/O

If the combination of LONG_WAITS and SHORT_WAITS is a significant fraction of IO_COUNT, then the file indicated in V$BACKUP_SYNCH_IO and V$BACKUP_ASYNCH_IO is probably a bottleneck. Some platforms' implementation of asynchronous I/O can cause the caller to wait for I/O completion when performing a non-blocking poll for I/O. Because this behavior can vary among platforms, the V$BACKUP_ASYNC_IO view shows the total time for both "short" and "long" waits.

Long waits are the number of times the backup/restore process told the operating system to wait until an I/O was complete. Short waits are the number of times the backup/restore process made an operating system call to poll for I/O completion in a non-blocking mode. Both types of waits the operating system should respond immediately.

If the SHORT_WAIT_TIME_TOTAL column is equal to or greater than the LONG_WAIT_TIME_TOTAL column, then your platform probably blocks for I/O completion when performing non-blocking I/O polling. In this case, the SHORT_WAIT_TIME_TOTAL represents real I/O time for this file. If the SHORT_WAIT_TIME_TOTAL is low compared to the total time for this file, then the delay is most likely caused by other factors, such as process swapping. If possible, tune your operating system so that the I/O wait time appears up in the LONG_WAIT_TIME_TOTAL column.

Columns Common to V$BACKUP_SYNC_IO and V$BACKUP_ASYNC_IO

Table 20-17 lists columns and their descriptions that are common to the V$BACKUP_SYNC_IO and V$BACKUP_ASYNC_IO views.

Table 20-17 Common Columns of V$BACKUP_SYNC_IO and V$BACKUP_ASYNC_IO

SID

SERIAL

USE_COUNT

DEVICE_TYPE

TYPE 

STATUS

FILENAME

SET_COUNT

SET_STAMP

BUFFER_SIZE

BUFFER_COUNT

TOTAL_BYTES

OPEN_TIME

CLOSE_TIME

ELAPSED_TIME

MAXOPENFILES

BYTES

EFFECTIVE_BYTES_PER_ 
SECOND

IO_COUNT

Columns Specific to V$BACKUP_SYNC_IO

Table 20-18 lists columns specific to the V$BACKUP_SYNC_IO view.

Table 20-18 Columns Specific to V$BACKUP_SYNC_IO

IO_TIME_TOTAL

IO_TIME_MAX

DISCRETE_BYTES_PER_ 
SECOND

Columns Specific to V$BACKUP_ASYNC_IO

Table 20-19 lists columns specific to the V$BACKUP_ASYNC_IO view.

Table 20-19 Columns Specific to V$BACKUP_ASYNC_IO

READY

SHORT_WAITS

SHORT_WAIT_TIME_TOTAL

SHORT_WAIT_TIME_MAX

LONG_WAITS

LONG_WAIT_TIME_TOTAL

LONG_WAIT_TIME_MAX

Improving Backup Throughput

In optimally tuned backups, tape components should create the only bottleneck. You should keep the tape and its device streaming, or constantly rotating. If the tape is not streaming, then the data flow to the tape may be inadequate.

This section contains the following topics to maintain streaming by improving backup throughput:

• Understanding Factors Affecting Data Transfer Rates

• Determining If Tape is Streaming for Synchronous I/O

• Determining If Tape is Streaming for Asynchronous I/O

• Increasing Throughput to Enable Tape Streaming

• Spreading I/O Across Multiple Disks

• Backing Up Empty Files or Files with Few Changes

• Backing Up Full Files

Understanding Factors Affecting Data Transfer Rates

The rate at which the host sends data to keep the tape streaming depends on the following factors:

• The raw capacity of the tape device.

• Compression.

Tape device raw capacity is the smallest amount of data required to keep the tape streaming.

Compression is implemented either in the tape hardware or by the media management software. If you do not use compression, then the raw capacity of the tape device keeps it streaming. If you use compression, then the amount of data that must be sent to stream the tape is the raw device capacity multiplied by the compression factor. The compression factor varies for different types of data.

Determining If Tape is Streaming for Synchronous I/O

To determine whether your tape is streaming when the I/O is synchronous, query the EFFECTIVE_BYTES_PER_SECOND column in the V$BACKUP_SYNC_IO view.

Table 20-20 V$BACKUP_SYNC_IO View

Determining If Tape is Streaming for Asynchronous I/O

If the I/O is asynchronous, then the tape is streaming if the combination of LONG_WAITS and SHORT_WAITS is a significant fraction of I/O count. Place more importance on SHORT_WAITS if the time indicated in the SHORT_WAIT_TIME_TOTAL column is equal to or greater than the LONG_WAIT_TIME_TOTAL column.

Increasing Throughput to Enable Tape Streaming

If the tape is not streaming, then the basic strategy is to supply more bytes-per-second to the tape. Modify this strategy depending on the how many blocks Oracle must read from disk and how many disks Oracle must access.

Spreading I/O Across Multiple Disks

Using the DISKRATIO parameter of the BACKUP statement to distribute backup I/O across multiple volumes, specify how many disk drives RMAN uses to distribute file reads when backing up multiple concurrent datafiles. For example, assume that your system uses 10 disks. The disks supply data at 10 byes per second, and the tape drive requires 50 bytes per second to keep streaming. In this case, set DISKRATIO to 5 to spread the backup load onto 5 disks.

When setting DISKRATIO, spread the I/O over only as many disks as needed to keep the tape streaming: any more can increase the time it takes to recover a single file and provides no performance benefit. If you do not specify DISKRATIO, but you do specify FILESPERSET, then DISKRATIO defaults to FILESPERSET. If neither is specified, then DISKRATIO defaults to 4.

Backing Up Empty Files or Files with Few Changes

When performing a full backup of files that are largely empty, or when performing an incremental backup when few blocks have changed, you may not be able to supply data to the tape fast enough to keep it streaming.

In this case, you can achieve optimal performance by the following:

• Use the highest possible value for the MAXOPENFILES parameter of the Recovery Manager SET LIMIT CHANNEL statement.

  See Also: For more information about the RMAN SET statement, see Oracle8i Backup and Recovery Guide.

• Use asynchronous disk I/O. This takes advantage of asynchronous read-ahead that fills input buffers from one file while processing data from others.

Backing Up Full Files

When you perform a full backup of files that are mostly full and the tape is not streaming, you can improve performance in several ways, as shown in Table 20-21:

Table 20-21 Throughput Performance Improvement Methods

run{
allocate channel foo type disk;
SETLIMIT CHANNEL foo maxopenfiles=4
backup database...}

READRATE

run{
allocate channel foo1 type disk format '/filesys1/oracle_
backups/%d/%u_%p';
allocate channel foo2 type disk format '/filesys2/oracle_
backups/%d/%u_%p';
...}

Configuring the Large Pool

You can optionally configure the large pool so that Oracle has a separate pool from which it can request large memory allocations. This prevents competition with other subsystems for the same memory.

As Oracle allocates more shared pool memory for the multi-threaded server session memory, the amount of shared pool memory available for the shared SQL cache decreases. If you allocate session memory from another area of shared memory, then Oracle can use the shared pool primarily for caching shared SQL and not incur the performance overhead from shrinking the shared SQL cache.

For I/O server processes and backup and restore operations, Oracle allocates buffers that are a few hundred kilobytes in size. Although the shared pool may be unable to satisfy this request, the large pool is able to do so. The large pool does not have an LRU list; Oracle does not attempt to age memory out of the large pool.

Use the LARGE_POOL_SIZE parameter to configure the large pool. To see in which pool (shared pool or large pool) the memory for an object resides, see the column POOL in V$SGASTAT.