Ajouter un hint sans toucher à la requête (grâce au SQL patch)

Dans mon article précédent, j'avais tenté de montrer comment grâce à l'utilisation d'un SQL profile il m'a été possible d'injecter un hint à une requête pour forcer le CBO à utiliser un index plus performant. Franck Pachot a alors rebondi sur ce post via un commentaire pour me faire remarquer que l'utilisation d'un SQL patch aurait permis d'arriver au même résultat comme intelligemment montré dans l'article Using SQL Patch to add hints to a packaged application issue du blog Oracle Optimizer (blog que je recommande fortement puisque les articles sont écrits par des membres de l'équipe de développement de l'optimiseur Oracle, autant dire que ces gens savent de quoi ils parlent).

Le SQL patch pour ceux qui ne le savent pas est une sorte d'objet (généralement crée par le SQL Repair Advisor) et qui permet d'indiquer au CBO de changer de plans pour éviter des bugs ou des problèmes  lors par exemple de l'accès à certaines structures ou à des jointures. Il est toutefois possible de se créer son propre SQL patch en utilisant la procédure (non documentée) i_create_patch du package dbms_sqldiag_internal. Un peu comme avec le package DBMS_SQLTUNE, on peut détourner le but premier du SQL patch pour indiquer à l'optimiseur d'utiliser tel ou tel hint pour une requête donnée.

Pour illustrer le principe, je vais repartir de ma requête précédente pour laquelle j'avais crée un SQL profile.
Je supprime d'abord le SQL profile pour que le CBO se remette à utiliser le mauvais index

SQL>  exec dbms_sqltune.drop_sql_profile('PROFILE_9ngkg85n7v9ks_MANUAL');

PL/SQL procedure successfully completed.

SQL> select h.TOTAL_NAV
  2    from historique h, AM_NAV_STATUS_TRANSITIONS am
  3   where h.sicovam = am.code
  4     and h.sicovam = 68000339
  5     and h.TOTAL_NAV is not null
  6     and h.jour <= 39623 + to_date('01/01/1904', 'DD/MM/YYYY')
  7     and am.new_state not in (7, 8)
  8   order by h.jour desc;

....

 
7290 rows selected.

Plan hash value: 2065181612

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                           |      1 |        |   7290 |00:00:00.05 |    1453 |
|   1 |  SORT ORDER BY                |                           |      1 |   1588 |   7290 |00:00:00.05 |    1453 |
|*  2 |   HASH JOIN                   |                           |      1 |   1588 |   7290 |00:00:00.05 |    1453 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| HISTORIQUE                |      1 |      5 |    486 |00:00:00.04 |    1447 |
|*  4 |     INDEX RANGE SCAN          | IHISTOJOUR                |      1 |      1 |    486 |00:00:00.04 |    1404 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| AM_NAV_STATUS_TRANSITIONS |      1 |    335 |     15 |00:00:00.01 |       6 |
|*  6 |     INDEX RANGE SCAN          | IDX_AM_NAV_CODE           |      1 |    596 |     16 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------------------

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

   2 - access("H"."SICOVAM"="AM"."CODE")
   3 - filter("H"."TOTAL_NAV" IS NOT NULL)
   4 - access("H"."SYS_NC00093$">=HEXTORAW('878FF9E6FEF8FEFAFF')  AND "H"."SICOVAM"=68000339 AND
              "H"."SYS_NC00093$" IS NOT NULL)
       filter(("H"."SICOVAM"=68000339 AND SYS_OP_UNDESCEND("H"."SYS_NC00093$")<=TO_DATE(' 2012-06-25
              00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
   5 - filter(("AM"."NEW_STATE"<>7 AND "AM"."NEW_STATE"<>8))
   6 - access("AM"."CODE"=68000339)

Le SQL profile n'existant plus le CBO se remet à choisir l'index IHISTOJOUR au lieu de l'index IHISTO.

Je crée un SQL patch pour injecter le hint index(h@SEL$1 IHISTO):

SQL> begin
  2     sys.dbms_sqldiag_internal.i_create_patch(
  3             sql_text        =>      'select h.TOTAL_NAV
  4                                               from historique h, AM_NAV_STATUS_TRANSITIONS am
  5                                              where h.sicovam = am.code
  6                                                and h.sicovam = 68000339
  7                                                and h.TOTAL_NAV is not null
  8                                                and h.jour <= 39623 + to_date(''01/01/1904'', ''DD/MM/YYYY'')
  9                                                and am.new_state not in (7, 8)
 10                                              order by h.jour desc',
 11     hint_text       =>      'index(h@SEL$1 IHISTO)',
 12     name    => 'TEST_SQL_PATCH');
 13  END;
 14  /

PL/SQL procedure successfully completed.

On peut vérifier dans la vue DBA_SQL_PATCHES que le patch a bien été crée:

SQL> select name,status from DBA_SQL_PATCHES;

NAME                           STATUS
------------------------------ --------
TEST_SQL_PATCH                 ENABLED

1 row selected.

Je relance ma requête pour voir si le hint est bien pris en compte:

SQL> select h.TOTAL_NAV
  2    from historique h, AM_NAV_STATUS_TRANSITIONS am
  3   where h.sicovam = am.code
  4     and h.sicovam = 68000339
  5     and h.TOTAL_NAV is not null
  6     and h.jour <= 39623 + to_date('01/01/1904', 'DD/MM/YYYY')
  7     and am.new_state not in (7, 8)
  8   order by h.jour desc;

........

7290 rows selected.

Plan hash value: 1572620151

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                           |      1 |        |   7290 |00:00:00.01 |      54 |
|   1 |  SORT ORDER BY                |                           |      1 |   1588 |   7290 |00:00:00.01 |      54 |
|*  2 |   HASH JOIN                   |                           |      1 |   1588 |   7290 |00:00:00.01 |      54 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| HISTORIQUE                |      1 |      5 |    486 |00:00:00.01 |      48 |
|*  4 |     INDEX RANGE SCAN          | IHISTO                    |      1 |    112 |    486 |00:00:00.01 |       5 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| AM_NAV_STATUS_TRANSITIONS |      1 |    335 |     15 |00:00:00.01 |       6 |
|*  6 |     INDEX RANGE SCAN          | IDX_AM_NAV_CODE           |      1 |    596 |     16 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------------------

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

   2 - access("H"."SICOVAM"="AM"."CODE")
   3 - filter("H"."TOTAL_NAV" IS NOT NULL)
   4 - access("H"."SICOVAM"=68000339 AND "H"."JOUR"<=TO_DATE(' 2012-06-25 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
   5 - filter(("AM"."NEW_STATE"<>7 AND "AM"."NEW_STATE"<>8))
   6 - access("AM"."CODE"=68000339)

Note
-----
   - SQL patch "TEST_SQL_PATCH" used for this statement

Oui, l'index IHISTO est bien utilisé et la note à la fin du plan d'exécution nous indique que le SQL patch
TEST_SQL_PATCH a bien été utilisé pour l'exécution de cette requête.

En adaptant le script de Kerry Osborne sur le SQL profile, il est possible de se créer un script create_1_hint_sql_patch.sql permettant de générer un SQL patch pour un SQL_ID donné:

accept sql_id -
       prompt 'Enter value for sql_id: ' -
       default 'X0X0X0X0'
accept patch_name -
       prompt 'Enter value for patch_name (PATCH_sqlid_MANUAL): ' -
       default 'X0X0X0X0'
accept category -
       prompt 'Enter value for category (DEFAULT): ' -
       default 'DEFAULT'

set sqlblanklines on

declare
l_patch_name varchar2(30);
cl_sql_text clob;
l_category varchar2(30);

begin

select
sql_fulltext
into
cl_sql_text
from
v$sqlarea
where
sql_id = '&&sql_id';

select decode('&&patch_name','X0X0X0X0','PATCH_'||'&&sql_id'||'_MANUAL','&&patch_name')
into l_patch_name
from dual;

sys.dbms_sqldiag_internal.i_create_patch(
sql_text => cl_sql_text,
hint_text => '&hint',
name => l_patch_name,
category => '&&category'
);

dbms_output.put_line('SQL Patch '||l_patch_name||' created.');

end;
/

undef patch_name
undef sql_id
undef category

Supprimons le SQL patch crée précédemment et recréons le en utilisant le script ci-dessus pour la requête dont le SQL_ID est 9ngkg85n7v9ks:

SQL> begin
  2    sys.dbms_sqldiag.drop_sql_patch('TEST_SQL_PATCH');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> @create_1_hint_sql_patch.sql
SQL> accept sql_id -
>        prompt 'Enter value for sql_id: ' -
>        default 'X0X0X0X0'
Enter value for sql_id: 9ngkg85n7v9ks
SQL> accept patch_name -
>        prompt 'Enter value for patch_name (PATCH_sqlid_MANUAL): ' -
>        default 'X0X0X0X0'
Enter value for patch_name (PATCH_sqlid_MANUAL):
SQL> accept category -
>        prompt 'Enter value for category (DEFAULT): ' -
>        default 'DEFAULT'
Enter value for category (DEFAULT):
SQL>
SQL> set sqlblanklines on
SQL>
SQL> declare
  2  l_patch_name varchar2(30);
  3  cl_sql_text clob;
  4  l_category varchar2(30);
  5
  6  begin
  7
  8  select
  9  sql_fulltext
 10  into
 11  cl_sql_text
 12  from
 13  v$sqlarea
 14  where
 15  sql_id = '&&sql_id';
 16
 17  select decode('&&patch_name','X0X0X0X0','PATCH_'||'&&sql_id'||'_MANUAL','&&patch_name')
 18  into l_patch_name
 19  from dual;
 20
 21  sys.dbms_sqldiag_internal.i_create_patch(
 22  sql_text => cl_sql_text,
 23  hint_text => '&hint',
 24  name => l_patch_name,
 25  category => '&&category'
 26  );
 27
 28  dbms_output.put_line('SQL Patch '||l_patch_name||' created.');
 29
 30  end;
 31  /
old  15: sql_id = '&&sql_id';
new  15: sql_id = '9ngkg85n7v9ks';
old  17: select decode('&&patch_name','X0X0X0X0','PATCH_'||'&&sql_id'||'_MANUAL','&&patch_name')
new  17: select decode('X0X0X0X0','X0X0X0X0','PATCH_'||'9ngkg85n7v9ks'||'_MANUAL','X0X0X0X0')
Enter value for hint: index(h@SEL$1 IHISTO)
old  23: hint_text => '&hint',
new  23: hint_text => 'index(h@SEL$1 IHISTO)',
old  25: category => '&&category'
new  25: category => 'DEFAULT'

PL/SQL procedure successfully completed.

SQL> undef patch_name
SQL> undef sql_id
SQL> undef category

On vérifie que le SQL patch a bien été crée :

SQL> select name from dba_sql_patches;

NAME
------------------------------
PATCH_9ngkg85n7v9ks_MANUAL

1 row selected.

En exécutant de nouveau la requête on obtient le bon plan:

Plan hash value: 1572620151

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                           |      1 |        |   7290 |00:00:00.04 |      54 |      5 |
|   1 |  SORT ORDER BY                |                           |      1 |   1588 |   7290 |00:00:00.04 |      54 |      5 |
|*  2 |   HASH JOIN                   |                           |      1 |   1588 |   7290 |00:00:00.03 |      54 |      5 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| HISTORIQUE                |      1 |      5 |    486 |00:00:00.03 |      48 |      5 |
|*  4 |     INDEX RANGE SCAN          | IHISTO                    |      1 |    112 |    486 |00:00:00.03 |       5 |      5 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| AM_NAV_STATUS_TRANSITIONS |      1 |    335 |     15 |00:00:00.01 |       6 |      0 |
|*  6 |     INDEX RANGE SCAN          | IDX_AM_NAV_CODE           |      1 |    596 |     16 |00:00:00.01 |       2 |      0 |
------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("H"."SICOVAM"="AM"."CODE")
   3 - filter("H"."TOTAL_NAV" IS NOT NULL)
   4 - access("H"."SICOVAM"=68000339 AND "H"."JOUR"<=TO_DATE(' 2012-06-25 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   5 - filter(("AM"."NEW_STATE"<>7 AND "AM"."NEW_STATE"<>8))
   6 - access("AM"."CODE"=68000339)

Note
-----
   - SQL patch "PATCH_9ngkg85n7v9ks_MANUAL" used for this statement

CONCLUSION:

On a vu qu'en utilisant la technique du SQL patch il était possible comme avec le SQL profile d'injecter un hint à une requête SQL sans toucher au code de cette requête.

L'avantage ici par rapport au SQL profile c'est qu'il n'est pas nécessaire d'avoir payé la licence du TUNING PACK. Il suffit juste d'utiliser une Enterprise Edition.

L'inconvénient c'est qu'il ne semble pas possible de forcer l'utilisation du SQL patch pour les requêtes qui ne diffèrent qu'au niveau des valeurs littérales. En effet, à l'inverse de la procédure DBMS_SQLTUNE.import_sql_profile, il n'existe pas de paramètre FORCE_MATCH pour la procédure dbms_sqldiag_internal.i_create_patch même si pourtant une colonne FORCE_MATCHING existe dans la vue DBA_SQL_PATCHES ce qui laisse supposer que ça pourrait être possible...

Ajouter un hint sans toucher à la requête

Dans un article précédent, j'avais tenté d'expliquer comment grâce à la création d'un SQL profile il était possible de forcer l'utilisation d'un plan d'exécution qui avait déjà fait l'objet d'une utilisation par le passé (plan d'exécution existant dans la shared pool ou dans l'AWR).

Dans ce nouvel article il s'agit d'une autre problématique: Comment ajouter un hint à une requête SQL sans modifier le code de cette requête? On va voir que là aussi les SQL profiles vont nous aider.

Les DBAs doivent souvent faire face à ce genre de problèmes soit parce que personne n'a accès au code applicatif (c'est le cas lorsqu'on utilise un progiciel ) ou bien parce que le délai de livraison d'un patch pouvant corriger la requête serait trop long alors que le problème doit être traité urgemment.

Pour illustrer ce principe, je vais partir d'une requête que j'ai eu à tuner récemment.

select h.TOTAL_NAV
  from historique h, AM_NAV_STATUS_TRANSITIONS am
 where h.sicovam = am.code
   and h.sicovam = 68000339
   and h.TOTAL_NAV is not null
   and h.jour <= 39623 + to_date('01/01/1904', 'DD/MM/YYYY')
   and am.new_state not in (7, 8)
 order by h.jour desc;

Cette requête est générée par un progiciel et est exécutée plusieurs centaines de milliers de fois par jour.
Comme le montre le plan ci-dessous elle génère plus de 1500 logical reads à chaque exécution:

SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
SQL_ID  9ngkg85n7v9ks, child number 0
-------------------------------------
select h.TOTAL_NAV   from historique h, AM_NAV_STATUS_TRANSITIONS am
where h.sicovam = am.code    and h.sicovam = 68000339    and
h.TOTAL_NAV is not null    and h.jour <= 39623 + to_date('01/01/1904',
'DD/MM/YYYY')    and am.new_state not in (7, 8)  order by h.jour desc

Plan hash value: 510344764

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                           |      1 |        |   7290 |00:00:00.44 |    1599 |   1597 |
|   1 |  SORT ORDER BY                |                           |      1 |   1588 |   7290 |00:00:00.44 |    1599 |   1597 |
|*  2 |   HASH JOIN                   |                           |      1 |   1588 |   7290 |00:00:00.43 |    1599 |   1597 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| HISTORIQUE                |      1 |      5 |    486 |00:00:00.41 |    1447 |   1447 |
|*  4 |     INDEX RANGE SCAN          | IHISTOJOUR                |      1 |      1 |    486 |00:00:00.25 |    1404 |   1404 |
|*  5 |    TABLE ACCESS FULL          | AM_NAV_STATUS_TRANSITIONS |      1 |    335 |     15 |00:00:00.02 |     152 |    150 |
------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("H"."SICOVAM"="AM"."CODE")
   3 - filter("H"."TOTAL_NAV" IS NOT NULL)
   4 - access("H"."SYS_NC00093$">=HEXTORAW('878FF9E6FEF8FEFAFF')  AND "H"."SICOVAM"=68000339 AND "H"."SYS_NC00093$"
              IS NOT NULL)
       filter(("H"."SICOVAM"=68000339 AND SYS_OP_UNDESCEND("H"."SYS_NC00093$")<=TO_DATE(' 2012-06-25 00:00:00',
              'syyyy-mm-dd hh24:mi:ss')))
   5 - filter(("AM"."CODE"=68000339 AND "AM"."NEW_STATE"<>7 AND "AM"."NEW_STATE"<>8))

L'essentiel des logical reads consommés est lié à l'accès à la table HISTORIQUE via l'index IHISTOJOUR. En forçant l'utilisation d'un autre index existant via un hint et en créant un index sur le champ CODE de la table AM_NAV_STATUS_TRANSITIONS j'obtiens de bien meilleurs performances:

SQL> create index idx_AM_NAV_CODE on AM_NAV_STATUS_TRANSITIONS(code);

Index created.



SQL> select /*+ index(h IHISTO) */ h.TOTAL_NAV
  2    from historique h, AM_NAV_STATUS_TRANSITIONS am
  3   where h.sicovam = am.code
  4     and h.sicovam = 68000339
  5     and h.TOTAL_NAV is not null
  6     and h.jour <= 39623 + to_date('01/01/1904', 'DD/MM/YYYY')
  7     and am.new_state not in (7, 8)
  8   order by h.jour desc;



Plan hash value: 1572620151

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                           |      1 |        |   7290 |00:00:00.18 |      54 |     32 |
|   1 |  SORT ORDER BY                |                           |      1 |   1588 |   7290 |00:00:00.18 |      54 |     32 |
|*  2 |   HASH JOIN                   |                           |      1 |   1588 |   7290 |00:00:00.18 |      54 |     32 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| HISTORIQUE                |      1 |      5 |    486 |00:00:00.16 |      48 |     31 |
|*  4 |     INDEX RANGE SCAN          | IHISTO                    |      1 |    112 |    486 |00:00:00.01 |       5 |      3 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| AM_NAV_STATUS_TRANSITIONS |      1 |    335 |     15 |00:00:00.01 |       6 |      1 |
|*  6 |     INDEX RANGE SCAN          | IDX_AM_NAV_CODE           |      1 |    596 |     16 |00:00:00.01 |       2 |      1 |
------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("H"."SICOVAM"="AM"."CODE")
   3 - filter("H"."TOTAL_NAV" IS NOT NULL)
   4 - access("H"."SICOVAM"=68000339 AND "H"."JOUR"<=TO_DATE(' 2012-06-25 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   5 - filter(("AM"."NEW_STATE"<>7 AND "AM"."NEW_STATE"<>8))
   6 - access("AM"."CODE"=68000339)

Ma requête ne consomme plus que 54 logical reads (au lieu de 1599). Vu que cette requête est exécutée plus de 100 000 fois par jour, le hint peut être vraiment bénéfique pour les performances globales de la base.
Cependant, il ne nous est pas possible d'ajouter ce hint dans la requête car elle est générée par un progiciel. Pour pouvoir utiliser ce hint lors de l'exécution de cette requête il suffit de créer un SQL profile via la procédure dbms_sqltune.import_sql_profile qui contiendra le hint. Ce SQL profile sera associé au SQL_ID de la requête qu'on veut tuner.

Pour créer un SQL profile contenant un hint, j'utilise le script suivant de Kerry Osborne:

----------------------------------------------------------------------------------------
--
-- File name:   create_1_hint_sql_profile.sql
--
-- Purpose:     Prompts for a hint and makes a profile out of it.
-
-- Author:      Kerry Osborne
--
-- Usage:       This scripts prompts for four values.
--
--              profile_name: the name of the profile to be attached to a new statement
--
--              sql_id: the sql_id of the statement to attach the profile to (must be in theshared pool)
--
--              category: the category to assign to the new profile 
--
--              force_macthing: a toggle to turn on or off the force_matching feature
--
-- Description: This script prompt for a hint. It does not validate the hint. It creates a 
--              SQL Profile with the single hint and attaches it to the provided sql_id.
--              This script should now work with all flavors of 10g and 11g.
--              
--
--              See kerryosborne.oracle-guy.com for additional information.
----------------------------------------------------------------------------------------- 

accept sql_id -
       prompt 'Enter value for sql_id: ' -
       default 'X0X0X0X0'
accept profile_name -
       prompt 'Enter value for profile_name (PROFILE_sqlid_MANUAL): ' -
       default 'X0X0X0X0'
accept category -
       prompt 'Enter value for category (DEFAULT): ' -
       default 'DEFAULT'
accept force_matching -
       prompt 'Enter value for force_matching (false): ' -
       default 'false'


set sqlblanklines on

declare
l_profile_name varchar2(30);
cl_sql_text clob;
l_category varchar2(30);
l_force_matching varchar2(3);
b_force_matching boolean;
begin

select
sql_fulltext
into
cl_sql_text
from
v$sqlarea
where
sql_id = '&&sql_id';

select decode('&&profile_name','X0X0X0X0','PROFILE_'||'&&sql_id'||'_MANUAL','&&profile_name')
into l_profile_name
from dual;

dbms_sqltune.import_sql_profile(
sql_text => cl_sql_text, 
profile => sqlprof_attr('&hint'),
category => '&&category',
name => l_profile_name,
-- use force_match => true
-- to use CURSOR_SHARING=SIMILAR
-- behaviour, i.e. match even with
-- differing literals
force_match => &&force_matching
);

dbms_output.put_line('Profile '||l_profile_name||' created.');

end;
/

undef profile_name
undef sql_id
undef category
undef force_matching

Lors de l'exécution de ce script vous êtes invités à saisir le SQL_ID de la requête et bien sûr le hint à associer à la requête. Pour le reste on a des valeurs par défaut (nom du SQL profile, category, force_match).

Maintenant, j'exécute le script pour ma requête 9ngkg85n7v9ks:

SQL> @sp_create_1_hint_sql_profile.sql
SQL>
SQL> ----------------------------------------------------------------------------------------
SQL> --
SQL> -- File name:      create_1_hint_sql_profile.sql
SQL> --
SQL> -- Purpose:        Prompts for a hint and makes a profile out of it.
SQL> -
> -- Author:      Kerry Osborne
SQL> --
SQL> -- Usage:       This scripts prompts for four values.
SQL> --
SQL> --              profile_name: the name of the profile to be attached to a new statement
SQL> --
SQL> --              sql_id: the sql_id of the statement to attach the profile to (must be in theshared pool)
SQL> --
SQL> --              category: the category to assign to the new profile
SQL> --
SQL> --              force_macthing: a toggle to turn on or off the force_matching feature
SQL> --
SQL> -- Description: This script prompt for a hint. It does not validate the hint. It creates a
SQL> --              SQL Profile with the single hint and attaches it to the provided sql_id.
SQL> --              This script should now work with all flavors of 10g and 11g.
SQL> --
SQL> --
SQL> --              See kerryosborne.oracle-guy.com for additional information.
SQL> -----------------------------------------------------------------------------------------
SQL>
SQL> accept sql_id -
>        prompt 'Enter value for sql_id: ' -
>        default 'X0X0X0X0'
Enter value for sql_id: 9ngkg85n7v9ks
SQL> accept profile_name -
>        prompt 'Enter value for profile_name (PROFILE_sqlid_MANUAL): ' -
>        default 'X0X0X0X0'
Enter value for profile_name (PROFILE_sqlid_MANUAL):
SQL> accept category -
>        prompt 'Enter value for category (DEFAULT): ' -
>        default 'DEFAULT'
Enter value for category (DEFAULT):
SQL> accept force_matching -
>        prompt 'Enter value for force_matching (false): ' -
>        default 'false'
Enter value for force_matching (false): TRUE
SQL>
SQL>
SQL> set sqlblanklines on
SQL>
SQL> declare
  2  l_profile_name varchar2(30);
  3  cl_sql_text clob;
  4  l_category varchar2(30);
  5  l_force_matching varchar2(3);
  6  b_force_matching boolean;
  7  begin
  8
  9  select
 10  sql_fulltext
 11  into
 12  cl_sql_text
 13  from
 14  v$sqlarea
 15  where
 16  sql_id = '&&sql_id';
 17
 18  select decode('&&profile_name','X0X0X0X0','PROFILE_'||'&&sql_id'||'_MANUAL','&&profile_name')
 19  into l_profile_name
 20  from dual;
 21
 22  dbms_sqltune.import_sql_profile(
 23  sql_text => cl_sql_text,
 24  profile => sqlprof_attr('&hint'),
 25  category => '&&category',
 26  name => l_profile_name,
 27  -- use force_match => true
 28  -- to use CURSOR_SHARING=SIMILAR
 29  -- behaviour, i.e. match even with
 30  -- differing literals
 31  force_match => &&force_matching
 32  );
 33
 34  dbms_output.put_line('Profile '||l_profile_name||' created.');
 35
 36  end;
 37  /
Enter value for hint: index(h@SEL$1 IHISTO)

PL/SQL procedure successfully completed.

SQL> undef profile_name
SQL> undef sql_id
SQL> undef category
SQL> undef force_matching

Vous noterez que le hint que j'ai associé est le suivant: INDEX(h@SEL$1 IHISTO).
Celui que j'avais utilisé dans la requête était celui-ci:  INDEX(h IHISTO)
La différence se situe au niveau de l'alias du "query block" SEL$1 qui est généré par défaut par le CBO et qui permet d'identifier chaque bloc de requête (utile notamment lorsqu'on a des subqueries dans la requête).
Dans mon cas je n'ai qu'un seul bloc de requête et donc le hint lorsqu'il est mis directement dans la requête fonctionne même s'il ne contient pas d'alias au query block. Pour les SQL profiles c'est différent. Le hint ne fonctionnera pas s'il n'est pas aliassé. Pour connaitre les alias générés par l'optimiseur pour une requête donnée il suffit d'utiliser l'option ALIAS dans les fonctions du package dbms_xplan.
Exemple:

 SQL> select * from table(dbms_xplan.display_cursor(null,null,'basic +alias'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select h.TOTAL_NAV   from historique h, AM_NAV_STATUS_TRANSITIONS am
where h.sicovam = am.code    and h.sicovam = 68000339    and
h.TOTAL_NAV is not null    and h.jour <= 39623 + to_date('01/01/1904',
'DD/MM/YYYY')    and am.new_state not in (7, 8)  order by h.jour desc

Plan hash value: 2065181612

-------------------------------------------------------------------
| Id  | Operation                     | Name                      |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                           |
|   1 |  SORT ORDER BY                |                           |
|   2 |   HASH JOIN                   |                           |
|   3 |    TABLE ACCESS BY INDEX ROWID| HISTORIQUE                |
|   4 |     INDEX RANGE SCAN          | IHISTOJOUR                |
|   5 |    TABLE ACCESS BY INDEX ROWID| AM_NAV_STATUS_TRANSITIONS |
|   6 |     INDEX RANGE SCAN          | IDX_AM_NAV_CODE           |
-------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / H@SEL$1
   4 - SEL$1 / H@SEL$1
   5 - SEL$1 / AM@SEL$1
   6 - SEL$1 / AM@SEL$1

Revenons au SQL profile que j'ai crée grâce au script de Kerry Osborne. La requête suivante permet de vérifier qu'il a bien été crée:

SQL> @sp_list_sql_profiles.sql
SQL> col category for a15
SQL> col sql_text for a70 trunc
SQL> select name, category, status, sql_text, force_matching
  2  from dba_sql_profiles
  3  where sql_text like nvl('&sql_text','%')
  4  and name like nvl('&name',name)
  5  order by last_modified
  6  /
Enter value for sql_text:
Enter value for name: %9ngkg85n7v9ks%

NAME                           CATEGORY        STATUS   SQL_TEXT                                                       FOR
------------------------------ --------------- -------- ---------------------------------------------------------------------- ---
PROFILE_9ngkg85n7v9ks_MANUAL   DEFAULT         ENABLED  select h.TOTAL_NAV                                             YES

1 row selected.

Je relance la requête initiale générée par le progiciel (celle qui ne contient pas le hint dans le corps de la requête):

SQL> select h.TOTAL_NAV
  2    from historique h, AM_NAV_STATUS_TRANSITIONS am
  3   where h.sicovam = am.code
  4     and h.sicovam = 68000339
  5     and h.TOTAL_NAV is not null
  6     and h.jour <= 39623 + to_date('01/01/1904', 'DD/MM/YYYY')
  7     and am.new_state not in (7, 8)
  8   order by h.jour desc;

.............

7290 rows selected.



SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
SQL_ID  9ngkg85n7v9ks, child number 0
-------------------------------------
select h.TOTAL_NAV   from historique h, AM_NAV_STATUS_TRANSITIONS am
where h.sicovam = am.code    and h.sicovam = 68000339    and
h.TOTAL_NAV is not null    and h.jour <= 39623 + to_date('01/01/1904',
'DD/MM/YYYY')    and am.new_state not in (7, 8)  order by h.jour desc

Plan hash value: 1572620151

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                           |      1 |        |   7290 |00:00:00.03 |      54 |      3 |
|   1 |  SORT ORDER BY                |                           |      1 |   1588 |   7290 |00:00:00.03 |      54 |      3 |
|*  2 |   HASH JOIN                   |                           |      1 |   1588 |   7290 |00:00:00.02 |      54 |      3 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| HISTORIQUE                |      1 |      5 |    486 |00:00:00.02 |      48 |      3 |
|*  4 |     INDEX RANGE SCAN          | IHISTO                    |      1 |    112 |    486 |00:00:00.02 |       5 |      3 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| AM_NAV_STATUS_TRANSITIONS |      1 |    335 |     15 |00:00:00.01 |       6 |      0 |
|*  6 |     INDEX RANGE SCAN          | IDX_AM_NAV_CODE           |      1 |    596 |     16 |00:00:00.01 |       2 |      0 |
------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("H"."SICOVAM"="AM"."CODE")
   3 - filter("H"."TOTAL_NAV" IS NOT NULL)
   4 - access("H"."SICOVAM"=68000339 AND "H"."JOUR"<=TO_DATE(' 2012-06-25 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   5 - filter(("AM"."NEW_STATE"<>7 AND "AM"."NEW_STATE"<>8))
   6 - access("AM"."CODE"=68000339)

Note
-----
   - SQL profile PROFILE_9ngkg85n7v9ks_MANUAL used for this statement

On s'aperçoit que le SQL profile a fonctionné puique maintenant l'index IHISTO est bien utilisé, et la petite note à la fin du plan d'exécution indique que le SQL profile PROFILE_9ngkg85n7v9ks_MANUAL a été utilisé pour cette requête.

Ainsi, grâce aux SQL profiles et à la procédure dbms_sqltune.import_sql_profile on a réussi à obtenir un meilleur plan pour notre requête (en utilisant un hint) sans avoir à toucher au code de la requête.