Sometimes application queries come with the specific hint, which may impact your database performance. And it is difficult to find and remove these hints from each query.

Oracle provided an undocumented hidden parameter, _optimizer_ignore_hint. If this parameter is set to true, Then it will ignore the hints mentioned in the SQL queries.

DEFAULT VALUE OF _optimizer_ignore_hint is FALSE.

Let’s run a test, to see how it behaves.

EXAMPLE:
Parameter is set to FALSE:(DEFAULT)

Execute a query with a  HINT.

We can see, as we have used a FULL hint, TABLE ACCESS FULL is used. That does not change the query behavior.

Now, lets set it to TRUE and re-run the same query.

The parameter is set to TRUE:

Now despite using a FULL hint, it was ignored because of the parameter _optimizer_ignore_hints.

NOTE – Don’t use in production database without proper testing, Because it will disable all the hints used in sql queries, which might be recommended by your Application.

EXCEPTION:

One interesting point, we observed is that, this parameter is not having any impact on PARALLEL hint. I.e even if we set this to TRUE, PARALLEL hint will work as expected.