optimizer_features_enable hint

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

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.