Execution Plan(1): Qu'est-ce qu'un plan d'exécution?

Ce post est une introduction à une série d'articles sur les plans d'exécution.
Il s'agit ici de définir à quoi sert un plan d'exécution.

Dans les autres postes je tenterai d'expliquer:
- comment on peut récupérer un plan d'exécution ?
- comment lire un plan d'exécution ?
- comment tuner une requête SQL non performante à partir de son plan d'exécution ?

Le plan d'exécution correspond en fait aux différentes opérations effectuées par Oracle pour exécuter une requête SQL c'est-à-dire qu'il définit la méthode de récupération des lignes désirées dans le carde d'un SELECT ou bien des lignes à modifier dans le carde d'une requête DML. Ce plan est calculé par l'optimiseur d'Oracle, qu'on appelle le CBO (COST-BASED OPTIMZER), pendant la phase de hard parsing de la requête. Il est choisi parmi plusieurs autres plans car il correspond au plan le moins couteux (COST le plus faible).

Le plan d'exécution va donc décrire la méthode d'accès aux tables (ex: Full scan de la table, accès via un index B-TREE etc.), l'ordre des tables jointes et la méthode de jointure adoptée.

Par exemple, dans le plan d'exécution suivant on voit que le CBO a choisi d'effectuer une jointure par NESTED LOOP pour joindre les tables EMPLOYEES et DEPARTMENT. On voit aussi que l'accès à la table EMPLOYEES se fait via l'index unique EMP_EMP_ID_PK et l'accès à la table DEPARTMENT se fait via l'index unique DEPT_ID_PK.

-------------------------------------------------------------
| Id  | Operation                           | Name          |
-------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |
|   1 |  NESTED LOOPS                       |               |
|   2 |   TABLE ACCESS BY INDEX ROWID       | EMPLOYEES     |
|*  3 |    INDEX UNIQUE SCAN                | EMP_EMP_ID_PK |
|   4 |   TABLE ACCESS BY INDEX ROWID       | DEPARTMENTS   |
|*  5 |    INDEX UNIQUE SCAN                | DEPT_ID_PK    |
-------------------------------------------------------------

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

3 - access("E"."EMPLOYEE_ID"=206)
5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

Le plan d'exécution pour une requête donnée peut être influencé par les éléments suivants:
- Les statistiques systèmes
- Les statistiques collectées sur les objets impliqués dans la requête
- Les paramètres d'instance liés à l'optimiseur (OPTIMZER_*, CURSOR_SHARING)
- Les hints

Exemple, si je rajoute le hint /*+ FULL(EMPLOYEES) */ à ma requête précédente le plan ne sera plus le même car je force ainsi le CBO à faire un FTS (Full table Scan) de la table EMPLOYEES:

-------------------------------------------------
| Id  | Operation                    | Name        |
----------------------------------------------------
|   0 | SELECT STATEMENT             |             |
|   1 |  NESTED LOOPS                |             |
|   2 |   NESTED LOOPS               |             |
|*  3 |    TABLE ACCESS FULL         | EMPLOYEES   |
|*  4 |    INDEX UNIQUE SCAN         | DEPT_ID_PK  |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |
----------------------------------------------------

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

3 - filter("E"."EMPLOYEE_ID"=206)
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")