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)
SQL> show parameter _optimizer NAME TYPE VALUE ------------------ ----------- ------------- _optimizer_ignore_hints boolean FALSE
Execute a query with a HINT.
SQL> select /*+ FULL(TEST5) */ count(*) from TEST5 where owner='SYS';
COUNT(*)
----------
42814
Execution Plan
----------------------------------------------------------
Plan hash value: 529722805
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 480 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | TABLE ACCESS FULL| TEST5 | 1184 | 8288 | 480 (1)| 00:00:01 | ------- >>>> .FULL SCAN DUE TO FULL 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:
SQL> alter session set "_optimizer_ignore_hints"=TRUE ;
Session altered.
SQL> select /*+ FULL(TEST5) */ count(*) from TEST5 where owner='SYS';
COUNT(*)
----------
42814
Execution Plan
----------------------------------------------------------
Plan hash value: 1056693648
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX RANGE SCAN| TE | 1184 | 8288 | 3 (0)| 00:00:01 | ------ >>>> IGNORED FULL HINT, WENT FOR INDEX .
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS')
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.
SQL> show parameter _optimizer
NAME TYPE VALUE
------------------ ----------- -------------
_optimizer_ignore_hints boolean TRUE
SQL> select /*+ parallel(4) */ count(*) from TEST5;
COUNT(*)
----------
107708
Execution Plan
----------------------------------------------------------
Plan hash value: 4160549356
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 133 (0)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 107K| 133 (0)| 00:00:01 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| TEST5 | 107K| 133 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 4 because of hint
For PARALLEL hints Oracle has another setting that can be set by:
alter session set optimizer_ignore_parallel_hints=true;