Profils et instructions d'optimisation

Un profil d'optimisation est un document XML qui peut contenir des instructions d'optimisation pour une ou plusieurs instructions SQL. La correspondance entre chaque instruction SQL et ses instructions d'optimisation associées est établie à l'aide du texte SQL et d'autres informations nécessaires pour identifier sans ambiguïté une instruction SQL.

L'optimiseur Db2® est l'un des optimiseurs basés sur les coûts les plus sophistiqués du secteur. Cependant, dans de rares cas, l'optimiseur peut sélectionner un plan d'exécution moins qu'optimal. En tant qu'administrateur de base de données familiarisé avec la base de données, vous pouvez utiliser des utilitaires tels que db2advis, runstats et db2expln, ainsi que le paramètre de classe d'optimisation pour vous aider à optimiser l'optimiseur afin d'améliorer les performances de la base de données. Si vous ne recevez pas les résultats attendus une fois toutes les options d'optimisation épuisées, vous pouvez fournir des instructions d'optimisation explicites à l'optimiseur Db2 .

Par exemple, supposons que même après avoir mis à jour les statistiques de la base de données et effectué toutes les autres étapes d'optimisation, l'optimiseur n'a toujours pas choisi l'index I_SUPPKEY pour accéder à la table FOURNISSEURS dans la sous-requête suivante:

    SELECT S.S_NAME, S.S_ADDRESS, S.S_PHONE, S.S_COMMENT
    FROM PARTS P, SUPPLIERS S, PARTSUPP PS
    WHERE P_PARTKEY = PS.PS_PARTKEY
      AND S.S_SUPPKEY = PS.PS_SUPPKEY
      AND P.P_SIZE = 39
      AND P.P_TYPE = 'BRASS'
      AND S.S_NATION = 'MOROCCO'
      AND S.S_NATION IN ('MOROCCO', 'SPAIN')
      AND PS.PS_SUPPLYCOST = (SELECT MIN(PS1.PS_SUPPLYCOST) 
                              FROM PARTSUPP PS1, SUPPLIERS S1 
                              WHERE P.P_PARTKEY = PS1.PS_PARTKEY
                                AND S1.S_SUPPKEY = PS1.PS_SUPPKEY
                                AND S1.S_NATION = S.S_NATION))

Dans ce cas, une instruction d'optimisation explicite peut être utilisée pour influencer l'optimiseur. Exemple :

<OPTGUIDELINES><IXSCAN TABLE="S" INDEX="I_SUPPKEY"/></OPTGUIDELINES>

Les instructions d'optimisation sont spécifiées à l'aide d'une spécification XML simple. Chaque élément de l'élément OPTGUIDELINES est interprété comme une instruction d'optimisation par l'optimiseur Db2 . Il existe un élément d'instruction d'optimisation dans cet exemple. L'élément IXSCAN demande à l'optimiseur d'utiliser l'accès à l'index. L'attribut TABLE de l'élément IXSCAN indique la référence de table cible (à l'aide du nom exposé de la référence de table) et l'attribut INDEX indique l'index.

L'exemple suivant est basé sur la requête précédente et montre comment une instruction d'optimisation peut être transmise à l'optimiseur Db2 à l'aide d'un profil d'optimisation.

<?xml version="1.0" encoding="UTF-8"?>

<OPTPROFILE VERSION="9.1.0.0">
<STMTPROFILE ID="Guidelines for SAMP Q9"> 
  <STMTKEY SCHEMA="SAMP">
    SELECT S.S_NAME, S.S_ADDRESS, S.S_PHONE, S.S_COMMENT
    FROM PARTS P, SUPPLIERS S, PARTSUPP PS
    WHERE P_PARTKEY = PS.PS_PARTKEY
      AND S.S_SUPPKEY = PS.PS_SUPPKEY
      AND P.P_SIZE = 39
      AND P.P_TYPE = 'BRASS'
      AND S.S_NATION = 'MOROCCO'
      AND S.S_NATION IN ('MOROCCO', 'SPAIN')
      AND PS.PS_SUPPLYCOST = (SELECT MIN(PS1.PS_SUPPLYCOST) 
                              FROM PARTSUPP PS1, SUPPLIERS S1 
                              WHERE P.P_PARTKEY = PS1.PS_PARTKEY
                                AND S1.S_SUPPKEY = PS1.PS_SUPPKEY
                                AND S1.S_NATION = S.S_NATION))
  </STMTKEY>
  <OPTGUIDELINES><IXSCAN TABLE="S" INDEX="I_SUPPKEY"/></OPTGUIDELINES>
</STMTPROFILE>
</OPTPROFILE>

Chaque élément STMTPROFILE fournit un ensemble d'instructions d'optimisation pour une instruction d'application. L'instruction ciblée est identifiée par le sous-élément STMTKEY. Le profil d'optimisation reçoit ensuite un nom qualifié de schéma et est inséré dans la base de données. Le profil d'optimisation est mis en application pour l'instruction en spécifiant ce nom dans la commande BIND ou PRECOMPILE .

Les profils d'optimisation permettent de fournir des instructions d'optimisation à l'optimiseur sans modification de la configuration de l'application ou de la base de données. Il vous suffit de composer le document XML simple, de l'insérer dans la base de données et de spécifier le nom du profil d'optimisation dans la commande BIND ou PRECOMPILE . L'optimiseur fait automatiquement correspondre les instructions d'optimisation à l'instruction appropriée.

Les instructions d'optimisation n'ont pas besoin d'être exhaustives, mais doivent être ciblées sur un plan d'exécution souhaité. L'optimiseur Db2 continue d'envisager d'autres plans d'accès possibles à l'aide des méthodes basées sur les coûts existantes. Les instructions d'optimisation ciblant des références de table spécifiques ne peuvent pas remplacer les paramètres d'optimisation généraux. Par exemple, une instruction d'optimisation spécifiant la jointure de fusion entre les tables A et B n'est pas valide pour la classe d'optimisation 0.

Vous pouvez également spécifier des instructions d'optimisation via des instructions d'optimisation imbriquées à la fin des instructions SQL. Exemple :

/*<OPTGUIDELINES><IXSCAN TABLE="S" INDEX="I_SUPPKEY"/></OPTGUIDELINES>*/

Cette méthode de spécification des instructions d'optimisation ne nécessite aucune configuration supplémentaire.

L'optimiseur ignore les instructions d'optimisation non valides ou inapplicables. Si des instructions d'optimisation sont ignorées, un plan d'exécution est créé et SQL0437W avec le code anomalie 13 est renvoyé. Vous pouvez ensuite utiliser l'instruction EXPLAIN pour obtenir des informations de diagnostic détaillées concernant le traitement des instructions d'optimisation.