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