EBS 10g upgrade problem

Recently we tried to upgrade our database from 9.2.0.6 to 10.2.0.2 following Metalink Note: 362203.1. [FYI: 10.2.0.3 is now certified with EBS and the metalink note has been updated to reflect that.]

The upgrade was applied to a dev environment with no issues and passed the developers sanity checks. Next we upgraded our stage environment but an issue was flagged during user acceptance testing. The issue was isolated to one custom form.

What made this issue confusing was that the problem only existed in stage, not our dev environment. To cut a long story short the problem was related to a known 10g upgrade issue.

Note: 345048.1
Subject: 'Group By' does not sort if you don't use order by in 10g.

The gist behind this note is that in 10g the group by clause uses a hash algorithm to group data while in 9i a sort algorithm was used. As all DBA's know, there is no guarantee that rows will be returned in the same order after each execution unless you use an order by clause. I guess at the time the form was created, the results were returned in an order favorable for the developer.

This doesn't answer why this problem wasn't consistent between the two upgraded environments. When comparing init parameters between both databases, it was noticed that the optimizer_features_enable parameter was not the same. In the environment with the form issue the value was 10.2.0 and the other environment was set to 9.2.0. This is actually one of the workarounds stated in the note above.

As part of the 10g upgrade for EBS you have to modify the init parameters detailed in Note 216205.1, Database Initialization Parameters (init.ora settings) in Oracle Applications Release 11i. While upgrading the problem environment, the DBA had issues reseting a few parameters, optmimizer_features_enable was one of them, so it remained at its 9.2.0 value.

The command used to reset an init parameter if your using an spfile is:

SQL> alter system reset optimizer_features_enable scope=spfile sid='*';

In one environment it worked, but the problem environment returned an error:

ERROR at line 1:
ORA-32010: cannot find entry to delete in SPFILE

In the end the problem was solved, but if both upgrades had been identical some of the confusion and alot of work would have been avoided. The process to find this solution was pretty lengthy.

As a result, we have asked our developers to go through custom code and verify order by clauses are being used when the query also has a group by. It also hightlights the importance of making sure identical steps are followed when promoting changes and to investigate anything different.