Initiation à l'optimisation de requêtes SQL sous ORACLE

L'optimisation des bases de données est souvent perçue comme étant une tâche du DBA, cependant sur de gros volumes de données et un grand nombre de bases, le DBA ne peut pas connaître la logique métier de toutes ses bases ni passer réécrire les requêtes de chacun des développeurs.
La conception d'applications performantes passera nécessairement par la connaissance des différents éléments permettant d'avoir des bases répondant aux besoins et évolutives. Il est fréquent qu'une requête mal écrite coute 10 fois plus de temps et de ressources qu'elle ne le devrait.
Lorsque l'application n'est pas seule sur le serveur de base de données, les ralentissements ou la surconsommation de ressources peuvent s'avérer gênants non seulement pour l'application, mais aussi les autres projets/ applications partageant les ressources.
Pour que les développeurs puissent écrire des requêtes efficaces, il leur est nécessaire de connaître quelques-uns des mécanismes mis en œuvre lors de l'exécution de leurs requêtes, ici dans le cas d'Oracle.

Cet article s'adresse aux développeurs et à toute personne sachant exécuter des ordres SQL simples, des jointures simples, désirant rapidement améliorer les performances de ses requêtes et la conception de sa base. Le tuning de base de données est une chose complexe, cet article se veut une initiation à des opérations simples qui permettent d'améliorer les performances.
Aucune connaissance DBA ou autre n'est requise.

Cet article ne se veut en aucun cas un guide exhaustif du tuning sous Oracle ! Il n'est que la retranscription écrite de connaissances que j'ai pu acquérir. Je ne détaillerai ici que les éléments concernant vraiment les développeurs et omettrai parfois par souci de simplification d'expliquer des notions pointues…

Un des objectifs des bases de données est de stocker l'information tout en l'organisant de manière à pouvoir y accéder rapidement. De la même manière qu'il y a plusieurs manières de se rendre de Paris à Tokyo, il y a plusieurs manières d'accéder à l'information. Pour accéder à une ligne donnée d'une table, Oracle dispose de plusieurs solutions que nous expliquerons sous peu. Citons parmi celles-ci par exemple le « parcours complet de table » ou « full table scan », le « parcours d'index » ou « Index range scan »… (promis, on va expliquer tout cela après…). Ces différentes voies pour l'accès aux données sont tout simplement appelées « chemin d'accès ».

Il est souvent nécessaire sur des requêtes d'interroger une ou plusieurs tables qui feront donc l'objet d'un ou plusieurs chemins d'accès. L'ensemble des chemins d'accès utilisés pour une requête a pour nom « Plan d'exécution ».
On peut comparer le plan d'exécution à un itinéraire complet entre deux villes et le chemin d'accès aux indications (vous savez le « tourner à droite »…) qui le composent.

Trouver le chemin le plus intéressant pour accéder aux données, c'est le boulot de l'optimiseur Oracle. Évidemment le terme « chemin le plus intéressant » est assez subjectif (et réglable par le DBA dans le cas d'Oracle), mais on demande le plus souvent à Oracle d'utiliser le chemin qu'il estime avoir le meilleur « cout » (cost-based).
Le cout tient compte de l'utilisation des ressources (CPU, accès disques, mémoire…) pour un plan donné.
L'élément à mon avis le plus important est le nombre d'I/O Disque que nécessite la requête. Lire sur un disque c'est lent. Pire encore, devoir effectuer un tri de données sur le disque s'avère souvent extrêmement couteux…

L'optimiseur choisit nous disions… d'accord, mais comment il sait par où il faut passer lui ?
Pour choisir le bon chemin entre deux villes il faut savoir les distances, les risques d'embouteillage… bref, posséder des données sur les trajets… Pour Oracle ces données sur les objets qu'il contient (métadonnées) s'appellent les statistiques. Les statistiques permettent à l'optimiseur d'estimer le cout d'accès aux données. Normalement, elles sont en général calculées automatiquement / périodiquement (Oracle 10G) ou manuellement(10G et avant…).
Pour en savoir plus sur le calcul des stats sur vos bases, demandez à votre DBA. Calculer des stats se fait en lançant une requête « ANALYZE TABLE matable COMPUTE STATISTICS; » par exemple ou mieux en utilisant le package dbms_stats comme ici :

EXEC DBMS_STATS.gather_database_stats; // pour toute la base ... argl !
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15); // pour la base, avec un échantillon de 15%

EXEC DBMS_STATS.gather_schema_stats('SCOTT'); // collecte pour le schéma SCOTT
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15); // idem avec 15% d'échantillon

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES'); // pour une table
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15); // idem avec 15% ...

EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK'); // pour un index
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK', estimate_percent => 15);

Le web est plein d'indications sur la chose.

Les hints ou suggestions sont les instructions que nous pouvons insérer dans nos ordres SQL pour influencer l'optimiseur. Dans certains cas l'optimiseur peut ne pas prendre le meilleur chemin, du moins à mon goût. On peut alors l'influencer en insérant dans l'ordre SQL un hint se présentant comme ceci : /*+ MONHINT */. Oracle le suivra si c'est possible et l'ignorera sinon.
Personnellement je ne recommande pas l'utilisation des hints tant qu'il n'y a pas de souci. L'inconvénient des hints est le fait qu'ils sont écrits en dur et donc inévitablement figés. Quand la volumétrie ou d'autres éléments changent le hint peut devenir caduc et contre-performant. L'utilisation des hints suppose donc de bonnes connaissances dans le domaine et de Oracle.
Beaucoup des éléments que je décrirai par la suite feront l'objet de hints possibles. Je tâcherai d'indiquer en vert ces hints au fur et à mesure.

Pas mal d'outils (SQL Developer de Oracle [gratuit] et TOAD) ont un petit bouton magique qui permet de voir le plan.
Sous SQLPlus, plusieurs manières s'offrent à vous. Le plus simple à mon goût est le mode autotrace :

sqlplus hr/hr

SQL*Plus: Release 10.2.0.1.0 - Production on Mer. Nov. 19 17:40:22 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connecté Ó :
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> select first_name,last_name from employees where last_name like 'T%';

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Jonathon             Taylor
Winston              Taylor
Sigal                Tobias
Peter                Tucker
Oliver               Tuvault

Activons le autotrace :

SQL> set autotrace on;
SQL> select first_name,last_name from employees where last_name like 'T%';

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Jonathon             Taylor
Winston              Taylor
Sigal                Tobias
Peter                Tucker
Oliver               Tuvault


Plan d'exécution
----------------------------------------------------------
Plan hash value: 3085132068

--------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |             |     5 |    75 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| EMP_NAME_IX |     5 |    75 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("LAST_NAME" LIKE 'T%')
filter("LAST_NAME" LIKE 'T%')


Statistiques
----------------------------------------------------------
0  recursive calls
0  db block gets
2  consistent gets
0  physical reads
0  redo size
595  bytes sent via SQL*Net to client
384  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
5  rows processed

SQL>

Le plan est à lire comme suit : Oracle a utilisé un scan (on y revient plus tard) sur l'index « EMP_NAME_IX » et c'est tout.
Vos pires ennemis sont dans les statistiques… Tous les physical reads seront à réduire et les sorts (disk) ou tris sur le disque également.

Une autre solution est de faire un EXPLAIN PLAN sur une commande :

SQL> explain plan for (select * from employees where last_name like 'T%');
Explicité.
SQL> select plan_table_output from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------

Plan hash value: 2077747057

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     5 |   340 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |     5 |   340 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_NAME_IX |     5 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------


2 - access("LAST_NAME" LIKE 'T%')
filter("LAST_NAME" LIKE 'T%')

15 ligne(s) sélectionnée(s).

SQL> explain plan for (select * from employees where last_name like 'T%');

Explicité.

SQL> select plan_table_output from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------

Plan hash value: 2077747057

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     5 |   340 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |     5 |   340 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_NAME_IX |     5 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------


2 - access("LAST_NAME" LIKE 'T%')
filter("LAST_NAME" LIKE 'T%')

15 ligne(s) sélectionnée(s).

Les colonnes cost et time sont précieuses… Ici on peut lire dans le plan que Oracle a effectué un scan sur l'index EMP_NAME_IX puis a accédé à la table EMPLOYEES via les ROWID (on décodera tout cela après).

N. B. Si Oracle vous insulte avec un message « SP2-0618: Impossible de trouver l'identificateur de session. Le rôle PLUSTRACE doit être activé. SP2-0611: Erreur lors de l'activation de l'état STATISTICS » lorsque vous voulez activer le autotrace demandez à votre dba de lancer une commande qui vous donnera le droit de le faire: « grant plustrace to votreUser; »

Voilà pour ces quelques définitions, passons donc maintenant à quelques notions sur les index.

On peut voir un index comme un glossaire, un sommaire. Avoir un glossaire basé sur le bon critère vous permet d'accéder rapidement à la page du livre traitant du sujet désiré. De même, un index base sur la bonne valeur permettra à Oracle de retrouver rapidement l'ensemble de lignes concernées par la requête.
Exemple : Créer un index sur le champ « date de naissance » d'un individu permettra de retrouver rapidement tous les individus nés à une date donnée. L'index représente souvent le premier niveau d'optimisation d'une requête : positionner un index là où il faut permet assez souvent de diminuer grandement le cout d'accès à une table (voir les chemins d'accès).
Un index peut être basé sur une ou plusieurs valeurs… on peut par exemple indexer au sein du même index la date de naissance et le nom de la personne, on parle alors d'index composé ou composite. Si on effectue souvent la recherche avec date de naissance et nom de la personne cet index sera probablement intéressant.
L'index permet donc à Oracle de trouver les lignes de la table qui correspondent à la requête.

Mettre un index c'est bien, connaître les types d'index permet d'aller plus loin. Oracle gère plusieurs types d'index, qui ont chacun leurs spécificités et sont plus avantageux dans tel ou tel cas. Les voici :

create index monIndex on individus(NOM,PRENOM)

CREATE BITMAP INDEX monIndex ON INDIVIDUS(NOM)

• B-tree : les index b-tree sont le type d'index par défaut quand on ne précise rien. Comme son nom l'indique, l'index b-tree est organisé en arbre, les racines constituées par les valeurs précisées à gauche. En clair, si je crée un index avec (NOM,PRENOM) via la commande
  cela crée un index en arbre avec les personnes classées par nom puis par prenom…
  Attention : les valeurs NULL ne sont pas indexées dans un index, si elles sont seules. Par exemple l'index basé sur le champ (numero) de téléphone ne sera pas utilisable pour un « where telephone is not null ». Par contre un index basé sur deux colonnes dont une ne sera pas nulle sera utilisable. L'index (nom, telephone) sera ainsi utilisable si nom n'est pas nul. En résumé une valeur nulle peut exister dans l'index à condition qu'elle soit associée à une valeur non nulle ;
  
• Bitmap : un mot binaire est créé, composé d'autant de bits que de possibilités de valeurs de l'index, ce type d'index est particulièrement efficace lorsque le nombre de valeurs est petit ainsi que pour les opérations AND et OR. Là je vais illustrer par un exemple cela vaut mieux…
  Prenons en exemple le champ « civilité » de ma table individus. Ce champ contient un nombre très limité de valeurs possibles: 'Monsieur' ; 'Madame' ; 'Mademoiselle' et NULL (vide).
  Les valeurs de l'index pourront par exemple être:
  000 pour NULL, 001 pour Monsieur, 010 pour Mademoiselle, 100 pour Madame
  Lors de la recherche, Oracle effectuera un simple AND sur la valeur de l'index pour comparer. Ce type d'index est très performant lorsque le nombre valeur est petit. Toutefois, les index bitmaps provoquent lors des opérations d'écriture des verrous importants qui font qu'ils sont très adaptés aux bases en lecture, mais deviennent contre-indiqués dès lors que la base devra subir un nombre important d'insert par exemple.
  Le verrou se fait sur la valeur dans l'index et on se retrouvera lors d'une mise à jour avec un verrou sur toutes les lignes concernées par cette valeur de l'index et ça peut faire beaucoup…
  Ces index sont particulièrement efficaces pour des requêtes avec des clauses WHERE complexes, des opérations count ou sum…
  Ils sont également économiques en stockage. À utiliser en connaissant ses limites ;
  
• les index à clefs inversées : là c'est très simple : « 1234 » deviendra « 4321 »… La raison de cette inversion provient du fait que les index peuvent devenir un goulot d'étranglement quand un grand nombre d'insertions est fait… L'index étant ordonné, quand vous insérez par exemple beaucoup de valeurs venant d'une séquence, c'est le même bloc de l'index qui est alors systématiquement sollicité. En cassant la continuité, on enlève cet inconvénient… ce type d'index a toutefois un inconvénient, on ne peut y effectuer des « range scan » (parcours d'une partie de l'index) de par sa nature discontinue. Concrètement cet index pourra être utilisé lors d'une clause where monchamp=maValeur, mais pas avec une clause where monChamps > maValeur. Seul l'opérateur égalité peut l'utiliser. Cela peut facilement être gênant ;
  
• les index basés sur des fonctions : là on n'indexe plus un champ, mais le résultat d'une opération sur un champ. Par exemple, on crée un index sur UPPER(monChamp) plutôt que sur monChamp. Pour l'utilité on y vient juste après :) Ce type d'index revient donc à précalculer une valeur.

Comme nous l'avons vu, les index composés sont des index sur plusieurs valeurs, par exemple

CREATE INDEX monIndex on invidus(nom,prenom);

Une requête portant une partie des champs indexés ne pourra utiliser au mieux l'index que si ces champs sont placés en tête d'index. Par exemple l'index (NOM, PRENOM) pourra être utilisé le plus efficacement sur une requête dont la clause ne porterait que sur NOM, l'index (NOM,PRENOM,TELEPHONE) sera toutefois utilisable pour une requête ne contenant qu'une clause sur TELEPHONE, mais via une opération moins efficace.
Il sera donc intéressant de placer les colonnes les plus interrogées en début d'index.
Il est également plus efficace d'avoir les valeurs les plus restrictives en début d'index.

Nous avons vu dans le chapitre sur les chemins d'accès, les balayages d'index sont une possibilité d'accès. Nous allons les détailler, simplement pour les comprendre.

• Le balayage d'intervalle d'index (index range scan): L'index est parcouru pour trouver les valeurs. Les clauses d'égalité, supériorité ou infériorité déclenchent généralement ces opérations. L'index peut servir à effectuer un tri, car il est déjà trié.
  
• Le balayage unique (unique index scan): il s'applique lorsque l'index repose sur une colonne possédant une contrainte d'unicité (Unique).
  
• Le balayage à contresens (descending index scan): L'index est parcouru à contresens. Ceci est généralement fait pour un tri. Oracle se basera sur l'index à contresens pour son tri.
  
• Le balayage par saut (index skip scan): l'index est parcouru en sautant les zones où les clefs ne pourraient pas se trouver. Par exemple si la première colonne d'un index composé n'est pas mentionnée dans la requête Oracle peut choisir de quand même utiliser l'index et effectuera cette opération.
  

IV-E-a. Une grosse limite : les fonctions▲

CREATE INDEX INDVIDU_NOM on INDVIDUS(NOM) COMPUTE STATISTICS;

SELECT * from INDVIDUS where nom='MACHIN';

TABLE ACCESS BY INDEX ROWID TABLE INDVIDUS Cost: 2  Bytes: 270  Cardinality: 1
1 INDEX RANGE SCAN INDEX INDVIDU_NOM Cost: 1  Cardinality: 1

SELECT * from INDVIDUS where UPPER(nom)='MACHIN';

TABLE ACCESS FULL TABLE INDVIDUS Cost: 6  Bytes: 540  Cardinality: 2

SELECT * from INDVIDUS where nom=UPPER('MACHIN');

TABLE ACCESS BY INDEX ROWID TABLE INDVIDUS Cost: 2  Bytes: 270  Cardinality: 1
1 INDEX RANGE SCAN INDEX INDVIDU_NOM Cost: 1  Cardinality: 1

CREATE INDEX INDVIDU_U_NOM on INDVIDUS(UPPER(NOM));

IV-E-b. Trop d'index tue l'index▲

IV-E-c. Quand l'index se substitue à la table…▲

explain plan for (SELECT nom from indVidus where nom='MACHIN');
Plan: 
1 INDEX RANGE SCAN INDEX OBLADI.I_CANDIDAT_NOM Cost: 1  Bytes: 8  Cardinality: 1

SQL> explain plan for (select last_name,first_name from employees where last_name like 'T%');
Explicité.
SQL> select plan_table_output from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 3085132068

--------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |             |     5 |    75 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| EMP_NAME_IX |     5 |    75 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Et oui, quand on applique une fonction, l'index ne peut pas être utilisé. Solution : Soit indexer le champ calculé avec la fonction, soit s'arranger pour ne pas avoir à appliquer de fonction (typiquement, forcer les noms de famille en Majuscules par exemple afin d'éviter un upper lors des recherches).

Si le champ MONTANT est un NUMBER, qu'il possède un INDEX BITMAP et qu'on exécute « select * from maTable where montant='1' », on effectue une conversion de type implicite. Oracle fera un TO_CHAR sur le champ, et donc une fonction et l'index bitmap ne sera pas utilisé… En 10G Oracle arrive à éviter ce piège, mais c'est une bonne pratique que de veiller à respecter les types, car on peut vite arriver sur des cas complexes, autant prendre de suite les bonnes habitudes. Solution : Surveillez les types dans les requêtes…

Si vous avez fait de lourds changements dans une table, n'oubliez pas de relancer l'analyse afin d'avoir des stats à jour. Avec de mauvaises stats cela ne facilite pas la tâche à Oracle pour faire les bons choix… et peut même le conduire à en faire des mauvais…

Oracle peut considérer au regard de ses statistiques que l'utilisation de l'index sera plus couteuse que de parcourir complètement la table. Il a souvent raison… Si vous voulez le vérifier, rien ne vous empêche de le forcer avec un hint, vous serez fixé…
Quand Oracle estime de par les statistiques et les histogrammes qu'il devra ramener un gros pourcentage de lignes, il préfèrera parfois faire un FULL TABLE SCAN, car finalement un accès par index a un cout : au moins un I/O pour l'index, un I/O pour accéder à la ligne…
Vous feriez pareil devant un livre où vous savez que vous devez lire 90 % du contenu… vous oublierez le sommaire pour allez chercher directement…