Re: system statistics

Jonathan Lewis wrote:

>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
>>
>>
>>

Have you had any luck with CPU costing in DBMS_XPLAN? I have followed every bit of advice Connor sent me
and still get a message indicating statistics collection is not turned on ... while simultaneously seeing the values collecting.

Win2K SP4 Oracle EE 9.2.0.4.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)