how to evaluate optimizer_index

From: Polarski, Bernard <Bernard.Polarski_at_atosorigin.com>
Date: Mon, 22 Jan 2007 12:17:28 +0100
Message-ID: <25D4919915CCF742A88EE3366D6D913D11771DD2@mailserver1>

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

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

Received on Mon Jan 22 2007 - 05:17:28 CST

how to evaluate optimizer_index_caching