Forcer un plan d'exécution via un SQL profile

Avec la 10g est arrivée la notion de SQL profile, un objet contenant des informations par rapport à une requête donnée (définie par son SQL_ID) et qui permet au CBO de choisir un plan optimal lors du parsing. Ce SQL profile est en générale défini lorsqu'on fait appel au Tuning advisor pour analyser une requête non performante (un article plus détaillé est à venir sur ce sujet).

Le SQL profile, à l'inverse des OUTLINES apparus avec la 8i ou du SQL Plan Management apparu en 11g, n'a pas pour but de forcer un plan d'exécution mais plutôt d'augmenter la flexibilité de l'optimiseur.

Toutefois, une astuce géniale découverte sur le blog de Randolf GEIST permet de forcer pour une requête donnée un plan existant dans le référentiel AWR ou dans la shared pool en utilisant un SQL profile. Imaginons que vous ayez une requête sensible exécutée tous les soirs pendant un process batch et que cette requête met en général 10 minutes pour s'exécuter. Puis un jour vous vous rendez compte que le plan de cette requête a changé et qu'elle met désormais 1 heure. Vous êtes face à un problème d'instabilité de plans d'exécution. S'il s'agit d'un process lancé par un progiciel vous n'aurez pas la main pour modifier la requête. Par contre en regardant l'historique d'exécution pour cette requête dans l'AWR vous savez qu'un bon plan existe et vous souhaiteriez qu'Oracle utilise ce plan.

La technique de Randolf Geist que je vais illustrer ici vous permet d'atteindre cet objectif. Son article sur ce sujet est accessible ici.

TEST CASE:

Je crée d'abord une table t contenant un million de lignes avec une PK sur la colonne ID:

SQL> DROP TABLE t;

Table supprimée.

SQL> CREATE TABLE t
  2  AS
  3  SELECT rownum AS id, rpad('*',100,'*') AS pad
  4  FROM dual
  5  CONNECT BY level <= 1000000;

Table créée.

SQL> ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id);

Table modifiée.

SQL> BEGIN
  2    dbms_stats.gather_table_stats(
  3      ownname          => user,
  4      tabname          => 't',
  5      method_opt       => 'for all columns size 1'
  6    );
  7  END;
  8  /

Procédure PL/SQL terminée avec succès.

J'exécute ensuite une requête me retournant 9 lignes, donc un plan utilisant un INDEX RANGE SCAN de l'index T_PK est utilisé

SQL> VARIABLE id NUMBER
SQL> EXECUTE :id := 10;

Procédure PL/SQL terminée avec succès.

Ecoulé : 00 :00 :00.03
SQL> SELECT count(pad) FROM t WHERE id < :id;

COUNT(PAD)
----------
         9

Ecoulé : 00 :00 :00.04
SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'OUTLINE'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID  asth1mx10aygn, child number 0
-------------------------------------
SELECT count(pad) FROM t WHERE id < :id

Plan hash value: 4270555908

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     4 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |   106 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     9 |   954 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T_PK |     9 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------
Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."ID"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ID"<:ID)

La partie OUTLINE DATA du plan contient l'ensemble des hints qui détermine le plan d'exécution de cette requête. C'est cet ensemble d'information qu'on souhaite associer définitivement à une requête donnée via un SQL profile.

Maintenant je modifie légèrement un paramètre du CBO juste pour forcer le HARD PARSE sur cette requête, sinon le plan précédent sera utilisé. Je simule ainsi une autre requête identique s'exécutant sous un autre environnement d'exécution.

SQL> alter session set optimizer_index_cost_adj=95;

Session modifiée.

SQL> SELECT count(pad) FROM t WHERE id < :id;

COUNT(PAD)
----------
    999989

Ecoulé : 00 :00 :00.09
SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'OUTLINE'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
SQL_ID  asth1mx10aygn, child number 1
-------------------------------------
SELECT count(pad) FROM t WHERE id < :id

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |  4228 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |   106 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   999K|   101M|  4228   (1)| 00:00:51 |
---------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('optimizer_index_cost_adj' 95)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   2 - filter("ID"<:ID)

Cette fois si, comme je récupère quasiment toutes les lignes de ma table, le CBO a estimé qu'un FTS était le plus approprié, et il a raison. Maintenant je reste sur le même environnement d'exécution et je réexécute la première requête censée me retourner 9 lignes.

SQL> EXECUTE :id := 10;

Procédure PL/SQL terminée avec succès.

Ecoulé : 00 :00 :00.00
SQL> SELECT count(pad) FROM t WHERE id < :id;

COUNT(PAD)
----------
         9

Ecoulé : 00 :00 :00.07
SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'OUTLINE'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID  asth1mx10aygn, child number 1
-------------------------------------
SELECT count(pad) FROM t WHERE id < :id

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |  4228 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |   106 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   999K|   101M|  4228   (1)| 00:00:51 |
---------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('optimizer_index_cost_adj' 95)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   2 - filter("ID"<:ID)

Et là qu'est-ce que je vois? Un Full Table Scan.
Imaginons que la table soit beaucoup plus volumineuse et la requête beaucoup plus compliquée, le plan ici peut s'avérer désastreux. Face à cette situation je peux vérifier dans la shared pool si un autre plan existe pour cette requête.

SQL> select sql_id, child_number, executions,  PARSING_SCHEMA_NAME, round(elapsed_time/1000000,2) "elapsed_sec",
  2  round((elapsed_time/1000000)/executions,2) "elapsed_per_exec",plan_hash_value, buffer_gets
  3  from v$sql where sql_id='asth1mx10aygn';

SQL_ID        CHILD_NUMBER EXECUTIONS PARSING_SCHEMA_NAME            elapsed_sec elapsed_per_exec PLAN_HASH_VALUE BUFFER_GETS
------------- ------------ ---------- ------------------------------ ----------- ---------------- --------------- -----------
asth1mx10aygn            0          1 UBXADMIN                               ,04              ,04      4270555908         958
asth1mx10aygn            1          2 UBXADMIN                               ,16              ,08      2966233522       30974

Je m'aperçois que la requête a déjà fait l'objet d'un autre plan par le passé(PLAN_HASH_VALUE=4270555908) et qu'il donne de meilleures stats d'exécution (elapsed_time, buffer_gets). Je souhaiterais forcer l'utilisation de ce plan pour cette requête via un SQL profile. Pour cela j'exécute le script suivant:

DECLARE 
   ar_profile_hints   sys.sqlprof_attr; 
   cl_sql_text        CLOB; 
BEGIN 
   SELECT   EXTRACTVALUE (VALUE (d), '/hint') AS outline_hints 
     BULK   COLLECT 
     INTO   ar_profile_hints 
     FROM   XMLTABLE ('/*/outline_data/hint' PASSING (SELECT   xmltype ( 
                      other_xml) 
                         AS xmlval 
                                                        FROM 
                      v$sql_plan
                                                       WHERE       sql_id = 'asth1mx10aygn' 
                      AND plan_hash_value = 4270555908 
                      AND other_xml IS NOT NULL)) d; 

   SELECT   sql_fulltext 
     INTO   cl_sql_text 
     FROM   v$sql 
    WHERE   sql_id = 'asth1mx10aygn'
    and rownum=1; 

   DBMS_SQLTUNE.import_sql_profile (sql_text      => cl_sql_text, 
                                    profile       => ar_profile_hints, 
                                    category      => 'DEFAULT', 
                                    name          => 'PROFILE_asth1mx10aygn', 
                                    force_match   => TRUE); 
END; 
/

La première requête du script permet de récupérer dans la variable ar_profile_hints l'outline data correspondant au bon plan que je souhaite forcer. Ce qui diffèrera lorsque vous utiliserez ce script est la valeur du SQL_ID et du PLAN_HASH_VALUE.
La deuxième requête permet de récupérer le texte exact de la requête dans la variable cl_sql_text. Une fois ces 2 informations récupérées la 3ème partie du script permet d'attacher un SQL profile nommé PROFILE_asth1mx10aygn au texte de la requête. Le paramètre FORCE_MATCH à TRUE permet de forcer le plan également aux requêtes qui diffèrent sur la valeur littéral utilisée dans la clause WHERE. Par exemple, le SQL profile défini dans mon test précédent s'appliquera à toutes les requêtes suivantes:

SELECT count(pad) FROM t WHERE id < :id;
SELECT count(pad) FROM t WHERE id < 10;
SELECT count(pad) FROM t WHERE id < 12;
SELECT count(pad) FROM t WHERE id < 100000;
etc.

Maintenant que le SQL profile a été attaché à la requête on peut vérifier s'il est bien pris en compte lorsque j'exécute ma requête:

SQL> EXECUTE :id := 10;

Procédure PL/SQL terminée avec succès.

Ecoulé : 00 :00 :00.00
SQL> SELECT count(pad) FROM t WHERE id < :id;

COUNT(PAD)
----------
         9

Ecoulé : 00 :00 :00.01
SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'OUTLINE'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  asth1mx10aygn, child number 1
-------------------------------------
SELECT count(pad) FROM t WHERE id < :id

Plan hash value: 4270555908

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     4 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |   106 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     9 |   954 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T_PK |     9 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('optimizer_index_cost_adj' 95)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."ID"))
      END_OUTLINE_DATA
  */

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

   3 - access("ID"<:ID)

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

On voit que le bon plan est bien pris en compte ici et que la partie NOTE tout en bas du plan indique bien qu'un SQL profile a été utilisé pour cette requête.

En remplaçant dans le script les vues V$SQL et v$SQL_PLAN par les vues DBA_HIST_SQLTEXT et DBA_HIST_SQL_PLAN, il aurait été possible de forcer un plan se trouvant dans le référentiel AWR et non pas dans la shared pool.

La liste des SQL profiles peut être trouvée dans la vue DBA_SQL_PROFILES.

CONCLUSION:
Contrairement à son rôle premier qui n'est pas de forcer un plan d'exécution, le SQL profile via la procédure DBMS_SQLTUNE.import_sql_profile peut finalement être utilisé à cet effet. Toutefois, il faut bien avoir à l'esprit qu'en utilisant cette astuce le plan importé sera toujours utilisé. Le risque est que ce même plan peut ne pas être du tout approprié pour d'autres valeurs des "FILTER predicates" ou bien selon la volumétrie des tables impliquées.
Vous pouvez utiliser à tout moment les procédures du package DBMS_SQLTUNE pour dropper ou désactiver un SQL profile donné.