init.ora parameter COMPATIBLE= ?.?.?

[sreddy]

Initially database was having COMPATIBLE parm set to 815 and to take advantage of 817 features of optimizer it has been set to 817. Performance gone worse so, we set it back to 815, its not giving me the same plan as it was giving me before... Any experiences with this parameter ? specific to Optimizer behaviour ??

It was using SORT, MERGE JOIN before and now its using HASH JOIn thought I put back the compatible parameter the way it was ?

[stecal]

You have to do some other steps to make Oracle "forget" about its current compatible parameter.

Note: it is very important that you perform a full OFFLINE backup *before* restarting the instance with the new(changed) COMPATIBLE setting and also *afterwards*. Oracle internally writes the COMPATIBLE version number to the Oracle file headers. This includes controlfiles, archive files, datafiles etc.

When restarting your instance, the new archive files being written will include the new COMPATIBLE setting. Even though Oracle does guarantee backward compatiblity, it is still recommendable to perform backups as described above:
one backup with old COMPATIBLE parameter and another backup immediately after changing the COMPATIBLE parameter.