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

Comme les autres SGBD, Oracle utilise un optimiseur permettant de définir un plan d'exécution des ordres SQL.  L'optimiseur d'Oracle est réputé pour réaliser des prodiges de performances... sauf dans certains cas, non prévisibles. Ce premier article d'une série a pour but de faire un point sur ce sujet important, en posant les bases d'abord...

1. Introduction

Voici quelques faits à prendre en compte :

• L'exécution d'un ordre SQL nécessite un plan d'exécution (dans quel ordre et de quelle façon Oracle va accéder aux tables)
• L'optimiseur Oracle génère et sélectionne ce plan
• Il existe deux approches d'optimisation
  • Optimisation basée sur les règles (Rule Based Optimization = RBO); cette méthode n'est plus supportée par Oracle depuis quelques version
  • Optimisation basée sur les coûts (Cost Based Optimization  = CBO)

• La performance dépend du plan choisi
• Un plan peut s'avérer plus performant qu'un autre
• Occasionnellement, un plan moins bon est choisi

D'où cette question légitime : comment empêcher l'optimiseur de choisir le "mauvais plan d'exécution" ?

2. Les plans d'exécution

Les plans d'exécutions sont générés par l'optimiseur en fonction

• de l'ordre SQL envoyé à Oracle
• des objets présents dans le schéma Oracle (tables, index, etc...)

De plus, si l'optimiseur basé sur les coûts est utilisé (CBO), sont également pris en compte

• les paramètres Système d'Oracle
• les paramètres Session d'Oracle
• les statistiques des objets Oracle (tables et index)
• les statistiques du système informatique utilisé (temps de recherche d'un bloc sur disque, temps de transfert entre les disques et la mémoire, vitesse de la CPU, etc...)
• divers autres critères

SELECT e.employee_id, j.job_title, e.salary, d.department_name
    FROM employees e, jobs j, departments d
    WHERE  e.employee_id < 103
       AND e.job_id = j.job_id 
       AND e.department_id = d.department_id;

3. Pourquoi un plan d'exécution peut-il changer ?

Un plan d'exécution peut changer si :

• les objets du schéma changent (ajout ou suppression d'index, etc...)
• la version d'Oracle est différente (ex. mise  à jour)

De plus, si l'optimiseur basé sur les coûts est utilisé (CBO), il existe d'autres causes de changement de plan d'exécution :

• changements des paramètres Système de la base ou l'instance Oracle
• changements des paramètres de la session Oracle
• changements dans les statistiques objets (nombre de lignes d'une table, histogrammes, etc...)
• changements dans les statistiques système  (temps de recherche d'un bloc sur disque, temps de transfert entre les disques et la mémoire, vitesse de la CPU, etc...)

4. Une modification du plan d'exécution est-elle bénéfique ?

Comme toujours, cela dépend....

5. Comment éviter les modifications de plan d'exécution ?

Pour éviter les modifications de plan, il y a deux approches.

5.1 Empêcher toute modification

A coup sûr cette approche ne conviendra à PERSONNE :

• ne pas modifier les données
• ne pas recalculer les statistiques
• ne pas changer les paramètres
• ne pas changer le logiciel Oracle

5.2 Contrôler le fonctionnement de l'optimiseur

Utiliser une des méthodes suivantes pour contrôler le fonctionnement de l'optimiseur :

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

Dans la partie 2 de cette série d'articles, DIGORA reviendra sur ces différentes méthodes avec explications, avantages, inconvénients et cas d'utilisation. Vous souhaitez en savoir plus ? Contactez-nous ici