changinging compatible parameter

[ivo]

Hello all,

I've been studying statspack reports for the past few weeks and I noticed that after letting the oraperf.com site analyze these reports, they kept saying that the compatible parameter should be set to 8.1.7. It is currently on 8.1.0. I am not going to second guess this advice, however could someone explain what the advantages are by changing this parameter to 8.1.7?

thanks in advance

[marist89]

Originally posted by ivo

I've been studying statspack reports for the past few weeks and I noticed that after letting the oraperf.com site analyze these reports, they kept saying that the compatible parameter should be set to 8.1.7. It is currently on 8.1.0. I am not going to second guess this advice, however could someone explain what the advantages are by changing this parameter to 8.1.7?

Always question free advice, especially boiler-plate advice using outdated "rules of thumb".

The compatible parameter tells the cost based optimizer what features to use in determining it's query plan. Setting it to Y will tell the optimizer that you are on version Y and you wish to use any optimizations available. By setting compatible to a lower version, you are telling Oracle that you don't want to use any of the new features available (you've just migrated, etc.). By setting your compatible parameter to 8.1.7, you are telling Oracle you want to use any optimizations new to 8.1.7. Keep in mind, this may change some of your query plans and negatively affect performance.

There is a good discussion at http://download-west.oracle.com/docs...ompa.htm#11959 on this very topic.