RE: Oracle 10g upgrade - performance issue
- From: "Laimutis Nedzinskas" <Laimutis.Nedzinskas@xxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 12 Jul 2006 12:14:46 -0000
First of all - 10r2 does not support rule hints, does it?
Next, my understanding is that the biggest flaw in this CBO thing is the false
believe that CBO is not RULE based.
Kind of "collect accurate statistics and hapiness will come"
A closer look reveals that CBO is rule (i.e. heuristics) based. Which seem to
change between Oracle versions and even more important there are a number of
bugs, fixed in one version, introduced in another and so on.
Which means that sometimes those heuristics work but sometimes not.
One rather practical approach is to try to disable as many new features as
possible. I actually liked this recommendation:
_b_tree_bitmap_plan FALSE
hash_join_enabled TRUE
log_buffer 1048576 # at least, can go to 2M
optimize_index_cost_adj 10
share_pool_size 218103808 # at least, can go to 1-2G
May be optimizer features parameter can help too?
Another approach is try to play wiht plan stability, i.e. outlines. I was not
very impressed, i.e. it did not work for me. It worked for one simple test SQL
but for a real 100 row explain plan it failed. May be I've done something wrong
but Oracle official and unofficial(not supported) documentation is rather
little, Oracle engine says nothing why it accepts or not accepts outline
recommendation. The are mixed opinions how it works for literal sql and
cursor_sharing=force.
One more approach is to try to manipulate statistics which are input into CBO
but you have to know exactly which sql fails and how.
The last thing is to de-grade to 8i unless you have a chance to fix sql for
10r2.
Brgds, Laimis N.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Alex Gorbachev
Sent: 12. júlí 2006 09:02
To: peterdixon001@xxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Oracle 10g upgrade - performance issue
And this is called "upgraded smoothly"? ;-) Anyway, if you desperate than some
of quick fixes might be:
- optimizer_features_enable init.ora parameter
- statistics
- optimizer_mode init.ora parameter
- enable/disable CPU consting.
There is no silver bullet and as Allen mentioned "Method R" might be your
friend but you need some time to get into it and actually some time to use it.
But its results would be most probably the best. ;-)
2006/7/11, Peter Dixon <peterdixon001@xxxxxxxxxxx>:
> Help!
>
> Upgraded 8.1.7.4 to 10g rel 2 smoothly over the weekend. Batch works
> fine, but OLTP is causing CPU and disk i/o to max out at 100%, i
> believe the optimiser is making bad decisions as my introducing rule
> hints the problem has slightly improved. Any ideas as our SR with Oracle says
> "tune the code"
> also we never got the issues in test.
>
> Peter Dixon
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Best regards,
Alex Gorbachev
http://blog.oracloid.com
--
//www.freelists.org/webpage/oracle-l
Fyrirvari/Disclaimer
http://www.landsbanki.is/disclaimer
--
//www.freelists.org/webpage/oracle-l
Other related posts:
