Plans d'exécution sous Oracle 9i

Afin de mieux analyser les possibilités d'optimisation, il est possible de demander à Oracle d'expliciter les opérations qui ont été effectuées lors de l'exécution d'une requête et d'en estimer les coûts.

Le moteur Oracle utilise par ailleurs cette mécanique pour optimiser l'exécution des requêtes.

Syntaxe : Initialisation de l'environnement de gestion de plans d'exécution

Exécuter le script "utlxplan.sql" permettant de créer la table dans laquelle sera inscrite le plan d'exécution, puis les commandes d'activation du traçage des plans d'exécution.

utlxplan.sql [pdf]

set autotrace on
alter session set optimizer_mode="all_rows";

set autotrace on
alter session set optimizer_mode="all_rows";

Exemple : Exemple de plan d'exécution

select * from t_client c, t_ventes v where c.pk_num=v.fk_client ;
Plan d'exécution
-----------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=409 Bytes=38446)
  1    0   HASH JOIN (Cost=5 Card=409 Bytes=38446)
   2    1     TABLE ACCESS (FULL) OF 'T_VENTES' (Cost=2 Card=82 Bytes=5330)
   3    1     TABLE ACCESS (FULL) OF 'T_CLIENT' (Cost=2 Card=409 Bytes=11861)

select * from t_client c, t_ventes v where c.pk_num=v.fk_client ;

Plan d'exécution
-----------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=409 Bytes=38446)

  1    0   HASH JOIN (Cost=5 Card=409 Bytes=38446)
   2    1     TABLE ACCESS (FULL) OF 'T_VENTES' (Cost=2 Card=82 Bytes=5330)
   3    1     TABLE ACCESS (FULL) OF 'T_CLIENT' (Cost=2 Card=409 Bytes=11861)

Une fois les instructions d'activation exécutées toute question SQL se termine par un affichage du plan d'exécution. On observe ainsi toutes les opérations faites par le moteur de requête. On notera que la lecture se fait du bas vers le haut : Ainsi dans l'exemple simple ci-avant le moteur commence par effectuer un balayage complet des tables t_ventes et t_client, puis la jointure entre ces deux tables, puis il renvoit le résultat pour la question posée, le SELECT.

On notera les indicateurs suivants pour chaque ligne du plan :

Cost est un indicateur de coût de calcul de la requête à ce stade (exemple : le coût de l'opération de balayage de la table t_client est de 2). Cet indicateur est une synthèse estimée des différents coûts (accès disque, CPU, etc.), sans unité qui ne sert donc qu'à comparer différents plans entre eux.
Card est une estimation a priori du nombre de lignes remontées par la requête à ce stade (exemple : le nombre de lignes remontées par la jointure est de 409).
Bytes est le volume de données concernée par la requête à ce stade (exemple : le volume global de données pour le SELECT est de 38446 octets).

Syntaxe : Collecte de statistiques pour l'optimisation des plans d'exécution

Les clauses ANALYSE permettent à Oracle d'analyser les données afin de mieux prévoir l'utilisation des index, des vues matérialisées, etc. en fonction des coûts estimés.

ANALYZE TABLE nom_table COMPUTE STATISTICS;

ANALYZE TABLE nom_table COMPUTE STATISTICS;

Attention :

Pour que les plans soient correctement évalués a priori - et donc que Oracle fasse les bons choix d'optimisation - il est nécessaire d'exécuter les instructions ANALYSE.

Les statistiques doivent être recalculées après chaque modification de la structure de la base ainsi qu'après chaque changement significatif des données.