RE: optimizer_index_cost_adj and optimizer_index_caching

Sorry for the delay in my response. I am just packing things as I start my
journey to Hotsos Symposium in Dallas in 8 hours and I will try to catch
some sleep.

As Wolfgang already mentioned I have personally used system stats on 4 sites
after the migration to 9i. I had also a positive feedback from several DBAs
on Metalink when I suggested them to gather system statistics. An important
fact is that system stats turns on the new costing model that includes also
the cost of CPU processing. I haven't set the O_I_C and O_I_C_A on this
sites (they have default values) but I plan to do some investigation in this
area. As Jonathan Lewis always says that we have to tell the truth to the
CBO so setting O_I_C to the value of BCHR (buffer cache hit ratio) would be
a good idea. Setting of O_I_C_A is good for 8i but for 9i an investigation
would be beneficial as well. According to my experience from those 4 sites
almost all problems vanished when we gathered system statistics. The
problematic statements where those that were specially tuned for RBO and we
had to tune them separately (at 2 sites we also switched from RBO to CBO).
According to the Metalink note you can even achieve 8.1.7 behavior when you
set MREADTIM = 1.2 * SREADTIM and MBRC=8, but I haven't tested this. And
finally I haven't heard for a negative feedback till now and this counts as
well. But as usual happens in real life also the system stats is not solving
all problems. 

Regards,
Joze


-----Original Message-----
From: Wolfgang Breitling [mailto:breitliw@xxxxxxxxxxxxx]
Sent: Friday, March 05, 2004 5:59 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: optimizer_index_cost_adj and optimizer_index_caching


I already said in an earlier post (possibly on a different subject) that I 
am biased against changing O_I_C and particularly O_I_C_A, especially in 
light that it has gained almost silver bullet status. Every post in a 
metalink forum or on c.d.o.s about performance of a sql gets at least one 
response suggesting lowering O_I_C_A as a solution. For me it is just 
another flavour of the folly that views all FTS as evil and index access as 
good.
OK, I step down from the soap box. I do not have experience with Oracle 9 
in a production environment, so I have not had a chance to test it for 
real, but Joze Senegacnik (joze.senegacnik@xxxxxx) seems to have had 
several clients with Oracle 9 and reports good experience with it.

As for "blowing up in ones face", if you think system_stats may be 
dynamite, I would consider O_I_C_A<100 nitroglycerine.

At 08:55 AM 3/5/2004, you wrote:
>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)
> >

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

Other related posts: