Db_file_multiblock_read_count

[tamilselvan]

You can experiment with different values for db_file_multiblock_read_count parameter and choose
the optimum value.

Below is the one test:

SQL> col plan_plus_exp format a99
SQL> set lines 100 pages 55 wrap off
SQL>  alter session set optimizer_mode = choose;
Session altered.
SQL> alter session set db_file_multiblock_read_count= 8 ;
Session altered.

SQL> alter session set optimizer_index_caching= 50   ;
Session altered.

SQL> alter session set optimizer_index_cost_adj = 50 ;
Session altered.

SQL> set autot trace expla
SQL>  select * from t2
  2   where  object_id between 10  and  109;

Execution Plan
---------------------------------------------------------------------------- 
rows will be truncated
   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=97 Card=192,bytes=18432)                          
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=97 Card=192,Bytes=18432)                    
   2    1     INDEX (RANGE SCAN) OF 'T2_INDEX' (NON-UNIQUE) (Cost=2,Card=192)     

SQL> set autot off
SQL> alter session set db_file_multiblock_read_count= 16 ;

Session altered.

SQL> set autot trace expla
SQL> select * from t2
  2  where  object_id between 10  and  109;

Execution Plan
----------------------------------------------------------
rows will be truncated

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=63 Card=192,Bytes=18432)
   1    0   TABLE ACCESS (FULL) OF 'T2' (Cost=63 Card=192,Bytes=18432) 

SQL> spool off

Also, choose the correct values for optimizer_index_caching
and optimizer_index_cost_adj.

Tamil