Tuning the Storage Subsystem - Expert Oracle RAC Performance Diagnostics and Tuning - page 358

Oracle Files and RAID
There are several types of RAID, each of which has its positives and negatives when it comes to its usage. Based on the
type of data stored and retrieved, a specific type of RAID implementation is used. In this section, a few of the RAID
implementations and their corresponding usage with respect to Oracle database files are discussed.
Different types of files in Oracle have different types of access behavioral patterns. TEMP tablespace/datafile is
temporary; sort data that does not fit into the sort area of the buffer are written to this file for a temporary period of
time and then flushed when the sort operation is completed. This means there are frequent writes and deletes.
A datafile used by an OLTP database application has frequent small INSERT / DELETE / UPDATE operations compared to a
datafile used for a data warehouse application. In a data warehouse the inserts are batch/bulk (large numbers of rows
are inserted via a batch-load process) and seldom are there any updates or deletes. Figure 12-4 illustrates the various
types of database files and the kinds of operations they do in the database.

X X X X

Datafile Deletes

X X X

Datafile Inserts

X X X X X X

Datafile Update

1 2 3 4 5 6 7

Redo logs

Figure 12-4. Database file operation

Datafiles
Figure 12-4 illustrates two different file types: datafile and redolog file. Datafile operations ( INSERT / UPDATE / DELETE )
indicated by X illustrate that the operations are random and occur at different locations of the physical file. Deleting
data from the database could occur at different locations of the datafile, depending on the type of data being deleted
and based on the business criteria (for example, an employee leaves the organization). Similarly, INSERT operations
can also occur at different locations of the physical datafile. In Figure 12-4 , when rows are deleted the space becomes
available, and the DBWR process may decide to use that space for inserting a new row into an existing block if the
space is sufficient, otherwise it would write to the end of the file, depending on the amount of contiguous space
available. The third scenario illustrated for a datafile in Figure 12-4 is an UPDATE operation. In the case of updates, the
row already exists at a specific location, but the values of certain columns are changed or populated. In certain cases,
the row update may fit back into the same location provided if there is no significant row-length increase; that is,
the column values don't change significantly or existing NULL columns have not been populated with data. Most of the
time, depending on the how well the PCTFREE is sized, the existing space may be sufficient. However, if this is not the
case the updated row may not fit back into the same location, and so part of the row would be moved into a different
segment location, which causes either row movement or row chaining.