| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Re: optimizer_index_cost_adj and optimizer_index_caching
>SYSSTATS_INFO STATUS NOWORKLOAD
This says it all. You tried to capture system statistics when there was no
workload on the system, so you got nothing. The purpose of system_stats is
to tell the optimizer about the workload, so therefore the must be
something going on in the database to capture statistics about.
At 02:23 PM 3/9/2004, you wrote:
>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_at_wangtrading.com>
>To: <oracle-l_at_freelists.org>
>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_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
> > > -----------------------------------------------------------------
> > >
> > ----------------------------------------------------------------
> > 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
>-----------------------------------------------------------------
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
-- 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 Tue Mar 09 2004 - 22:18:48 CST
 |
 |