In my posting Remedy for bad hints: SQL Profiles and in the PDF with the same content on my Downloads Page, I mentioned the ability of SQL Profiles to speed up statements dramatically, in a special case even if statements are coded with a „bad hint“, forcing the Optimizer to use a certain access path that is not (longer) good.

Although that is still true, I just got aware of an even simpler method to enable the Optimizer to ignore hints. An undocumented parameter can be used to achieve that, as Jonathan Lewis mentions in this article about handling hints. Of course, you should always be careful in using undocumented parameters and set them only with cooperation of Oracle Support.

Following demonstrates the effect of the parameter _OPTIMIZER_IGNORE_HINTS, introduced in 10g:

SQL> set autotrace on explain
SQL> select sum(amount_sold) from sales where channel_id=1;

SUM(AMOUNT_SOLD)
----------------
 1232.16

Execution Plan
----------------------------------------------------------
Plan hash value: 479458069

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time    
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |     1 |    26 |     4   (0)| 00:00:01
|   1 |  SORT AGGREGATE              |            |     1 |    26 |            |         
|   2 |   TABLE ACCESS BY INDEX ROWID| SALES      |     1 |    26 |     4   (0)| 00:00:01
|*  3 |    INDEX RANGE SCAN          | SALES_CHID |     1 |       |     3   (0)| 00:00:01
-----------------------------------------------------------------------------------------

SQL> select sum(amount_sold) from sales where channel_id=3;

SUM(AMOUNT_SOLD)
----------------
 463000853

Execution Plan
----------------------------------------------------------
Plan hash value: 1047182207

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |    26 |  9833   (2)| 00:01:58 |
|   1 |  SORT AGGREGATE    |       |     1 |    26 |            |          |
|*  2 |   TABLE ACCESS FULL| SALES |  4541K|   112M|  9833   (2)| 00:01:58 |
----------------------------------------------------------------------------

SQL> select /*+ index (sales,SALES_CHID) */ sum(amount_sold) 
     from sales where channel_id=3;

SUM(AMOUNT_SOLD)
----------------
 463000853

Execution Plan
----------------------------------------------------------
Plan hash value: 479458069

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time    
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |     1 |    26 | 70278   (1)| 00:14:04
|   1 |  SORT AGGREGATE              |            |     1 |    26 |            |         
|   2 |   TABLE ACCESS BY INDEX ROWID| SALES      |  4541K|   112M| 70278   (1)| 00:14:04
|*  3 |    INDEX RANGE SCAN          | SALES_CHID |  4541K|       |  8999   (1)| 00:01:48
-----------------------------------------------------------------------------------------

In the same way as already explained in my above mentioned posting, I force the Optimizer with a hint to use an index, which in this case is suboptimal. The underscore parameter lets the Optimizer ignore any hint. Session as DBA:

SQL> alter system set "_optimizer_ignore_hints"=true;

System altered.

Same session as „application user“ as before:

SQL> select /*+ index (sales,SALES_CHID) */ sum(amount_sold) 
     from sales where channel_id=3;

SUM(AMOUNT_SOLD)
----------------
 463000853

Execution Plan
----------------------------------------------------------
Plan hash value: 1047182207

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |    26 |  9833   (2)| 00:01:58 |
|   1 |  SORT AGGREGATE    |       |     1 |    26 |            |          |
|*  2 |   TABLE ACCESS FULL| SALES |  4541K|   112M|  9833   (2)| 00:01:58 |
----------------------------------------------------------------------------

As you see, the exactly same valid hint as before is now ignored. You can set the undocumented parameter session- or systemwide. But be aware that it will make the Optimizer ignore any hint, useful or not. That is why you may still prefer to use the SQL Profile method. Also, SQL Profiles can do more than only enable the Optimizer to ignore hints of course.