RE: optimizer_index_cost_adj and optimizer_index

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Fri, 05 Mar 2004 15:55:11 +0000
Message-Id: <s048a2fd.003@bristol21.bristol.ac>

Hi wolfgang

I actually asked dan fink about this at UKOUG, because I haven't seen any investigations into system stats anywhere, do you have some evidence, links etc looking into the use of system stats anywhere? It instinctively sounds a good idea to me, but one that has the capability of blowing up in ones face. I don;'t like explosions much - I do like trying to do things the right way.

Niall Litchfield
Oracle DBA
Audit Commission
+44 117 975 7805

> -----Original Message-----
> From: breitliw_at_centrexcc.com
> Sent: 05 March 2004 15:04
> To: breitliw_at_centrexcc.com; oracle-l_at_freelists.org
> Subject: Re: optimizer_index_cost_adj and optimizer_index_caching
>
>
> If you are using Oracle 9, forget about O_I_C_A and O_I_C
> (i.e. leave them
> at their default) and use dbms_stats.gather_system_stats
>
> My 0.014938 cents (Canadian currency)
>
> At 07:30 AM 3/5/2004, you wrote:
> >Oracle support(without knowing anything about my system) is
> telling me to
> >use the following settings:
> >
> >OPTIMIZER_INDEX_CACHING = 50
> >OPTIMIZER_INDEX_COST_ADJ = 5
> >
> >Tom Kyte's book effective Oracle by Design recommends starting
> >optimizer_index_caching at my cache/hit ratio and adjusting
> as needed.
> >
> >Tim Gorman's paper 'Search for Intelligent Life in the Cost-Based
> >Optimizer' states that OPTIMIZER_INDEX_COST_ADJ should be
> set between 10
> >and 50 for most OLTPs.
> >
> >This is a hybrid system, but I only have the OLTP parts to
> stress test(the
> >other parts are still in early phase development). Does
> anyone have any
> >opinions on this?
>
> Wolfgang Breitling
> Oracle7, 8, 8i, 9i OCP DBA
> Centrex Consulting Corporation
> http://www.centrexcc.com
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
>

This email contains information intended for the addressee only. It may be confidential and may be the subject of legal and/or
professional privilege. Any dissemination, distribution, copyright or use of this
communication without prior permission of the sender is strictly prohibited.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Mar 05 2004 - 10:44:36 CST

RE: optimizer_index_cost_adj and optimizer_index_caching