dbaXchange.com


Database Resources
- RDBMS Server
-
Database Administration
-
Performance Tuning
- Backup and Recovery
-
Oracle Utilities

- SQL and PLSQL
-
Network Administration
-
Client Applications
-
Miscellaneous Stuff

Oracle's technology Sites

http://technet.oracle.com

http://metalink.oracle.com

http://asktom.oracle.com/

http://www.oramag.com


Search this site or the web


Site search Web search powered by FreeFind


 

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.