Re: The responsible

There are alternatives to setting OICA and OIC.

I had a similar situation with an app at one time.

Setting OICA and OIC had the desired effect, but had the undesired side effect of being global.

Generating a histogram for a single column on one of the tables in the query also resolved the problem, and as a solution was more limited in the scope of its effects.

Another possibility is using a stored outline.

Yet another is to manually set the statistics.

Jared

On 2/7/06, Juan Carlos Reyes Pacheco <juancarlosreyesp_at_gmail.com> wrote:
>
> No I checked, the problem started when the table and index grow up to a
> limit size (some day in january).
> before the table were too small and I had to set the parameters to get the
> optimizer use the index, now after 5 years had grown up to the point
> one or more execution paths changed and the configuration became
> self-defeating.
>
> On 2/7/06, Grabowy, Chris <chris.grabowy_at_lmco.com> wrote:
> >
> > Just curious, was Oracle upgraded? Patched?
> >
> > ------------------------------
> > *From:*oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> > *On Behalf Of *Juan Carlos Reyes Pacheco
> > *Sent:* Tuesday, February 07, 2006 12:21 PM
> > *To:* Mike Killough
> > *Cc:* oracle-l_at_freelists.org; jonathan_at_jlcomp.demon.co.uk
> > *Subject:* Re: The responsible
> >
> > Hi Mike, the parameters was ok, and they solved the problem (5 years
> > ago)
> > But 5 years after (now), the situation reversed and I had to comment
> > them out.
> > To get the optimize choose the right way.
> >
> > On 2/7/06, Mike Killough <mwkillough_at_hotmail.com > wrote:
> > >
> > > This is why I always set the parameters generic in test and force them
> > > to
> > > reprove their need, before including them in the upgrade.
> > >
> > > Mike
> > >
> > >
> > >
> > > >From: Juan Carlos Reyes Pacheco < juancarlosreyesp_at_gmail.com>
> > > >Reply-To: juancarlosreyesp_at_gmail.com
> > > >To: oracle-l_at_freelists.org, jonathan_at_jlcomp.demon.co.uk
> > > >Subject: The responsible
> > > >Date: Tue, 7 Feb 2006 12:46:54 -0400
> > > >
> > > >HI I want to share this info, not asking.
> > > >
> > > >Some years ago I changed the optimizer parameters, and this solved a
> > > query,
> > > >from minutes to seconds
> > > >OPTIMIZER_INDEX_COST_ADJ = 10
> > > >OPTIMIZER_INDEX_CACHING = 90
> > > >Now the situation reversed, and I had to comment them
> > > >To move a process from 4 minutes to 7 seconds.
> > > >
> > > >using optimizer parameters 10,90
> > > >trace file 870 M
> > > >time 4 minutes (without trace)
> > > >recursive 465.43 s
> > > >non recursive 233.21 s
> > > >
> > > >
> > > >commenting optimizer parameters and leaving with their defaults
> > > >Trace file 6 M
> > > >time 7 seconds
> > > >recursive 5.87 s
> > > >non recursive 1.79 s
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >--
> > > >Oracle Certified Profesional 9i 10g
> > > >Orace Certified Professional Developer 6i
> > > >
> > > >8 years of experience in Oracle 7,8i,9i,10g and developer 6i
> > >
> > >
> > >
> >
> >
> > --
> > Oracle Certified Profesional 9i 10g
> > Orace Certified Professional Developer 6i
> >
> > 8 years of experience in Oracle 7,8i,9i,10g and developer 6i
> >
>
>
>
> --
> Oracle Certified Profesional 9i 10g
> Orace Certified Professional Developer 6i
>
> 8 years of experience in Oracle 7,8i,9i,10g and developer 6i
>

--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l