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@xxxxxxxxxxxxxxxxxxxxxxx>
> Date: 2004/03/05 Fri AM 10:55:11 EST
> To: <oracle-l@xxxxxxxxxxxxx>
> 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@xxxxxxxxxxxxx
> > Sent: 05 March 2004 15:04
> > To: breitliw@xxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
> > 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?
> >
---------------------------------------------------------------- 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 -----------------------------------------------------------------
