Re: RE: optimizer_index_cost_adj and optimizer_index

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Fri, 05 Mar 2004 10:24:39 -0700
Message-Id: <6.0.3.0.2.20040305101513.02b78ea0@pop.centrexcc.com>

Again, as with database stats, I am against micro management. Identify your distinct workloads, typical scenario is OLTP-type during the day, batch processing at off-hours, but there may be different scenarios and more distinct workloads than just two, You could also have different scenarios at different times of the month or year. But again, I wouldn't overdo it. We are talking about statistics here. No point in trying to be over-accurate. Once you identified the distinct workloads, capture the system statistics for a representative time while that workload is in effect and store the result in the stattab table under a descriptive statid (read the documentation, it explains it rather well). You do that once, then set up a process (dbms_job comes to mind) that imports the system statistics for the workload prior to the time you expect the conditions to be there for the workload - e.g. load the OLTP system stats at say 6am and the batch stats at 6pm.

Joze, I copied you because I referred to you in earlier posts as reporting good experience with using system statistics in Oracle 9.

At 09:35 AM 3/5/2004, you wrote:
>how do you determine how often to gather system stats?
> >
> > From: "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk>
> > Date: 2004/03/05 Fri AM 10:55:11 EST
> > To: <oracle-l_at_freelists.org>
> > Subject: RE: optimizer_index_cost_adj and optimizer_index_caching
> >
> > Hi wolfgang
> >
> > I actually asked dan fink about this at UKOUG, because I haven't seen
> any investigations into system stats anywhere, do you have some evidence,
> links etc looking into the use of system stats anywhere? It instinctively
> sounds a good idea to me, but one that has the capability of blowing up
> in ones face. I don;'t like explosions much - I do like trying to do
> things the right way.
> >
> > Niall Litchfield
> > Oracle DBA
> > Audit Commission
> > +44 117 975 7805
> >
> > > -----Original Message-----
> > > From: breitliw_at_centrexcc.com
> > > Sent: 05 March 2004 15:04
> > > To: breitliw_at_centrexcc.com; oracle-l_at_freelists.org
> > > Subject: Re: optimizer_index_cost_adj and optimizer_index_caching
> > >
> > >
> > > If you are using Oracle 9, forget about O_I_C_A and O_I_C
> > > (i.e. leave them
> > > at their default) and use dbms_stats.gather_system_stats
> > >
> > > My 0.014938 cents (Canadian currency)
> > >
> > > At 07:30 AM 3/5/2004, you wrote:
> > > >Oracle support(without knowing anything about my system) is
> > > telling me to
> > > >use the following settings:
> > > >
> > > >OPTIMIZER_INDEX_CACHING = 50
> > > >OPTIMIZER_INDEX_COST_ADJ = 5
> > > >
> > > >Tom Kyte's book effective Oracle by Design recommends starting
> > > >optimizer_index_caching at my cache/hit ratio and adjusting
> > > as needed.
> > > >
> > > >Tim Gorman's paper 'Search for Intelligent Life in the Cost-Based
> > > >Optimizer' states that OPTIMIZER_INDEX_COST_ADJ should be
> > > set between 10
> > > >and 50 for most OLTPs.
> > > >
> > > >This is a hybrid system, but I only have the OLTP parts to
> > > stress test(the
> > > >other parts are still in early phase development). Does
> > > anyone have any
> > > >opinions on this?
> > >

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

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
-----------------------------------------------------------------

Received on Fri Mar 05 2004 - 11:46:21 CST

Re: RE: optimizer_index_cost_adj and optimizer_index_caching