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_at_atosorigin.com> 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