Re: how to evaluate optimizer_index_caching

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."
--
http://www.freelists.org/webpage/oracle-l


Other related posts: