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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Alex Gorbachev Sent: 12. júlí 2006 09:02
To: peterdixon001_at_hotmail.com
Cc: oracle-l_at_freelists.org
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_at_hotmail.com>:
> 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