That's right - they are just guesses ... I used them on all our OLTP
databases - and performance
improved on all of them ...
When we went from RBO to CBO I got complaints that things were slow - ok - I
saw that about
20% of all queries got crappy execution plans. Now I can start tuning here
and there, adding hints,
stored outlines, rewrite stuff ... but basically if the RBO gives a more
performant execution than CBO
then ... hey CBO is not doing a great job.
With these parameters set, those 20% of queries gave me no trouble anymore.
Now - I'm not saying you should do this in warehouse environments (heck you
will not
like this either - but there's an event you can set that makes the optimizer
choose for
hash join every time ... no need to tell i've set this on our warehouse
...oh we have no
indexes in it :-) )
Anyways - I think it's unrealistic to leave optimizer_index_caching=0 for
OLTP.
I know Jonathan Lewis wrote a paper on it ... or has a presentation on it
...maybe he will share
his findings with us because those parameters are sure handy.But I
think these values 90&10 are quite save on OLTP.
Unlike a posting a saw
<QUOTE>
If all else fails, try init.ora
optimizer_index_cost_adj = 1
optimizer_index_caching = 100
--
Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
</QUOTE>
This is an old quote - so he has probably corrected himself over time :-)
Anayways - maybe I should have said :'This does the trick for me on OLTP ...
and on VLDB warehouses
I set a wicked event and optimizer_index_cost_adj=500' :-)
Cheers,
Kurt
"Richard Foote" <richard.foote_at_bigpond.com> wrote in message
news:DSFGa.1373$Ab2.34951_at_newsfeeds.bigpond.com...
> "koert54" <nospam_at_nospam.com> wrote in message
> news:MpnGa.51059$1u5.3954_at_afrodite.telenet-ops.be...
> > Try setting
> > optimizer_index_caching to 90
> > optimizer_index_cost_adj to 10
> > It does the trick for me when I see RBO is giving better performance
than
> > CBO
>
> Hi Koert54,
>
> I agree that these parameters are important to the CBO and that in many
> cases your suggested values are probably better than the defaults
*however*
> because they are so influential they need to be set appropriately
according
> to your particular environments.
>
> Otherwise, you suggested values are just guesses and could result in more
> efficient FTS plans being ignored.
>
> And if it wasn't so late I'd go into it in more detail.
>
> Cheers
>
> Richard
>
>
Received on Sat Jun 14 2003 - 09:51:08 CDT
