Dans ce post je vais tenter d'expliquer comment on peut forcer l’utilisation d’un hint sans toucher au corps de la requête en utilisant là aussi la procédure dbms_sqltune.import_sql_profile.
Pour illustrer cette astuce je vais partir d’une optimisation que j’ai eu à réaliser récemment sur une base 10g.
La requête à optimiser était la suivante (j’ai supprimé volontairement la partie SELECT qui était trop longue) :
SELECT
….
FROM ORDER_DISPATCH_STATUS, VMAT_FLAT_EVENT_DESCS
WHERE ods_order_sending_type_id = :"SYS_B_22"
AND ods_state = :"SYS _B_23"
AND viev_root_id = ods_event_id
AND TRUNC(lyx_day) = TRUNC(SYSDATE)
AND (TO_NUMBER(TO_CHAR(SYSDATE, :"SYS_B_24")) -
TO_NUMBER(TO_CHAR(ods_sending_date_theo, :"SYS_B_25"))) *
:"SYS_B_26" + TO_NUMBER(TO_CHAR(SYSDATE, :"SYS_B_27")) -
TO_NUMBER(TO_CHAR(ods_sending_date_theo, :"SYS_B_28")) > :"SYS_B_29"
AND ods_message IS NULL;
Les stats ci-dessous montrent que cette requête a toujours utilisé un seul plan d ‘exécution et que ce plan a été exécuté 47 621 lors des 40 derniers jours (40 jours est la durée de rétention du référentiel AWR appliquée à cette base) et génère en moyenne 61562 logical reads par exécution :
SQL> @awr_plan_stats Enter value for sql_id: 0tsubdyu2zbaz SQL_ID PLAN_HASH_VALUE EXECS AVG_ETIME AVG_CPU_TIME AVG_LIO AVG_PIO ------------- --------------- ------------ ------------ ------------ -------------- ------------ 0tsubdyu2zbaz 2703446289 47,621 1.582 1.608 61,562.2 .4
En exécutant cette requête avec les bonnes valeurs des binds variables, j’obtiens 61000 logical reads pour aucune ligne retournée. C’est très très cher payé.
Plan hash value: 2703446289
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:02.10 | 61413 | | | |
|* 1 | HASH JOIN | | 1 | 481 | 0 |00:00:02.10 | 61413 | 894K| 894K| 207K (0)|
|* 2 | TABLE ACCESS BY INDEX ROWID| ORDER_DISPATCH_STATUS | 1 | 481 | 0 |00:00:02.10 | 61413 | | | |
|* 3 | INDEX RANGE SCAN | IDX_ODS_SND_TYPE_ID | 1 | 4163 | 2367K|00:00:00.01 | 6280 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| VMAT_FLAT_EVENT_DESCS | 0 | 313K| 0 |00:00:00.01 | 0 | | | |
|* 5 | INDEX RANGE SCAN | IDX_TRUNC_LYX_DAY | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("VIEV_ROOT_ID"="ODS_EVENT_ID")
2 - filter(("ODS_STATE"=:SYS_B_23 AND (TO_NUMBER(TO_CHAR(SYSDATE@!,:SYS_B_24))-TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("ODS_SENDING_
DATE_THEO"),:SYS_B_25)))*:SYS_B_26+TO_NUMBER(TO_CHAR(SYSDATE@!,:SYS_B_27))-TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("ODS_SENDING_DATE_TH
EO"),:SYS_B_28))>:SYS_B_29 AND "ODS_MESSAGE" IS NULL))
3 - access("ORDER_DISPATCH_STATUS"."SYS_NC00012$"=SYS_OP_DESCEND(:SYS_B_22))
filter(SYS_OP_UNDESCEND("ORDER_DISPATCH_STATUS"."SYS_NC00012$")=:SYS_B_22)
5 - access("VMAT_FLAT_EVENT_DESCS"."SYS_NC00116$"=SYS_OP_DESCEND(TRUNC(SYSDATE@!)))
filter(SYS_OP_UNDESCEND("VMAT_FLAT_EVENT_DESCS"."SYS_NC00116$")=TRUNC(SYSDATE@!))
La complexité des clauses WHERE pour cette requête fait que le CBO estime mal les cardinalités retournées pour chaque opération et choisit donc la mauvaise table comme table leader, en l’occurrence la table ORDER_DISPATCH_STATUS au lieu de VMAT_FLAT_EVENT_DESCS.
Voyons ce que donne le plan en forçant le CBO à choisir la table VMAT_FLAT_EVENT_DESCS comme table directrice. Pour ce faire on peut utiliser le hint LEADING : /*+ leading(VMAT_FLAT_EVENT_DESCS) */
Plan hash value: 1318622414 ---------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.02 | 4 | 8 | | | | |* 1 | HASH JOIN | | 1 | 481 | 0 |00:00:00.02 | 4 | 8 | 1011K| 1011K| 554K (0)| | 2 | TABLE ACCESS BY INDEX ROWID| VMAT_FLAT_EVENT_DESCS | 1 | 313K| 0 |00:00:00.02 | 4 | 8 | | | | |* 3 | INDEX RANGE SCAN | IDX_TRUNC_LYX_DAY | 1 | 1 | 0 |00:00:00.02 | 4 | 8 | | | | |* 4 | TABLE ACCESS BY INDEX ROWID| ORDER_DISPATCH_STATUS | 0 | 481 | 0 |00:00:00.01 | 0 | 0 | | | | |* 5 | INDEX RANGE SCAN | IDX_ODS_SND_TYPE_ID | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | ----------------------------------------------------------------------------------------------------------------------------------------------------
On voit que cette fois la table conductrice est bien VMAT_FLAT_EVENT_DESCS et que le nombre de logical reads est passé de 61413 à 4.
Mon souci ici était que bien que j’avais la solution je ne pouvais pas toucher au corps de la requête pour implémenter ce hint. Il me fallait donc un moyen d’ajouter ce hint sans toucher au code SQL.
La solution consiste à créer un SQL profile qui va contenir ce hint dans l’OUTLINE DATA. L’OUTLINE DATA contient l’ensemble des hints qui déterminent le plan d’exécution d’une requête.
Affichons l’OUTLINE DATA de la requête contenant le hint LEADING :
select * from table(dbms_xplan.display_cursor('6s3y9wn6basrf',0,'OUTLINE'));
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
OPT_PARAM('_optimizer_cost_model' 'io')
OPT_PARAM('_complex_view_merging' 'false')
OPT_PARAM('optimizer_index_cost_adj' 20)
OPT_PARAM('optimizer_index_caching' 30)
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "VMAT_FLAT_EVENT_DESCS"@"SEL$1" "IDX_TRUNC_LYX_DAY")
INDEX_RS_ASC(@"SEL$1" "ORDER_DISPATCH_STATUS"@"SEL$1" "IDX_ODS_SND_TYPE_ID")
LEADING(@"SEL$1" "VMAT_FLAT_EVENT_DESCS"@"SEL$1" "ORDER_DISPATCH_STATUS"@"SEL$1")
USE_HASH(@"SEL$1" "ORDER_DISPATCH_STATUS"@"SEL$1")
END_OUTLINE_DATA
*/
La partie de l’outline data qui nous interesse est celle contenant le mot LEADING :
LEADING(@"SEL$1" "VMAT_FLAT_EVENT_DESCS"@"SEL$1" "ORDER_DISPATCH_STATUS"@"SEL$1")
C’est ce hint qu’on souhaiterait associer via un profile à la requête exécutée par l’application c'est-à-dire celle qui ne contient pas le hint. Pour atteindre ce but on peut utiliser le script de Kerry OSBORNE que j'ai recopié ici:
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_text
into
cl_sql_text
from
dba_hist_sqltext
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
La variable SQL_ID doit contenir le SQL_ID de la requête qui s ‘exécute mal en production.
La variable PROFILE_NAME contient le nom du SQL profile qu’on souhaite créer.
La variable CATEGORY correspond à la catégorie du SQL profile qu’on veut créer.
La variable FORCE_MATCHING permet d’indiquer si on souhaite forcer le plan aux autres requêtes qui diffèrent sur la valeur littéral utilisée dans la clause WHERE.
La variable HINT doit être settée avec le hint qu’on a récupéré précédemment de l’OUTLINE DATA du plan contenant le bon ordre de jointure.
Dans mon cas voici les valeurs que j’ai indiquées pour ces variables :
SQL_ID = 0tsubdyu2zbaz PROFILE_NAME = PROFILE_0tsubdyu2zbaz_MANUAL CATEGORY = DEFAULT FORCE_MATCHING = TRUE HINT= LEADING(@"SEL$1" "VMAT_FLAT_EVENT_DESCS"@"SEL$1" "ORDER_DISPATCH_STATUS"@"SEL$1")
Une fois le script exécuté, je vérifie que le SQL profile existe bien en base:
SQL> select name,category,created,force_matching 2 from dba_sql_profiles 3 where name='PROFILE_0tsubdyu2zbaz_MANUAL'; NAME CATEGORY CREATED FOR ------------------------------ ------------------------------ --------- --- PROFILE_0tsubdyu2zbaz_MANUAL DEFAULT 18-JUL-11 YES
Maintenant lorsque je réexecute ma requête initiale voici ce que j’obtiens :
Plan hash value: 1318622414
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 4 | | | |
|* 1 | HASH JOIN | | 1 | 516 | 0 |00:00:00.01 | 4 | 1011K| 1011K| 572K (0)|
| 2 | TABLE ACCESS BY INDEX ROWID| VMAT_FLAT_EVENT_DESCS | 1 | 314K| 0 |00:00:00.01 | 4 | | | |
|* 3 | INDEX RANGE SCAN | IDX_TRUNC_LYX_DAY | 1 | 1 | 0 |00:00:00.01 | 4 | | | |
|* 4 | TABLE ACCESS BY INDEX ROWID| ORDER_DISPATCH_STATUS | 0 | 516 | 0 |00:00:00.01 | 0 | | | |
|* 5 | INDEX RANGE SCAN | IDX_ODS_SND_TYPE_ID | 0 | 4181 | 0 |00:00:00.01 | 0 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("VIEV_ROOT_ID"="ODS_EVENT_ID")
3 - access("VMAT_FLAT_EVENT_DESCS"."SYS_NC00116$"=SYS_OP_DESCEND(TRUNC(SYSDATE@!)))
filter(SYS_OP_UNDESCEND("VMAT_FLAT_EVENT_DESCS"."SYS_NC00116$")=TRUNC(SYSDATE@!))
4 - filter(("ODS_STATE"=:SYS_B_23 AND (TO_NUMBER(TO_CHAR(SYSDATE@!,:SYS_B_24))-TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("ODS_SENDING_
DATE_THEO"),:SYS_B_25)))*:SYS_B_26+TO_NUMBER(TO_CHAR(SYSDATE@!,:SYS_B_27))-TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("ODS_SENDING_DATE
EO"),:SYS_B_28))>:SYS_B_29 AND "ODS_MESSAGE" IS NULL))
5 - access("ORDER_DISPATCH_STATUS"."SYS_NC00012$"=SYS_OP_DESCEND(:SYS_B_22))
filter(SYS_OP_UNDESCEND("ORDER_DISPATCH_STATUS"."SYS_NC00012$")=:SYS_B_22)
Note
-----
- cpu costing is off (consider enabling it)
- SQL profile "PROFILE_0tsubdyu2zbaz_MANUAL" used for this statement
On voit que le bon plan est pris en compte.
Dans la partie Note tout en bas du plan d’exécution il est indiqué que le SQL profile PROFILE_0tsubdyu2zbaz_MANUAL a été utilisée pour l’exécution de cette requête.
CONCLUSION :
En utilisant des SQL profile "manuels", il est tout à fait possible d’ajouter des hints à une requête sans avoir à toucher au corps de la requête.
Ce genre de solutions est pratique lorsqu’on utilise un progiciel pour lequel on ne peut pas toucher au code source ou bien lorsqu’on veut rapidement améliorer l’exécution d’une requête en production sans attendre la livraison d’un patch ou la prochaine livraison applicative.
Bonjour Ahmed,
RépondreSupprimerCeci est très intéressant.
Je profite de ton exemple pour te poser des questions: as tu constaté qu'avec oracle 11 le CBO a du mal parfois à determiner des plans d'exécution optimaux?
J'ai constaté que sur plusieurs requêtes nous sommes obligés de passer par les hints et surtout les requêtes ou on utilisait des tables histo sur lesquelles on a plusieurs index bitmap, pour avoir le bon, plan je suis oblgé de faire appel à ce hint(INDEX_COMBINE(table_alias)).
Qu'en penses tu?
Merci
Après chaque migration il est fréquent d'avoir d'avoir des requêtes qui régressent. Le hint doit servir de rustine pour débloquer rapidement un problème en prod. Il est important d'analyser le comportement du CBO via une trace 10053 pour comprendre dans ton cas pourquoi le CBO décide de pas combiner les index bitmap. Le hint peut être la solution finale par défaut si vraiment pour une raison ou une autre (bug, complexité des filter predicates etc.) et si toutes les autres solutions plus durables ont été explorées (calcul d'histogrammes, calcul de stats étendues, réecriture de la requête...)le CBO n'arrive toujours pas à s'en sortir.
RépondreSupprimer