RE: MULTI-LOCK IO

Hi Deepak,

1> If I set db_file_multiblock_read_count=128 (MAX_IO_SIZE=1M) then the optimizer may choose FTS
for the other queries as well. Can I set optimizer_index_cost_adj=10 to influence/override the FTS
decission?

     You can, and it will for sure affect profoundly many plans. One bet is to activate it for the OLTP sessions only, via a after logon trigger for example,

     if you conclude that using indexes more thorougly will be ok for the plans. Best is to get as many queries as possible and make some

     tests using traces/set autotrace traceonly (for example), using typical values from the app/users, with OICA set and not. Also, the other

     param commonly set for favoring index access
(optimizer_index_caching) would be adjusted as well (but this last
setting will only influence Nested loops

     joins, favoring them more, via lowering the cost of indexes for them).  

     You can also forgo (on 9.2) optimizer_index_cost_adj, and gather system stats in a realistic interval. Even better, gathering 2 sets of system

     stats, one for OLTP and the other for DSS. You will see that the plans generated using these stats will be good in the majority of cases.

2> Is it a good idea to set db_file_multiblock_read_count=128 at the session level wherever FTS is
required?  

    On 9i, setting this will not guarantee or force a FTS if you are using the cost-based optimizer. There are others factor as well
(clustering factor of indexes, cpu/IO cost, ndv, nulls, etc, etc....)

    which will led the CBO to get a final decision.

3> Will the system statistics override the db_file_multiblock_read_count settings?  

   I don't know if it will be *totally* overriden, but f you're in 9.2 or later, and using the defaults , mbrc and other stats from system stats

   will be the main metrics used for costing.

Please help me on this.

-- 
Regards,

Deepak
Oracle DBA 


--
http://www.freelists.org/webpage/oracle-l