DB_FILE_MULTIBLOCK_READ_COUNT 소개
==================================

Purpose
---------
데이터웨어하우스 와 같이 대용량 데이터를 처리하는 시스템 구성시 반드시
사용되는 DB_FILE_MULTIBLOCK_READ_COUNT 의 개념과 사용법에 대해서 소개.

Explanation
-----------

1. He heard somewhere that if he increases db_file_multi_block_read_count
and utilizes sort/merge that this gives better performance than nested
loop. True/false or depends?
(Can he even control whether Oracle does sort/merge
or nested loop?)

A. He can force sort/merge by using hints. As for the performance,
it would depend on the queries and what percentage of data was being
returned. Increasing the parameter does increase speed of full table
scans since we need to make fewer trips to the disk.

2. What is the maximum number for this parameter in VMS?
A. Don't know

3. What are the negative effects of increasing this parameter?
A. Each time blocks are read off of the disk, a large number of blocks
would be read and this would cause other blocks to be flushed (written)
from the buffer pool.

4. Oracle9i Information for DB_FILE_MULTIBLOCK_READ_COUNT

* Note: Default values and ranges may differ from those shown here due
to variations between releases..

Parameter type Integer
Default value 8
Parameter class Dynamic: ALTER SESSION, ALTER SYSTEM
Range of values Operating system-dependent

DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to
minimize I/O during table scans. It specifies the maximum number of
blocks read in one I/O operation during a sequential scan. The total
number of I/Os needed to perform a full table scan depends on such
factors as the size of the table, the multiblock read count, and
whether parallel execution is being utilized for the operation.

Online transaction processing (OLTP) and batch environments typically
have values in the range of 4 to 16 for this parameter. DSS and data
warehouse environments tend to benefit most from maximizing the value
of this parameter. The optimizer is more likely to choose a full table
scan over an index if the value of this parameter is high.

The maximum value is always less than the operating system's maximum
I/O size expressed as Oracle blocks ((max I/O size)/DB_BLOCK_SIZE). If
you set this parameter to a value greater than the maximum, Oracle
uses the maximum.