Optimiser des requêtes Oracle suite à une migration 10g

Un important groupe pétrolier a bénéficié à plusieurs reprises de l'expertise de DIGORA. Ayant à faire face à des problèmes de performances de requêtes SQL dans le cadre de la migration d'une application spécifique vers Oracle 10g, ce client a de nouveau fait appel à un spécialiste en optimisation de DIGORA.  En effet, une requête s'exécutant en 13mn sous Oracle 9i met désormais plus de 15h, ce qui est.... plutôt gênant !

Contexte

Une requête SQL s'exécute en moins de 13mn sous Oracle 9i. Suite à la migration (en test) vers Oracle 10g, cette même requête s'exécute maintenant en plus de 15h. La requête est complexe, fait l'objet de nombreuses sous-requêtes imbriquées et utilise la forme hiérarchique SQL (CONNECT BY).

Démarche adoptée

Une première démarche consiste à examiner de façon systématique les éléments suivants entre les deux environnements :

• paramètres d'instance et de base
• mise à jours des statistiques Oracle

Ces contrôles n'ont rien révélé d'anormal. Puis, notre expert examine le code SQL et constate le mise en œuvre d'un hint dans la requête. Ce hint, positionné dans une des sous-requêtes imbriqués est un moyen pour forcer l'optimiseur à utiliser un index bien précis pour accéder à la table concernée. Il enlève le hint et exécute de nouveaux tests. Voici le résultat :

Tableau 1 : comparaison des temps d'exécution avec et sans Hint (en heures, minutes, secondes et centièmes de seconde)

Le fait d'enlever ce hint permet de revenir aux temps d'Oracle 9i. Pour aller plus loin, l'ajout de hints adaptés permet d'améliorer grandement les performances :

Tableau 2 : gain de performances supplémentaires avec d'autres Hints

Nous constatons un temps final de moins de 5 secondes au lieu des 12 minutes constatées sous Oracle 9i.

Solution

Mise en œuvre

La mise en œuvre s'effectue en deux temps :

• changement de nom de l'index, ce qui évite de toucher au code SQL
• modification de l'application pour enlever le hint et ajouter les deux nouveaux hints et bénéficier des améliorations

Résultat

L'application va connaitre des performances meilleures qu'avant la migration vers Oracle 10g. D'autres suggestions ont été apportées à l'occasion de cette mission (utilisation de tables temporaires globales, etc...). Sans aucun doute, notre client continuera de faire appel à notre expertise.