Créer un profile SQL avec des hints

My Oracle Support - Créer un profile SQL avec des hints

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.