Re: The responsible
- From: Jared Still <jkstill@xxxxxxxxx>
- To: juancarlosreyesp@xxxxxxxxx
- Date: Tue, 7 Feb 2006 10:40:07 -0800
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@xxxxxxxxx> 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@xxxxxxxx> wrote:
> >
> > Just curious, was Oracle upgraded? Patched?
> >
> > ------------------------------
> > *From:*oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> > *On Behalf Of *Juan Carlos Reyes Pacheco
> > *Sent:* Tuesday, February 07, 2006 12:21 PM
> > *To:* Mike Killough
> > *Cc:* oracle-l@xxxxxxxxxxxxx; jonathan@xxxxxxxxxxxxxxxxxx
> > *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@xxxxxxxxxxx > 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@xxxxxxxxx>
> > > >Reply-To: juancarlosreyesp@xxxxxxxxx
> > > >To: oracle-l@xxxxxxxxxxxxx, jonathan@xxxxxxxxxxxxxxxxxx
> > > >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
Other related posts:
