Digora blog

Comment expliquer les caprices de l'optimiseur Oracle et y faire face - partie 2

28/08/2012
Données

Après avoir posé les bases dans le premier article de cette série, abordons les différentes méthodes permettant de "dompter" l'optimiseur Oracle...

1. Vue d'ensemble

Conscient que son optimiseur est quelquefois capricieux, Oracle fournit plusieurs méthodes pour contrôler son  fonctionnement. A la décharge d'Oracle, il faut reconnaître que la complexité des requêtes (rédigées par les développeurs d'applications ou générées par les applications) est souvent énorme ! On peut se demander parfois si les inventeurs du langage SQL avaient imaginé jusqu'à quelle complexité le SQL allait être utilisé... Certaines requêtes font plusieurs milliers de lignes... Voici quelques méthodes proposées par Oracle pour influencer l'optimiseur sans modifier (ou presque) le code SQL :

  • les Hints
  • les Stored Outlines
  • le SQL Profile
  • Le SQL Plan Management

2. Comment influencer l'optimiseur ?

2.1 Les Hints

Oracle permet d'insérer dans l'ordre SQL des indications (Hints) en tant que commentaire permettant d'influencer l'optimiseur sur le choix du plan d'exécution. Même si il faut éviter au maximum cette méthode, elle peut rendre de grands services. Une modification dans le code SQL est néanmoins nécessaire. Il existe un grand nombre de Hints différents. Exemple de Hint (en rouge) :

     select /*+ index(emp_alias ix_emp) */ ... from scott.emp emp_alias

2.2 Les Stored Outlines

Les Stored Outlines sont disponibles avec Oracle 8i EE, 9i EE, 10g SE et EE et  obsolètes  depuis Oracle 11g. Les Stored Outlines permettent de forcer le plan via des Hints, sans modifier la requête. Pour créer un Stored Outline, il suffit de modifier le contexte de la session pour obtenir le bon plan d'exécution, de spécifier à Oracle de créer l'Outline et de lancer la requête. Pour que les Outlines soient utilisées, il faut mettre en place un paramètre au niveau de la session ou du système. Attention, il n'est pas possible de positionner ce paramètre dans l'init.ora ou le spfile; il faut le repositionner à chaque démarrage d'instance. Inconvénient du Stored Outline : on ne peut pas profiter d’évolutions de l'optimiseur ou du schéma permettant une requête plus rapide (un nouvel index par exemple).

2.3 Le SQL Profile

La fonctionnalité SQL Profile est disponible à partir de la version Oracle 10g. Elle nécessite l'édition Enterprise et les options Diagnostick Pack et Tuning Pack. Le SQL Profile, contrairement à un Outline, n'est en règle générale pas directif (nous verrons l'exception plus loin). Il est peut être créé via le SQL Tuning Advisor. Cette méthode rajoute des indications d'ajustements qui seront utilisées par l'optimiseur Oracle. Il peut également être utilisé (et c'est un de ses gros avantages) en mode "force match" ce qui lui permet de s'appliquer même pour des requêtes dont les valeurs des constantes sont différentes. Voici un inconvénient de l'utilisation du SQL Profile : on ne maîtrise pas  les Hints ajoutés par Oracle, et, comme les Hints sont des ajustements , il se peut qu'avec l'évolution des données, ceux-ci deviennent caduques et que la requête repasse par un mauvais plan d'exécution.

Compare Explain Plans

 2.4 Le SQL Plan Management

La fonctionnalité SQL Plan Management  est disponible à partir de la version Oracle 11g. Elle nécessite l'édition Enterprise. Les options Diagnostick Pack et Tuning Pack sont nécessaires pour une utilisation automatisée via des Tuning Sets. Le SQL Plan Management est une évolution des Stored Outlines : il permet de forcer un plan via des Hints. Mais contrairement aux Stored Outlines, il est possible de valider plusieurs plans différents pour une même requête et ainsi de faire évoluer les plans disponibles. L'optimiseur choisira au moment de l'exécution un de ces différents plans pré-validés. Le SQL Plan Management ne dispose pas d’un mode « force match ».

SQL management base

3. Exemple d'utilisation avancée

Dernièrement, suite à une migration d'Oracle 10g à Oracle 11g chez un client de Digora, plusieurs  requêtes sont devenues très lentes et très consommatrices en ressources, entraînant la saturation du serveur. Le code de l'application n'était pas modifiable de manière rapide. Les requêtes n'utilisant pas de variables (les valeurs des champs étaient en dur), le SQL Plan Management et les Stored Outlines n'étaient donc pas utilisables. Un SQL Profile a été créé, mais certains Hints n'étaient pas satisfaisants comparativement à la situation précédente : utilisation du parallélisme de manière forcée et utilisation d'une valeur de Dynamic Sampling élevée. La requête utilisait plus de ressources et restait plus lente qu'en Oracle 10g. Une investigation poussée a permis de trouver une solution plus satisfaisante. Elle utilise un script disponible sur le site du support Oracle : "coe_xfr_sql_profile.sql". Grace à ce script, il est possible de générer manuellement des SQL Profiles utilisant les mêmes Hints que les Stored Outlines ou le SQL Plan Management. Après quelques tests, nous avons donc mis en place les SQL profiles générés manuellement et avons ainsi résolu  les problèmes de performances sur ces requêtes. Dans la partie 3 de cette série d'articles, DIGORA vous apportera d'autres précisions sur l'optimiseur Oracle. Vous souhaitez en savoir plus ? Contactez-nous ici