I thought I had already posted on this, but I didn’t. I recently used this hint to resolve an issue with a custom PeopleSoft Financials batch process running too long. A particular query was taking over an hour instead of seconds. Here is the hint:
/*+ optimizer_features_enable('10.1.0') */
The plan for the problem query included a HASH GROUP BY operation and there are known bugs in the 10.2 version of Oracle with hash group by taking a lot of temp space and running a long time. The cool thing about the optimizer_features_enable hint is that it only affects the one SQL statement. The hint I used told the optimizer only to use features available in 10.1.0 and then the query did a SORT GROUP BY. With the hint the query runs in a second.
So, if you have a problem query that is getting an error or running too long because of a bug in a feature that is new in your release it might work better if you use optimizer_features_enable to disable the use of the latest features.
Note that only certain version numbers are valid and those are documented in the reference manual. Here is the current 11.2 document: link to manual
– Bobby
