Re: formula for OPTIMIZER_INDEX_COST_ADJ

On Dec 11, 9:12 am, Helma <helma.vi..._at_hotmail.com> wrote:
> Hello *,
>
> I am now looking at setting the optimizer_index_cost_adj. I read that
> a nice starting point would be to look at the ratio of the dbfile
> sequential read vs the scattered read (seehttp://www.evdbt.com/SearchIntelligenceCBO.doc
> for details.) However,
>
> EVENT                                                   TOTAL_WAITS
> AVERAGE_WAIT
> ----------------------------------------------------------------
> ----------- ------------
> db file sequential read
> 1704269587            0
> db file scattered read
> 238876588            0
>
> this would be a  0/0 division. I don't think these numbers are
> correct. Timed statistics is TRUE:
>
> SQL> show parameter timed
>
> NAME                                 TYPE
> VALUE
> ------------------------------------ --------------------------------
> ------------------------------
> timed_os_statistics                  integer
> 0
> timed_statistics                     boolean
> TRUE
>
> Startup time is 3 weeks. So how is it possible that the average wait
> is always zero?
>
> Thanks for any help

The guideline is to query v$system_event for the TIME_WAITED value for each statistic, not the average time waited. The average could very well be so small that is essentially 0. Run this instead and I think you'll get a 'better' value:

select round((s.time_waited/e.time_waited)*100, 0) optimizer_index_cost_adj
from v$system_event s, v$system_event e
where s.event = 'db file sequential read' and e.event = 'db file scattered read'
/

David Fitzjarrell Received on Thu Dec 11 2008 - 09:48:29 CST