Bernard, I dont know whether my reply will be useful or you already might be knowing this. Tempering with OPTIMIZER_INDEX* parameter needs very careful testing and setting other than default value to OPTIMIZER_INDEX* would result favoring NESTED LOOP. On 1/22/07, Polarski, Bernard <Bernard.Polarski@xxxxxxxxxxxxxx> wrote:
I am trying to evaluate a value for optimizer_index_caching. The default setting is 0 which tell CBO to ignore the probability that an index block is in memory Ok, it is obviously false since some blocks are always in memory. I made the following query and determine that 1/7 of my blocks are index type: select count(case when o.object_type= 'INDEX' then 1 end) index_blocks, count(case when o.object_type= 'INDEX PARTITION' then 1 end) idx_part_blk, count(case when o.object_type= 'TABLE' then 1 end) table_blocks, count(case when o.object_type= 'TABLE PARTITION' then 1 end) tbl_part_blcks, count(case when o.object_type != 'TABLE' and o.object_type != 'INDEX' and o.object_type != 'TABLE PARTITION' and o.object_type != 'INDEX PARTITION' then 1 end) others_blocks from dba_objects o, v$bh bh where o.data_object_id = bh.objd; INDEX_BLOCKS IDX_PART_BLK TABLE_BLOCKS TBL_PART_BLCKS OTHERS_BLOCKS ------------ ------------ ------------ -------------- ------------- 5373 2078 41395 283 2465 Big deal, I learned that one on seven bocks belong to the family index : (5373+2078)/ (41395+283) += 1/7 But this still does not preclude the chances of the CBO to find the index block in memory. Any idea how I could further develop the search on this parameter? Same problem with OPTIMIZER_INDEX_COST_ADJ how to calculate his value? Bernard Polarski Oracle DBA
-- Best Regards, Syed Jaffar Hussain 8i,9i & 10g OCP DBA I blog at :http://jaffardba.blogspot.com/ http://www.oracle.com/technology/community/oracle_ace/ace1.html#hussain ---------------------------------------------------------------------------------- "Winners don't do different things. They do things differently." -- //www.freelists.org/webpage/oracle-l