RE: Oracle 10g upgrade - performance issue

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
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>


--
Best regards,
Alex Gorbachev

http://blog.oracloid.com
--
http://www.freelists.org/webpage/oracle-l


Fyrirvari/Disclaimer
http://www.landsbanki.is/disclaimer
--
http://www.freelists.org/webpage/oracle-l

Other related posts: