Hint oracle et choix du plan d'exécution

Lors d'une requête SQL, Oracle construit dans un premier temps son plan d'exécution (execution plan). C'est ce qui lui permettra de savoir comment accéder aux données.
Pour construire ce plan, il s'appuie sur des règles qui sont paramétrables : en mode RULE, si la colonne possède un index, il faut utiliser cet index. Mais depuis la 8i, le mode CHOOSE (CBO) utilise, en complément de ces règles, les statistiques relatives à ses tables et index ; dans ce cas, si la colonne est indexée mais que le nombre de lignes à parcourir est très faible, il choisira de ne pas utiliser l'index et de faire un FULL SCAN sur la table.

Ce paramètre est visible comme ceci :
show parameters optimizer_mode
Afin de savoir le seuil pour lequel il vaut mieux utiliser l'index plutôt qu'un full scan il s'appuie sur un autre paramètre :
show parameters optimizer_index_cost_adj

Malgré toutes ces règles, Oracle peut ne pas choisir le plan d'exécution optimal. La première méthode de tuning consiste alors à utiliser des ordres appelés hint qui se placent dans la requête sous forme de commentaires afin d'optenir les meilleures performances possibles :
select /*+ use_nl(t1, t2) */
* from t1 inner join t2 on t1.col1 = t2.col3

indique que oracle doit considérer la jointure sous forme de NESTED LOOP¨.

De très nombreux hint existent, par exemple :

Forcer l'utilisation d'un index (IX4) sur un select :
select /*+ index(t1 IX4) */
* from t1 where col3='M'


Forcer l'ordre de jointure des tables présentent dans la clause FROM
select /*+ ordered */
* from t1, t2, t3 where t1.id1 = t2.id1 and t2.id2 = t3.id2 and t1.col1='M'


Utilisation de plusieurs hints sur plusieurs index :
select /*+ index(l1 I_l40) use_nl(l1, l2) index(l I_l38) use_nl(l, ps) index(ps I_ps41) use_nl(ps, ca) index(ca I_ca12) use_nl(ca, conv) index(conv I_conv12) use_nl(conv, co) index(co I_co2) */
distinct co.id_co, co.nom, co.ville
[ ... ]

Les hints multiples permettent de contrôler entièrement le plan de notre requête pour une optimisation très précises.

Cependant, globalement et de manière générale, le mode CHOOSE d'oracle suffit et le plan est bien construit.
Il s'avère notamment utile lorsque le nombre de jointures devient trop important pour qu'oracle estime tous les plans d'exécution possibles ; en effet le paramètre optimizer_search_limit (jusque Oracle 8.1.7) se combine au paramètre optimizer_max_permutations (qui devient caché en 10g -> _optimizer_max_permutations) pour indiquer à oracle le nombre de plans d'exécution à tester avant d'en choisir un.

En effet il n'est pas très bon d'avoir un optimizer_max_permutations trop haut, oracle peut alors passer un temps trop important à construire son plan d'exécution avant de faire sa recherche. D'un autre coté, si ce paramètre est trop faible, trop peu de plans seront considérés et la requête sera exécutée par un plan pas tout à fait optimisé.

Un conseil peut alors consister à placer ce paramètre très haut lors d'un premier test (sur base de même structure et volumétrie) pour connaître le meilleur plan d'exécution et de construire la requête avec les hints répondant à ce plan pour une mise en production avec le paramètre optimizer_max_permutations plus faible (dans le cas de requêtes extrèmement complexes seulement) ou encore, utiliser des outlines.

Voir aussi :
- l'article sur les jointures pour connaître les différents types de jointure : nested loop (use_nl), hash join (use_hash), merge join (use_merge)
- Les Index
- Les statistiques Oracle
- Pourquoi mon index n'est pas utilisé ?
- Comparer l'exécution d'une requête avec 2 index différents

Articles présentant des hints spécifiques :
- Hint sur le cache Oracle 
- Hint sur les DBlink




Vous n'avez pas trouver réponse à votre question ? Préciser votre recherche :

Catégories