Introduction
Vous savez que pour avoir rapidement un plan d'exécution sous Oracle, il faut utiliser la commande EXPLAIN PLAN. Cela permet de ne pas attendre une heure que s'exécute la requête (mais attention aux bind variables, elles ne sont pas gérées). Pour avoir le vrai plan, calculé par le CBO, utilisez en revanche dbms_xplan.display_cursor ou bien dbms_xplan.display_awr, qui eux gèrent parfaitement les bind variables (mais attention au temps  d'exécution).
     
Et la commande AUTOTRACE, elle génère un vrai plan ou un plan deviné? Quel est son lien avec EXPLAIN PLAN et dbms_xplan.display_cursor ? Hé bien c'est le sujet de cet article :-)    

Qui ici ne connaît pas Tom (Thomas Kyte) du site Ask Tom? L'exemple même du DBA Oracle guru, comme disent les anglais. J'ai énormément appris sur son site, en lisant son livre aussi. Un jour je suis tombé sur ce lien https://asktom.oracle.com/pls/asktom/asktom.search?tag=difference-of-explain-plan-autotrace-and-tkprof où il dit que le plan affiché par la commande AUTOTRACE est un EXPLAIN PLAN, c'est à dire un plan deviné par Oracle mais pas le vrai plan calculé par le CBO.

Comment le prouver? Suivez-moi!



 
Points d'attention
Aucun...



Base de tests
N'importe quelle base Oracle.


Exemples

============================================================================================
Test avec les bind variables
============================================================================================
   

Dans le lien donné au début de cet article, Tom dit que EXPLAIN PLAN et AUTOTRACE sont aveugles sur les valeurs des bind variables, que des sélectivités sont mises en dur et que les bind variables sont considérées systématiquement comme des VARCHAR2. Voyons cela :-)

Test avec EXPLAIN_PLAN : bind variable pas prise en compte
     SQL> variable v_n_num NUMBER
     SQL> exec :v_n_num := 101;
     PL/SQL procedure successfully completed.
    
     SQL> EXPLAIN PLAN FOR SELECT * FROM zztest WHERE ID < :v_n_num ;    
     Explained.
     
Ma table comporte 10 000 enregistrements; le SELECT devrait utiliser un index car 100 représente 0.1% de la table. C'est le cas MAIS Oracle a choisi arbitrairement une cardinalité de 500 rows au lieu de 100! Il a forcé le nombre mais comme avec 500 on a 0.5% de la table, il utilise quand même l'index. Mais la valeur de la bind variable est fausse pour EXPLAIN PLAN.

Vous noterez aussi que pour un EXPLAIN PLAN, les bind variables sont toutes de type VARCHAR2 pour Oracle, même si elles ont été déclarées comme NUMBER. Ceci explique pourquoi nous avons un TO_NUMBER dans le prédicat d'accès.

     SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE' ,NULL, 'TYPICAL'));
     PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
     Plan hash value: 3838972641    
----------------------------------------------------------------------------------------------------

     | Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time  |
----------------------------------------------------------------------------------------------------
     |   0 | SELECT STATEMENT |           |   500 | 20500 |     3   (0)| 00:00:01 |
     |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ZZTEST |  500 | 20500 | 3  (0)| 00:00:01 |
     |*  2 |   INDEX RANGE SCAN| SYS_C0011870 |    90 |       |     2   (0)| 00:00:01 |    
----------------------------------------------------------------------------------------------------

     Predicate Information (identified by operation id):
     ---------------------------------------------------
        2 - access("ID"<TO_NUMBER(:V_N_NUM))
     14 rows selected.
     
     SQL> DELETE FROM PLAN_TABLE;
     3 rows deleted.
     
     SQL> commit;
     Commit complete.

Test avec AUTOTRACE : bind variable pas prise en compte     

Tiens, avec AUTOTRACE on a le même nombre de rows que avec EXPLAIN PLAN. Est-ce la preuve que AUTOTRACE fait un EXPLAIN PLAN? Hum, on dirait bien :-) Et en plus, comme pour EXPLAIN PLAN, nous avons un TO_NUMBER.
     SQL> set autotrace on
     SQL> exec :v_n_num := 101;
     PL/SQL procedure successfully completed.
     
     SQL> SELECT * FROM zztest WHERE ID < :v_n_num ;        
     Execution Plan
     ----------------------------------------------------------
     Plan hash value: 3838972641    
----------------------------------------------------------------------------------------------------

     | Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time    |
----------------------------------------------------------------------------------------------------
     |   0 | SELECT STATEMENT |           |   500 | 20500 | 3   (0)| 00:00:01 |
     |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ZZTEST | 500 | 20500 |  3 (0)| 00:00:01 |
     |*  2 |   INDEX RANGE SCAN | SYS_C0011870 |    90 |  |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
     Predicate Information (identified by operation id):
---------------------------------------------------
        2 - access("ID"<TO_NUMBER(:V_N_NUM))

Test avec DISPLAY_CURSOR : bind variable bien prise en compte         

Voyons maintenant comment Oracle gère les bind variables quand l'ordre est exécuté sans faire un AUTOTRACE.
Ah, c'est mieux, on a la bonne valeur! Et on a pas de TO_NUMBER dans le prédicat d'accès, preuve que là, Oracle gère parfaitement bien ce type de variable.!
     SQL> set autotrace off

     SQL> exec :v_n_num := 101;
     PL/SQL procedure successfully completed.
     SQL> SELECT * FROM zztest WHERE ID < :v_n_num ;        
     SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL ,NULL, 'TYPICAL'));
     PLAN_TABLE_OUTPUT
     ---------------------------------------------------------------------------
     SQL_ID    d6f1s65xndvcv, child number 0
     -------------------------------------
      SELECT * FROM zztest WHERE ID < :v_n_num
     Plan hash value: 3838972641    
----------------------------------------------------------------------------------------------------

     | Id  | Operation       | Name       | Rows  | Bytes | Cost (%CPU)| Time  |
---------------------------------------------------------------------------------------------------
     |   0 | SELECT STATEMENT|           |       |       |     3 (100)|       |
     |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ZZTEST| 100 |  4100 | 3(0)| 00:00:01 |
     |*  2 |   INDEX RANGE SCAN| SYS_C0011870 |   100 |  |     2   (0)| 00:00:01 |    
----------------------------------------------------------------------------------------------------

     Predicate Information (identified by operation id):
     ---------------------------------------------------
        2 - access("ID"<:V_N_NUM)
     19 rows selected.
    
Test avec EXPLAIN_PLAN et bind variable qui doit changer le plan d'exécution : bind variable pas prise en compte
On voit que la valeur de la bind variable n'est pas prise en compte lors d'un EXPLAIN PLAN ou d'un AUTOTRACE. Néanmoins le plan est le même que lors de l'exécution réelle de la requête. Testons maintenant avec une valeur de bind variable qui change le plan d'exécution entre ces techniques : ramenons 30% des données au lieu de 1% et voyons comment Oracle réagit.
     SQL> ALTER SYSTEM FLUSH shared_pool;
     SQL> exec :v_n_num := 3001;
     PL/SQL procedure successfully completed.
     SQL> EXPLAIN PLAN FOR SELECT * FROM zztest WHERE ID < :v_n_num ;

Ahah, erreur : 500 lignes au lieu de 3000! Les bind variables ne sont pas gérées par EXPLAIN PLAN, le plan n'a pas changé.    
     SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE' ,NULL, 'TYPICAL'));

     PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
     Plan hash value: 3838972641    
----------------------------------------------------------------------------------------------------

     | Id  | Operation       | Name       | Rows  | Bytes | Cost (%CPU)| Time|
----------------------------------------------------------------------------------------------------
     |   0 | SELECT STATEMENT|           |   500 | 20500 |     3   (0)| 00:00:01 |
     |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ZZTEST| 500 | 20500 |  3 (0)| 00:00:01 |
     |*  2 |   INDEX RANGE SCAN| SYS_C0011870 |    90 |  |     2   (0)| 00:00:01 |    
----------------------------------------------------------------------------------------------------

     Predicate Information (identified by operation id):
     ---------------------------------------------------
        2 - access("ID"<TO_NUMBER(:V_N_NUM))
     14 rows selected.

Test avec AUTOTRACE et bind variable qui doit changer le plan d'exécution : bind variable pas prise en compte    

Avec AUTOTRACE, même résultat qu'avec EXPLAIN PLAN! ==> la conclusion est faite, AUTOTRACE utilise bien EXPLAIN PLAN pour afficher le plan d'exécution.
     SQL> exec :v_n_num := 3001;
     PL/SQL procedure successfully completed.
     
     SQL> SELECT * FROM zztest WHERE ID < :v_n_num ;
     Execution Plan
     ----------------------------------------------------------
     Plan hash value: 3838972641
----------------------------------------------------------------------------------------------------
     | Id  | Operation       | Name       | Rows  | Bytes | Cost (%CPU)| Time   |    
----------------------------------------------------------------------------------------------------

     |   0 | SELECT STATEMENT|           |   500 | 20500 |     3   (0)| 00:00:01 |
     |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ZZTEST| 500 | 20500 | 3 (0)| 00:00:01 |
     |*  2 |   INDEX RANGE SCAN| SYS_C0011870 |    90 |  |     2   (0)| 00:00:01 |    
----------------------------------------------------------------------------------------------------

     Predicate Information (identified by operation id):
     ---------------------------------------------------
        2 - access("ID"<TO_NUMBER(:V_N_NUM))
    
Test avec DISPLAY_CURSOR et bind variable qui doit changer le plan d'exécution : bind variable prise en compte, on a un nouveau plan    
BINGO : cette fois on a bien 3000 rows dans le plan, donc la vraie valeur de la bind variable et, SURTOUT, le plan a changé, on est passé de l'utilisation d'un index à un FULL TABLE SCAN!
     SQL> exec :v_n_num := 3001;

     PL/SQL procedure successfully completed.
     SQL> SELECT * FROM zztest WHERE ID < :v_n_num ;
     ...
     
     SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL ,NULL, 'TYPICAL'));
     PLAN_TABLE_OUTPUT
     -------------------------------------------------------------------------------
     SQL_ID    15692wxt92ayb, child number 0
     -------------------------------------
     SELECT * FROM zztest WHERE ID < :v_n_num
     Plan hash value: 3582063246
     ----------------------------------------------------------------------------
     | Id  | Operation      | Name   | Rows  | Bytes | Cost (%CPU)| Time       |
     ----------------------------------------------------------------------------
     |   0 | SELECT STATEMENT  |       |       |       |    19 (100)|       |
     |*  1 |  TABLE ACCESS FULL| ZZTEST | 3000 |   120K|    19   (0)| 00:00:01 |
     ----------------------------------------------------------------------------
     Predicate Information (identified by operation id):
     ---------------------------------------------------
        1 - filter("ID"<:V_N_NUM)
     
     18 rows selected.


============================================================================================
Test avec la trace 10046
============================================================================================
   

Bon, on a bien avancé mais voici le test définitif pour moi, la trace 10046! Celle-ci nous montre ce que Oracle exécute en mémoire comme code.
On prépare l'environnement pour la trace.
     SQL> ALTER SESSION SET timed_statistics = TRUE;
     SQL> ALTER SESSION SET max_dump_file_size = unlimited;
     SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER = 'zztest_auto';              
     SQL> ALTER SESSION SET events '10046 trace name context forever, level 4';

On active l'AUTOTRACE et on exécute un ordre SQL.
     SQL> set autotrace on
     SQL> select count(*) from employees;
       COUNT(*)
     ----------
            107

     Execution Plan
     ----------------------------------------------------------
     Plan hash value: 3580537945

     -------------------------------------------------------------------------
     | Id  | Operation     | Name     | Rows    | Cost (%CPU)| Time    |
     -------------------------------------------------------------------------
     |   0 | SELECT STATEMENT |        |     1 |     1   (0)| 00:00:01 |
     |   1 |  SORT AGGREGATE  |        |     1 |         |        |
     |   2 |   INDEX FULL SCAN| EMP_EMAIL_UK |   107 |     1   (0)| 00:00:01 |
     -------------------------------------------------------------------------

     Statistics
     ----------------------------------------------------------
         188  recursive calls
          0  db block gets
         392  consistent gets
          1  physical reads
           0  redo size
         543  bytes sent via SQL*Net to client
         607  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          68  sorts (memory)
          0  sorts (disk)
           1  rows processed

Bien penser à désactiver l'AUTOTRACE et, surtout, la trace car on a mis auparavant une taille infinie pour la taille des fichiers trace.
     SQL> set autotrace off
     SQL> ALTER SESSION SET events '10046 trace name context off';

On transforme le fichier trace brute en format tkprof, plus condensé et, surtout, plus lisible.
     [oracle@vbgeneric trace]$ cd /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/
     [oracle@vbgeneric trace]$ tkprof orcl12c_ora_5538_zztest_auto.trc orcl12c_ora_5538_zztest_auto.txt

Allez, le grand saut dans les entrailles d'Oracle!
On fait une recherche sur EXPLAIN PLAN (en majuscules) : bingo!!!!! AUTOTRACE utilise bien la commande EXPLAIN PLAN.
     [oracle@vbgeneric trace]$ vi orcl12c_ora_5538_zztest_auto.txt
     SQL ID: 1jawf7kjc7xv8 Plan Hash: 3580537945
     EXPLAIN PLAN SET STATEMENT_ID='PLUS3320004' FOR select count(*) from employees



 

Conclusion
Conformément à ce que disait Thomas Kyte, la commande AUTOTRACE utilise, pour calculer le plan d'exécution, la commande EXPLAIN PLAN. La conclusion est que seule la commande DBMS_XPLAN.DISPLAY_CURSOR permet d'avoir le vrai plan d'exécution, celui calculé et appliqué par le CBO lors du traitement des données. Petite précision : il existe aussi dans le package DBMS_XPLAN les procédures DISPLAY_BASELINE et DISPLAY_AWR mais on sort du cadre de cet article.

Et puis, petite remarque, AUTOTRACE prend plusieurs paramètres; si on veut afficher uniquement le plan sans l'exécuter, il faut saisir 
     SQL> set AUTOTRACE TRACEONLY EXPLAIN;
Et oui, EXPLAIN... ce qui montre une fois de plus que AUTOTRACE utilise la commande EXPLAIN PLAN :-)