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

In a data warehouse application, the operations are normally range retrievals and the data is normally retrieved
through expensive scan operations. In both cases, based on the configuration, the data may be retrieved using
Oracle's parallel query technology. In certain cases, this could be a complex operation in which multiple tables are
joined, which is done after sorting the data in a specific order. When data is retrieved it could be possible that an
appropriate index is available and Oracle performs index retrieval, but if the optimizer decides that a scan operation is
more efficient, the process steps through all the rows in the table to retrieve the appropriate data.
Besides the DML operations and SELECT statements, Oracle's methods of operation when managing redo and
undo are also different. For example, SQL operations involve different amounts of data, and DML operations generate
a variable amount of redo (open-ended write) and undo, which will at some point be written to disk (redo to the
online logs, undo to the undo tablespaces) or read from disk (undo for large, consistent reads).
Oracle databases have to support a wide range of data access operations, some of which are relatively simple,
while others maybe complicated. The challenge for Oracle Corporation and Oracle DBAs is to establish a storage
subsystem that is easy to manage yet capable of handling a wide range of data access requests.
The characteristics of an application are:

• I/O access patterns

• Variables affecting I/O performance

I/O Access Patterns
Business data needs to be persisted for future processing, needs to be able to be modified, and needs to be able to be
removed from the database in many cases. Apart from modifications and deletions, data is queriered several times for
analysis and processing. Methods of accessing this data could vary depending on the purpose for which it's retrieved.
This section discusses various methods of data access.

Sequential Data Access
Sequential data access is typically observed in a data warehouse operation where data is retrieved using range-scan
or full-table-scan operations. During such operations, physically contiguous blocks are retrieved, causing large I/O
requests. Normally when there is contention for resources during sequential access, Oracle reflects this resource
contention using the “ db file scattered read ” wait event.
Synchronous scattered vector reads (corresponds to full-table scans) •

• Asynchronous sequential reads (corresponds to direct-path reads). Direct-path reads are

performed based on the DISK_ASYNC_IO parameter
Synchronous sequential writes (corresponds to direct-path writes). Direct-path writes are

•

performed based on the DISK_ASYNC_IO parameter
Synchronous gather writes (corresponds to LGWR writes) •

• Typically noticed in:

• Data warehouses

• Queries with table or index scans

• Direct data loads

• Backup, restore, and archiving