Créer un profile SQL avec des hints nécessite Tuning Pack mais est utile lorsqu’on fait face à un progiciel qui ne peut aller modifier son code. En effet, quel DBA n’est pas régulièrement confronté au problème suivant :
- J’ai trouvé ce qui ne marche pas dans mon code SQL.
- J’ai testé avec certains hints et ça a réglé le problème.
- L’éditeur (ou le développeur) refuse de modifier son code pour implémenter les hints.
Il est possible depuis la version 10g de positionner des hints sur une requête sans aller modifier le code applicatif. La technique utilise le package dbms_sqltune et est donc soumise à licence.
De manière générique on procède comme suit :
1°) Trouver les <em>Hints</em> ( script de <a href=”http://kerryosborne.oracle-guy.com/2009/04/oracle-sql-profiles/” target=_new>Kerry Osborne</a> ) si on souhaite fixer un plan
select extractvalue(value(d), '/hint') as outline_hints
from xmltable('/*/outline_data/hint' passing
(select xmltype(other_xml) as xmlval
from dba_hist_sql_plan
where sql_id = 'SQL_ID_DE_LA_REQUETE_A_MODIFIER'
and plan_hash_value = PLAN_HASH_VALUE_QUE_L_ON_SOUHAITE_APPLIQUER
and other_xml is not null)) d;
2°) Créer un profile SQL à partir de hints
DECLARE
sql_code clob;
BEGIN
select sql_text into sql_code
from sys.dba_hist_sqltext
where sql_id='SQLID_DE_LA_REQUETE_A_MODIFIER';
dbms_sqltune.import_sql_profile( sql_text => l_sql
, name => 'SQLPROFILE_GENERE'
, description => 'SQL Profile pour ma requête qui bogue'
, profile => sqlprof_attr( q'! hint1 !'
, q'! hint2 !' [...])
, force_match => true );
end;
/
La procédure utilisée :
PROCEDURE IMPORT_SQL_PROFILE
Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SQL_TEXT CLOB IN PROFILE SQLPROF_ATTR IN NAME VARCHAR2 IN DEFAULT DESCRIPTION VARCHAR2 IN DEFAULT CATEGORY VARCHAR2 IN DEFAULT VALIDATE BOOLEAN IN DEFAULT REPLACE BOOLEAN IN DEFAULT FORCE_MATCH BOOLEAN IN DEFAULT
Par défaut
- category vaut “DEFAULT”
- replace vaut “FALSE”
- match vaut “FALSE”
La fonction n’est pas documentée et assez peu commentée, mais supportée depuis peu par Oracle. Donc, plus de raison de s’en priver et de créer un profile SQL avec des hints quand le besoin s’en fait sentir.