Plan d'exécution : la ligne la plus indentée n'est pas exécutée en premier - The most indented line is not executed first

8 juin 2017

Plan d'exécution : la ligne la plus indentée n'est pas exécutée en premier - The most indented line is not executed first

Introduction
Un plan d'exécution Oracle peut rapidement devenir incompréhensible dès qu'il comporte des dizaines de lignes. On peut s'aider de Google ou de livres d'administration Oracle pour le décrypter mais, curieusement, même les DBA les plus connus ne sont pas d'accord sur l'ordre dans lequel Oracle exécute les différentes opérations de ce plan!

Pour rappel, il est fondamental de comprendre l'exécution de ce plan pour tuner un ordre SQL! Si on ne sait pas quelle est la première opération exécutée, il est impossible de mettre en place une stratégie pour diminuer dès le début le nombre d'enregistrements qu'Oracle va devoir traiter.

En résumé il existe deux théories expliquant quelles sont les opérations exécutées en premier :
- théorie 1 : ce sont les opérations qui sont les plus indentées
- théorie 2 : ce sont les opérations feuilles (opérations sans sous-opération) de Id le plus faible

Points d'attention
N/A.

Base de tests
N'importe quelle base Oracle mais avoir Toad d'installé.

Exemples
============================================================================================
Les deux théories dans la littérature
============================================================================================
Voici des extraits de deux livres écrits par des DBA réputés dans le monde Oracle décrivant ces théories .

Sam, Darl, Bill, Christian, if you want me to remove your text, please contact me with the canalblog formular and I will do it.

Première théorie : un livre écrit par Darl Kuhn disant que ce sont les opérations les plus indentées qui s'exécutent en premier, "Oracle Database 11g Performance Tuning Recipes" par Sam Alapati, Darl Kuhn, Bill Padfield. Voir pages 307 et 308.

"Once you have an explain plan to interpret, you can tell which steps are executed first because the innermost or most indented steps are executed first, and are executed from the inside out, in top-down order. In the foregoing query, we are joining the EMP and DEPT tables. Here are the steps of how the query is processed based on the execution plan:
          1. The PK_DEPT index is scanned (ID 3).
          2. All EMP table rows are scanned (ID 5).
          3. Rows are retrieved from the DEPT table based on the matching entries in the PK_DEPT index (ID 2).
          4. Resulting data from the EMP table is sorted (ID 4).
          5. Data from the EMP and DEPT tables are then joined via a MERGE JOIN (ID 1).
          6. The resulting data from the query is returned to the user (ID 0)."

Deuxième théorie : Christian Antognini dit que c'est la feuille d'id le plus faible qui s'exécute en premier puis la feuille d'id suivante etc etc dans son excellent livre "Troubleshooting Oracle performance"; par feuille on entend une opération qui n'a pas besoin d'opération fille pour ramener des données. Voir pages 228 et 229.
          SELECT *
          FROM emp, dept
          WHERE emp.deptno = dept.deptno
          AND emp.comm IS NULL
          AND dept.dname != 'SALES';

Le plan d'exécution a deux opérations filles, celles d'Id 2 et 4 et une opération la plus indentée, celle d'Id 4. Laquelle s'exécute en premier?
          ------------------------------------------------------------------
          | Id | Operation | Name | Starts | A-Rows |
          ------------------------------------------------------------------
          | 1 |   NESTED LOOPS | | 1 | 8 |
          |* 2 |      TABLE ACCESS FULL | EMP | 1 | 10 |
          |* 3 |      TABLE ACCESS BY INDEX ROWID| DEPT | 10 | 8 |
          |* 4 |            INDEX UNIQUE SCAN | DEPT_PK | 10 | 10 |
          ------------------------------------------------------------------
          2 - filter("EMP"."COMM" IS NULL)
          3 - filter("DEPT"."DNAME"<>'SALES')
          4 – access("EMP"."DEPTNO"="DEPT"."DEPTNO"

Christian construit ensuite un arbre des opérations et explique comment Oracle exécute celui-ci.

1. Operation 1 has two children (2 and 3), and among them, operation 2 is the first in ascending order. Therefore, the execution starts with operation 2 (the outer loop).

2. Operation 2 scans the table emp, applies the filter predicate "EMP"."COMM" IS NULL, and returns the data of ten rows to its parent operation (1).

3. For each row returned by operation 2, the second child of the operation NESTED LOOPS, the inner loop, is executed once. This is confirmed by comparing the column A-Rows of operation 2 with the column Starts of operations 3 and 4.

4. The inner loop is composed of two stand-alone operations. Based on the rules that apply to this type of operation, operation 4 is executed before operation 3.

5. Operation 4 scans the index dept_pk by applying the access predicate "EMP"."DEPTNO"= "DEPT"."DEPTNO". In doing so, it extracts ten rowids from the index over the ten executions and passes them to its parent operation (3).

6. Operation 3 accesses the table dept through the ten rowids passed from operation 4. For each rowid, a row is read. Then it applies the filter predicate "DEPT"."DNAME"<>'SALES'. This filter leads to the exclusion of two rows. It passes the data of the remaining eight rows to its
parent operation (1).

7. Operation 1 sends the data of eight rows to the caller.

============================================================================================
Conclusion : le test avec Toad et ses plans d'exécution personnalisés et Oracle Cloud Control
============================================================================================
Les deux théories sont pour moi incompatibles dès que nous avons des opérations feuilles qui ne sont pas les plus indentées, comme dans le deuxième exemple ci-dessus. Dans le cas contraire, les deux théories décrivent la même réalité.

Ne sachant plus à quel saint me vouer, j'ai décidé de faire mon propre test pour en avoir le coeur net! Le problème est : comment valider quelle théorie est la bonne? Je n'ai pas accès au code source d'Oracle, les traces 10053 ou 10046 ne semblent pas utiles... et puis, un jour, j'ai découvert une astuce dans le logiciel Toad. Dans cette outil, dans la fenêtre affichant le plan d'exécution, il faut faire clic droit et choisir Display mode pour afficher le plan d'exécution dans un mode plus ou moins lisible AVEC des informations complémentaires ajoutées par Toad.

Ma requête de test est on ne peut plus basique.
          SQL> SELECT table_name FROM dba_tables;

Voici le résultat avec le mode d'affichage par défaut, celui de DBMS_XPLAN; à afficher avec la police "Courier New" pour avoir une indentation plus lisible.
----------------------------------------------------------------------------------------
  | Id | Operation                   | Name     | Rows | Bytes | Cost (%CPU)| Time     |
  ----------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT            |          | 3103 |   424K|   669   (1)| 00:00:01 |
  |* 1 | HASH JOIN RIGHT OUTER      |          | 3103 |   424K|   669   (1)| 00:00:01 |
  |   2 |   TABLE ACCESS FULL         | SEG$     | 7158 | 78738 |    58   (0)| 00:00:01 |
  |* 3 |   HASH JOIN RIGHT OUTER     |          | 2662 |   335K|   611   (1)| 00:00:01 |
  |   4 |    INDEX FULL SCAN          | I_USER2 |    78 |   312 |     1   (0)| 00:00:01 |
  |* 5 |    HASH JOIN OUTER          |          | 2662 |   324K|   610   (1)| 00:00:01 |
  |* 6 |     HASH JOIN OUTER         |          | 2662 |   304K|   548   (1)| 00:00:01 |
  |* 7 |      HASH JOIN              |          | 2662 |   291K|   485   (1)| 00:00:01 |
  |   8 |       INDEX FULL SCAN       | I_USER2 |    78 |   312 |     1   (0)| 00:00:01 |
  |* 9 |       HASH JOIN             |          | 2662 |   280K|   484   (1)| 00:00:01 |
  |* 10 |        HASH JOIN            |          | 2662 |   192K|   380   (1)| 00:00:01 |
  | 11 |         MERGE JOIN CARTESIAN|          |    29 | 1276 |    11 (10)| 00:00:01 |
  |* 12 |          HASH JOIN          |          |     1 |    41 |     1 (100)| 00:00:01 |
  |* 13 |           FIXED TABLE FULL | X$KSPPI |     1 |    28 |     1 (100)| 00:00:01 |
  | 14 |           FIXED TABLE FULL | X$KSPPCV | 3350 | 43550 |     1 (100)| 00:00:01 |
  | 15 |          BUFFER SORT        |          |    29 |    87 |    11 (10)| 00:00:01 |
  | 16 |           TABLE ACCESS FULL | TS$      |    29 |    87 |    10   (0)| 00:00:01 |
  |* 17 |         TABLE ACCESS FULL   | TAB$     | 2662 | 79860 |   369   (1)| 00:00:01 |
  |* 18 |        TABLE ACCESS FULL    | OBJ$     | 22479 |   746K|   104   (1)| 00:00:01 |
  | 19 |      INDEX FAST FULL SCAN   | I_OBJ1   | 23308 |   113K|    62   (0)| 00:00:01 |
  | 20 |     INDEX FAST FULL SCAN    | I_OBJ1   | 23308 |   182K|    62   (0)| 00:00:01 |
  ----------------------------------------------------------------------------------------

Donc la question qui se pose est : quelle opération est exécutée en premier, celle d'Id 2, 13 ou 14?
Pour Toad, c'est la 2! Si vous changez le mode d'affichage du plan d'exécution, vous avez le mode Graphic donnant l'arbre ci-dessous avec, dans chaque bloc, le numéro d'ordre dans lequel Oracle exécute l'opération. Regardez sur la troisième ligne, Toad a ajouté un 1 pour le FULL TABLE ACCESS de SYS.SEG$ qui est l'opération d'Id 2 ci-dessus.

Le mode Graphic.

Le mode MS Graphic est encore plus lisible si on affiche l'image dans un autre onglet.

Vous pouvez aussi afficher le mode "Plain english", qui liste les opérations de façon séquentielle dans un tableau avec des commentaires additionnels de Toad qui sont bien intéressants!

Voici une copie écran d'un plan d'exécution obtenu sous Oracle Cloud Control, sans les noms des objets car ils ne sont pas importants. Là aussi on voit bien, avec la colonne Order, que ce n'est pas l'opération la plus indentée qui s'exécute en premier, elle n'est même qu'en quatrième position.

Si on part du principe que Toad et le Cloud Control ont raison, la conclusion est : c'est la théorie 2 qui est juste, c'est l'opération feuille (opération sans sous-opération) de Id le plus faible qui s'exécute en premier.

[EDIT 28/09/2019]
Sur l'excellent site Ask Tom, un expert confirme bien que c'est la première feuille qui s'exécute en premier :
https://asktom.oracle.com/pls/apex/asktom.search?tag=confuse-at-the-order-of-execution-plan-table
"The execution order in EXPLAIN PLAN output begins with the line that is the furthest indented to the right"
No!
The plan starts with the first leaf. This is the top-most operation with no children"

[EDIT 22/08/2020]
Un autre moyen de valider cette affirmation est d'avoir un ordre SQL avec une opération feuille d'Id le plus faible qui ne soit pas l'opération la plus indentée. Une fois cet ordre trouvé, ajoutez-y le hint MONITOR (attention, il faut le Tuning Pack). Ensuite utilisez le Cloud Control (ou la vue V$SQL_PLAN_MONITOR) pour voir l'heure de démarrage de chaque ligne du plan; vous aurez confirmation de ce que je dis.
Voici un exemple de l'écran du Cloud : aller dans le menu Performance/SQL Monitoring, cliquer sur un ordre SQL puis sur l’onglet Activity. Ensuite, dans la liste déroulante à gauche, choisir « By plan line ».