Below issue pertains to Solaris 10 with Oracle 18.104.22.168 and 10.2.0.3.
There was one particular query which is reported to be working fine on a database running on 10.2.0.3. The same was running fine on all such databases on same version in the platform until one of the databases were upgraded to 22.214.171.124. Initially after upgrade the query was taking 3 mins to run, but as per client requirement it should complete by 30 seconds! I used all possible ways to tune it and the execution time of the query came down to 1min20secs, which was not acceptable.
However when I changes the "optimizer_features_enable" parameter on this newly upgraded databases from 126.96.36.199 to 10.2.0.3 (I.e., I am making it similar to what is there in other databases where this query is performing good!), then the query returns in 5 seconds! Even if I remove the indexes I created to tune it, it completes by 10 secs.
I am not entirely sure (especially after this incident!) what "optimizer_features_enable" parameter is doing internally?
Can you please throw some light on it?
Note: I can provide you with the SQL Query and its previous and new Explain plans, if you want to have a look.