lundi 9 juillet 2012

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...

2 commentaires:

  1. excellent ! Par contre est-il possible d'utiliser SQL Patch afin de fixer un plan récuperé dans la vue awr DBA_HIST_SQL_PLAN sans utilisé le tuning pack ?

    RépondreSupprimer
    Réponses
    1. La solution consisterait à récupérer l'outline dans l'awr pour la requête en question (qui est en fait une liste de hints) et injecter ces hints via le sql patch.

      Supprimer