Re: system statistics

Even when system stats are active, the
optimizer_index_cost_adj and optimizer_index_caching still have an effect.

The system stats allow Oracle to work out the relative cost if block acquisition turns into a physical I/O - the other two give it an indication (effectively) of how much of that I/O is likely to be buffered and therefore 'free'.

Taking the view that the older parameters indicate caching, you need to view the
optimized_index_cost_adj as the percentage of the I/O that DOES become physical if
you want a 'rational' justification for choosing a setting.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____Iceland__November (tbc)
____Belgium__November (EOUG event)
____UK_______December (UKOUG conference)


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____USA__October
____UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Richard Kuhler" <noone_at_nowhere.com> wrote in message
news:ROXgb.2708$Z86.2251_at_twister.socal.rr.com...


> I'm reading "Effective Oracle by Design" by Thomas Kyte.  He seems

to be


> advocating using GATHER_SYSTEM_STATS _instead_of_ tuning

> OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ directly.  I

can't


> find anything in the Oracle documentation that suggests that the

> presence of system statistics causes the optimizer to ignore the

other


> settings.  While I intend to do some experimentation, I'm wondering

if


> anyone can make a definitive statement in this regard.  Or, am I

just


> misinterpreting the passage?

>

> Anyone have an opinion or real world experience to share on this?

>

> --

> Richard Kuhler

>