Databases Reference
In-Depth Information
the compensating resource as well. For example, if you run out of physical memory, the
operating system might swap areas of memory out to the disk and can cause I/O bot‐
tlenecks.
Oracle and Disk I/O Resources
From the perspective of machine resources, an input/output operation, or I/O, can be
defined as the operating system of the computer reading or writing some bytes from or
to the underlying disk subsystem of the database server. I/Os can be small, such as 4 KB
of data, or large, such as 64 KB or 128 KB of data. The lower and upper limits on the
size of an I/O operation vary according to the operating system. The Oracle Database
also has a block size that you can define, called the
database block size
. The default size
for Oracle is typically 4 KB or 8 KB depending on operating system.
An Oracle Database can issue I/O requests in two basic ways:
Single database block I/Os
For example, one 8 KB datablock I/O request at a time. This type of request reads
or writes a specific block. After looking up a row in an index, Oracle uses a single
block I/O to retrieve the desired database block.
Multiblock I/Os
For example, 32 database blocks, each consisting of 8 KB, for a total I/O size of 256
KB. Multiblock I/O is used for large-scale operations. The number of blocks in one
multiblock I/O is determined by the initialization parameter DB_FILE_MULTI‐
BLOCK_READ_COUNT. Setting this value too high will favor full table scans.
The Oracle Database can read larger amounts of data with multiblock I/Os, so there are
times when a full table scan might actually retrieve data faster than an index-based
retrieval (e.g., if the selectivity of the index is low). Oracle can perform multiblock
operations faster than the corresponding collection of single-block operations.
I/O Planning Principles for an Oracle Database
When you're planning the disk layout and subsequent placement of the various files
that make up your database, you need to consider why Oracle performs I/O and the
potential performance impacts.
The amount of I/O is affected by the following in Oracle:
• Redo logs
• Data contained in tables
• Indexes on the tables
• The data dictionary, which goes in the SYSTEM tablespace
