how to evaluate optimizer_index_caching
- From: "Polarski, Bernard" <Bernard.Polarski@xxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 22 Jan 2007 12:17:28 +0100
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
Other related posts:
