SYSSTATS_INFO STATUS NOWORKLOAD
This is more acurrate for me (more acurrate than the default values) OPTIMIZER_INDEX_COST_ADJ = 10 # Cambiar a 50 en datawarehousing OPTIMIZER_INDEX_CACHING = 90 # No cambiar, posibilidad de encontrar indice en cache
But could you please giveme an example, I never did that And I agre this approach is not too scientific, but this is the advice I got before to set them and then try distinct values.
I runed dbms_stats.gather_system_stats And this is from sys.aux_stats$ :) as you said, but I don't know how can I set them based on this.
SNAME PNAME PVAL1 PVAL2 ------------------------------ ------------------------------ -------------- ------------------------- -------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ----------------------------------------------------- SYSSTATS_INFO STATUS NOWORKLOAD SYSSTATS_INFO DSTART 03-09-2004 17:20 SYSSTATS_INFO DSTOP 03-09-2004 17:20 SYSSTATS_INFO FLAGS 1 SNAME PNAME PVAL1 PVAL2 ------------------------------ ------------------------------ -------------- ------------------------- -------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ----------------------------------------------------- SYSSTATS_INFO STATUS NOWORKLOAD SYSSTATS_INFO DSTART 03-09-2004 17:20
SYSSTATS_INFO DSTOP 03-09-2004 17:20 SYSSTATS_INFO FLAGS 1
----- Original Message ----- From: "Mladen Gogala" <mladen@xxxxxxxxxxxxxxx> To: <oracle-l@xxxxxxxxxxxxx> Sent: Tuesday, March 09, 2004 5:11 PM Subject: Re: Re: optimizer_index_cost_adj and optimizer_index_caching
> More accurate value? What is "more accurate value"? If you want accurate values, > you have dbms_stats.gather_system_stats and sys.aux_stats$ table. > What you want is to make CBO work like RBO and you succeeded. > > On 03/09/2004 03:25:55 PM, Juan Cachito Reyes Pacheco wrote: > > 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@xxxxxxx> > > To: <oracle-l@xxxxxxxxxxxxx> > > 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@xxxxxxxxxxxxxxxx> > > > > Date: 2004/03/09 Tue AM 08:19:01 EST > > > > To: <oracle-l@xxxxxxxxxxxxx> > > > > 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@xxxxxxxxxxxxxxxxxxxxxxx> > > > > To: <oracle-l@xxxxxxxxxxxxx> > > > > 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@xxxxxxxxxxxxx > > > > put 'unsubscribe' in the subject line. > > > > -- > > > > Archives are at //www.freelists.org/archives/oracle-l/ > > > > FAQ is at //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@xxxxxxxxxxxxx > > > > put 'unsubscribe' in the subject line. > > > > -- > > > > Archives are at //www.freelists.org/archives/oracle-l/ > > > > FAQ is at //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@xxxxxxxxxxxxx > > > put 'unsubscribe' in the subject line. > > > -- > > > Archives are at //www.freelists.org/archives/oracle-l/ > > > FAQ is at //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@xxxxxxxxxxxxx > > put 'unsubscribe' in the subject line. > > -- > > Archives are at //www.freelists.org/archives/oracle-l/ > > FAQ is at //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@xxxxxxxxxxxxx > put 'unsubscribe' in the subject line. > -- > Archives are at //www.freelists.org/archives/oracle-l/ > FAQ is at //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@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //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@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------
