|
|
| |
 |
|
Oracle Tips
by Burleson Consulting
|
The Data Warehouse Development Life Cycle
Oracle Features for the Data Warehouse
Oracle’s Table CACHE Option
When Oracle manages I/O against the database, it uses different
rules about where new data blocks are placed in the buffer cache.
For all I/O’s except full-table scans, rows are read into the
most-recently-used section of the Oracle buffer pool. As new data
blocks are fetched, the older blocks work their way down to the
least-recently-used end of the buffer, where they are eventually
erased from the buffer to make room for newly acquired data blocks.
(Figure 8.2)
Figure 8.2 Aging blocks from the Oracle buffers
The exception to this rule are data blocks that are acquired by
using full-table scans. As data blocks are read into the buffer,
blocks are placed on the opposite end of the buffer, in the
least-recently-used option of the buffer. In this fashion, full
table scans will not interfere with buffers on the
most-recently-used end of the buffer (i.e. rows from
non-full-table-scan transactions). Since these rows are already at
the least-most-recently used end of the buffer, they will be flushed
quickly as new rows are fetched as part of the full-table scan.
(Figure 8.3) As we may know, full-table scan data blocks are
physically read into Oracle’s buffer in chunk sizes that are
specified by the init.ora parameter DB_FILE_MULTIBLOCK_READ_COUNT.
For example, assume that the DB_BLOCK_SIZE is set to 8192 bytes (8K)
and the DB_FILE_MULTIBLOCK_READ_COUNT is set to 8. When Oracle
detects a full-table scan, Oracle will perform reads of four
physical blocks at a time, pulling in 64K with each I/O. Remember,
physical I/O is very time consuming, and anything that can be done
to reduce I/O will improve Oracle performance.
Figure 8.3 Different ends of the buffer cache may be used for
different tables
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
|
|
|
