mardi 10 février 2015

Forcer un plan d'une requête à partir d'une autre requête

Dans un article précèdent, j'avais montré comment il était possible grâce aux SQL profiles d'ajouter un hint à une requête identifiée par son SQL_ID sans avoir à toucher au code de la requête.

Cette fois il s'agit d'une autre problématique. J'ai deux requêtes similaires mais chacune avec un SQL_ID différent et je veux que la première utilise le plan de la 2ème. Grâce à un script de Kerry Osborne et l'utilisation de la procédure DBMS_SQLTUNE il est possible d'attacher le plan d'une requête A à une requête B.

Pour améliorer les performances d'une requête d'un client, j'ai récemment eu à utiliser cette technique. Je vais tenter dans cet article de vous expliquer comment j'ai procédé.

Mon client m'a envoyé un email la semaine dernière car il se plaignait d'une requête s'exécutant lentement en PROD alors qu'elle était plutôt rapide en recette. En me connectant sur les 2 bases et en exécutant la requête  j'ai pu m'apercevoir qu'effectivement en PROD le plan différait de celui en RECETTE. 

Voici un extrait du plan en PROD. On voit qu'il génère 1992K logical I/Os:
Plan hash value: 685980531


------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |               |      1 |        |     18 |00:01:18.50 |    1992K|      2 |       |       |          |
|   1 |  SORT AGGREGATE                        |               |     18 |      1 |     18 |00:00:00.01 |      74 |      0 |       |       |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID          | CI_FT         |     18 |      4 |     44 |00:00:00.01 |      74 |      0 |       |       |          |

Le plan en recette ne génère quant à lui que 2K logical reads.
Plan hash value: 3994356561

------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |               |      1 |        |     18 |00:00:01.40 |    2158 |     41 |       |       |          |
|   1 |  SORT AGGREGATE                        |               |     18 |      1 |     18 |00:00:00.01 |      55 |      0 |       |       |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID          | CI_FT         |     18 |      4 |     44 |00:00:00.01 |      55 |      0 |       |       |          |
Tout d'abord ma première idée a été de tester le plan de la base de recette en prod. Pour ce faire j'ai récupéré l'outline (c'est à dire l'ensemble des hints qui constituent le plan) du plan de la base de rectte en utilisant l'opion ADVANCED de la fonction DBMS_XPLAN:
SQL> explain plan for
........

SQL> SELECT * FROM table(dbms_xplan.display(NULL, NULL, 'advanced'));

.......

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

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH_AGGREGATION(@"SEL$16")
      INDEX_RS_ASC(@"SEL$16" "FT2"@"SEL$16" ("CI_FT"."BILL_ID"))
      PUSH_SUBQ(@"SEL$16")
      NLJ_BATCHING(@"SEL$15" "PAY"@"SEL$15")
      USE_NL(@"SEL$15" "PAY"@"SEL$15")
      USE_NL(@"SEL$15" "FT"@"SEL$15")
      LEADING(@"SEL$15" "BILL2"@"SEL$15" "FT"@"SEL$15" "PAY"@"SEL$15")
      INDEX(@"SEL$15" "PAY"@"SEL$15" ("CI_PAY"."PAY_ID"))
      INDEX_RS_ASC(@"SEL$15" "FT"@"SEL$15" ("CI_FT"."MATCH_EVT_ID"))
      INDEX_RS_ASC(@"SEL$15" "BILL2"@"SEL$15" ("CI_BILL"."BILL_ID"))
      USE_HASH_AGGREGATION(@"SEL$17")
      USE_NL(@"SEL$17" "PAY"@"SEL$17")
      LEADING(@"SEL$17" "BILL2"@"SEL$17" "PAY"@"SEL$17")
      INDEX_RS_ASC(@"SEL$17" "PAY"@"SEL$17" ("CI_PAY"."ACCT_ID"))
      INDEX_RS_ASC(@"SEL$17" "BILL2"@"SEL$17" ("CI_BILL"."BILL_ID"))
      USE_HASH_AGGREGATION(@"SEL$0D753FAC")
      NLJ_BATCHING(@"SEL$0D753FAC" "FT"@"SEL$18")
      USE_NL(@"SEL$0D753FAC" "FT"@"SEL$18")
      USE_NL(@"SEL$0D753FAC" "FT2"@"SEL$19")
      LEADING(@"SEL$0D753FAC" "BILL2"@"SEL$18" "FT2"@"SEL$19" "FT"@"SEL$18")
      INDEX(@"SEL$0D753FAC" "FT"@"SEL$18" ("CI_FT"."MATCH_EVT_ID"))
      INDEX_RS_ASC(@"SEL$0D753FAC" "FT2"@"SEL$19" ("CI_FT"."BILL_ID"))
      INDEX_RS_ASC(@"SEL$0D753FAC" "BILL2"@"SEL$18" ("CI_BILL"."BILL_ID"))
      INDEX_RS_ASC(@"SEL$267CE17A" "A1"@"SEL$11" ("CI_FT"."BILL_ID"))
      NLJ_BATCHING(@"SEL$7B312CD2" "MATCH"@"SEL$12")
      USE_NL(@"SEL$7B312CD2" "MATCH"@"SEL$12")
      USE_NL(@"SEL$7B312CD2" "FT2"@"SEL$12")
      LEADING(@"SEL$7B312CD2" "A1"@"SEL$13" "FT2"@"SEL$12" "MATCH"@"SEL$12")
      INDEX(@"SEL$7B312CD2" "MATCH"@"SEL$12" ("CI_MATCH_EVT"."MATCH_EVT_ID"))
      INDEX_RS_ASC(@"SEL$7B312CD2" "FT2"@"SEL$12" ("CI_FT"."MATCH_EVT_ID"))
      INDEX_RS_ASC(@"SEL$7B312CD2" "A1"@"SEL$13" ("CI_FT"."BILL_ID"))
      USE_HASH_AGGREGATION(@"SEL$10")
      USE_NL(@"SEL$10" "TST"@"SEL$10")
      LEADING(@"SEL$10" "BILL2"@"SEL$10" "TST"@"SEL$10")
      NO_ACCESS(@"SEL$10" "TST"@"SEL$10")
      INDEX_RS_ASC(@"SEL$10" "BILL2"@"SEL$10" ("CI_BILL"."BILL_ID"))
      INDEX_RS_ASC(@"SEL$2" "CI_FT"@"SEL$2" ("CI_FT"."BILL_ID"))
      INDEX(@"SEL$3" "CI_BSEG"@"SEL$3" ("CI_BSEG"."BILL_ID"))
      INDEX_RS_ASC(@"SEL$4" "CI_BSEG"@"SEL$4" ("CI_BSEG"."BILL_ID"))
      INDEX_RS_ASC(@"SEL$5" "CI_BSEG"@"SEL$5" ("CI_BSEG"."BILL_ID"))
      INDEX_RS_ASC(@"SEL$6" "CI_BSEG"@"SEL$6" ("CI_BSEG"."BILL_ID"))
      NLJ_BATCHING(@"SEL$7" "ME"@"SEL$7")
      USE_NL(@"SEL$7" "ME"@"SEL$7")
      LEADING(@"SEL$7" "FT"@"SEL$7" "ME"@"SEL$7")
      INDEX(@"SEL$7" "ME"@"SEL$7" ("CI_MATCH_EVT"."MATCH_EVT_ID"))
      INDEX_RS_ASC(@"SEL$7" "FT"@"SEL$7" ("CI_FT"."BILL_ID"))
      NLJ_BATCHING(@"SEL$8" "L"@"SEL$8")
      USE_NL(@"SEL$8" "L"@"SEL$8")
      LEADING(@"SEL$8" "BCHAR"@"SEL$8" "L"@"SEL$8")
      INDEX(@"SEL$8" "L"@"SEL$8" ("CI_CHAR_VAL_L"."CHAR_TYPE_CD" "CI_CHAR_VAL_L"."CHAR_VAL"
              "CI_CHAR_VAL_L"."LANGUAGE_CD"))
      INDEX_RS_ASC(@"SEL$8" "BCHAR"@"SEL$8" ("CI_BILL_CHAR"."BILL_ID" "CI_BILL_CHAR"."CHAR_TYPE_CD"
              "CI_BILL_CHAR"."SEQ_NUM"))
      NO_ACCESS(@"SEL$9" "MA_BALANCE"@"SEL$9")
      NO_ACCESS(@"SEL$14" "ELEMENTS"@"SEL$14")
      INDEX_RS_ASC(@"SEL$20" "CI_FT"@"SEL$20" ("CI_FT"."BILL_ID"))
      NLJ_BATCHING(@"SEL$21" "ME"@"SEL$21")
      USE_NL(@"SEL$21" "ME"@"SEL$21")
      LEADING(@"SEL$21" "FT"@"SEL$21" "ME"@"SEL$21")
      INDEX(@"SEL$21" "ME"@"SEL$21" ("CI_MATCH_EVT"."MATCH_EVT_ID"))
      INDEX_RS_ASC(@"SEL$21" "FT"@"SEL$21" ("CI_FT"."BILL_ID"))
      INDEX_RS_ASC(@"SEL$1" "BILL"@"SEL$1" ("CI_BILL"."ACCT_ID"))
      OUTLINE(@"SEL$13")
      OUTLINE(@"SEL$12")
      OUTLINE(@"SEL$19")
      OUTLINE(@"SEL$18")
      OUTLINE(@"SEL$10")
      OUTLINE(@"SET$1")
      MERGE(@"SEL$13")
      OUTLINE(@"SEL$61262C81")
      OUTLINE(@"SEL$11")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$21")
      OUTLINE_LEAF(@"SEL$20")
      OUTLINE_LEAF(@"SEL$14")
      OUTLINE_LEAF(@"SET$2")
      UNNEST(@"SEL$19")
      OUTLINE_LEAF(@"SEL$0D753FAC")
      OUTLINE_LEAF(@"SEL$17")
      OUTLINE_LEAF(@"SEL$15")
      OUTLINE_LEAF(@"SEL$16")
      OUTLINE_LEAF(@"SEL$9")
      OUTLINE_LEAF(@"SEL$10")
      PUSH_PRED(@"SEL$10" "TST"@"SEL$10" 1)
      OUTLINE_LEAF(@"SET$5715CE2E")
      OUTLINE_LEAF(@"SEL$7B312CD2")
      OUTLINE_LEAF(@"SEL$267CE17A")
      OUTLINE_LEAF(@"SEL$8")
      OUTLINE_LEAF(@"SEL$7")
      OUTLINE_LEAF(@"SEL$6")
      OUTLINE_LEAF(@"SEL$5")
      OUTLINE_LEAF(@"SEL$4")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$2")
      ALL_ROWS
      OPT_PARAM('optimizer_index_caching' 50)
      OPT_PARAM('optimizer_index_cost_adj' 30)
      DB_VERSION('11.2.0.3')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
...................
(Je vous ai épargné ce qui n'était pas utile dans l'output)

Ensuite j'ai copié cet outline pour le mettre sous forme de hint dans la requête et je l'ai exécutée en PROD. J'ai obtenu le même plan avec des stats d'exécution équivalents à celle de la recette.

L'idéal aurait été de faire une analyse approfondie pour comprendre pourquoi un mauvais plan était choisi en PROD mais le temps ne nous le permettait pas et mon client était satisfait du plan en recette d'autant plus que la requête n'est pas censé être modifiée et que les tables ont une volumétrie stable.

La solution la plus efficace dans ce cas était donc de forcer le bon plan en demandant au client d'ajouter l'ensemble des hints constituant l'outline du bon plan dans le code de la requête. L'inconvénient c'est que mon client n'avait pas la possibilité de modifier cette requête et il n'y avait pas dans l'historique d'exécution de la requête en PROD le bon plan ou un plan avec des statistiques d'exécution satisfaisantes.

Et c'est là que le script de Kerry Osborne entre en jeu:
----------------------------------------------------------------------------------------
--
-- File name:   move_sql_profile.sql
--
-- Purpose:     Moves a SQL Profile from one statement to another.
-
-- 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
--
--              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 is based on a script originally written by Randolf Giest. 
--              It's purpose is to allow a statements text to be manipulated in whatever
--              manner necessary (typically with hints) to get the desired plan. Then 
--              once a SQL Profile has been created on the new statement, it's SQL Profile
--              can be moved (or attached) to the orignal statement with unmodified text.
--
-- Mods:        This script should now work wirh all flavors of 10g and 11g.
--              
--
--              See kerryosborne.oracle-guy.com for additional information.
----------------------------------------------------------------------------------------- 

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

----------------------------------------------------------------------------------------
--
-- File name:   profile_hints.sql
--
---------------------------------------------------------------------------------------
--
set sqlblanklines on


declare
ar_profile_hints sys.sqlprof_attr;
cl_sql_text clob;
version varchar2(3);
l_category varchar2(30);
l_force_matching varchar2(3);
b_force_matching boolean;
begin
 select regexp_replace(version,'\..*') into version from v$instance;

if version = '10' then

-- dbms_output.put_line('version: '||version);
   execute immediate -- to avoid 942 error 
   'select attr_val as outline_hints '||
   'from dba_sql_profiles p, sqlprof$attr h '||
   'where p.signature = h.signature '||
   'and name like (''&&profile_name'') '||
   'order by attr#'
   bulk collect 
   into ar_profile_hints;

elsif version = '11' then

-- dbms_output.put_line('version: '||version);
   execute immediate -- to avoid 942 error 
   'select hint as outline_hints '||
   'from (select p.name, p.signature, p.category, row_number() '||
   '      over (partition by sd.signature, sd.category order by sd.signature) row_num, '||
   '      extractValue(value(t), ''/hint'') hint '||
   'from sys.sqlobj$data sd, dba_sql_profiles p, '||
   '     table(xmlsequence(extract(xmltype(sd.comp_data), '||
   '                               ''/outline_data/hint''))) t '||
   'where sd.obj_type = 1 '||
   'and p.signature = sd.signature '||
   'and p.name like (''&&profile_name'')) '||
   'order by row_num'
   bulk collect 
   into ar_profile_hints;

end if;

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

dbms_sqltune.import_sql_profile(
sql_text => cl_sql_text
, profile => ar_profile_hints
, category => '&&category'
, name => 'PROFILE_'||'&&sql_id'||'_moved'
-- use force_match => true
-- to use CURSOR_SHARING=SIMILAR
-- behaviour, i.e. match even with
-- differing literals
, force_match => &&force_matching
);
end;
/
undef profile_name
undef sql_id
undef category
undef force_matching
L'idée de ce script est d'attacher un plan d'une requête (qu'on a réussi à obtenir d'une manière ou d'une autre) à une requête s'exécutant avec un plan non satisfaisant et qu'on ne peut modifier.
L'exécution de ce script est en fait la dernière étape d'un plan en 3 étapes:

1) Exécuter la requête avec le bon plan (en ajoutant les hints de l'outline)
2) Création d'un SQL profile pour y coller le plan obtenu en (1)
3) Coller le SQL Profile créé en (2) à la requête exécutée par l'application

La 3ème étape correspond en fait à l'exécution du script de Kerry Osborne.
L'étape 1 je l'ai réalisée lorsque j'ai exécuté la requête avec l'outline.
L'étape 2 consiste à exécuter un autre script de kerry Osborne que j'avais expliqué dans un de mes tous premiers articles. A l'étape 1 j'ai obtenu un SQL_ID dccyz592gpzpq pour lequel je veux créer un SQL profile qui va me permettre de figer le plan obtenu:
SQL> @sp_create_sql_profile.sql
SQL> ----------------------------------------------------------------------------------------
SQL> --
SQL> -- File name:      create_sql_profile.sql
SQL> --
SQL> -- Purpose:        Create SQL Profile based on Outline hints in V$SQL.OTHER_XML.
SQL> --
SQL> -- Author: Kerry Osborne
SQL> --
SQL> -- Usage:       This scripts prompts for four values.
SQL> --
SQL> --              sql_id: the sql_id of the statement to attach the profile to (must be in the shared pool)
SQL> --
SQL> --              child_no: the child_no of the statement from v$sql
SQL> --
SQL> --              profile_name: the name of the profile to be generated
SQL> --
SQL> --              category: the name of the category for the profile
SQL> --
SQL> --              force_macthing: a toggle to turn on or off the force_matching feature
SQL> --
SQL> -- Description:
SQL> --
SQL> --              Based on a script by Randolf Giest.
SQL> --
SQL> -- Mods:        This is the 2nd version of this script which removes dependency on rg_sqlprof1.sql.
SQL> --
SQL> --              See kerryosborne.oracle-guy.com for additional information.
SQL> ---------------------------------------------------------------------------------------
SQL> --
SQL>
SQL> -- @rg_sqlprof1 '&&sql_id' &&child_no '&&category' '&force_matching'
SQL>
SQL> set feedback off
SQL> set sqlblanklines on
SQL>
SQL> accept sql_id -
>        prompt 'Enter value for sql_id: ' -
>        default 'X0X0X0X0'
Enter value for sql_id: dccyz592gpzpq
SQL> accept child_no -
>        prompt 'Enter value for child_no (0): ' -
>        default '0'
Enter value for child_no (0):
SQL> accept profile_name -
>        prompt 'Enter value for profile_name (PROF_sqlid_planhash): ' -
>        default 'X0X0X0X0'
Enter value for profile_name (PROF_sqlid_planhash):
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> declare
  2  ar_profile_hints sys.sqlprof_attr;
  3  cl_sql_text clob;
  4  l_profile_name varchar2(30);
  5  begin
  6  select
  7  extractvalue(value(d), '/hint') as outline_hints
  8  bulk collect
  9  into
 10  ar_profile_hints
 11  from
 12  xmltable('/*/outline_data/hint'
 13  passing (
 14  select
 15  xmltype(other_xml) as xmlval
 16  from
 17  v$sql_plan
 18  where
 19  sql_id = '&&sql_id'
 20  and child_number = &&child_no
 21  and other_xml is not null
 22  )
 23  ) d;
 24
 25  select
 26  sql_fulltext,
 27  decode('&&profile_name','X0X0X0X0','PROF_&&sql_id'||'_'||plan_hash_value,'&&profile_name')
 28  into
 29  cl_sql_text, l_profile_name
 30  from
 31  v$sql
 32  where
 33  sql_id = '&&sql_id'
 34  and child_number = &&child_no;
 35
 36  dbms_sqltune.import_sql_profile(
 37  sql_text => cl_sql_text,
 38  profile => ar_profile_hints,
 39  category => '&&category',
 40  name => l_profile_name,
 41  force_match => &&force_matching
 42  -- replace => true
 43  );
 44
 45    dbms_output.put_line(' ');
 46    dbms_output.put_line('SQL Profile '||l_profile_name||' created.');
 47    dbms_output.put_line(' ');
 48
 49  exception
 50  when NO_DATA_FOUND then
 51    dbms_output.put_line(' ');
 52    dbms_output.put_line('ERROR: sql_id: '||'&&sql_id'||' Child: '||'&&child_no'||' not found in v$sql.');
 53    dbms_output.put_line(' ');
 54
 55  end;
 56  /
old  19: sql_id = '&&sql_id'
new  19: sql_id = 'dccyz592gpzpq'
old  20: and child_number = &&child_no
new  20: and child_number = 0
old  27: decode('&&profile_name','X0X0X0X0','PROF_&&sql_id'||'_'||plan_hash_value,'&&profile_name')
new  27: decode('X0X0X0X0','X0X0X0X0','PROF_dccyz592gpzpq'||'_'||plan_hash_value,'X0X0X0X0')
old  33: sql_id = '&&sql_id'
new  33: sql_id = 'dccyz592gpzpq'
old  34: and child_number = &&child_no;
new  34: and child_number = 0;
old  39: category => '&&category',
new  39: category => 'DEFAULT',
old  41: force_match => &&force_matching
new  41: force_match => TRUE
old  52:   dbms_output.put_line('ERROR: sql_id: '||'&&sql_id'||' Child: '||'&&child_no'||' not found in v$sql.');
new  52:   dbms_output.put_line('ERROR: sql_id: '||'dccyz592gpzpq'||' Child: '||'0'||' not found in v$sql.');

On vérifie qu'un SQL profile nommé PROF_dccyz592gpzpq_3994356561 a bien été créé:
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:
old   3: where sql_text like nvl('&sql_text','%')
new   3: where sql_text like nvl('','%')
Enter value for name:
old   4: and name like nvl('&name',name)
new   4: and name like nvl('',name)

NAME                           CATEGORY        STATUS   SQL_TEXT                                                       FOR
------------------------------ --------------- -------- ---------------------------------------------------------------------- ---
PROF_dccyz592gpzpq_3994356561  DEFAULT         ENABLED  SELECT                                                         YES

Et c'est ce SQL profile qu'on veut coller à la requête exécutée par l'application et pour ce faire on utilise le script que j'ai affiché plus haut:
SQL> @sp_move_sql_profile.sql
SQL> ----------------------------------------------------------------------------------------
SQL> --
SQL> -- File name:      move_sql_profile.sql
SQL> --
SQL> -- Purpose:        Moves a SQL Profile from one statement to another.
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
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 is based on a script originally written by Randolf Giest.
SQL> --              It's purpose is to allow a statements text to be manipulated in whatever
SQL> --              manner necessary (typically with hints) to get the desired plan. Then
SQL> --              once a SQL Profile has been created on the new statement, it's SQL Profile
SQL> --              can be moved (or attached) to the orignal statement with unmodified text.
SQL> --
SQL> -- Mods:        This script should now work wirh all flavors of 10g and 11g.
SQL> --
SQL> --
SQL> --              See kerryosborne.oracle-guy.com for additional information.
SQL> -----------------------------------------------------------------------------------------
SQL>
SQL> accept profile_name -
>        prompt 'Enter value for profile_name: ' -
>        default 'X0X0X0X0'
Enter value for profile_name: PROF_dccyz592gpzpq_3994356561
SQL> accept sql_id -
>        prompt 'Enter value for sql_id: ' -
>        default 'X0X0X0X0'
Enter value for sql_id: 0496r075a27c8
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> ----------------------------------------------------------------------------------------
SQL> --
SQL> -- File name:      profile_hints.sql
SQL> --
SQL> ---------------------------------------------------------------------------------------
SQL> --
SQL> set sqlblanklines on
SQL>
SQL> declare
  2  ar_profile_hints sys.sqlprof_attr;
  3  cl_sql_text clob;
  4  version varchar2(3);
  5  l_category varchar2(30);
  6  l_force_matching varchar2(3);
  7  b_force_matching boolean;
  8  begin
  9   select regexp_replace(version,'\..*') into version from v$instance;
 10
 11  if version = '10' then
 12
 13  -- dbms_output.put_line('version: '||version);
 14     execute immediate -- to avoid 942 error
 15     'select attr_val as outline_hints '||
 16     'from dba_sql_profiles p, sqlprof$attr h '||
 17     'where p.signature = h.signature '||
 18     'and name like (''&&profile_name'') '||
 19     'order by attr#'
 20     bulk collect
 21     into ar_profile_hints;
 22
 23  elsif version = '11' then
 24
 25  -- dbms_output.put_line('version: '||version);
 26     execute immediate -- to avoid 942 error
 27     'select hint as outline_hints '||
 28     'from (select p.name, p.signature, p.category, row_number() '||
 29     '      over (partition by sd.signature, sd.category order by sd.signature) row_num, '||
 30     '      extractValue(value(t), ''/hint'') hint '||
 31     'from sys.sqlobj$data sd, dba_sql_profiles p, '||
 32     '     table(xmlsequence(extract(xmltype(sd.comp_data), '||
 33     '                               ''/outline_data/hint''))) t '||
 34     'where sd.obj_type = 1 '||
 35     'and p.signature = sd.signature '||
 36     'and p.name like (''&&profile_name'')) '||
 37     'order by row_num'
 38     bulk collect
 39     into ar_profile_hints;
 40
 41  end if;
 42
 43
 44  /*
 45  declare
 46  ar_profile_hints sys.sqlprof_attr;
 47  cl_sql_text clob;
 48  begin
 49  select attr_val as outline_hints
 50  bulk collect
 51  into
 52  ar_profile_hints
 53  from dba_sql_profiles p, sqlprof$attr h
 54  where p.signature = h.signature
 55  and name like ('&&profile_name')
 56  order by attr#;
 57  */
 58
 59  select
 60  sql_fulltext
 61  into
 62  cl_sql_text
 63  from
 64  v$sqlarea
 65  where
 66  sql_id = '&&sql_id';
 67
 68  dbms_sqltune.import_sql_profile(
 69  sql_text => cl_sql_text
 70  , profile => ar_profile_hints
 71  , category => '&&category'
 72  , name => 'PROFILE_'||'&&sql_id'||'_moved'
 73  -- use force_match => true
 74  -- to use CURSOR_SHARING=SIMILAR
 75  -- behaviour, i.e. match even with
 76  -- differing literals
 77  , force_match => &&force_matching
 78  );
 79  end;
 80  /
old  18:    'and name like (''&&profile_name'') '||
new  18:    'and name like (''PROF_dccyz592gpzpq_3994356561'') '||
old  36:    'and p.name like (''&&profile_name'')) '||
new  36:    'and p.name like (''PROF_dccyz592gpzpq_3994356561'')) '||
old  55: and name like ('&&profile_name')
new  55: and name like ('PROF_dccyz592gpzpq_3994356561')
old  66: sql_id = '&&sql_id';
new  66: sql_id = '0496r075a27c8';
old  71: , category => '&&category'
new  71: , category => 'DEFAULT'
old  72: , name => 'PROFILE_'||'&&sql_id'||'_moved'
new  72: , name => 'PROFILE_'||'0496r075a27c8'||'_moved'
old  77: , force_match => &&force_matching
new  77: , force_match => true

PL/SQL procedure successfully completed.
Ce script prend notamment en paramètre le nom du SQL profile qu'on veut attacher et le SQL_ID de la requête pourlaquelle on veut forcer le plan. Dans mon cas la requête en question avait pour SQL_ID 0496r075a27c8.
Si j'affiche les SQL profiles de ma base je vois que j'ai maintenant un 2ème SQL Profile nommé PROFILE_0496r075a27c8_moved et qui est attaché au SQL_ID 0496r075a27c8:
NAME                           CATEGORY        STATUS   SQL_TEXT                                                       FOR
------------------------------ --------------- -------- ---------------------------------------------------------------------- ---
PROF_dccyz592gpzpq_3994356561  DEFAULT         ENABLED  SELECT                                                         YES
PROFILE_0496r075a27c8_moved    DEFAULT         ENABLED  SELECT   DECODE (bill.alt_bill_id, 0, ' ', alt_bill_id) alt_bill_id,   YES
Maintenant lorsque mon client lance son application c'est le bon plan qui est exécuté. D'ailleurs lorsque j'affiche le plan exécuté désormais pour cette requête je vois la note suivante à la fin plan qui m'indique que c'est bien grâce au SQL profile que ce plan a été généré:
Note
-----
   - SQL profile PROFILE_0496r075a27c8_moved used for this statement


Dans le même thème, voir aussi les articles suivants:

Voir également l' article de mon ami Mohamed Houri expliquant comment utiliser SPM pour obtenir à peu près le même résultat que moi avec les SQL profiles: