Re: how to evaluate optimizer_index_caching

  • From: "Syed Jaffar Hussain" <sjaffarhussain@xxxxxxxxx>
  • To: Bernard.Polarski@xxxxxxxxxxxxxx
  • Date: Mon, 22 Jan 2007 14:39:11 +0300


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:


  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)

  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;


------------ ------------ ------------ -------------- -------------

        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 :
"Winners don't do different things. They do things differently."

Other related posts: