|
Category :
Performance Tunimg
DB Version
:
Oracle 10G
OS Details :
Sun Solaris 10
Starting 10G, a new
hidden parameter (_db_file_optimizer_read_count) expressed in number
of oracle blocks, seems to have been introduced that seem to influence
the optimizer decisions with respect to access path. In prior versions
the db_file_multiblock_read_count parameter controlled the number of
oracle blocks per read during table FULL / Index fast full scans and
the optimizer in its decision making process. Starting 10G number of
blocks read per read has been separated and is controlled by the
hidden parameter while the db_file_multiblock_read_count parameter
just deals with IO. Here are a few more details:
(1) If
db_file_multiblock_read_count is set manually for the instance or the
session, then oracle sets the value of _db_file_optimizer_read_count
to match db_file_multiblock_read_count value.
(2) If DFMBRC's value
is not set and if we allow oracle to pick the value, then oracle seem
to default the hidden parameter's value to 8 (in solaris 10) and sets the DFMBRC's
value to the highest allowed value for the OS.
Based on scenario
(2), oracle seem to use DFMBRC's value for "db file scattered read"
operations associated with full table / index scans and uses the _db_file_optimizer_read_count
value for optimizer decisions on FULL scans.
On a different note,
even if system stats are not collected for the database, 10G has some
basic system level statistics captured automatically within the
aux_stats$ table which is used for estimating CPU and IO costs.
|