RE: optimizer_index_cost_adj and optimizer_index

From: Larry Elkins <elkinsl_at_flash.net>
Date: Fri, 5 Mar 2004 11:23:40 -0600
Message-ID: <MPBBKDBLJAGDLMINJNKBOEAIBEAB.elkinsl@flash.net>

So you define the timeframe for gathering the stats. And in a mixed case workload, say OLTP during the day, big batch processing during the night, do you gather two sets of stats with an appropriate timeframe, and swap them out, just as described in chapter 3 of the 9i tuning guide? Do you find yourself needing to manually tweak the system stats? And I've seen systems where there is a predictable change in workload during the day, simply based on how people do their work, so in a case like that, maybe even swap out system stats during the day (assuming you have a good handle on how the system is used)?

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Wolfgang Breitling
> Sent: Friday, March 05, 2004 9:05 AM
> To: 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
> -----------------------------------------------------------------
>

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 - 12:25:06 CST

RE: optimizer_index_cost_adj and optimizer_index_caching