lundi 2 juillet 2012

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.

5 commentaires:

  1. Bonjour,
    En 11g il y a aussi SQL Patch qui permet cela (https://blogs.oracle.com/optimizer/entry/how_can_i_hint_a) même sans avoir Tuning Pack.
    Cordialement,
    Franck.

    RépondreSupprimer
    Réponses
    1. Salut Franck,
      Merci pour le lien.
      J'ai essayé avec la requête en exemple dans mon article et ça marche très bien.
      un petit bémol néanmoins: il n'existe pas avec le SQL patch un équivalent au FORCE_MATCH pour que le hint fonctionne peu importe la ou les valeurs littérales.

      Supprimer
  2. Bonsoir,
    pourquoi pas une outline?
    Ca me semble être leur but que d'essayer de fixer le plan?

    Cdt

    RépondreSupprimer
  3. Bonjour,
    L'outline est une "feature deprecated" depuis la 10g et donc plus maintenus ni supportés par Oracle. Si vous êtes en 9i l'OUTLINE est effectivement le seul recours possible à ma connaissance pour figer un plan sans toucher au code la requête.

    RépondreSupprimer
  4. Bonsoir,
    merci de l'info. je pensais que c’était deprecated seulement pour la 11G.
    En tout cas, les outlines m'ont sauvé la mise plus d'une fois.Je ne les oublierai jamais :)

    cdt

    RépondreSupprimer