Re: Re: optimizer_index_cost_adj and optimizer_index_caching

I have a feeling that you were playing a bit with the event 10053 to learn what statistics the optimizer is looking into and that gave you the clue about which histogram you needed. I also have problems with hints, although they're considered a "scalpel" and not a "katana" type tool. My problem with hints is that if the background engine changes, my hints can actually mess things up instead of improving them. In version 7.3.2, I've had quite a few /*+ USE_NL INDEX */ and /*+ USE_MERGE */ hints. In 7.3.4 hash join became a a good alternative. Guess what happened to my "hinted" queries?

On 03/10/2004 12:13:46 AM, Jared.Still_at_radisys.com wrote:
> If I correctly understand your post, what you are saying is that
> the init.ora parms O_I_C_A and O_I_C were set on all your
> databases because it helped a problem on two of them.
>
> This is not generally considered a good practice, that is, making
> changes to a parameter with global implications without a specific
> reason for doing so, and the testing to back it up.
>
> I once set these parameters on a database that houses an application
> of somewhat questionable design. It did help, but I was really only
> trying to fix a single query.
>
> So, I put the shotgun back in the cabinet, pulled out the laser scalpal
> and created a histogram on a single column of a single table and
> solved the problem.
>
> HTH
>
> Jared
>
>
>
>
>
>
>
> "Juan Cachito Reyes Pacheco" <jreyes_at_dazasoftware.com>
> Sent by: oracle-l-bounce_at_freelists.org
> 03/09/2004 12:25 PM
> Please respond to oracle-l
>
>
> To: <oracle-l_at_freelists.org>
> cc:
> Subject: Re: Re: optimizer_index_cost_adj and optimizer_index_caching
>
>
> I asked and this how it happened
> one database gave problem, then we found setting this parameters solved
> this
> problem, then we set
> them ONLY in one database
> after about one year other database had the same problem, after some
> months
> other,
> then we decided to set it to all at once.
> because the worload is between and oltp and a dss, we don't have to change
> it.
> That is why I suggest to set it, is better to have a more acurrate value
> set
> in the database. in 9.2 windows
> ----- Original Message -----
> From: <ryan.gaffuri_at_cox.net>
> To: <oracle-l_at_freelists.org>
> Sent: Tuesday, March 09, 2004 9:36 AM
> Subject: Re: Re: optimizer_index_cost_adj and optimizer_index_caching
>
>
> > the debate on this topic is exactly why we need to keep this list going.
> Thanks for all your help guys.
> > >
> > > From: "Juan Cachito Reyes Pacheco" <jreyes_at_dazasoftware.com>
> > > Date: 2004/03/09 Tue AM 08:19:01 EST
> > > To: <oracle-l_at_freelists.org>
> > > Subject: Re: optimizer_index_cost_adj and optimizer_index_caching
> > >
> > > I'm using, because in a specific database a query gave trouble, and I
> fixed
> > > it setting this parameters. Curiously in other production databases
> (more
> > > than 15) similar to that, didn't gave that problem.
> > > But we decided to set it any way.
> > > I think is better to set them, even when in most situation there is
> not
> > > problems.
> > > :)
> > >
> > > ----- Original Message -----
> > > From: "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk>
> > > To: <oracle-l_at_freelists.org>
> > > Sent: Tuesday, March 09, 2004 5:01 AM
> > > Subject: RE: optimizer_index_cost_adj and optimizer_index_caching
> > >
> > >
> > > Thanks Joze and Wolfgang
> > >
> > > I'm in a position where gathering system stats seems to me to be the
> Right
> > > Thing (tm) to do, certainly an advance on setting parameters based on
> > > guesses/measurements ahead of time. I hadn't until now seen any
> indications
> > > as to whether the feature worked as advertised or if in fact gathering
> > > system stats introduced for example unexpected and unwanted plan
> changes,
> > > curious bugs etc etc. In fact I hadn't seen *any* feedback at all
> which
> made
> > > me suspicious that anyone was actually using it - I guess folks recall
> the
> > > introduction of the CBO which was equally the right thing to do but..
> > >
> > > Niall Litchfield
> > > Oracle DBA
> > > Audit Commission
> > > +44 117 975 7805
> > >
> > >
> > >
> > > **********************************************************************
> > > 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
> > > -----------------------------------------------------------------
> > >
> > >
> > > ----------------------------------------------------------------
> > > 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
> > -----------------------------------------------------------------
>
>
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------
>
>
>

-- 
Mladen Gogala
Oracle DBA
----------------------------------------------------------------
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
-----------------------------------------------------------------