Ignorer les hints ( _OPTIMIZER_IGNORE_HINTS)

A la question "est-il possible de dire au CBO d’ignorer les hints ? " la réponse est OUI.
Il existe en effet un paramètre caché nommé _OPTIMZER_IGNORE_HINTS qui par défaut est à FALSE et qui peut être mis à TRUE si l’on veut que l’optimiseur ne prenne pas en considération lors du parsing les hints contenus dans les requêtes SQL.

Cette astuce peut être utile notamment après un upgrade de votre base Oracle pour déterminer si les hints présents dans la version précédente de la base sont toujours nécessaires dans la nouvelle version. En combinant cette astuce avec SQL Performance Analyzer (fonctionnalité du Real Application Testing disponible en 11g seulement) vous pouvez très rapidement déterminer si en supprimant les hints les requêtes s’exécutent mieux ou moins bien après le passage en 11g.

Exemple:

Voici un petit test case très simple permettant de constater l' efficacité du paramètre _OPTIMZER_IGNORE_HINTS :

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> create index t1_idx on t1(object_id);

Index created.

SQL> select count(1) from t1;

  COUNT(1)
----------
     50574

SQL> exec dbms_stats.gather_table_stats(user,'T1',method_opt=>'for all columns size 1');

PL/SQL procedure successfully completed.

SQL> explain plan for select * from t1 where object_id=20;

Explained.

SQL> select * from table(dbms_xplan.display);

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    93 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |    93 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=20)


SQL> explain plan for select /*+ full(t1) */ * from t1 where object_id=20;

Explained.

SQL> select * from table(dbms_xplan.display);

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    93 |   157   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    93 |   157   (2)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=20)

13 rows selected.

L’exemple précédent montre que le hint FULL(T1) force le Full Table Scan.
Sans le hint le CBO choisit un accès indexé.

Activons maintenant le paramètre _OPTIMZER_IGNORE_HINTS au niveau de la session:

SQL> alter session set "_optimizer_ignore_hints"=TRUE;

Session altered.

SQL> explain plan for select /*+ full(t1) */ * from t1 where object_id=20;

Explained.

SQL> select * from table(dbms_xplan.display);

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    93 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |    93 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=20)

On s’aperçoit que le CBO a bien ignoré le hint qui pourtant fonctionnait bien lorsque le paramètre caché _OPTIMZER_IGNORE_HINTS était à sa valeur par défaut à savoir FALSE.

On peut aussi désactiver ce paramètre uniquement au niveau de la requête via un hint OPT_PARAM.
On a donc là un hint permettant de désactiver les hints. N’est-ce pas magnifique ?

SQL> explain plan for select /*+ opt_param('_optimizer_ignore_hints' 'TRUE') full(t1) */ * from t1 where object_id=20;

Explained.

SQL> select * from table(dbms_xplan.display);


--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    93 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |    93 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=20)

Si on met le OPT_PARAM après le hint FULL, le hint n’est alors pas ignoré. J’avoue avoir été surpris lorsque j’ai fait le test :

SQL> explain plan for select /*+ full(t1) opt_param('_optimizer_ignore_hints' 'TRUE') */ * from t1 where object_id=20;

Explained.

SQL> select * from table(dbms_xplan.display);

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    93 |   157   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    93 |   157   (2)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=20)