Introduction

Certaines requêtes complexes (nombreuses jointures et restrictions, agrégats …) peuvent mettre plusieurs heures pour s'exécuter. Dans ce cas l'optimiseur d'Oracle va chercher à mettre en œuvre le meilleur plan d'exécution pour minimiser les ressources à mobiliser notamment via la méthode CBO (Cost-Based Optimizer).

Le CBO

L'optimiseur génère plusieurs plans d'exécution basés sur les E/S, le temps CPU, les statistiques, les ressources mémoire et choisit le "meilleur".
Il peut être configurer selon que les requêtes soient dans des programmes batch ou interactifs.

Programmes batch

ALTER SESSION SET OPTIMIZER_MODE=ALL_ROWS;

Dans ce cas TOUTES les lignes seront traitées via des accès FULL-SCAN aux tables.

Programmes interactifs

ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS_N; 

Avec n pouvant varier selon les besoins (1, 10, 100 …).
Permet de minimiser les ressources en ne traitant que les n premières lignes.

Les statistiques

Le CBO s'appuie en partie sur les statistiques qui depuis la version 10g sont active automatiquement. Il est possible de les activer manuellement.

Sur toute la base

EXEC DBMS_STATS.gather_database_stats; 

Sur 20% de la base

EXEC DBMS_STATS.gather_database_stats(estimate_percent => 20); 

Sur le schéma d'un utilisateur

EXEC DBMS_STATS.gather_schema_stats('dupond'); 

Sur 20% du schéma d'un utilisateur

EXEC DBMS_STATS.gather_schema_stats('dupond', estimate_percent => 20); 

Sur une table d'un utilisateur

EXEC DBMS_STATS.gather_table_stats('dupond', 'client'); 

Sur 20% d'une table d'un utilisateur

EXEC DBMS_STATS.gather_table_stats('dupond', 'client ', estimate_percent => 20); 

Sur un index

EXEC DBMS_STATS.gather_index_stats('dupond', 'pk_client'); 

Sur 20% d'un index

EXEC DBMS_STATS.gather_index_stats('dupond', 'pk_client', estimate_percent => 20);

Actions d'un optimiseur

Les actions successives d'un optimiseur pour exécuter une requête SQL sont :

  • Evaluation des expressions et conditions contenant des constantes ;
  • Transformation de l'ordre SQL (transformation éventuelle des sous-requêtes, des vues en jointures) ;
  • Choix de l'approche d'optimisation (mode de l'optimiseur, hints, statistiques) ;
  • Choix des chemins d'accès ;
  • Choix de l'ordre des jointures (si la requête joint plus de 2 tables) et choix des opérations de jointure.

Les HINTS

L'optimiseur peut utiliser les suggestions de l'utilisateur via les HINTS qui se placent sous forme de commentaires dans la requête, exemple :

SELECT /* + index(client idx_rs_client) */ * FROM client ORDER BY rs_client DESC;

Ici on force l'usage de l'index sur la raison sociale du client pour optimiser les temps de réalisation du tri descendant.

Note : les HINTS sont à manier avec précaution car ils sont écrits en dur dans les requêtes alors que le contexte peut évoluer.

Les chemins d'accès

Les plans d'exécutions sont constitués par les différents chemins d'accès (plusieurs façons d'exécuter une même requête) qui utilisent les méthodes d'accès suivantes :

  • Parcours séquentiel (FULL SCAN) : très couteux car toute la table est parcourue.
  • Par adresse (BY INDEX ROWID SCAN) : rapide mais limité.
  • Parcours de regroupement (CLUSTER SCAN) : lorsque les tables sont réparties en cluster.
  • Recherche par hashage (HASH SCAN) : utilise une table de hachage temporaire.
  • Parcours d'index (UNIQUE SCAN, RANGE SCAN, FULL SCAN) : utilisation systématique des index présents.

Traitement des jointures 

Boucles imbriquées (NESTED LOOP) : pour chaque ligne d'une table T1, recherche directe dans la table T2 ou via son index, puis accès son occurrence.

Tri et Fusion (SORT and MERGE) : cas de jointures non basées sur équijointures.

Hachage (HASH JOIN) : Une des tables ou sources de données est choisie et utilisée pour construire une table de hashage sur la clé de jointure. Chaque valeur est comparée avec les valeurs HASH de l'autre table ou source.

Obtention des plans d'exécution

Il faut disposer des droits SELECT_CATALOG_ROLE et SELECT ANY DICTIONARY.
Activer SQL_TRACE dans SqlDeveloper ou SET AUTOTRACE ON dans Sql*Plus.

Exemple pour la requête suivante :

Tous les clients (N° client, RS Client) ayant commandé l’article ayant le plus fort chiffre d’affaires (N° article, Désignation) de tous les articles commandés.

Son plan d'exécution :