Blog d'un DBA sur le SGBD Oracle et SQL

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. All other trademarks are the property of their respectives owners.

Oracle Corporation does not make any representation or warranties as to the accuracy, adequacy, or completeness of any information contained in this Work, and is not responsible for any errors or omissions.

Oracle Corporation cannot be held responsible for the following:

• the potential errors or inaccuracies of this book
• any actions by readers of this book that could negatively impact their Oracle databases, including financial loss, data loss, data corruption as a result of these actions

Introduction
Je suppose que vous connaissez tous le mantra Oracle suivant : "Readers don't block writers and writers don't block readers". Dans certains cas, les writers peuvent bloquer d'autres writers, notamment s'ils veulent modifier les mêmes données. Vous savez aussi que les readers ne bloquent pas les readers. Même si un reader peut ralentir d'autres readers avec un SELECT consommant toutes les ressources de la base, dans l'absolu, ce reader ne bloque pas les autres readers, il les ralentit, c'est tout.

Eh bien, nous allons justement voir un cas où un reader bloque vraiment un autre reader : c'est quand les deux utilisent un SELECT FOR UPDATE sur les mêmes enregistrements.

Points d'attention
N/A.

Base de tests
Une base Oracle 19 multi-tenants.

Exemples

============================================================================================
Les tests
============================================================================================ 
Nous ouvrons deux sessions sqlcl (avec la commande sqlui) dans deux terminaux.

Terminal 1 : session 370957
     [oracle@localhost ~]$ . oraenv
     ORACLE_SID = [orclcdb] ?

     [oracle@localhost ~]$ sqlui
     SQLcl: Release 19.1 Production on Wed Aug 31 10:57:26 2022
     ...

     SQL> set sqlformat ansiconsole
     SQL> set pages 5000

     SQL> select sys_context('userenv','sessionid') Session_ID from dual;
     SESSION_ID
     _____________
     370957

Le SELECT FOR UPDATE pose un lock sur tous les enregistrements ramenés par le SELECT; ici c'est toute la table countries. Et tant que ce lock n'aura pas été levé, aucun UPDATE par exemple ne pourra avoir lieu sur ces enregistrements, car ils sont protégés par le lock, mais également aucun SELECT FOR UPDATE sur tout ou une partie des enregistrements verrouillés.
Remarque, la commande pour afficher la date est set time on et non pas set timing on.
     SQL> set time on

     11:08:38 SQL>
     11:08:40 SQL> SELECT * FROM countries FOR UPDATE;
     COUNTRY_ID COUNTRY_NAME REGION_ID
     _____________ ___________________________ ____________
     AR Argentina 2
     AU Australia 3
     BE Belgium 1
     BR Brazil 2
     CA Canada 2
     CH Switzerland 1
     CN China 3
     DE Germany 1
     DK Denmark 1
     EG Egypt 4
     FR France 1
     HK HongKong 3
     IL Israel 4
     IN India 3
     IT Italy 1
     JP Japan 3
     KW Kuwait 4
     MX Mexico 2
     NG Nigeria 4
     NL Netherlands 1
     SG Singapore 3
     UK United Kingdom 1
     US United States of America 2
     ZM Zambia 4
     ZW Zimbabwe 4
     25 rows selected.

Pendant ce temps, dans le terminal 2, un SELECT FOR UPDATE est bloqué. Je lève à 11:09:29, en faisant un COMMIT cinquante secondes après le SELECT, le lock posé par le SELECT FOR UPDATE.
     11:09:02 SQL>
     11:09:28 SQL>
     11:09:29 SQL> commit;
     Commit complete.


Terminal 2 : session 370958
     [oracle@localhost oracle]$ . oraenv
     ORACLE_SID = [orclcdb] ?

     [oracle@localhost oracle]$ sqlui
     SQLcl: Release 19.1 Production on Wed Aug 31 10:59:14 2022
     ...

     SQL> set sqlformat ansiconsole
     SQL> set pages 5000

     SQL> select sys_context('userenv','sessionid') Session_ID from dual;
     SESSION_ID
     _____________
     370958

     SQL> set time on

J'exécute le même SELECT que dans le terminal 1, mais à 11:08:43, soit après le SELECT de la session 1. Oracle me rend la main à 11:09:31.
     11:08:10 SQL>
     11:08:43 SQL> SELECT * FROM countries FOR UPDATE;
     COUNTRY_ID COUNTRY_NAME REGION_ID
     _____________ ___________________________ ____________
     AR Argentina 2
     ...
     ZW Zimbabwe 4
     25 rows selected.
     11:09:31 SQL>


============================================================================================
Résumé
============================================================================================
En résumé, voilà ce qui s'est passé  :
11:08:40 : lancement du SELECT terminal 1 et pose d'un verrou sur la table COUNTRIES
11:08:43 : lancement du SELECT terminal 2 et attente pour poser un verrou sur la table COUNTRIES que celui du terminal 1 soit levé
11:08:43 à 11:09:29 : attente dans le terminal 2 de la levée du lock sur la table COUNTRIES de la session 1
11:09:29 : COMMIT dans le terminal 1 et levée du lock sur la table COUNTRIES
11:09:31 : pose automatique du lock dans le terminal 2 sur la table COUNTRIES et exécution du SELECT (temps d'exécution 2 secondes : de 11:09:29 à 11:09:31)


Voilà, nous avons prouvé que sous Oracle, un reader peut bloquer un autre reader.

Introduction
Autres articles sur DBMS_XPLAN.DISPLAY_CURSOR

• Partie 1 : DBMS_XPLAN.DISPLAY_CURSOR 01 : les fondamentaux - DBMS_XPLAN.DISPLAY_CURSOR 01 : the fundamentals
• Partie 2 : DBMS_XPLAN.DISPLAY_CURSOR 02 : appels simples et complexes - DBMS_XPLAN.DISPLAY_CURSOR 02: simple and complex calls
• Partie 3 : DBMS_XPLAN.DISPLAY_CURSOR 03 : ajouter des champs, les ordres DM et DDL traités - add fields, DML and DDL orders processed

Dans cet article, nous traiterons des pièges et points d'attention concernant DBMS_XPLAN.DISPLAY_CURSOR :

• ne pas mettre serveroutput on
  
• par défaut, c'est le plan d'exécution du dernier ordre SQL qui est affiché
• par défaut, seul le plan d'exécution de la première exécution est affiché
• paramètres all et gather_plan_statistics : les colonnes a-rows et e-rows sont absentes
• SELECT avec des bind variables : mauvaises valeurs affichées
• ALL et ALLSTATS ne sont pas identiques
• Format LAST et plusieurs child cursors

Pour des raisons de lisibilité, je n'afficherai que les infos importantes du plan d'exécution.

Points d'attention
N/A.

Base de tests
Une base Oracle 19 multi-tenants.

Exemples

============================================================================================
Ne pas mettre serveroutput on
============================================================================================ 
Avec DBMS_XPLAN, il ne faut pas mettre serveroutput à on.
     SQL> set serveroutput on;

     SQL> select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME, E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID = D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = 50 OR D.DEPARTMENT_ID = 80) order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME;

     DEPARTMENT_NAME DEPARTMENT_ID FIRST_NAME LAST_NAME EMPLOYEE_ID
     ------------------------------ ------------- -------------------- ------
     ...
     79 rows selected.
     SQL_ID: 9420hwvnq6jsj

Ah, gros problème... DBMS_XPLAN.DISPLAY_CURSOR cherche le sql_id 9babjv8yq8ru3 alors que le SELECT exécuté a le sql_id 9420hwvnq6jsj. Et puis c'est quoi cet appel à DBMS_OUTPUT.GET_LINES?
     SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
     PLAN_TABLE_OUTPUT
     ------------------------------------------------------------------------
     SQL_ID 9babjv8yq8ru3, child number 0

     BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;

     NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
     Please verify value of SQL_ID and CHILD_NUMBER;
     It could also be that the plan is no longer in cursor cache (check v$sql_plan)

     8 rows selected.

Que trouve-t-on comme explication sur Internet ? "Well, if you have "SERVEROUTPUT" set to "on" in SQL Plus, then when your SQL statement has completed, SQL Plus makes an additional call to the database to pick up any data in the DBMS_OUTPUT buffer." En français : un appel à la base (un SELECT ?) est fait après votre SELECT quand on a serveroutput on sous SQL*Plus. Cet appel a le sql_id 9babjv8yq8ru3 MAIS il n'aurait pas de plan d'exécution associé, d'où l'erreur affichée. On peut avoir des ordres SQL sans plan d'exécution? Oui, voir le lien au début de cet article sur la partie 3.

Si on met serveroutput off, tout est OK.
     SQL> set serveroutput off;
     SQL> select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME, E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID = D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = 50 OR D.DEPARTMENT_ID = 80) order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME;

     DEPARTMENT_NAME DEPARTMENT_ID FIRST_NAME LAST_NAME EMPLOYEE_ID
     ------------------------------ ------------- -------------------- ------
     ...
     79 rows selected.

     SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
     PLAN_TABLE_OUTPUT
     ------------------------------------------------------------------------
     SQL_ID 9420hwvnq6jsj, child number 1
     -------------------------------------
     select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME,
     E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID =
     D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = 50 OR D.DEPARTMENT_ID = 80)
     order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME

     Plan hash value: 2480766633
     ...

============================================================================================
Par défaut, c'est le plan d'exécution du dernier ordre SQL qui est affiché
============================================================================================
Précision : c'est le plan d'exécution du dernier ordre SQL qui a généré un plan d'exécution car des ordres SQL comme CREATE, ALTER, DROP, ne génèrent pas de plan. Attention, on est dans le cas où j'appelle DBMS_XPLAN.DISPLAY_CURSOR sans paramètre, c'est à dire avec les valeurs par défaut.

Exemple de deux ordres SQL : mon SELECT de test et un SELECT sur SYSDATE. C'est à chaque fois le dernier SELECT qui est affiché.
     SQL> select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME, E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID = D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = 50 OR D.DEPARTMENT_ID = 80) order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME;
     DEPARTMENT_NAME DEPARTMENT_ID FIRST_NAME LAST_NAME EMPLOYEE_ID
     ------------------------------ ------------- -------------------- ------
     ...
     79 rows selected.

     SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);
     PLAN_TABLE_OUTPUT
     ------------------------------------------------------------------------
     SQL_ID 9420hwvnq6jsj, child number 0
     -------------------------------------
     select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME,
     E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID =
     D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = 50 OR D.DEPARTMENT_ID = 80)
     order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME

     Plan hash value: 2480766633
     ...

Autre SELECT.
     SQL> select sysdate from dual;
     SYSDATE
     ---------
     23-JUN-22
     1 row selected.
     SQL_ID: 7h35uxf5uhmm1


Le plan d'exécution est bien celui du dernier SELECT.
     SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);
     PLAN_TABLE_OUTPUT
     ------------------------------------------------------------------------
     SQL_ID 7h35uxf5uhmm1, child number 0
     -------------------------------------
     select sysdate from dual

     Plan hash value: 1388734953
     -----------------------------------------------------------------
     | Id | Operation | Name | Rows | Cost (%CPU)| Time |
     -----------------------------------------------------------------
     | 0 | SELECT STATEMENT | | | 2 (100)| |
     | 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
     -----------------------------------------------------------------
     13 rows selected.


============================================================================================
Par défaut, seul le plan d'exécution de la première exécution est affiché
============================================================================================
Le deuxième paramètre de DBMS_XPLAN.DISPLAY_CURSOR est le "Child number of the cursor to display. If not supplied, the execution plan of the child_number=0 cursor matching the supplied sql_id parameter are displayed. The child_number can be specified only if sql_id is specified."

J'exécute le SELECT une fois.
     SQL> select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME, E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID = D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = 50 OR D.DEPARTMENT_ID = 80) order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME;

     DEPARTMENT_NAME DEPARTMENT_ID FIRST_NAME LAST_NAME EMPLOYEE_ID
     ------------------------------ ------------- -------------------- ------
     ...
     SQL_ID: 9420hwvnq6jsj

Je mets NULL comme deuxième paramètre pour afficher tous les curseurs; pour le moment, on en a un seul en mémoire.
     SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL, 'ADVANCED'));
     PLAN_TABLE_OUTPUT
     ------------------------------------------------------------------------
     SQL_ID 9420hwvnq6jsj, child number 0
     -------------------------------------
     ...

Je change le mode du CBO en FIRST_ROWS; auparavant on était en mode ALL_ROWS.
     SQL> alter session set optimizer_mode = 'FIRST_ROWS';
     Session altered.

On exécute à nouveau la requête.
     SQL> select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME, E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID = D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = 50 OR D.DEPARTMENT_ID = 80) order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME;
     DEPARTMENT_NAME DEPARTMENT_ID FIRST_NAME LAST_NAME EMPLOYEE_ID
     ------------------------------ ------------- -------------------- ------
     ...
     79 rows selected.

Liste des child cursor en base : il y en a 2.
     SQL> select CHILD_NUMBER from V$SQL_SHARED_CURSOR where SQL_ID = '9420hwvnq6jsj';
     CHILD_NUMBER
     ------------
     0
     1
     2 rows selected.

Si on utilise NULL : les deux curseurs sont affichés.
     SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('9420hwvnq6jsj',NULL,' ADVANCED '));
     PLAN_TABLE_OUTPUT
     ------------------------------------------------------------------------
     SQL_ID 9420hwvnq6jsj, child number 0
     -------------------------------------
     ...
     Outline Data
     -------------
     /*+
     ...
     ALL_ROWS
     ...
     */
     ...

     SQL_ID 9420hwvnq6jsj, child number 1
     -------------------------------------
     ...
     Outline Data
     -------------
     /*+
     ...
     FIRST_ROWS
     ...
     */

Si on ne met aucun paramètre pour le child cursor, c'est le premier qui est affiché, ce qui n'est pas "normal", i lserait plus logique d'avoir le dernier. 
     SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('9420hwvnq6jsj'));
     PLAN_TABLE_OUTPUT
     ------------------------------------------------------------------------
     SQL_ID 9420hwvnq6jsj, child number 0
     -------------------------------------
     ...


============================================================================================
Paramètre all et hint gather_plan_statistics : les colonnes a-rows et e-rows sont absentes
============================================================================================
Dans la doc, on lit que le format ALL signifie que Oracle va afficher le maximum de champs après le plan d'exécution proprement dit mais pas que cela concerne les colonnes du plan : "ALL: Maximum user level. Includes information displayed with the TYPICAL level with additional information (PROJECTION, ALIAS and information about REMOTE SQL if the operation is distributed)."
     SQL> select /*+ GATHER_PLAN_STATISTICS */ D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME, E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID = D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = 50 OR D.DEPARTMENT_ID = 80) order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME;

     DEPARTMENT_NAME DEPARTMENT_ID FIRST_NAME LAST_NAME EMPLOYEE_ID
     ------------------------------ ------------- -------------------- ------
     ...
     79 rows selected.
     SQL_ID: 16w1adqsd8hnb

ATTENTION : le sql_id a changé car le texte du SELECT n'est plus le même avec le hint.
     SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('16w1adqsd8hnb',0,'ALL'));
     PLAN_TABLE_OUTPUT
     ------------------------------------------------------------------------
     SQL_ID 16w1adqsd8hnb, child number 0
     -------------------------------------
     select /*+ GATHER_PLAN_STATISTICS */ D.DEPARTMENT_NAME,
     E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME, E.EMPLOYEE_ID from
     employees E, departments D where E.DEPARTMENT_ID = D.DEPARTMENT_ID AND
     (D.DEPARTMENT_ID = 50 OR D.DEPARTMENT_ID = 80) order by
     D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME

     Plan hash value: 2480766633

     ------------------------------------------------------------------------
     | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
     ------------------------------------------------------------------------
     ...

Si on met ALLSTATS : les colonnes A-ROWS et E-ROWS sont bien là... le mot clé ALL est donc trompeur.
     SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('16w1adqsd8hnb',0,'ALLSTATS'));
     PLAN_TABLE_OUTPUT
     ------------------------------------------------------------------------
     SQL_ID 16w1adqsd8hnb, child number 0
     -------------------------------------
     ...
     Plan hash value: 2480766633

     ------------------------------------------------------------------------
     | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | O/1/M |
     ------------------------------------------------------------------------
     ...

============================================================================================
SELECT avec des bind variables : mauvaises valeurs affichées
============================================================================================
Dans le paramètre FORMAT, on peut utiliser le paramètre PEEKED_BINDS pour afficher les valeurs des bind variables.
Attention: le sql_id change car le texte du SELECT change avec l'usage des bind variables.
     SQL> set feedback on sql_id

     SQL> VARIABLE DPT_ID01 NUMBER;
     SQL> VARIABLE DPT_ID02 NUMBER;
     SQL> EXEC :DPT_ID01 := 50;
     SQL> EXEC :DPT_ID02 := 80;

     SQL> select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME, E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID = D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = :DPT_ID01 OR D.DEPARTMENT_ID = :DPT_ID02) order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME;
     DEPARTMENT_NAME DEPARTMENT_ID FIRST_NAME LAST_NAME EMPLOYEE_ID
     ------------------------------ ------------- -------------------- ------
     ...
     79 rows selected.
     SQL_ID: 43gugwnrgt1mf

Pour ce premier test, les valeurs des bind variables affichées est bonne.
     SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR('43gugwnrgt1mf', NULL, FORMAT => 'TYPICAL +PEEKED_BINDS'));
     PLAN_TABLE_OUTPUT
     ------------------------------------------------------------------------
     SQL_ID 43gugwnrgt1mf, child number 0
     -------------------------------------     
     ...
     Peeked Binds (identified by position):
     --------------------------------------
     1 - :DPT_ID01 (NUMBER): 50
     2 - :DPT_ID02 (NUMBER): 80

Maintenant je change la valeur des bind variables; le SELECT ne renvoi plus que trois lignes.
     SQL> EXEC :DPT_ID01 := 90;
     SQL> EXEC :DPT_ID02 := 120;
     SQL> select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME, E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID = D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = :DPT_ID01 OR D.DEPARTMENT_ID = :DPT_ID02) order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME;

     DEPARTMENT_NAME DEPARTMENT_ID FIRST_NAME LAST_NAME EMPLOYEE_ID
     ------------------------------ ------------- -------------------- ------
     Executive 90 Lex De Haan 102
     Executive 90 Neena Kochhar 101
     Executive 90 Steven King 100

     3 rows selected.
     SQL_ID: 43gugwnrgt1mf

Ah, problème... les valeurs des bind variables affichées sont encore les mêmes...
     SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR('43gugwnrgt1mf', NULL, FORMAT => 'TYPICAL +PEEKED_BINDS'));
     PLAN_TABLE_OUTPUT
     ------------------------------------------------------------------------
     SQL_ID 43gugwnrgt1mf, child number 0
     -------------------------------------
     ...
     Peeked Binds (identified by position):
     --------------------------------------
     1 - :DPT_ID01 (NUMBER): 50
     2 - :DPT_ID02 (NUMBER): 80

Hum, le stockage de ces variables n'est pas bon... d'une moins à première vue.
     SQL> select NAME, VALUE_STRING from V$SQL_BIND_CAPTURE where SQL_ID = '43gugwnrgt1mf';
     NAME VALUE_STRING
     --------------------------------------------
     :DPT_ID01 50
     :DPT_ID02 80

     2 rows selected.

Merci à Mohamed Houri pour son explication.
https://www.developpez.net/forums/d2133272/bases-donnees/oracle/administration/select-bind-variables-plan-d-execution-affiche-errone/#post11849088

Plus d'infos avec AskTom.
https://asktom.oracle.com/pls/apex/asktom.search?tag=select-with-bind-variables-wrong-execution-plan-and-wrong-values-in-vsql-bind-capture
"This is expected behaviour; v$sql_bind_capture only stores samples, not every bind value used. From the docs: Bind values are captured when SQL statements are executed. To limit the overhead, binds are captured at most every 15 minutes for a given cursor. The PEEKED_BINDS option for DBMS_xplan shows the bind values the optimizer used to generate the plan. It only does this on the initial parse (when you first run the query) or the optimizer decides to reparse the statement (e.g. because adaptive cursor sharing kicked in, stats were gathered recently, ...)"


============================================================================================
ALL et ALLSTATS ne sont pas identiques
============================================================================================
La doc Oracle fait une distinction entre ALL et ALLSTATS pour le paramètre FORMAT

• ALL: Maximum user level. Includes information displayed with the TYPICAL level with additional information (PROJECTION, ALIAS and information about REMOTE SQL if the operation is distributed).
• ALLSTATS : A shortcut for 'IOSTATS MEMSTATS'

Pour ces deux paramètres, les plans affichés sont différents : les noms des colonnes diffèrent et le nombre de lignes diffère aussi : 37 contre 60 donc on a pas les mêmes infos.
     SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR('9420hwvnq6jsj', 0, FORMAT => 'ALLSTATS'));
     PLAN_TABLE_OUTPUT
     ------------------------------------------------------------------------
     SQL_ID 9420hwvnq6jsj, child number 0
     -------------------------------------
     select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME,
     E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID =
     D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = 50 OR D.DEPARTMENT_ID = 80)
     order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME

     Plan hash value: 2480766633

     ------------------------------------------------------------------------
     | Id | Operation | Name | E-Rows | OMem | 1Mem | O/1/M |
     ------------------------------------------------------------------------
     ...
     37 rows selected.


     SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR('9420hwvnq6jsj', 0, FORMAT => 'ALL'));
     PLAN_TABLE_OUTPUT
     ------------------------------------------------------------------------
     SQL_ID 9420hwvnq6jsj, child number 0
     -------------------------------------
     select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME,
     E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID =
     D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = 50 OR D.DEPARTMENT_ID = 80)
     order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME

     Plan hash value: 2480766633

     ------------------------------------------------------------------------
     | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
     ------------------------------------------------------------------------
     ...
     60 rows selected.

============================================================================================
Format LAST et plusieurs child cursors
============================================================================================
Dans la doc Oracle je lis la chose suivante « LAST - By default, plan statistics are shown for all executions of the cursor. The keyword LAST can be specified to see only the statistics for the last execution." Déjà, je ne suis pas d'accord, par défaut Oracle affiche uniquement la première exécution de l'ordre SQL et non pas toutes les exécutions (il faut utiliser NULL pour cela).

Mon SELECT a deux childs cursors :
     SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'9420hwvnq6jsj', cursor_child_no=>NULL, format=>'TYPICAL'));
     PLAN_TABLE_OUTPUT
     ------------------------------------------------------------------------
     SQL_ID 9420hwvnq6jsj, child number 0
     -------------------------------------
     ...

     SQL_ID 9420hwvnq6jsj, child number 1
     -------------------------------------
     ...

Et là, on voit quoi ? Même si on utilise LAST, c'est le child cursor 0 qui est affiché.
     SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'9420hwvnq6jsj', format=>'LAST'));
     PLAN_TABLE_OUTPUT
     ------------------------------------------------------------------------
     SQL_ID 9420hwvnq6jsj, child number 0
     -------------------------------------
     ... 

Introduction
Autres articles sur DBMS_XPLAN.DISPLAY_CURSOR

• Partie 1 : DBMS_XPLAN.DISPLAY_CURSOR 01 : les fondamentaux - DBMS_XPLAN.DISPLAY_CURSOR 01 : the fundamentals
• Partie 2 : DBMS_XPLAN.DISPLAY_CURSOR 02 : appels simples et complexes - DBMS_XPLAN.DISPLAY_CURSOR 02: simple and complex calls
• Partie 4 : DBMS_XPLAN.DISPLAY_CURSOR 04 : pièges et points d’attention - DBMS_XPLAN.DISPLAY_CURSOR 04 : pitfalls and points of attention

Dans cet article, nous traiterons deux points concernant DBMS_XPLAN.DISPLAY_CURSOR:

• Ajouter et enlever des champs au plan d'exécution affiché
• Voir quels sont les ordres DML et DDL qui génèrent un plan d'exécution
  

Pour des raisons de lisibilité, je n'afficherai que les infos importantes du plan d'exécution.

Points d'attention
N/A.

Base de tests
Une base Oracle 19 multi-tenants.

Exemples

============================================================================================
Ajout de champs ou colonnes au plan d'exécution
============================================================================================ 
Avec DBMS_XPLAN.DISPLAY_CURSOR, il est possible de modifier le plan affiché, quel qu'en soit le format.
Je vous renvoie à mon premier article (URL ci-dessus) pour avoir la liste des noms associés aux différents champs et colonnes d'un plan d'exécution.

On exécute notre SELECT de test et on affiche le plan d'exécution associé au format BASIC.
     SQL> select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME, E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID = D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = 50 OR D.DEPARTMENT_ID = 80) order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME;
     DEPARTMENT_NAME            DEPARTMENT_ID FIRST_NAME       LAST_NAME    EMPLOYEE_ID
     ------------------------------ ------------- -------------------- -------------------
     Sales                      80 Alberto          Errazuriz                147
     ...
     Shipping                  50 Winston          Taylor                180
     79 rows selected.

     SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'9420hwvnq6jsj', cursor_child_no=>0, format=>'BASIC'));
     PLAN_TABLE_OUTPUT
     --------------------------------------------------------------------
     EXPLAINED SQL STATEMENT:
     ------------------------
     select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME,
E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID =
D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = 50 OR D.DEPARTMENT_ID = 80)
order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME

     Plan hash value: 2480766633
     -------------------------------------------------------------
     | Id  | Operation            | Name            |
     -------------------------------------------------------------
     |   0 | SELECT STATEMENT        |            |
     |   1 |  SORT ORDER BY            |            |
     |   2 |   NESTED LOOPS            |            |
     |   3 |    NESTED LOOPS         |            |
     |   4 |     INLIST ITERATOR        |            |
     |   5 |      TABLE ACCESS BY INDEX ROWID| DEPARTMENTS        |
     |   6 |       INDEX UNIQUE SCAN     | DEPT_ID_PK        |
     |   7 |     INDEX RANGE SCAN        | EMP_DEPARTMENT_IX |
     |   8 |    TABLE ACCESS BY INDEX ROWID    | EMPLOYEES        |
     -------------------------------------------------------------

     23 rows selected.

L'ajout d'information se fait avec le signe + et le nom des infos à ajouter : il peut s'agir de colonnes ou de champs après le plan proprement dit. Pour afficher les colonnes ROWS et BYTES, c'est très simple.
     SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'9420hwvnq6jsj', cursor_child_no=>0, format=>'BASIC +ROWS +BYTES'));

     PLAN_TABLE_OUTPUT
     ------------------------------------------------------
     ...
     ------------------------------------------------------
     | Id | Operation | Name | Rows | Bytes |
     ------------------------------------------------------
     | 0 | SELECT STATEMENT | | | |
     ...
     | 8 |    TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 39 | 858 |
     -----------------------------------------------------

     23 rows selected.

En plus des colonnes, on peut ajouter des champs. Exemple avec les champs ALIAS et NOTE.
     SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'9420hwvnq6jsj', cursor_child_no=>0, format=>'BASIC +ROWS +BYTES +ALIAS +NOTE'));

     PLAN_TABLE_OUTPUT
     ----------------------------------------------
     ...
     ----------------------------------------------
     | Id | Operation | Name | Rows | Bytes |
     ----------------------------------------------
     | 0 | SELECT STATEMENT | | | |
     ...
     | 8 |    TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 39 | 858 |
     -----------------------------------------------

     Query Block Name / Object Alias (identified by operation id):
     -------------------------------------------------------------
     1 - SEL$1
          5 - SEL$1 / D@SEL$1
     6 - SEL$1 / D@SEL$1
     7 - SEL$1 / E@SEL$1
     8 - SEL$1 / E@SEL$1

     Note
     -----
     - this is an adaptive plan

     36 rows selected.

A noter que tous les champs ne sont pas logés à la même enseigne. Par exemple, le champ OUTLINE_DATA, affiché avec le format ADVANCED, ne peut pas être ajouté.
     SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'9420hwvnq6jsj', cursor_child_no=>0, format=>'BASIC +ROWS +BYTES +OUTLINE_DATA'));
     PLAN_TABLE_OUTPUT
     -----------------------------------------------------------------------------------------
     Error: format 'BASIC +ROWS +BYTES +OUTLINE_DATA' not valid for DBMS_XPLAN.DISPLAY_CURSOR()

     1 row selected.


============================================================================================
Suppression de champs ou colonnes au plan d'exécution
============================================================================================
Tout comme on peut ajouter des informations, on peut aussi en supprimer en remplaçant le signe + par le signe -. Voici un plan d'exécution avec le format ALL.
     SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'9420hwvnq6jsj', cursor_child_no=>0, format=>'ALL'));

     PLAN_TABLE_OUTPUT
     -------------------------------------------------------------------
     SQL_ID    9420hwvnq6jsj, child number 0
     -------------------------------------
     select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME,
E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID =
D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = 50 OR D.DEPARTMENT_ID = 80)
order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME

     Plan hash value: 2480766633
     -----------------------------------------------------------------------------------------------------
     | Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
     -----------------------------------------------------------------------------------------------------
     |   0 | SELECT STATEMENT        |            |        |        |      4 (100)|        |
     ...
     |   8 |    TABLE ACCESS BY INDEX ROWID    | EMPLOYEES        |     39 |    858 |      1   (0)| 00:00:01 |
     -----------------------------------------------------------------------------------------------------

     Query Block Name / Object Alias (identified by operation id):
     -------------------------------------------------------------

        1 - SEL$1
        5 - SEL$1 / D@SEL$1
        6 - SEL$1 / D@SEL$1
        7 - SEL$1 / E@SEL$1
        8 - SEL$1 / E@SEL$1

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

        6 - access(("D"."DEPARTMENT_ID"=50 OR "D"."DEPARTMENT_ID"=80))
        7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
            filter(("E"."DEPARTMENT_ID"=50 OR "E"."DEPARTMENT_ID"=80))

     Column Projection Information (identified by operation id):
     -----------------------------------------------------------

        1 - (#keys=3) "D"."DEPARTMENT_NAME"[VARCHAR2,30], "E"."FIRST_NAME"[VARCHAR2,20],
       "E"."LAST_NAME"[VARCHAR2,25], "E"."DEPARTMENT_ID"[NUMBER,22], "E"."EMPLOYEE_ID"[NUMBER,22]
        2 - "D"."DEPARTMENT_ID"[NUMBER,22], "D"."DEPARTMENT_NAME"[VARCHAR2,30],
       "E"."EMPLOYEE_ID"[NUMBER,22], "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25],
       "E"."DEPARTMENT_ID"[NUMBER,22]
        3 - "D"."DEPARTMENT_ID"[NUMBER,22], "D"."DEPARTMENT_NAME"[VARCHAR2,30],
       "E".ROWID[ROWID,10], "E"."DEPARTMENT_ID"[NUMBER,22]
        4 - "D"."DEPARTMENT_ID"[NUMBER,22], "D"."DEPARTMENT_NAME"[VARCHAR2,30]
        5 - "D"."DEPARTMENT_ID"[NUMBER,22], "D"."DEPARTMENT_NAME"[VARCHAR2,30]
        6 - "D".ROWID[ROWID,10], "D"."DEPARTMENT_ID"[NUMBER,22]
        7 - "E".ROWID[ROWID,10], "E"."DEPARTMENT_ID"[NUMBER,22]
        8 - "E"."EMPLOYEE_ID"[NUMBER,22], "E"."FIRST_NAME"[VARCHAR2,20],
       "E"."LAST_NAME"[VARCHAR2,25]

     Note
     -----
        - this is an adaptive plan

     60 rows selected.

On enlève maintenant les infos BYTES et PROJECTION, à savoir une colonne et un champ après le plan.
     SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'9420hwvnq6jsj', cursor_child_no=>0, format=>'ALL -BYTES -PROJECTION'));
     ...
     ---------------------------------------------------------------------------------------------
     | Id | Operation | Name | Rows | Cost (%CPU)| Time |
     ---------------------------------------------------------------------------------------------
     | 0 | SELECT STATEMENT | | | 4 (100)| |
     ...
     | 8 |    TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 39 | 1 (0)| 00:00:01 |
     ---------------------------------------------------------------------------------------------

     Query Block Name / Object Alias (identified by operation id):
     -------------------------------------------------------------

     1 - SEL$1
     5 - SEL$1 / D@SEL$1
     6 - SEL$1 / D@SEL$1
     7 - SEL$1 / E@SEL$1
     8 - SEL$1 / E@SEL$1

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

     6 - access(("D"."DEPARTMENT_ID"=50 OR "D"."DEPARTMENT_ID"=80))
     7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
     filter(("E"."DEPARTMENT_ID"=50 OR "E"."DEPARTMENT_ID"=80))

     Note
     -----
     - this is an adaptive plan

     43 rows selected.

Bien sur, on ne peut pas enlever n'importe quelle information, sinon le plan affiché n'a plus de sens. Exemple avec un plan BASIC sans la colonne NAME : cela génère un message d'erreur.
     SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'9420hwvnq6jsj', cursor_child_no=>0, format=>'BASIC -NAME'));

     PLAN_TABLE_OUTPUT
     --------------------------------------------------------------------
     Error: format 'BASIC -NAME' not valid for DBMS_XPLAN.DISPLAY_CURSOR()

     1 row selected.


============================================================================================
Les ordres SQL DML autres que SELECT
============================================================================================
Oracle génère un plan d'exécution pour les SELECT mais aussi pour les opérations de mises à jour des données. Comme vous pouvez le voir, les ordres UPDATE, DELETE, INSERT sans SELECT, génèrent des plans d'exécution très simples. A noter qu'il y a deux parties PLAN_TABLE_OUTPUT, l'une pour l'ordre DML proprement dit, l'autre pour décrire l'accès à la table.

UPDATE
Suivant que l'on ait ou non une clause WHERE, la partie "UPDATE | ZZDEPT" se trouve au dessus ou en dessous de la ligne PLAN_TABLE_OUTPUT; je penche pour un bug d'affichage car le DELETE est traité de façon homogène.

Update sans clause WHERE
     SQL> set feedback on sql_id

     SQL> update zzdept set DEPARTMENT_NAME = 'SALES';
     6 rows updated.
     SQL_ID: f1732qaw6m009

     SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('f1732qaw6m009', NULL));
     PLAN_TABLE_OUTPUT
     --------------------------------------------------------------------------------
     SQL_ID    f1732qaw6m009, child number 0
     -------------------------------------
     update zzdept set DEPARTMENT_NAME = 'SALES'
    
     Plan hash value: 2690762299
     -----------------------------------------------------------------------------
     | Id  | Operation       | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
     -----------------------------------------------------------------------------
     |   0 | UPDATE STATEMENT   |        |        |        |      4 (100)|        |
     |   1 |  UPDATE        | ZZDEPT |        |        |         |        |

     PLAN_TABLE_OUTPUT
     --------------------------------------------------------------------------------
     |   2 |   TABLE ACCESS FULL| ZZDEPT |      6 |    120 |      4   (0)| 00:00:01 |
     -----------------------------------------------------------------------------

     14 rows selected.


Update avec clause WHERE
     SQL> update zzdept set DEPARTMENT_NAME = 'SALES01' where DEPARTMENT_NAME = 'Sales';
     1 row updated.
     SQL_ID: 5squ0mr0f6u9z

     SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('5squ0mr0f6u9z', NULL));
     PLAN_TABLE_OUTPUT
     --------------------------------------------------------------------------------
     SQL_ID 5squ0mr0f6u9z, child number 0
     -------------------------------------
     update zzdept set DEPARTMENT_NAME = 'SALES01' where DEPARTMENT_NAME =
'Sales'

     Plan hash value: 2690762299

     -----------------------------------------------------------------------------
     | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
     -----------------------------------------------------------------------------
     | 0 | UPDATE STATEMENT | | | | 4 (100)| |

     PLAN_TABLE_OUTPUT
     --------------------------------------------------------------------------------
     | 1 | UPDATE | ZZDEPT | | | | |
     |* 2 | TABLE ACCESS FULL| ZZDEPT | 1 | 20 | 4 (0)| 00:00:01 |
     -----------------------------------------------------------------------------

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

     2 - filter("DEPARTMENT_NAME"='Sales')

     20 rows selected.

SQL> rollback;
Rollback complete.

INSERT
Tiens, on a une note curieuse ici, jamais vue auparavant "cpu costing is off (consider enabling it)".
     SQL> INSERT INTO ZZDEPT (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID) VALUES (100, 'TEST', 1, 1);
     1 row created.
     SQL_ID: 1wfyu4k2c4bfa

     SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('1wfyu4k2c4bfa', NULL));
     PLAN_TABLE_OUTPUT
     --------------------------------------------------------------------------------
     SQL_ID 1wfyu4k2c4bfa, child number 0
     -------------------------------------
     INSERT INTO ZZDEPT (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID,
     LOCATION_ID) VALUES (100, 'TEST', 1, 1)

     ---------------------------------------------------
     | Id | Operation | Name | Cost |
     ---------------------------------------------------
     | 0 | INSERT STATEMENT | | 1 |
     | 1 | LOAD TABLE CONVENTIONAL | ZZDEPT | |

     PLAN_TABLE_OUTPUT
     --------------------------------------------------------------------------------

     Note
     -----
     - cpu costing is off (consider enabling it)

     17 rows selected.

     SQL> rollback;

DELETE
A la différence de l'UPDATE, la partie "DELETE | ZZDEPT" se trouve toujours dans la partie PLAN_TABLE_OUTPUT de l'opération DML, pas dans la partie PLAN_TABLE_OUTPUT de l'accès à la table.

DELETE sans clause WHERE
     SQL> delete from zzdept;
     6 rows deleted.
     SQL_ID: chpf4pjbtrrmy

     SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('chpf4pjbtrrmy', NULL));
     PLAN_TABLE_OUTPUT
     --------------------------------------------------------------------------------
     SQL_ID chpf4pjbtrrmy, child number 0
     -------------------------------------
     delete from zzdept

     Plan hash value: 2154665309

     -----------------------------------------------------------------------------
     | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
     -----------------------------------------------------------------------------
     | 0 | DELETE STATEMENT | | | | 4 (100)| |
     | 1 | DELETE | ZZDEPT | | | | |

     PLAN_TABLE_OUTPUT
     --------------------------------------------------------------------------------
     | 2 | TABLE ACCESS FULL| ZZDEPT | 6 | 18 | 4 (0)| 00:00:01 |
     -----------------------------------------------------------------------------

     14 rows selected.

DELETE avec clause WHERE
     SQL> delete from zzdept where location_id = 2700;
     1 row deleted.
     SQL_ID: 3xbykjs6p1t65

     SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('3xbykjs6p1t65', NULL));
     PLAN_TABLE_OUTPUT
     --------------------------------------------------------------------------------
     SQL_ID 3xbykjs6p1t65, child number 0
     -------------------------------------
     delete from zzdept where location_id = 2700

     Plan hash value: 2154665309

     -----------------------------------------------------------------------------
     | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
     -----------------------------------------------------------------------------
     | 0 | DELETE STATEMENT | | | | 4 (100)| |
     | 1 | DELETE | ZZDEPT | | | | |

     PLAN_TABLE_OUTPUT
     --------------------------------------------------------------------------------
     |* 2 | TABLE ACCESS FULL| ZZDEPT | 1 | 6 | 4 (0)| 00:00:01 |
     -----------------------------------------------------------------------------

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

     2 - filter("LOCATION_ID"=2700)

     19 rows selected.

     SQL> rollback;


============================================================================================
Les ordres SQL DDL
============================================================================================
Et pour les ordres DDL, que se passe t-il? Pour le CTAS, nous avons un plan d'exécution classique car cet ordre comporte un SELECT.

CTAS (Create Table As Select)
     SQL> create table zzdept100 as select * from DEPARTMENTS;
     Table created.
     SQL_ID: 1r1r87zc299r3

     SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('1r1r87zc299r3',NULL));
     PLAN_TABLE_OUTPUT
     --------------------------------------------------------------------------------
     SQL_ID 1r1r87zc299r3, child number 0
     -------------------------------------
     create table zzdept100 as select * from DEPARTMENTS

     Plan hash value: 1765630329

     -------------------------------------------------------------------------------

     | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

     PLAN_TABLE_OUTPUT
     --------------------------------------------------------------------------------

     | 0 | CREATE TABLE STATEMENT | | | | 4 (     100)| |
     | 1 | LOAD AS SELECT | ZZDEPT100 | | |     | |
     | 2 | OPTIMIZER STATISTICS GATHERING | | 27 | 567 | 3

     PLAN_TABLE_OUTPUT
     --------------------------------------------------------------------------------
     (0)| 00:00:01 |
     | 3 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 567 | 3
     (0)| 00:00:01 |

     --------------------------------------------------------------------------------

En revanche, des opérations DDL comme CREATE TABLE, ALTER TABLE ou CREATE INDEX n'ont pas de plan d'exécution. Pourquoi ? Tout simplement parce que le plan d'exécution dit à Oracle comment accéder le plus rapidement possible aux données alors que là on veut juste créer un objet ou le modifier; c'est donc le dictionnaire de données d'Oracle qui est modifié, point ; il n'y a donc aucun accès aux données applicatives.

Il y a une exception pour le CREATE INDEX où la table de la colonne indexée est parcourue en entier. Elle l'est en FULL TABLE SCAN puisque Oracle doit lire toutes les données. Dans ce cas, pas besoin d'utiliser le CBO pour trouver un autre plan, meilleur, car il n'y en a qu'un de possible donc, si pas d'optimisation possible, c'est inutile d'appeler le CBO. N'oubliez pas, l'optimiseur est là pour optimiser l'accès aux données :-) Mais, s'il n'y a qu'un mode d'accès à l'instant t à ces données, pas la peine de déranger l'optimiseur puisqu'il n'en trouvera pas un deuxième.

Create table
     SQL> create table zzdept1000(id number);
     Table created.
     SQL_ID: 728j80u1zmggk

     SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('728j80u1zmggk', NULL));
     PLAN_TABLE_OUTPUT
     --------------------------------------------------------------------------------
     SQL_ID: 728j80u1zmggk cannot be found

Alter table
     SQL> alter table zzdept add (id100 number);
     Table altered.
     SQL_ID: 9xgk345a7v71b

     SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('9xgk345a7v71b', NULL));
     PLAN_TABLE_OUTPUT
     --------------------------------------------------------------------------------
     SQL_ID: 9xgk345a7v71b cannot be found

Create index
     SQL> create index idx_zztest_id on zzdept(DEPARTMENT_ID);
     Index created.
     SQL_ID: 8fjar2qknzvfu

     SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('8fjar2qknzvfu', NULL));
     PLAN_TABLE_OUTPUT
     --------------------------------------------------------------------------------
     SQL_ID: 8fjar2qknzvfu cannot be found

Introduction
Autres articles sur DBMS_XPLAN.DISPLAY_CURSOR

• Partie 1 : DBMS_XPLAN.DISPLAY_CURSOR 01 : les fondamentaux - DBMS_XPLAN.DISPLAY_CURSOR 01 : the fundamentals
• Partie 3 : DBMS_XPLAN.DISPLAY_CURSOR 03 : ajouter des champs, les ordres DM et DDL traités - add fields, DML and DDL orders processed
• Partie 4 : DBMS_XPLAN.DISPLAY_CURSOR 04 : pièges et points d’attention - DBMS_XPLAN.DISPLAY_CURSOR 04 : pitfalls and points of attention

La fonction DBMS_XPLAN.DISPLAY_CURSOR possède trois paramètres

• sql_id
• cursor_child_no
• format

Les valeurs par défaut sont les suivantes
     DBMS_XPLAN.DISPLAY_CURSOR(
          sql_id IN VARCHAR2 DEFAULT NULL,
          cursor_child_no IN NUMBER DEFAULT 0,
          format IN VARCHAR2 DEFAULT 'TYPICAL');

Nous allons voir comment appeler cette fonction avec les valeurs par défaut et quelles sont les autres valeurs intéressantes.

Points d'attention
N/A.

Base de tests
Une base Oracle 19 multi-tenants.

Exemples

============================================================================================
Appel simple de DBMS_XPLAN.DISPLAY_CURSOR
============================================================================================ 
Il est possible d'appeler DBMS_XPLAN.DISPLAY_CURSOR sans paramètre, avec uniquement la valeur par défaut de ceux-ci :

• Dernier ordre exécuté ayant généré un plan d'exécution (en général un SELECT)
• Première exécution de cet ordre
• Format Typical     
  

On exécute notre SELECT de test, ramenant 79 lignes.
     SQL> set lines 200 pages 30000
     SQL> select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME, E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID = D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = 50 OR D.DEPARTMENT_ID = 80) order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME;
     DEPARTMENT_NAME DEPARTMENT_ID FIRST_NAME LAST_NAME EMPLOYEE_ID
     ------------------------------ ------------- -----------------
     Sales 80 Alberto Errazuriz 147
     ...
     Shipping 50 Winston Taylor 180

     79 rows selected.

Pour afficher son plan d'exécution, la syntaxe la plus simple est la suivante.
     SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);
     PLAN_TABLE_OUTPUT
     --------------------------------------------------------------------
     SQL_ID 9420hwvnq6jsj, child number 0
     -------------------------------------
     select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME,
E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID =
D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = 50 OR D.DEPARTMENT_ID = 80)
order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME

     Plan hash value: 2480766633
     --------------------------------------------------------------------
     | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
     --------------------------------------------------------------------
     | 0 | SELECT STATEMENT | | | | 4 (100)| |
     | 1 | SORT ORDER BY | | 78 | 2964 | 4 (25)| 00:00:01 |
     | 2 | NESTED LOOPS | | 78 | 2964 | 3 (0)| 00:00:01 |
     | 3 | NESTED LOOPS | | 78 | 2964 | 3 (0)| 00:00:01 |
     | 4 | INLIST ITERATOR | | | | | |
     | 5 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 2 | 32 | 2 (0)| 00:00:01 |
     |* 6 | INDEX UNIQUE SCAN | DEPT_ID_PK | 2 | | 1 (0)| 00:00:01 |
     |* 7 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 7 | | 0 (0)| |
     | 8 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 39 | 858 | 1 (0)| 00:00:01 |
     --------------------------------------------------------------------

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

     6 - access(("D"."DEPARTMENT_ID"=50 OR "D"."DEPARTMENT_ID"=80))
     7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
     filter(("E"."DEPARTMENT_ID"=50 OR "E"."DEPARTMENT_ID"=80))

     Note
     -----
     - this is an adaptive plan

     34 rows selected.

============================================================================================
Appel plus complexe de DBMS_XPLAN.DISPLAY_CURSOR : le paramètre SQL_ID
============================================================================================
Pour des soucis de lisibilité, je n'affiche plus le plan d'exécution en entier, il est déjà présent ci-dessus, mais seulement les parties des points traités.

Voyons maintenant des appels un peu plus complexes. La fonction DBMS_XPLAN.DISPLAY_CURSOR accepte trois paramètres

• SQL_ID : identifiant de l'ordre SQL à étudier ou rien (dernier ordre exécuté qui a généré un plan d'exécution)
  
• cursor_child_no : soit rien (ce sera alors la valeur par défaut qui est 0), soit un numéro de cursor_child (0 à N) soit NULL
• format du plan d'exécution : voir mon article précédent (lien tout en haut)
  

Le SQL_ID de l'ordre a étudier, s'il n'est pas le dernier SELECT exécuté par exemple, peut se trouver de plusieurs façons différentes. Soit, on recherche le SQL_ID dans les vues Oracle comme V$SQL, DBA_HIST_SQLTEXT (si on a le Diagnostic Pack) soit on utilise la commande feedback sous SQL*Plus qui affiche, depuis la v18, le sql_id de l'ordre SQL exécuté.


Utilisation de V$SQL
Attention dans le select à bien exclure V$SQL de la recherche pour éviter que ce SELECT sur V$SQL n'apparaisse dans le résultat.
     SQL> select sql_id, SQL_TEXT from v$sql where SQL_TEXT like '%select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME, E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID = D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = 50 %' and SQL_TEXT NOT LIKE '%V$SQL%';
     SQL_ID                                                    SQL_TEXT
     ------------------------------------------------------------------------------
     9420hwvnq6jsj
     select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME, E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID = D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = 50 OR D.DEPARTMENT_ID
= 80) order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME

     1 row selected.


Utilisation de feedback
La syntaxe sous SQL*Plus est très simple : on saisit set feedback on sql_id, on exécute le SELECT et, bingo, le SQL_ID apparait à la fin du SELECT.
     SQL> set feedback on sql_id
     SQL> select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME, E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID = D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = 50 OR D.DEPARTMENT_ID = 80) order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME;
     DEPARTMENT_NAME DEPARTMENT_ID FIRST_NAME LAST_NAME EMPLOYEE_ID
     ------------------------------ ------------- ------------------
     Sales 80 Alberto Errazuriz 147
     ...
     Shipping 50 Winston Taylor 180

     79 rows selected.

     SQL_ID: 9420hwvnq6jsj


Dans les deux cas on a bien le même SQL_ID, à savoir 9420hwvnq6jsj. L'appel de DBMS_XPLAN.DISPLAY_CURSOR avec celui-ci est simple.
     SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('9420hwvnq6jsj'));
     PLAN_TABLE_OUTPUT
     --------------------------------------------------------------------
     SQL_ID    9420hwvnq6jsj, child number 0
     -------------------------------------
     select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME,
E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID =
D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = 50 OR D.DEPARTMENT_ID = 80)
order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME
...
     34 rows selected.



============================================================================================
Appel plus complexe de DBMS_XPLAN.DISPLAY_CURSOR : le paramètre cursor_child_no
============================================================================================
Que dit la doc Oracle? "cursor_child_no: child number of the cursor to display. If not supplied, the execution plan of all cursors matching the supplied sql_id parameter are displayed. The child_number can be specified only if sql_id is specified." J'avoue être surpris par ce qui est écrit car si on ne met rien pour ce champ, c'est en réalité le plan d'exécution du child_cursor numéro 0 qui sera affiché. Pour avoir tous les plans des différents child_cursor, il faudra utiliser NULL comme paramètre.

Je vide la SHARED_POOL pour les besoins de mon test.
     SQL> alter system flush shared_pool;
     System altered.

J'exécute une fois mon SELECT de test.
     SQL> select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME, E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID = D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = 50 OR D.DEPARTMENT_ID = 80) order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME;
     DEPARTMENT_NAME DEPARTMENT_ID FIRST_NAME LAST_NAME EMPLOYEE_ID
     ------------------------------ ------------- ------------------
     Sales 80 Alberto Errazuriz 147
     ...
     Shipping 50 Winston Taylor 180

     79 rows selected.

Maintenant je le réexécute une deuxième fois, sans rien modifier.
     SQL> /
     DEPARTMENT_NAME DEPARTMENT_ID FIRST_NAME LAST_NAME EMPLOYEE_ID
     ------------------------------ ------------- ------------------
     Sales 80 Alberto Errazuriz 147
     ...
     Shipping 50 Winston Taylor 180

     79 rows selected.

Je passe l'optimiseur en mode FIRST_ROWS et je réexécute la requête une troisième fois.
     SQL> alter session set optimizer_mode = FIRST_ROWS;
     Session altered.

     SQL> select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME, E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID = D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = 50 OR D.DEPARTMENT_ID = 80) order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME;
     DEPARTMENT_NAME DEPARTMENT_ID FIRST_NAME LAST_NAME EMPLOYEE_ID
     ------------------------------ ------------- -----------------
     Sales 80 Alberto Errazuriz 147
     ...
     Shipping 50 Winston Taylor 180

     79 rows selected.


Et maintenant, si on utilise le format ADVANCED, que voit-on avec les trois valeurs du paramètre child_cursor? Je ne copie ici que la partie intéressante, le numéro de child_cursor et le bloc OUTLINE DATA.

Aucune valeur : valeur par défaut
Le plan d'exécution affiché fait 85 lignes, c'est celui du child_cursor 0 et nous sommes en mode ALL_ROWS. Oracle affiche donc le plan de la première exécution du SELECT.
     SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'9420hwvnq6jsj', format=>'ADVANCED'));
     PLAN_TABLE_OUTPUT
     -------------------------------------
     SQL_ID 9420hwvnq6jsj, child number 0
     -------------------------------------
     ...
     Outline Data

     -------------

     /*+
     BEGIN_OUTLINE_DATA
     IGNORE_OPTIM_EMBEDDED_HINTS
     OPTIMIZER_FEATURES_ENABLE('19.1.0')
     DB_VERSION('19.1.0')
     ALL_ROWS
     OUTLINE_LEAF(@"SEL$1")
     INDEX_RS_ASC(@"SEL$1" "D"@"SEL$1" ("DEPARTMENTS"."DEPARTMENT_ID"))
     INDEX(@"SEL$1" "E"@"SEL$1" ("EMPLOYEES"."DEPARTMENT_ID"))
     LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
     USE_NL(@"SEL$1" "E"@"SEL$1")
     NLJ_BATCHING(@"SEL$1" "E"@"SEL$1")
     END_OUTLINE_DATA
     */
     ...

     85 rows selected.

Valeur 0 : c'est aussi la valeur par défaut
On a le même résultat que ci-dessus, à savoir que 0 est la valeur par défaut.
     SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'9420hwvnq6jsj', cursor_child_no=>0, format=>'ADVANCED'));
     PLAN_TABLE_OUTPUT
     -------------------------------------
     SQL_ID 9420hwvnq6jsj, child number 0
     -------------------------------------
     ...
     Outline Data
     -------------

     /*+
     BEGIN_OUTLINE_DATA
     IGNORE_OPTIM_EMBEDDED_HINTS
     OPTIMIZER_FEATURES_ENABLE('19.1.0')
     DB_VERSION('19.1.0')
     ALL_ROWS
     OUTLINE_LEAF(@"SEL$1")
     INDEX_RS_ASC(@"SEL$1" "D"@"SEL$1" ("DEPARTMENTS"."DEPARTMENT_ID"))
     INDEX(@"SEL$1" "E"@"SEL$1" ("EMPLOYEES"."DEPARTMENT_ID"))
     LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
     USE_NL(@"SEL$1" "E"@"SEL$1")
     NLJ_BATCHING(@"SEL$1" "E"@"SEL$1")
     END_OUTLINE_DATA
     */
     ...

     85 rows selected.


Autre valeur que 0 mais existante
Si je mets une autre valeur que 0, je vais voir le plan d'exécution d'un autre cursor child; attention, cela ne veut pas dire que le plan d'exécution change. Je teste la valeur 1 et, miracle, je vois quoi? On a le plan d'exécution avec le paramètre FIRST_ROWS. Notez que le child cursor affiché est 1 et que le plan fait 80 lignes au lieu de 85...
     SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'9420hwvnq6jsj', cursor_child_no=>1, format=>'ADVANCED'));
     PLAN_TABLE_OUTPUT
     -------------------------------------
     SQL_ID 9420hwvnq6jsj, child number 1
     -------------------------------------
     ...

     Outline Data
     -------------

     /*+
     BEGIN_OUTLINE_DATA
     IGNORE_OPTIM_EMBEDDED_HINTS
     OPTIMIZER_FEATURES_ENABLE('19.1.0')
     DB_VERSION('19.1.0')
     FIRST_ROWS
     OUTLINE_LEAF(@"SEL$1")
     INDEX_RS_ASC(@"SEL$1" "D"@"SEL$1" ("DEPARTMENTS"."DEPARTMENT_ID"))
     INDEX(@"SEL$1" "E"@"SEL$1" ("EMPLOYEES"."DEPARTMENT_ID"))
     LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
     USE_NL(@"SEL$1" "E"@"SEL$1")
     NLJ_BATCHING(@"SEL$1" "E"@"SEL$1")
     END_OUTLINE_DATA
     */
     ...

     80 rows selected.


Valeur inexistante
Si le numéro de child cursor n'existe pas en mémoire, Oracle affiche un message d'erreur.
     SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'9420hwvnq6jsj', cursor_child_no=>2, format=>'ADVANCED'));
     PLAN_TABLE_OUTPUT
     -------------------------------------------------------
     SQL_ID: 9420hwvnq6jsj, child number: 2 cannot be found

     2 rows selected.

NULL : afficher les plans de tous les child_cursor
Si on utilise NULL, Oracle affiche les deux child cursor en mémoire. Dans la partie Outline Data, on voit bien les deux valeurs pour le paramètre optimizer_mode. Notez que si ma requête a été exécutée 3 fois, il n'y a que deux child cursor : celui pour la première exécution du SELECT et celui pour la troisième exécution car un paramètre utilisé par le CBO a été modifié. La deuxième exécution du SELECT n'a pas généré de child cursor puisque le contexte était identique à celui de la première exécution.

     SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('9420hwvnq6jsj',NULL,'ADVANCED'));
     PLAN_TABLE_OUTPUT
     -------------------------------------
     SQL_ID 9420hwvnq6jsj, child number 0
     -------------------------------------
     ...
     Outline Data
     -------------

     /*+
     BEGIN_OUTLINE_DATA
     IGNORE_OPTIM_EMBEDDED_HINTS
     OPTIMIZER_FEATURES_ENABLE('19.1.0')
     DB_VERSION('19.1.0')
     ALL_ROWS
     OUTLINE_LEAF(@"SEL$1")
     INDEX_RS_ASC(@"SEL$1" "D"@"SEL$1" ("DEPARTMENTS"."DEPARTMENT_ID"))
     INDEX(@"SEL$1" "E"@"SEL$1" ("EMPLOYEES"."DEPARTMENT_ID"))
     LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
     USE_NL(@"SEL$1" "E"@"SEL$1")
     NLJ_BATCHING(@"SEL$1" "E"@"SEL$1")
     END_OUTLINE_DATA
     */

     ...
     SQL_ID 9420hwvnq6jsj, child number 1
     -------------------------------------
     Outline Data
     -------------

     /*+
     BEGIN_OUTLINE_DATA
     IGNORE_OPTIM_EMBEDDED_HINTS
     OPTIMIZER_FEATURES_ENABLE('19.1.0')
     DB_VERSION('19.1.0')
     FIRST_ROWS
     OUTLINE_LEAF(@"SEL$1")
     INDEX_RS_ASC(@"SEL$1" "D"@"SEL$1" ("DEPARTMENTS"."DEPARTMENT_ID"))
     INDEX(@"SEL$1" "E"@"SEL$1" ("EMPLOYEES"."DEPARTMENT_ID"))
     LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
     USE_NL(@"SEL$1" "E"@"SEL$1")
     NLJ_BATCHING(@"SEL$1" "E"@"SEL$1")
     END_OUTLINE_DATA
     */

     ...
     165 rows selected.


Liste des curseurs fils existants
Pour avoir la liste des curseurs fils actuellement en mémoire, il suffit de lancer l'ordre SQL suivant.
     SQL> select CHILD_NUMBER from V$SQL_SHARED_CURSOR where SQL_ID = '9420hwvnq6jsj';
     CHILD_NUMBER
     ------------
     0
     1

     2 rows selected.

============================================================================================
Appel plus complexe de DBMS_XPLAN.DISPLAY_CURSOR : le paramètre format
============================================================================================
Voir mon article précédent "DBMS_XPLAN.DISPLAY_CURSOR 01 : les fondamentaux - DBMS_XPLAN.DISPLAY_CURSOR 01 : the fundamentals"

Introduction
Autres articles sur DBMS_XPLAN.DISPLAY_CURSOR

• Partie 2 : DBMS_XPLAN.DISPLAY_CURSOR 02 : appels simples et complexes - DBMS_XPLAN.DISPLAY_CURSOR 02: simple and complex calls
• Partie 3 : DBMS_XPLAN.DISPLAY_CURSOR 03 : ajouter des champs, les ordres DM et DDL traités - add fields, DML and DDL orders processed
• Partie 4 : DBMS_XPLAN.DISPLAY_CURSOR 04 : pièges et points d’attention - DBMS_XPLAN.DISPLAY_CURSOR 04 : pitfalls and points of attention

Sous Oracle 19, il existe six fonctions pour afficher un plan d'exécution, suivant l'endroit où est stocké celui-ci ; ce sont les fonctions qui commencent par DISPLAY dans l'image ci-dessous.

Pour afficher un plan d'exécution estimé après un EXPLAIN PLAN : la requête n'est pas exécutée

• DISPLAY
• DISPLAY_PLAN

Pour afficher un plan d'exécution réel : la requête est vraiment exécutée. Ce plan peut être en SGA, dans le référentiel AWR, dans une SQL plan baseline ou bien dans un SQL tuning set.

• DISPLAY_AWR
• DISPLAY_CURSOR
• DISPLAY_SQL_PLAN_BASELINE
• DISPLAY_SQLSET

La fonction DIFF_PLAN, quant à elle, compare deux plans d'exécutions et affiche les différences.

Nous nous intéresserons ici uniquement à la fonction DBMS_XPLAN.DISPLAY_CURSOR car c'est elle qui dispose des informations les plus riches et qui est la plus utile pour l'optimisation d'un ordre SQL.

Attention, mon objectif est de vous montrer comment utiliser DBMS_XPLAN.DISPLAY_CURSOR mais pas comment lire un plan d'exécution, il y a d'autres sites excellents pour cela.
 

Points d'attention
N/A.

Base de tests
Une base Oracle 19 multi-tenants.

Exemples

============================================================================================
Base de test
============================================================================================ 
Nous utiliserons dans cet article la base de test d'Oracle du schéma HR (Human Ressources). Le SELECT est celui qui affiche les employés des départements d'id 50 et 80.
     SQL> show user con_name
     USER is "HR"

     CON_NAME
     ------------------------------
     ORCL

     SQL> select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME, E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID = D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = 50 OR D.DEPARTMENT_ID = 80) order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME;

     DEPARTMENT_NAME DEPARTMENT_ID FIRST_NAME LAST_NAME EMPLOYEE_ID
     ------------------------------ ------------- --------------------
     Sales 80 Alberto Errazuriz 147
     ...
     Shipping 50 Winston Taylor 180

     79 rows selected.

============================================================================================
La fonction DBMS_XPLAN.DISPLAY_CURSOR
============================================================================================ 
DBMS_XPLAN.DISPLAY_CURSOR est une fonction a priori simple avec seulement trois paramètres :

• sql_id
• cursor_child_no
• format
  

DBMS_XPLAN.DISPLAY_CURSOR(sql_id IN VARCHAR2 DEFAULT NULL, cursor_child_no IN NUMBER DEFAULT 0, format IN VARCHAR2 DEFAULT 'TYPICAL');

Mais la partie format est vraiment complexe, comme le montrent ces deux captures écran du site d'Oracle.

Sur une autre page, reprenant beaucoup d'infos de l’image précédente, il y a quelques ajouts, comme ADVANCED et ALL.

Un point à retenir est que, pour afficher le plus d'informations, nous avons tout en haut ADVANCED puis ALL puis ALLSTATS. Ces deux derniers, contrairement à leur nom, n'affichent pas toutes les statistiques. Attention, ADVANCED et ALL sont des formats d'affichage alors que ALLSTATS est un raccourci pour les stats mémoire et de disque dur.

Avec statistics_level=all et le hint gather_plan_statistics, Oracle peut afficher encore plus de stats :-)


============================================================================================
Les colonnes d'un plan d'exécution
============================================================================================
A ma grande surprise, dans la doc Oracle, il est très difficile de trouver une page décrivant le rôle des colonnes d'un plan d'exécution. On le trouve facilement pour la fonction EXPLAIN_PLAN (plan d'exécution estimé) mais pas pour DBMS_XPLAN.DISPLAY_CURSOR (plan d'exécution exécuté)... allez savoir pourquoi...

Sur ce site, nous avons un descriptif des colonnes d'un EXPLAIN PLAN (attention, il s'agit d'un plan d'exécution estimé et non réel mais le sens ne change pas pour un plan réel (à part les mots Estimated)).
https://www.red-gate.com/simple-talk/databases/oracle-databases/execution-plans-part-8-cost-time-etc/

Autres colonnes (doc trouvée sur le net)

• Starts : number of times that operation actually happened.
• Buffers : amount of buffer read/write (IO) performed.
• OMem : estimated size (in bytes) required by this work area to execute the operation completely in memory (optimal execution).
  
• 1Mem : estimated size (in bytes) required by this work area to execute the operation in a single pass. Memory estimate needed to perform the operation in a single pass (Read/Write from disk (temp) only once), called one-pass execution. A multi-pass execution is when the same data is written to and read from disk more than once.
• Used-Mem : memory (in bytes) used by this work area during the last execution of the cursor. ACTUAL amount of memory used for the operation. You also see some numbers in the brackets for this column. There is a significance for them – If the number is 0, then it was an optimal execution, used only memory and no temporary space. If the number is 1, then it was a one-pass execution. If the number is > 1, it was a multi-pass execution, and that number represents the number of passes.
• Used-Tmp : amount of temporary space used for this operation.

============================================================================================
Les principaux formats : BASIC, TYPICAL, ADAPTIVE, ALL, ADVANCED
============================================================================================
La fonction DBMS_XPLAN.DISPLAY_CURSOR accepte plusieurs valeurs pour le paramètre format. Voyons ce qui est affiché pour chacun d'entre eux, du format le moins verbeux au plus verbeux; cela se voit dans le nombre de lignes affichées en fin de chaque plan (on passe de 23 à 85).

Le SELECT de test a comme sql_id 9420hwvnq6jsj; j'affiche le plan d'exécution uniquement de la première exécution (deuxième paramètre à 0).
En bleu gras, les informations spécifiques au format choisi.


Format BASIC
Ce format est très peu utile car il ne possède que l'opération exécutée et le nom de l'objet concerné, sans aucune statistique.
     SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('9420hwvnq6jsj',0,'BASIC'));

     PLAN_TABLE_OUTPUT
     -----------------------------------------------
     EXPLAINED SQL STATEMENT:
     ------------------------
     select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME,
     E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID =
     D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = 50 OR D.DEPARTMENT_ID = 80)
     order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME

     Plan hash value: 2480766633

     -------------------------------------------------------------
     | Id  | Operation            | Name            |
     -------------------------------------------------------------
     |   0 | SELECT STATEMENT        |            |
     |   1 |  SORT ORDER BY            |            |
     |   2 |   NESTED LOOPS            |            |
     |   3 |    NESTED LOOPS         |            |
     |   4 |     INLIST ITERATOR        |            |
     |   5 |      TABLE ACCESS BY INDEX ROWID| DEPARTMENTS        |
     |   6 |       INDEX UNIQUE SCAN     | DEPT_ID_PK        |
     |   7 |     INDEX RANGE SCAN        | EMP_DEPARTMENT_IX |
     |   8 |    TABLE ACCESS BY INDEX ROWID    | EMPLOYEES        |
     -------------------------------------------------------------

     23 rows selected.


Format TYPICAL (valeur par défaut)
C'est le format par défaut. Quatre colonnes de stats en plus du format BASIC et ajout des zones "Predicate Information" et "Note".
     SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('9420hwvnq6jsj',0,'TYPICAL'));
     PLAN_TABLE_OUTPUT
     -----------------------------------------
     SQL_ID    9420hwvnq6jsj, child number 0
     -----------------------------------------
     select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME,
     E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID =
     D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = 50 OR D.DEPARTMENT_ID = 80)
     order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME

     Plan hash value: 2480766633

     -----------------------------------------------------------------------------------------------------
     | Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
     -----------------------------------------------------------------------------------------------------
     |   0 | SELECT STATEMENT        |            |        |        |      4 (100)|        |
     |   1 |  SORT ORDER BY            |            |     78 |  2964 |      4  (25)| 00:00:01 |
     |   2 |   NESTED LOOPS            |            |     78 |  2964 |      3   (0)| 00:00:01 |
     |   3 |    NESTED LOOPS         |            |     78 |  2964 |      3   (0)| 00:00:01 |
     |   4 |     INLIST ITERATOR        |            |        |        |         |        |
     |   5 |      TABLE ACCESS BY INDEX ROWID| DEPARTMENTS        |      2 |     32 |      2   (0)| 00:00:01 |
     |*  6 |       INDEX UNIQUE SCAN     | DEPT_ID_PK        |      2 |        |      1   (0)| 00:00:01 |
     |*  7 |     INDEX RANGE SCAN        | EMP_DEPARTMENT_IX |      7 |        |      0   (0)|        |
     |   8 |    TABLE ACCESS BY INDEX ROWID    | EMPLOYEES        |     39 |    858 |      1   (0)| 00:00:01 |
     -----------------------------------------------------------------------------------------------------

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

        6 - access(("D"."DEPARTMENT_ID"=50 OR "D"."DEPARTMENT_ID"=80))
        7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
            filter(("E"."DEPARTMENT_ID"=50 OR "E"."DEPARTMENT_ID"=80))

     Note
     -----
        - this is an adaptive plan

     34 rows selected.


Format ADAPTIVE
Notez que le plan hash value est le même que celui des plans affichés ci-dessus MAIS qu'il y a trois lignes en plus dans le plan d'exécution. On est en mode d'affichage ADAPTIVE et la note dit qu'il s'agit justement d'un Adaptive plan. Dans ce cas, les opérations abandonnées en cours de route par le CBO sont affichées dans ce mode avec, pour les identifier, un tiret devant leur id. Plus de lecture via Google :-)
     SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('9420hwvnq6jsj',0,'ADAPTIVE'));

     PLAN_TABLE_OUTPUT
     ----------------------------------------
     SQL_ID    9420hwvnq6jsj, child number 0
     ----------------------------------------
     select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME,
     E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID =
     D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = 50 OR D.DEPARTMENT_ID = 80)
     order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME

     Plan hash value: 2480766633

     ---------------------------------------------------------------------------------------------
     |   Id    | Operation                | Name        | Rows    | Bytes | Cost (%CPU)| Time    |
     ---------------------------------------------------------------------------------------------
     |     0 | SELECT STATEMENT            |            |    |    |     4 (100)|        |
     |     1 |  SORT ORDER BY            |            |    78 |  2964 |     4  (25)| 00:00:01 |
     |- *  2 |   HASH JOIN                |            |    78 |  2964 |     3   (0)| 00:00:01 |
     |     3 |    NESTED LOOPS            |            |    78 |  2964 |     3   (0)| 00:00:01 |
     |     4 |     NESTED LOOPS            |            |    78 |  2964 |     3   (0)| 00:00:01 |
     |-    5 |      STATISTICS COLLECTOR        |            |    |    |         |        |
     |     6 |    INLIST ITERATOR         |            |    |    |         |        |
     |     7 |     TABLE ACCESS BY INDEX ROWID| DEPARTMENTS    |     2 |    32 |     2   (0)| 00:00:01 |
     |  *  8 |      INDEX UNIQUE SCAN        | DEPT_ID_PK    |     2 |    |     1   (0)| 00:00:01 |
     |  *  9 |      INDEX RANGE SCAN         | EMP_DEPARTMENT_IX |     7 |    |     0   (0)|        |
     |    10 |     TABLE ACCESS BY INDEX ROWID   | EMPLOYEES     |    39 |   858 |     1   (0)| 00:00:01 |
     |- * 11 |    TABLE ACCESS FULL            | EMPLOYEES     |    39 |   858 |     1   (0)| 00:00:01 |
     ---------------------------------------------------------------------------------------------

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

        2 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
        8 - access(("D"."DEPARTMENT_ID"=50 OR "D"."DEPARTMENT_ID"=80))
        9 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
            filter(("E"."DEPARTMENT_ID"=50 OR "E"."DEPARTMENT_ID"=80))
       11 - filter(("E"."DEPARTMENT_ID"=50 OR "E"."DEPARTMENT_ID"=80))

     Note
     -----
        - this is an adaptive plan (rows marked '-' are inactive)

     39 rows selected.


Format ALL
Aucune colonne en plus mais des champs supplémentaires; pas hyper intéressantes de mon point de vue.
     SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('9420hwvnq6jsj',0,'ALL'));

     PLAN_TABLE_OUTPUT
     ------------------------------------------
     SQL_ID    9420hwvnq6jsj, child number 0
     ------------------------------------------
     select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME,
     E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID =
     D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = 50 OR D.DEPARTMENT_ID = 80)
     order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME

     Plan hash value: 2480766633
    
     -----------------------------------------------------------------------------------------------------
     | Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
     -----------------------------------------------------------------------------------------------------
     |   0 | SELECT STATEMENT        |            |        |        |      4 (100)|        |
     |   1 |  SORT ORDER BY            |            |     78 |  2964 |      4  (25)| 00:00:01 |
     |   2 |   NESTED LOOPS            |            |     78 |  2964 |      3   (0)| 00:00:01 |
     |   3 |    NESTED LOOPS         |            |     78 |  2964 |      3   (0)| 00:00:01 |
     |   4 |     INLIST ITERATOR        |            |        |        |         |        |
     |   5 |      TABLE ACCESS BY INDEX ROWID| DEPARTMENTS        |      2 |     32 |      2   (0)| 00:00:01 |
     |*  6 |       INDEX UNIQUE SCAN     | DEPT_ID_PK        |      2 |        |      1   (0)| 00:00:01 |
     |*  7 |     INDEX RANGE SCAN        | EMP_DEPARTMENT_IX |      7 |        |      0   (0)|        |
     |   8 |    TABLE ACCESS BY INDEX ROWID    | EMPLOYEES        |     39 |    858 |      1   (0)| 00:00:01 |
     -----------------------------------------------------------------------------------------------------

     Query Block Name / Object Alias (identified by operation id):
     -------------------------------------------------------------

        1 - SEL$1
        5 - SEL$1 / D@SEL$1
        6 - SEL$1 / D@SEL$1
        7 - SEL$1 / E@SEL$1
        8 - SEL$1 / E@SEL$1

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

        6 - access(("D"."DEPARTMENT_ID"=50 OR "D"."DEPARTMENT_ID"=80))
        7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
            filter(("E"."DEPARTMENT_ID"=50 OR "E"."DEPARTMENT_ID"=80))

     Column Projection Information (identified by operation id):
     -----------------------------------------------------------

        1 - (#keys=3) "D"."DEPARTMENT_NAME"[VARCHAR2,30], "E"."FIRST_NAME"[VARCHAR2,20],
            "E"."LAST_NAME"[VARCHAR2,25], "E"."DEPARTMENT_ID"[NUMBER,22], "E"."EMPLOYEE_ID"[NUMBER,22]
        2 - "D"."DEPARTMENT_ID"[NUMBER,22], "D"."DEPARTMENT_NAME"[VARCHAR2,30],
            "E"."EMPLOYEE_ID"[NUMBER,22], "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25],
            "E"."DEPARTMENT_ID"[NUMBER,22]
        3 - "D"."DEPARTMENT_ID"[NUMBER,22], "D"."DEPARTMENT_NAME"[VARCHAR2,30],
            "E".ROWID[ROWID,10], "E"."DEPARTMENT_ID"[NUMBER,22]
        4 - "D"."DEPARTMENT_ID"[NUMBER,22], "D"."DEPARTMENT_NAME"[VARCHAR2,30]
        5 - "D"."DEPARTMENT_ID"[NUMBER,22], "D"."DEPARTMENT_NAME"[VARCHAR2,30]
        6 - "D".ROWID[ROWID,10], "D"."DEPARTMENT_ID"[NUMBER,22]
        7 - "E".ROWID[ROWID,10], "E"."DEPARTMENT_ID"[NUMBER,22]
        8 - "E"."EMPLOYEE_ID"[NUMBER,22], "E"."FIRST_NAME"[VARCHAR2,20],
            "E"."LAST_NAME"[VARCHAR2,25]

     Note
     -----
        - this is an adaptive plan

     60 rows selected.


Format ADVANCED
Advanced permet d'avoir deux nouveaux blocs

• "OUTLINE DATA" donne des infos sur les paramètres de l'optimiseur, comme ALL_ROWS, FIRST_ROWS... utilisés pour cette exécution du SELECT
• "Query Blocks Registry"

     SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('9420hwvnq6jsj',0,'ADVANCED'));

     PLAN_TABLE_OUTPUT
     ----------------------------------------
     SQL_ID    9420hwvnq6jsj, child number 0
     ----------------------------------------
     select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME,
     E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID =
     D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = 50 OR D.DEPARTMENT_ID = 80)
     order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME

     Plan hash value: 2480766633

     -----------------------------------------------------------------------------------------------------
     | Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
     -----------------------------------------------------------------------------------------------------
     |   0 | SELECT STATEMENT        |            |        |        |      4 (100)|        |
     |   1 |  SORT ORDER BY            |            |     78 |  2964 |      4  (25)| 00:00:01 |
     |   2 |   NESTED LOOPS            |            |     78 |  2964 |      3   (0)| 00:00:01 |
     |   3 |    NESTED LOOPS         |            |     78 |  2964 |      3   (0)| 00:00:01 |
     |   4 |     INLIST ITERATOR        |            |        |        |         |        |
     |   5 |      TABLE ACCESS BY INDEX ROWID| DEPARTMENTS        |      2 |     32 |      2   (0)| 00:00:01 |
     |*  6 |       INDEX UNIQUE SCAN     | DEPT_ID_PK        |      2 |        |      1   (0)| 00:00:01 |
     |*  7 |     INDEX RANGE SCAN        | EMP_DEPARTMENT_IX |      7 |        |      0   (0)|        |
     |   8 |    TABLE ACCESS BY INDEX ROWID    | EMPLOYEES        |     39 |    858 |      1   (0)| 00:00:01 |
     -----------------------------------------------------------------------------------------------------

     Query Block Name / Object Alias (identified by operation id):
     -------------------------------------------------------------

        1 - SEL$1
        5 - SEL$1 / D@SEL$1
        6 - SEL$1 / D@SEL$1
        7 - SEL$1 / E@SEL$1
        8 - SEL$1 / E@SEL$1

     Outline Data
     -------------

       /*+
           BEGIN_OUTLINE_DATA
           IGNORE_OPTIM_EMBEDDED_HINTS
           OPTIMIZER_FEATURES_ENABLE('19.1.0')
           DB_VERSION('19.1.0')
           ALL_ROWS
           OUTLINE_LEAF(@"SEL$1")
           INDEX_RS_ASC(@"SEL$1" "D"@"SEL$1" ("DEPARTMENTS"."DEPARTMENT_ID"))
           INDEX(@"SEL$1" "E"@"SEL$1" ("EMPLOYEES"."DEPARTMENT_ID"))
           LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
           USE_NL(@"SEL$1" "E"@"SEL$1")
           NLJ_BATCHING(@"SEL$1" "E"@"SEL$1")
           END_OUTLINE_DATA
       */

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

        6 - access(("D"."DEPARTMENT_ID"=50 OR "D"."DEPARTMENT_ID"=80))
        7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
            filter(("E"."DEPARTMENT_ID"=50 OR "E"."DEPARTMENT_ID"=80))

     Column Projection Information (identified by operation id):
     -----------------------------------------------------------

        1 - (#keys=3) "D"."DEPARTMENT_NAME"[VARCHAR2,30], "E"."FIRST_NAME"[VARCHAR2,20],
            "E"."LAST_NAME"[VARCHAR2,25], "E"."DEPARTMENT_ID"[NUMBER,22], "E"."EMPLOYEE_ID"[NUMBER,22]
        2 - "D"."DEPARTMENT_ID"[NUMBER,22], "D"."DEPARTMENT_NAME"[VARCHAR2,30],
            "E"."EMPLOYEE_ID"[NUMBER,22], "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25],
            "E"."DEPARTMENT_ID"[NUMBER,22]
        3 - "D"."DEPARTMENT_ID"[NUMBER,22], "D"."DEPARTMENT_NAME"[VARCHAR2,30],
            "E".ROWID[ROWID,10], "E"."DEPARTMENT_ID"[NUMBER,22]
        4 - "D"."DEPARTMENT_ID"[NUMBER,22], "D"."DEPARTMENT_NAME"[VARCHAR2,30]
        5 - "D"."DEPARTMENT_ID"[NUMBER,22], "D"."DEPARTMENT_NAME"[VARCHAR2,30]
        6 - "D".ROWID[ROWID,10], "D"."DEPARTMENT_ID"[NUMBER,22]
        7 - "E".ROWID[ROWID,10], "E"."DEPARTMENT_ID"[NUMBER,22]
        8 - "E"."EMPLOYEE_ID"[NUMBER,22], "E"."FIRST_NAME"[VARCHAR2,20],
            "E"."LAST_NAME"[VARCHAR2,25]

     Note
     -----
        - this is an adaptive plan

     Query Block Registry:
     ---------------------

       <q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[D]]></t><s><![CDATA[SEL$1]]></s></h>
         <h><t><![CDATA[E]]></t><s><![CDATA[SEL$1]]></s></h></f></q>

     85 rows selected.



============================================================================================
Afficher plus de statistiques : statistics_level=all et gather_plan_statistics
============================================================================================
Le format ADVANCED permet d’afficher énormément d’informations, Id, Operation, Name, Rows, Bytes, Cost (%CPU), Time, en plus des nombreux blocs.

MAIS il y a mieux encore :-) Il est possible d'avoir des statistiques plus précises sur l'exécution du SELECT et voir si le CBO estime correctement les cardinalités :
-    statistics_level=all -- utile si on ne peut pas modifier l’ordre SQL en y ajoutant le hint gather_plan_statistics
-    gather_plan_statistics -- à utiliser si on a la main sur l'ordre SQL

L'objectif est de collecter le nombre de lignes retournées par chaque nœud du plan d'exécution. En comparant les colonnes A-ROWS (pour Actual Rows) et E-ROWS (pour Estimated Rows), on peut identifier si le CBO estime correctement ou non les cardinalités des phases du plan, ce qui est vital pour choisir le meilleur mode d'accès aux données.

Attention, l'utilisation de ces outils ralentit un peu l'ordre SQL puisque des stats supplémentaires sont collectées. Ils ne sont donc à utiliser que dans une étape de tuning.

A noter qu'avec ces outils, la colonne A-ROWS (nombre réel de lignes ) affiche des durées au dixième de seconde près alors que sans ces outils, le temps est limité à la seconde; c'est clairement insuffisant dans le monde informatique.

statistics_level=all
D'après la doc Oracle https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/STATISTICS_LEVEL.html#GUID-16B23F95-8644-407A-A6C8-E85CADFA61FF "STATISTICS_LEVEL specifies the level of collection for database and operating system statistics. The Oracle Database collects these statistics for a variety of purposes, including making self-management decisions. When the STATISTICS_LEVEL parameter is set to ALL, additional statistics are added to the set of statistics collected with the TYPICAL setting. The additional statistics are timed operating system statistics and plan execution statistics."

     SQL> ALTER SESSION SET STATISTICS_LEVEL=ALL;
     Session altered.

     SQL> show parameter statistics_level
     NAME                     TYPE     VALUE
     ------------------------------------ ----------- --
     client_statistics_level          string     TYPICAL
     statistics_level             string     ALL

Attention, je change l'appel de ma fonction (nous verrons pourquoi dans un autre article) et je vide la SHARED POOL car il y a, après X exécutions du même SELECT, plusieurs curseurs en mémoire pour le même plan d'exécution. Donc en flushant, j'élimine les plus anciens que je ne veux pas afficher. Cette astuce n'est surtout pas à faire en Production car cela va ralentir la base!
     SQL> alter system flush SHARED_POOL;
     System altered.

     SQL> select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME, E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID = D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = 50 OR D.DEPARTMENT_ID = 80) order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME;
     DEPARTMENT_NAME            DEPARTMENT_ID FIRST_NAME       LAST_NAME            EMPLOYEE_ID
     ------------------------------ ------------- -------------------- -------------------------
     Sales                      80 Alberto          Errazuriz                147
     ...
     Shipping                  50 Winston          Taylor                180

     79 rows selected.

Que voit-on? Plusieurs colonnes en plus : Starts, A-Rows, A-Time, Buffers, 0Mem, 1Mem, Used-Mem. Mais il y a eu aussi la disparition de plusieurs colonnes: Cost (%CPU) et Time; la colonne Time est-elle remplacé par A-Time? possible, à voir dans la doc. La colonne E-Rows remplace l'ancienne Rows si on en juge par le nombre de lignes, 78 dans les deux cas. La colonne Starts vous dit combien de fois l'opération a été exécuté; dans notre exemple, la table EMPLOYEES a été accédée 79 fois via l'opération "TABLE ACCESS BY INDEX ROWID".
     SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('9420hwvnq6jsj',0,'ALLSTATS LAST'));

     PLAN_TABLE_OUTPUT
     ----------------------------------------
     SQL_ID    9420hwvnq6jsj, child number 0
     ----------------------------------------
     select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME,
     E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID =
     D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = 50 OR D.DEPARTMENT_ID = 80)
     order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME

     Plan hash value: 2480766633
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |       1 |          |     79 |00:00:00.01 |        9 |       |       |      |
|   1 |  SORT ORDER BY            |            |       1 |       78 |     79 |00:00:00.01 |        9 | 11264 | 11264 |10240  (0)|
|   2 |   NESTED LOOPS            |            |       1 |       78 |     79 |00:00:00.01 |        9 |       |       |      |
|   3 |    NESTED LOOPS         |            |       1 |       78 |     79 |00:00:00.01 |        5 |       |       |      |
|   4 |     INLIST ITERATOR        |            |       1 |          |      2 |00:00:00.01 |        3 |       |       |      |
|   5 |      TABLE ACCESS BY INDEX ROWID| DEPARTMENTS        |       2 |        2 |      2 |00:00:00.01 |        3 |       |       |      |
|*  6 |       INDEX UNIQUE SCAN     | DEPT_ID_PK        |       2 |        2 |      2 |00:00:00.01 |        2 |       |       |      |
|*  7 |     INDEX RANGE SCAN        | EMP_DEPARTMENT_IX |       2 |        7 |     79 |00:00:00.01 |        2 |       |       |      |
|   8 |    TABLE ACCESS BY INDEX ROWID    | EMPLOYEES        |      79 |       39 |     79 |00:00:00.01 |        4 |       |       |      |
------------------------------------------------------------------------------------------------------------------------------------------

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

   6 - access(("D"."DEPARTMENT_ID"=50 OR "D"."DEPARTMENT_ID"=80))
   7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
       filter(("E"."DEPARTMENT_ID"=50 OR "E"."DEPARTMENT_ID"=80))

Note
-----
   - this is an adaptive plan


34 rows selected.


gather_plan_statistics
Pré-requis : il faut pouvoir modifier l'ordre SQL.
Si le paramètre STATISTICS_LEVEL est positionné à ALL, alors il n'est pas nécessaire d'utiliser ce hint.

L'intérêt de ce hint, par rapport au paramètre STATISTICS_LEVEL à ALL, c'est qu'on peut traiter un ordre SQL et un seul. Le problème avec STATISTICS_LEVEL, c'est qu'il s'applique à tous les ordres SQL de la session ou de la base selon qu'on ait fait un ALTER SESSION ou un ALTER SYSTEM.

     SQL> show parameter level
     NAME TYPE VALUE
     ---------------------------
     ...
     statistics_level string TYPICAL

     SQL> set feedback on sql_id

     SQL> select /*+ GATHER_PLAN_STATISTICS */ D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME, E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID = D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = 50 OR D.DEPARTMENT_ID = 80) order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME;

     DEPARTMENT_NAME DEPARTMENT_ID FIRST_NAME LAST_NAME EMPLOYEE_ID
     ------------------------------ ------------- -------------------- ------------------------- -----------
     Sales 80 Alberto Errazuriz 147
     ...
     Shipping 50 Winston Taylor 180

     79 rows selected.

     SQL_ID: 16w1adqsd8hnb

Attention au piège : le sql_id a changé, on est passé de 9420hwvnq6jsj à 16w1adqsd8hnb; dans l'ordre SQL. Comme on a ajouté les caractères /*+ GATHER_PLAN_STATISTICS */ dans le SELECT, la fonction de hashage génère un sql_id différent. On retrouve les mêmes colonnes qu'avec statistics_level=all.

     SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('16w1adqsd8hnb',0,'ALLSTATS LAST'));

     PLAN_TABLE_OUTPUT
     --------------------------------------
     SQL_ID 16w1adqsd8hnb, child number 0
     --------------------------------------
     select /*+ GATHER_PLAN_STATISTICS */ D.DEPARTMENT_NAME,
     E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME, E.EMPLOYEE_ID from
     employees E, departments D where E.DEPARTMENT_ID = D.DEPARTMENT_ID AND
     (D.DEPARTMENT_ID = 50 OR D.DEPARTMENT_ID = 80) order by
     D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME

     Plan hash value: 2480766633

     ---------------------------------------------------------------------------------------------------
     | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
     ---------------------------------------------------------------------------------------------------
     | 0 | SELECT STATEMENT | | 1 | | 79 |00:00:00.01 | 9 | | | |
     | 1 | SORT ORDER BY | | 1 | 78 | 79 |00:00:00.01 | 9 | 11264 | 11264 |10240 (0)|
     | 2 | NESTED LOOPS | | 1 | 78 | 79 |00:00:00.01 | 9 | | | |
     | 3 | NESTED LOOPS | | 1 | 78 | 79 |00:00:00.01 | 5 | | | |
     | 4 | INLIST ITERATOR | | 1 | | 2 |00:00:00.01 | 3 | | | |
     | 5 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 2 | 2 | 2 |00:00:00.01 | 3 | | | |
     |* 6 | INDEX UNIQUE SCAN | DEPT_ID_PK | 2 | 2 | 2 |00:00:00.01 | 2 | | | |
     |* 7 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 2 | 7 | 79 |00:00:00.01 | 2 | | | |
     | 8 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 79 | 39 | 79 |00:00:00.01 | 4 | | | |
     ---------------------------------------------------------------------------------------------------

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

     6 - access(("D"."DEPARTMENT_ID"=50 OR "D"."DEPARTMENT_ID"=80))
     7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
     filter(("E"."DEPARTMENT_ID"=50 OR "E"."DEPARTMENT_ID"=80))

     Note
     -----
     - this is an adaptive plan

     35 rows selected.


Dans les prochains articles sur DBMS_XPLAN.DISPLAY_CURSOR, nous verrons quelles sont les différentes façons d'appeler cette fonction et quels sont les pièges classiques.

Introduction
Vous est-il déjà arrivé de regarder, lors d'un export/import datapump, l'ordre de traitement des objets ? Si oui, quelle conclusion en avez-vous tiré : les objets sont exportés/importés selon un ordre bien précis ou bien Oracle traite cela de façon anarchique ?

Faisons un petit test pour essayer d'y voir plus clair.
 

Points d'attention
N/A.

Base de tests
Une base Oracle 19 multi-tenants.

Exemples

============================================================================================
Base de test
============================================================================================ 
Je suis dans une PDB, connecté comme SYS.
     [oracle@localhost ~]$ sqlplus sys@orcl as sysdba    
     SQL> show con_name
     CON_NAME
     ------------------------------
     ORCL

Création de tables et séquences dans les schémas SYSTEM et HR; attention à garder les guillemets autour des noms pour ce test.
     SQL> CREATE TABLE SYSTEM."TA"(id number, id02 number);
     SQL> CREATE TABLE SYSTEM."Tc"(name varchar2(50 char));
     SQL> CREATE TABLE SYSTEM."TF"(id number(6), name varchar2(50 char));
     SQL> CREATE SEQUENCE SYSTEM."seqA";
     SQL> CREATE SEQUENCE SYSTEM."seqb";
     SQL> CREATE SEQUENCE SYSTEM."seqD";
     SQL> BEGIN for i in 1..1000 loop INSERT INTO SYSTEM."TA" (id) values (i); end loop; END; /
     SQL> BEGIN for i in 1..10000 loop INSERT INTO SYSTEM."Tc" values('DURAND'||to_char(i)); end loop; END; /
     SQL> BEGIN for i in 1..100000 loopINSERT INTO SYSTEM."TF" values(i, 'DURAND'||to_char(i)); end loop; END; /
     SQL> commit;
        
     SQL> CREATE TABLE HR."TAA"(id number);
     SQL> CREATE TABLE HR."TBb"(name varchar2(50 char));
     SQL> CREATE TABLE HR."TXX"(id number(10), name varchar2(50 char));
     SQL> BEGIN for i in 1..500000 loop INSERT INTO HR."TAA" values(i); end loop; END; /
     SQL> BEGIN for i in 1..800000 loop INSERT INTO HR."TBb" values('DURAND'||to_char(i)); end loop; END; /
     SQL> BEGIN for i in 1..1000000 loop INSERT INTO HR."TXX" values(i, 'DURAND'||to_char(i)); end loop; END; /
     SQL> commit;

Quelle est la taille des segments créés ? Voyons ça par deux tris, le duo OWNER et SEGMENT_NAME pour le premier, et la taille pour le second. Attention aux minuscules, qui sont plus grandes que les majuscules en langage américain : Tc est bien affichée après TF.
     SQL> show parameter nls_
     NAME                     TYPE     VALUE
     ------------------------------------ ----------- ------------------------------
     ...
     nls_language                 string     AMERICAN
     nls_territory                 string     AMERICA
     ...

     SQL> SELECT OWNER, SEGMENT_TYPE, SEGMENT_NAME, sum(BYTES)/(1024*1024) AS "TAILLE Mo" FROM DBA_SEGMENTS WHERE SEGMENT_NAME LIKE ('T_') OR SEGMENT_NAME LIKE ('T__') GROUP BY OWNER, SEGMENT_TYPE, SEGMENT_NAME ORDER BY OWNER, SEGMENT_NAME ;
     OWNER       SEGMENT_TYPE       SEGMENT_NA  TAILLE Mo
     ---------- ------------------ ---------- ----------
     HR       TABLE          TAA          7
     HR       TABLE          TBb         16
     HR       TABLE          TXX         26
     SYSTEM       TABLE          TA          .0625
     SYSTEM       TABLE          TF          3
     SYSTEM       TABLE          Tc          .1875
     6 rows selected.

     SQL> SELECT OWNER, SEGMENT_TYPE, SEGMENT_NAME, sum(BYTES)/(1024*1024) AS "TAILLE Mo" FROM DBA_SEGMENTS WHERE SEGMENT_NAME LIKE ('T_') OR SEGMENT_NAME LIKE ('T__') GROUP BY OWNER, SEGMENT_TYPE, SEGMENT_NAME ORDER BY "TAILLE Mo";
     OWNER       SEGMENT_TYPE       SEGMENT_NA  TAILLE Mo
     ---------- ------------------ ---------- ----------
     SYSTEM       TABLE          TA          .0625
     SYSTEM       TABLE          Tc          .1875
     SYSTEM       TABLE          TF          3
     HR       TABLE          TAA          7
     HR       TABLE          TBb         16
     HR       TABLE          TXX         26
     6 rows selected.


============================================================================================
Export/Import datapump
============================================================================================
J'exporte les deux schémas de test, à savoir SYSTEM et HR. Dans ce qui est affiché à l'écran, l'ordre de traitement est celui alphabétique des schémas puis des tables, mais pas celui du nombre de lignes des tables ni de leur taille. À noter que les séquences exportées ne sont pas listées dans le fichier de log.
     [oracle@localhost ~]$ expdp SCHEMAS=SYSTEM, HR DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXP_SCHEMAS_20220221_test.dmp LOGFILE=EXP_SCHEMAS_20220221_test.log
     Export: Release 19.0.0.0.0 - Production on Mon Feb 21 10:32:15 2022
     Version 19.3.0.0.0
     ...
     . . exported "HR"."TAA"                                  4.282 MB  500000 rows
     . . exported "HR"."TBb"                                  12.86 MB  800000 rows
     . . exported "HR"."TXX"                                  20.86 MB 1000000 rows
     . . exported "SYSTEM"."TA"                               13.17 KB    1000 rows
     . . exported "SYSTEM"."TF"                               1.986 MB  100000 rows
     . . exported "SYSTEM"."Tc"                               150.4 KB   10000 rows
     Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
     ******************************************************************************

Quid de l'ordre lors de l'import ? L'ordre n'est pas le même que lors de l'export : les tables de SYSTEM sont traitées avant et après les tables de HR, sans respecter un ordre quelconque : alphabétique, nombre de lignes, taille. Les tables de HR sont importées selon le même ordre que lors de l'export. En outre, lors de l'import, on voit le nom des séquences traitées, alors que ce n'était pas le cas lors de l'export. Et que voit-on ? Que l'ordre alphabétique n'est pas respecté ; on devrait avoir seqA, seqD, seqb puisque seqb est entièrement en minuscules.
     [oracle@localhost ~]$ impdp SYSTEM DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXP_SCHEMAS_20220221_test.dmp logfile=IMP_SCHEMAS_20220222_test.log sqlfile=IMP_SCHEMAS_20220222_sqlfile.sql
     ...
     CREATE SEQUENCE  "SYSTEM"."seqA"
     CREATE SEQUENCE  "SYSTEM"."seqb"
     CREATE SEQUENCE  "SYSTEM"."seqD"
     CREATE TABLE "SYSTEM"."TF"
     CREATE TABLE "HR"."TAA"
     CREATE TABLE "HR"."TBb"
     CREATE TABLE "HR"."TXX"
     CREATE TABLE "SYSTEM"."TA"
     CREATE TABLE "SYSTEM"."Tc"
     ...

Tri par nom de schéma et des tables
C'est cet ordre qui est retenu pour l'export.
     SQL> SELECT OWNER, OBJECT_NAME, to_char(CREATED, 'DD/MM/YYYY HH24:MI:SSSS') FROM DBA_OBJECTS WHERE OBJECT_TYPE IN ('TABLE', 'SEQUENCE', 'USER') AND (OWNER = 'HR' OR (OWNER = 'SYSTEM' AND OBJECT_NAME LIKE ('T_'))) ORDER BY OWNER, OBJECT_NAME;
     OWNER        OBJECT_NAME               TO_CHAR(CREATED,'DD/M
     --------------- ------------------------------ ---------------------
     HR        TAA                   21/02/2022 10:26:5555
     HR        TBb                   21/02/2022 10:27:0202
     HR        TXX                   21/02/2022 10:29:5050
     SYSTEM        TA                   21/02/2022 10:25:4848
     SYSTEM        TF                   21/02/2022 10:25:5858
     SYSTEM        Tc                   21/02/2022 10:25:5454
     20 rows selected.

Tri sur date de création
Ce n'est pas l'ordre de l'import datapump.
     SQL> SELECT OWNER, OBJECT_NAME, to_char(CREATED, 'DD/MM/YYYY HH24:MI:SSSS') FROM DBA_OBJECTS WHERE OBJECT_TYPE IN ('TABLE', 'SEQUENCE', 'USER') AND (OWNER = 'HR' OR (OWNER = 'SYSTEM' AND OBJECT_NAME LIKE ('T_'))) ORDER BY OWNER, 3;
     OWNER        OBJECT_NAME               TO_CHAR(CREATED,'DD/M
     --------------- ------------------------------ ---------------------
     HR        TAA                   21/02/2022 10:26:5555
     HR        TBb                   21/02/2022 10:27:0202
     HR        TXX                   21/02/2022 10:29:5050
     SYSTEM        TA                   21/02/2022 10:25:4848
     SYSTEM        Tc                   21/02/2022 10:25:5454
     SYSTEM        TF                   21/02/2022 10:25:5858

Conclusion
Lors de l'export, un ordre semble être retenu : traiter les objets des schémas puis les tables selon l'ordre alphabétique. Lors de l'import, aucun ordre ne semble retenu mais, me direz-vous, cela est-il important ? A priori non puisqu'Oracle, selon mon test, importe n'importe comment les objets, notamment les tables. Et les contraintes d'intégrité Foreign Key? Elles, elles sont importantes, on ne peut pas importer n'importe comment deux tables qui ont des contraintes de clés étrangères sous risque d'INSERTs qui échouent. Comment fait Oracle ? C'est simple, les contraintes sont désactivées puis réactivées à la fin de l'import :-)  C'est tout bête, mais il fallait y penser.

Bref, en conclusion de ce test, aucun ordre ne semble respecté lors d'un import datapump.

Introduction
Vous avez dû lire à droite et à gauche qu'il ne fallait JAMAIS JAMAIS créer quelque chose dans le schéma SYS. Pour quelle raison ? Il parait que ça pollue le dictionnaire de données... OK, mais... et donc ? Ca pollue, OK mais, concrètement, ça se manifeste comment sous Oracle? Si je fais un SHUTDOWN IMMEDIATE, j'aurais un warning ? si je fais un STARTUP, quelque chose de spécifique sera écrit dans le fichier alert.log ?

Cette notion de pollution n'a aucun sens; pour rappel, quand on crée une base Oracle 19c, vous avez 80 000 objets dans DBA_OBJECTS donc, avant de vraiment polluer un schéma de 80 000 objets avec les vôtres, il va falloir créer des milliers et des milliers de tables, d'index...

La vraie raison est que, lors d'un export datapump FULL de la base, le schéma SYS n'est pas exporté (sauf une vingtaine de tables d'administration) et donc aucune de vos tables applicatives ne sera copiée dans l'autre base de données. De la sorte, impossible de rafraîchir la pré-prod avec la prod... et comment faire ensuite pour relocaliser vos tables dans un autre schéma que SYS pour corriger le problème? Impossible à faire simplement puisque la technique d'Oracle, pour relocaliser les objets du schéma U1 dans le schéma U2, est de faire un export datapump du schéma U1 et, lors de l'import, de faire un REMAP_SCHEMA de U1 dans U2. Mais, comme lors de l'export, le schéma SYS n'est pas exporté, impossible ensuite de faire un import datapump avec l'option REMAP_SCHEMA pour cette opération! bref, vous êtes dans les ennuis jusqu'au cou, et méchamment.

Et c'est justement pour cette raison qu'il est intéressant de créer une table dans SYS : cette table ne sera jamais exportée ! Quel intérêt ? Je crée une ou plusieurs tables pour des tests ET je ne veux pas que ces tables soient exportées par datapump car mes tests auront lieu uniquement dans un environnement précis, qui est en général la prod. Quand je supprimerais ces tables, je n'aurai pas à vérifier si elles ont été exportées dans les bases de test, de recette, de développement, de pré-prod etc etc etc puisque tout le monde veut des données de la prod via datapump! Bref, nettoyer toutes ces bases prendra un certain temps, j'aurais vraiment polluer des bases avec une ou plusieurs tables que je n'utiliserai pas dans ces environnement et donc, pour éviter cela, je décide de créer ma table dans SYS.

Mais, me direz-vous, est-ce qu'il n'existe pas un autre schéma moins sensible que SYS pour ça? Peut-être que d'autres schémas, plus exotiques, trouvés dans DBA_USERS, comme ANONYMOUS, APEX_PUBLIC_USER, APPQOSSYS, AUDSYS, CTXSYS etc etc feraient l'affaire mais leur statut est EXPIRED & LOCKED à la création de la base donc je préfère ne pas y toucher et, ne connaissant pas vraiment leur rôle, autant être prudent.

On me rétorquera alors : et le schéma SYSTEM ou bien le tablespace SYSAUX, on ne peut pas les utiliser? Eh bien c'est l'objet de cet article et on va voir que le schéma SYSTEM et que le tablespace SYSAUX sont eux aussi exportés par datapump donc ce n'est pas une solution de remplacement.

 

Points d'attention
Le test est fait dans une PDB, pas dans le CDB$ROOT.

Base de tests
Une base Oracle 19c multi-tenants.

Exemples

============================================================================================
Environnement de test
============================================================================================ 
Je suis dans une PDB, connecté comme SYS.
     SQL> show user con_name
     USER is "SYS"
     CON_NAME
     ------------------------------
     ORCL

Création d'une table dans le schéma SYSTEM et insertion d'un élément.
     SQL> CREATE TABLE SYSTEM.T_SYSTEM(id number);
     Table created.

     SQL> select owner from dba_tables where table_name = 'T_SYSTEM';
     OWNER
     -------------
     SYSTEM

     SQL> INSERT INTO SYSTEM.T_SYSTEM values(1);
     1 row created.

     SQL> commit;
     Commit complete.

Création d'un user dans le tablespace SYSAUX, d'une table et d'un enregistrement.
     SQL> CREATE USER ZZSYSAUX IDENTIFIED BY ZZSYSAUX DEFAULT TABLESPACE SYSAUX;
     User created.

     SQL> CREATE TABLE ZZSYSAUX.T_TBSSYSAUX(id number, name  VARCHAR2(50 CHAR));
     Table created.

Vérifier que la table T_TBSSYSAUX est bien dans le tba SYSAUX et que son propriétaire est ZZSYSAUX.
     SQL> SELECT TABLESPACE_name, owner FROM DBA_TABLES WHERE TABLE_NAME='T_TBSSYSAUX';
     TABLESPACE_NAME        OWNER
     ----------------  ------------
     SYSAUX                ZZSYSAUX

Tiens, l'INSERT est KO... Ah oui, même si c'est le user SYS qui exécute l'INSERT, il se fait dans une table dont le propriétaire est
ZZSYSAUX et celui-ci n'a eu, lors de sa création, aucun quota sur le tablespace SYSAUX, d'où l'échec. Je donne à celui-ci le droit dba (juste pour ce test) et là... tout est OK :-)
     SQL> INSERT INTO ZZSYSAUX.T_TBSSYSAUX values(1, 'DUPONT');
     INSERT INTO ZZSYSAUX.T_TBSSYSAUX values(1, 'DUPONT')
                     *
     ERROR at line 1:
     ORA-01950: no privileges on tablespace 'SYSAUX'

     SQL> grant dba to ZZSYSAUX;
     Grant succeeded.

     SQL> INSERT INTO ZZSYSAUX.T_TBSSYSAUX values(1, 'DUPONT');
     1 row created.

     SQL> commit;
     Commit complete.


============================================================================================
Exports datapump des schémas
============================================================================================
Je fais un export datapump du schéma SYSTEM.
     [oracle@localhost ~]$ expdp SYS SCHEMAS=SYSTEM DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXP_SCHEMA_SYSTEM.dmp LOGFILE=EXP_SCHEMA_SYSTEM.log
     Export: Release 19.0.0.0.0 - Production on Sun Feb 13 15:29:20 2022
     Version 19.3.0.0.0
     Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
     Password:
     UDE-28009: operation generated ORACLE error 28009
     ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
     Username: sys as sysdba
     Password:
     Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
     FLASHBACK automatically enabled to preserve database integrity.
     Starting "SYS"."SYS_EXPORT_SCHEMA_01":  sys/******** AS SYSDBA SCHEMAS=SYSTEM DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXP_SCHEMA_SYSTEM.dmp LOGFILE=EXP_SCHEMA_SYSTEM.log
     Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
     Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
     Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
     Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
     Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
     Processing object type SCHEMA_EXPORT/TABLE/TABLE

Ma table de test est exportée.
     . . exported "SYSTEM"."T_SYSTEM"                         5.054 KB       1 rows
     Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
     ******************************************************************************
     Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
       /u01/app/oracle/admin/orclcdb/dpdump/8A34DEF16CD55C76E0530100007F040C/EXP_SCHEMA_SYSTEM.dmp
     Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Feb 13 15:30:17 2022 elapsed 0 00:00:39


============================================================================================
Exports datapump du tablespace SYSAUX
============================================================================================
Export datapump du tablespace SYSAUX.
     [oracle@localhost ~]$ expdp SYS TABLESPACES=SYSAUX DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXP_TBS_SYSAUX.dmp LOGFILE=EXP_TBS_SYSAUX.log
     Export: Release 19.0.0.0.0 - Production on Sun Feb 13 15:31:58 2022
     Version 19.3.0.0.0
     Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
     Password:
     UDE-28009: operation generated ORACLE error 28009
     ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
     Username: sys as sysdba
     Password:
     Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
     Starting "SYS"."SYS_EXPORT_TABLESPACE_01":  sys/******** AS SYSDBA TABLESPACES=SYSAUX DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXP_TBS_SYSAUX.dmp LOGFILE=EXP_TBS_SYSAUX.log
     Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
     Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
     Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
     Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
     Processing object type TABLE_EXPORT/TABLE/TABLE
     Processing object type TABLE_EXPORT/TABLE/COMMENT
     Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
     Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
     Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
     Processing object type TABLE_EXPORT/TABLE/TRIGGER
     . . exported "ORDS_METADATA"."ORDS_OBJECTS"              11.27 KB       7 rows
     . . exported "ORDS_METADATA"."SEC_PRIVILEGES"            10.01 KB      12 rows
     . . exported "ORDS_METADATA"."ORDS_SCHEMAS"              9.531 KB       3 rows
     . . exported "ORDS_METADATA"."SEC_PRIVILEGE_ROLES"       9.140 KB      22 rows
     . . exported "ORDS_METADATA"."SEC_ROLES"                 8.695 KB      15 rows
     . . exported "ORDS_METADATA"."SEC_KEYS"                  8.171 KB       2 rows
     . . exported "ORDS_METADATA"."SEC_PRIVILEGE_MAPPINGS"    8.101 KB       1 rows
     . . exported "ORDS_METADATA"."ORDS_URL_MAPPINGS"         7.757 KB       3 rows
     . . exported "ORDS_METADATA"."ORDS_SCHEMA_VERSION"       5.953 KB       1 rows
     . . exported "ORDS_METADATA"."OAUTH_APPROVALS"               0 KB       0 rows
     . . exported "ORDS_METADATA"."OAUTH_APPROVAL_PRIVS"          0 KB       0 rows
     . . exported "ORDS_METADATA"."OAUTH_CLIENTS"                 0 KB       0 rows
     . . exported "ORDS_METADATA"."OAUTH_CLIENT_PRIVILEGES"       0 KB       0 rows
     . . exported "ORDS_METADATA"."OAUTH_CLIENT_ROLES"            0 KB       0 rows
     . . exported "ORDS_METADATA"."OAUTH_PENDING_APPROVALS"       0 KB       0 rows
     . . exported "ORDS_METADATA"."OAUTH_SESSIONS"                0 KB       0 rows
     . . exported "ORDS_METADATA"."ORDS_HANDLERS"                 0 KB       0 rows
     . . exported "ORDS_METADATA"."ORDS_MODULES"                  0 KB       0 rows
     . . exported "ORDS_METADATA"."ORDS_PARAMETERS"               0 KB       0 rows
     . . exported "ORDS_METADATA"."ORDS_TEMPLATES"                0 KB       0 rows
     . . exported "ORDS_METADATA"."ORDS_WORKSPACE_SCHEMAS"        0 KB       0 rows
     . . exported "ORDS_METADATA"."SEC_AUTHENTICATORS"            0 KB       0 rows
     . . exported "ORDS_METADATA"."SEC_ORIGINS_ALLOWED_MODULES"      0 KB       0 rows
     . . exported "ORDS_METADATA"."SEC_PRIVILEGE_AUTHS"           0 KB       0 rows
     . . exported "ORDS_METADATA"."SEC_PRIVILEGE_MODULES"         0 KB       0 rows
     . . exported "ORDS_METADATA"."SEC_ROLE_MAPPINGS"             0 KB       0 rows
     . . exported "ORDS_METADATA"."SEC_SESSIONS"                  0 KB       0 rows

La table de test est exportée.
     . . exported "ZZSYSAUX"."T_TBSSYSAUX"                    5.492 KB       1 rows
     Master table "SYS"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
     ******************************************************************************
     Dump file set for SYS.SYS_EXPORT_TABLESPACE_01 is:
       /u01/app/oracle/admin/orclcdb/dpdump/8A34DEF16CD55C76E0530100007F040C/EXP_TBS_SYSAUX.dmp
     Job "SYS"."SYS_EXPORT_TABLESPACE_01" successfully completed at Sun Feb 13 15:32:47 2022 elapsed 0 00:00:40


============================================================================================
Exports datapump Full Database
============================================================================================
Dans cet article précédent, "Datapump : le schéma SYS n'est jamais exporté par Oracle - Datapump: the SYS schema is never exported by Oracle", je montrais qu'une table créée dans le schéma SYS n'était pas exportée; seuls quelques objets d'administration le sont mais ils ne sont pas applicatifs.
     [oracle@localhost ~]$ expdp SYS FULL=y DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXP_FULL.dmp LOGFILE=EXP_FULL.log
     Export: Release 19.0.0.0.0 - Production on Sun Feb 13 15:34:15 2022
     Version 19.3.0.0.0
     Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
     Password:
     UDE-01005: operation generated ORACLE error 1005
     ORA-01005: null password given; logon denied
     Username: sys as sysdba
     Password:
     Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
     FLASHBACK automatically enabled to preserve database integrity.
     Starting "SYS"."SYS_EXPORT_FULL_01":  sys/******** AS SYSDBA FULL=y DIRECTORY=DATA_PUMP_DIR DUMPFILE=EXP_FULL.dmp LOGFILE=EXP_FULL.log
     Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
     Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
     Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
     Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
     Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
     Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
     Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
     Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
     Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
     Processing object type DATABASE_EXPORT/STATISTICS/MARKER
     Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
     Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
     Processing object type DATABASE_EXPORT/TABLESPACE
     Processing object type DATABASE_EXPORT/PROFILE
     Processing object type DATABASE_EXPORT/SCHEMA/USER
     Processing object type DATABASE_EXPORT/ROLE
     Processing object type DATABASE_EXPORT/RADM_FPTM
     Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
     Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
     Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
     Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
     Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
     Processing object type DATABASE_EXPORT/PROXY
     Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
     Processing object type DATABASE_EXPORT/RESOURCE_COST
     Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
     Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
     Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
     Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
     Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
     Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
     Processing object type DATABASE_EXPORT/SCHEMA/TYPE/INC_TYPE
     Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
     Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
     Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
     ORA-39127: unexpected error from call to TAG: SCHEDULER Calling: SYS.DBMS_SCHED_JOB_EXPORT.GRANT_EXP obj: SYS.ORA$AT_OS_OPT_SY_307 - SCHEDULER JOB
     ORA-01031: insufficient privileges
     ORA-06512: at "SYS.DBMS_SCHED_MAIN_EXPORT", line 2704
     ORA-06512: at "SYS.DBMS_SCHED_JOB_EXPORT", line 53
     ORA-06512: at line 1
     ORA-06512: at "SYS.DBMS_SCHED_MAIN_EXPORT", line 2704
     ORA-06512: at "SYS.DBMS_SCHED_JOB_EXPORT", line 53
     ORA-06512: at line 1
     ORA-06512: at "SYS.DBMS_METADATA", line 11144
     ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
     Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
     Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
     Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
     Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
     Processing object type DATABASE_EXPORT/SCHEMA/XMLSCHEMA/XMLSCHEMA
     Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
     Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
     Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER
     Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
     Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
     Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
     Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/CODE_BASE_GRANT
     Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
     Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
     Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
     Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
     Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
     Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
     Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
     Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT
     Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_BODY
     Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
     Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
     Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
     Processing object type DATABASE_EXPORT/SCHEMA/JAVA_SOURCE/JAVA_SOURCE
     Processing object type DATABASE_EXPORT/SCHEMA/JAVA_CLASS/JAVA_CLASS
     Processing object type DATABASE_EXPORT/SCHEMA/JAVA_CLASS/GRANT/OWNER_GRANT/OBJECT_GRANT
     Processing object type DATABASE_EXPORT/SCHEMA/JAVA_RESOURCE/JAVA_RESOURCE
     Processing object type DATABASE_EXPORT/SCHEMA/JAVA_RESOURCE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
     Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/BITMAP_INDEX/INDEX
     Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/INDEX
     Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
     Processing object type DATABASE_EXPORT/SCHEMA/VIEW/TRIGGER
     Processing object type DATABASE_EXPORT/SCHEMA/MATERIALIZED_VIEW
     Processing object type DATABASE_EXPORT/SCHEMA/DIMENSION
     Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
     Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE
     Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ
     Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
     Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
     Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
     Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
     . . exported "SYS"."KU$_USER_MAPPING_VIEW"               6.492 KB      63 rows
     . . exported "AUDSYS"."AUD$UNIFIED":"SYS_P221"           648.6 KB    1044 rows
     . . exported "AUDSYS"."AUD$UNIFIED":"SYS_P601"           53.72 KB       8 rows
     . . exported "AUDSYS"."AUD$UNIFIED":"SYS_P681"           53.63 KB       8 rows
     . . exported "AUDSYS"."AUD$UNIFIED":"SYS_P621"           52.22 KB       4 rows
     . . exported "AUDSYS"."AUD$UNIFIED":"SYS_P721"           51.80 KB       3 rows
     . . exported "AUDSYS"."AUD$UNIFIED":"SYS_P261"           51.37 KB       1 rows
     . . exported "AUDSYS"."AUD$UNIFIED":"SYS_P401"           51.25 KB       1 rows
     . . exported "SYS"."AUD$"                                26.48 KB      22 rows
     . . exported "SYSTEM"."REDO_DB"                          25.59 KB       1 rows
     . . exported "WMSYS"."WM$WORKSPACES_TABLE$"              12.10 KB       1 rows
     . . exported "WMSYS"."WM$HINT_TABLE$"                    9.984 KB      97 rows
     . . exported "LBACSYS"."OLS$INSTALLATIONS"               6.960 KB       2 rows
     . . exported "WMSYS"."WM$WORKSPACE_PRIV_TABLE$"          7.078 KB      11 rows
     . . exported "SYS"."DAM_CONFIG_PARAM$"                   6.531 KB      14 rows
     . . exported "SYS"."TSDP_SUBPOL$"                        6.328 KB       1 rows
     . . exported "WMSYS"."WM$NEXTVER_TABLE$"                 6.375 KB       1 rows
     . . exported "LBACSYS"."OLS$PROPS"                       6.234 KB       5 rows
     . . exported "WMSYS"."WM$ENV_VARS$"                      6.015 KB       3 rows
     . . exported "SYS"."TSDP_PARAMETER$"                     5.953 KB       1 rows
     . . exported "SYS"."TSDP_POLICY$"                        5.921 KB       1 rows
     . . exported "WMSYS"."WM$VERSION_HIERARCHY_TABLE$"       5.984 KB       1 rows
     . . exported "WMSYS"."WM$EVENTS_INFO$"                   5.812 KB      12 rows
     . . exported "LBACSYS"."OLS$AUDIT_ACTIONS"               5.757 KB       8 rows
     . . exported "LBACSYS"."OLS$DIP_EVENTS"                  5.539 KB       2 rows
     . . exported "AUDSYS"."AUD$UNIFIED":"AUD_UNIFIED_P0"         0 KB       0 rows
     . . exported "AUDSYS"."AUD$UNIFIED":"SYS_P761"           52.40 KB       4 rows
     . . exported "LBACSYS"."OLS$AUDIT"                           0 KB       0 rows
     . . exported "LBACSYS"."OLS$COMPARTMENTS"                    0 KB       0 rows
     . . exported "LBACSYS"."OLS$DIP_DEBUG"                       0 KB       0 rows
     . . exported "LBACSYS"."OLS$GROUPS"                          0 KB       0 rows
     . . exported "LBACSYS"."OLS$LAB"                             0 KB       0 rows
     . . exported "LBACSYS"."OLS$LEVELS"                          0 KB       0 rows
     . . exported "LBACSYS"."OLS$POL"                             0 KB       0 rows
     . . exported "LBACSYS"."OLS$POLICY_ADMIN"                    0 KB       0 rows
     . . exported "LBACSYS"."OLS$POLS"                            0 KB       0 rows
     . . exported "LBACSYS"."OLS$POLT"                            0 KB       0 rows
     . . exported "LBACSYS"."OLS$PROFILE"                         0 KB       0 rows
     . . exported "LBACSYS"."OLS$PROFILES"                        0 KB       0 rows
     . . exported "LBACSYS"."OLS$PROG"                            0 KB       0 rows
     . . exported "LBACSYS"."OLS$SESSINFO"                        0 KB       0 rows
     . . exported "LBACSYS"."OLS$USER"                            0 KB       0 rows
     . . exported "LBACSYS"."OLS$USER_COMPARTMENTS"               0 KB       0 rows
     . . exported "LBACSYS"."OLS$USER_GROUPS"                     0 KB       0 rows
     . . exported "LBACSYS"."OLS$USER_LEVELS"                     0 KB       0 rows
     . . exported "SYS"."DAM_CLEANUP_EVENTS$"                     0 KB       0 rows
     . . exported "SYS"."DAM_CLEANUP_JOBS$"                       0 KB       0 rows
     . . exported "SYS"."TSDP_ASSOCIATION$"                       0 KB       0 rows
     . . exported "SYS"."TSDP_CONDITION$"                         0 KB       0 rows
     . . exported "SYS"."TSDP_FEATURE_POLICY$"                    0 KB       0 rows
     . . exported "SYS"."TSDP_PROTECTION$"                        0 KB       0 rows
     . . exported "SYS"."TSDP_SENSITIVE_DATA$"                    0 KB       0 rows
     . . exported "SYS"."TSDP_SENSITIVE_TYPE$"                    0 KB       0 rows
     . . exported "SYS"."TSDP_SOURCE$"                            0 KB       0 rows
     . . exported "SYSTEM"."REDO_LOG"                             0 KB       0 rows
     . . exported "WMSYS"."WM$BATCH_COMPRESSIBLE_TABLES$"         0 KB       0 rows
     . . exported "WMSYS"."WM$CONSTRAINTS_TABLE$"                 0 KB       0 rows
     . . exported "WMSYS"."WM$CONS_COLUMNS$"                      0 KB       0 rows
     . . exported "WMSYS"."WM$LOCKROWS_INFO$"                     0 KB       0 rows
     . . exported "WMSYS"."WM$MODIFIED_TABLES$"                   0 KB       0 rows
     . . exported "WMSYS"."WM$MP_GRAPH_WORKSPACES_TABLE$"         0 KB       0 rows
     . . exported "WMSYS"."WM$MP_PARENT_WORKSPACES_TABLE$"        0 KB       0 rows
     . . exported "WMSYS"."WM$NESTED_COLUMNS_TABLE$"              0 KB       0 rows
     . . exported "WMSYS"."WM$RESOLVE_WORKSPACES_TABLE$"          0 KB       0 rows
     . . exported "WMSYS"."WM$RIC_LOCKING_TABLE$"                 0 KB       0 rows
     . . exported "WMSYS"."WM$RIC_TABLE$"                         0 KB       0 rows
     . . exported "WMSYS"."WM$RIC_TRIGGERS_TABLE$"                0 KB       0 rows
     . . exported "WMSYS"."WM$UDTRIG_DISPATCH_PROCS$"             0 KB       0 rows
     . . exported "WMSYS"."WM$UDTRIG_INFO$"                       0 KB       0 rows
     . . exported "WMSYS"."WM$VERSION_TABLE$"                     0 KB       0 rows
     . . exported "WMSYS"."WM$VT_ERRORS_TABLE$"                   0 KB       0 rows
     . . exported "WMSYS"."WM$WORKSPACE_SAVEPOINTS_TABLE$"        0 KB       0 rows
     . . exported "MDSYS"."RDF_PARAM$"                        6.515 KB       3 rows
     . . exported "SYS"."AUDTAB$TBS$FOR_EXPORT"               5.953 KB       2 rows
     . . exported "SYS"."DBA_SENSITIVE_DATA"                      0 KB       0 rows
     . . exported "SYS"."DBA_TSDP_POLICY_PROTECTION"              0 KB       0 rows
     . . exported "SYS"."FGA_LOG$FOR_EXPORT"                      0 KB       0 rows
     . . exported "SYS"."NACL$_ACE_EXP"                           0 KB       0 rows
     . . exported "SYS"."NACL$_HOST_EXP"                      6.914 KB       1 rows
     . . exported "SYS"."NACL$_WALLET_EXP"                        0 KB       0 rows
     . . exported "SYS"."SQL$TEXT_DATAPUMP"                       0 KB       0 rows
     . . exported "SYS"."SQL$_DATAPUMP"                           0 KB       0 rows
     . . exported "SYS"."SQLOBJ$AUXDATA_DATAPUMP"                 0 KB       0 rows
     . . exported "SYS"."SQLOBJ$DATA_DATAPUMP"                    0 KB       0 rows
     . . exported "SYS"."SQLOBJ$PLAN_DATAPUMP"                    0 KB       0 rows
     . . exported "SYS"."SQLOBJ$_DATAPUMP"                        0 KB       0 rows
     . . exported "SYSTEM"."SCHEDULER_JOB_ARGS"               8.648 KB       4 rows
     . . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS"           9.726 KB      13 rows
     . . exported "WMSYS"."WM$EXP_MAP"                        7.718 KB       3 rows
     . . exported "WMSYS"."WM$METADATA_MAP"                       0 KB       0 rows
     . . exported "HR"."ZZ_OBJECTS"                           15.93 MB   78994 rows
     . . exported "SH"."CUSTOMERS"                            10.27 MB   55500 rows
     . . exported "SCOTT"."SAMPLE_DATASET_INTRO"              11.84 MB   10000 rows
     . . exported "SCOTT"."SAMPLE_DATASET_PARTN"              11.79 MB   10000 rows
     . . exported "SCOTT"."SAMPLE_DATASET_XMLDB_HOL"          12.72 MB   10000 rows
     . . exported "SCOTT"."SAMPLE_DATASET_EVOLVE"             11.76 MB   10000 rows
     . . exported "SCOTT"."SAMPLE_DATASET_XQUERY"             11.73 MB   10000 rows
     . . exported "SCOTT"."SAMPLE_DATASET_FULLTEXT"           11.74 MB   10000 rows
     . . exported "AV"."SALES_FACT"                           3.159 MB   86880 rows
     . . exported "OE"."PRODUCT_DESCRIPTIONS"                 2.379 MB    8640 rows
     . . exported "SH"."SALES":"SALES_Q4_2001"                2.257 MB   69749 rows
     . . exported "SH"."SALES":"SALES_Q3_1999"                2.166 MB   67138 rows
     . . exported "SH"."SALES":"SALES_Q3_2001"                2.130 MB   65769 rows
     . . exported "SH"."SALES":"SALES_Q2_2001"                2.051 MB   63292 rows
     . . exported "SH"."SALES":"SALES_Q1_1999"                2.071 MB   64186 rows
     . . exported "SH"."SALES":"SALES_Q1_2001"                1.965 MB   60608 rows
     . . exported "SH"."SALES":"SALES_Q4_1999"                2.014 MB   62388 rows
     . . exported "SH"."SALES":"SALES_Q1_2000"                2.012 MB   62197 rows
     . . exported "SH"."SALES":"SALES_Q3_2000"                1.910 MB   58950 rows
     . . exported "SH"."SALES":"SALES_Q4_2000"                1.814 MB   55984 rows
     . . exported "SH"."SALES":"SALES_Q2_2000"                1.802 MB   55515 rows
     . . exported "SH"."SALES":"SALES_Q2_1999"                1.754 MB   54233 rows
     . . exported "SH"."SALES":"SALES_Q3_1998"                1.634 MB   50515 rows
     . . exported "SH"."SALES":"SALES_Q4_1998"                1.581 MB   48874 rows
     . . exported "SH"."SALES":"SALES_Q1_1998"                1.413 MB   43687 rows
     . . exported "SH"."SALES":"SALES_Q2_1998"                1.160 MB   35758 rows
     . . exported "SH"."SUPPLEMENTARY_DEMOGRAPHICS"           697.6 KB    4500 rows
     . . exported "SH"."TIMES"                                381.7 KB    1826 rows
     . . exported "SH"."FWEEK_PSCAT_SALES_MV"                 419.9 KB   11266 rows
     . . exported "SH"."COSTS":"COSTS_Q4_2001"                278.5 KB    9011 rows
     . . exported "SH"."COSTS":"COSTS_Q3_2001"                234.6 KB    7545 rows
     . . exported "SH"."COSTS":"COSTS_Q1_2001"                228.0 KB    7328 rows
     . . exported "SH"."COSTS":"COSTS_Q2_2001"                184.7 KB    5882 rows
     . . exported "SH"."COSTS":"COSTS_Q1_1999"                183.7 KB    5884 rows
     . . exported "SH"."COSTS":"COSTS_Q4_2000"                160.4 KB    5088 rows
     . . exported "SH"."COSTS":"COSTS_Q4_1999"                159.2 KB    5060 rows
     . . exported "SH"."COSTS":"COSTS_Q3_2000"                151.6 KB    4798 rows
     . . exported "SH"."COSTS":"COSTS_Q4_1998"                144.8 KB    4577 rows
     . . exported "SH"."COSTS":"COSTS_Q1_1998"                139.6 KB    4411 rows
     . . exported "SH"."COSTS":"COSTS_Q3_1999"                137.5 KB    4336 rows
     . . exported "SH"."COSTS":"COSTS_Q2_1999"                132.7 KB    4179 rows
     . . exported "SH"."COSTS":"COSTS_Q3_1998"                131.3 KB    4129 rows
     . . exported "SH"."COSTS":"COSTS_Q2_2000"                119.1 KB    3715 rows
     . . exported "SH"."COSTS":"COSTS_Q1_2000"                120.7 KB    3772 rows
     . . exported "XFILES"."XFILES_LOG_TABLE"                 130.9 KB     115 rows
     . . exported "OE"."PURCHASEORDER"                        264.6 KB     132 rows
     . . exported "OE"."PRODUCT_INFORMATION"                  73.05 KB     288 rows
     . . exported "SH"."COSTS":"COSTS_Q2_1998"                79.68 KB    2397 rows
     . . exported "OE"."CUSTOMERS"                            81.17 KB     319 rows
     . . exported "SH"."PROMOTIONS"                           59.17 KB     503 rows
     . . exported "SH"."PRODUCTS"                             26.71 KB      72 rows
     . . exported "PM"."PRINT_MEDIA"                          190.6 KB       4 rows
     . . exported "OE"."ORDER_ITEMS"                          21.01 KB     665 rows
     . . exported "AV"."GEOGRAPHY_DIM"                        17.83 KB     181 rows
     . . exported "OE"."INVENTORIES"                          21.76 KB    1112 rows
     . . exported "HR"."EMPLOYEES"                            17.07 KB     107 rows
     . . exported "HRREST"."EMPLOYEES"                        17.08 KB     107 rows
     . . exported "AV"."TIME_DIM"                             16.70 KB      60 rows
     . . exported "PM"."TEXTDOCS_NESTEDTAB"                   87.85 KB      12 rows
     . . exported "OE"."PRODUCT_REF_LIST_NESTEDTAB"           12.57 KB     288 rows
     . . exported "OE"."ORDERS"                               12.59 KB     105 rows
     . . exported "IX"."AQ$_STREAMS_QUEUE_TABLE_S"            11.57 KB       1 rows
     . . exported "ORDS_METADATA"."ORDS_OBJECTS"              11.27 KB       7 rows
     . . exported "IX"."AQ$_ORDERS_QUEUETABLE_S"              11.30 KB       4 rows
     . . exported "SH"."COUNTRIES"                            10.46 KB      23 rows
     . . exported "ORDS_METADATA"."SEC_PRIVILEGES"            10.01 KB      12 rows
     . . exported "ORDS_METADATA"."ORDS_SCHEMAS"              9.531 KB       3 rows
     . . exported "IX"."AQ$_ORDERS_QUEUETABLE_H"                  0 KB       0 rows
     . . exported "ORDS_METADATA"."SEC_PRIVILEGE_ROLES"       9.140 KB      22 rows
     . . exported "IX"."AQ$_ORDERS_QUEUETABLE_I"                  0 KB       0 rows
     . . exported "ORDS_METADATA"."SEC_ROLES"                 8.695 KB      15 rows
     . . exported "OE"."CATEGORIES_TAB"                       14.43 KB      22 rows
     . . exported "HR"."LOCATIONS"                            8.437 KB      23 rows
     . . exported "HRREST"."LOCATIONS"                        8.437 KB      23 rows
     . . exported "ORDS_METADATA"."SEC_KEYS"                  8.171 KB       2 rows
     . . exported "ORDS_METADATA"."SEC_PRIVILEGE_MAPPINGS"    8.101 KB       1 rows
     . . exported "ORDS_METADATA"."ORDS_URL_MAPPINGS"         7.757 KB       3 rows
     . . exported "OE"."WAREHOUSES"                           12.76 KB       9 rows
     . . exported "SH"."CHANNELS"                             7.414 KB       5 rows
     . . exported "HR"."JOB_HISTORY"                          7.195 KB      10 rows
     . . exported "HRREST"."JOB_HISTORY"                      7.195 KB      10 rows
     . . exported "HR"."JOBS"                                 7.101 KB      19 rows
     . . exported "HRREST"."JOBS"                             7.101 KB      19 rows
     . . exported "HR"."DEPARTMENTS"                          7.125 KB      27 rows
     . . exported "HRREST"."DEPARTMENTS"                      7.125 KB      27 rows
     . . exported "AV"."PRODUCT_DIM"                          6.843 KB       8 rows
     . . exported "OE"."SUBCATEGORY_REF_LIST_NESTEDTAB"       6.656 KB      21 rows
     . . exported "HR"."ZZ1"                                  6.421 KB       1 rows
     . . exported "HR"."COUNTRIES"                            6.367 KB      25 rows
     . . exported "HRREST"."COUNTRIES"                        6.367 KB      25 rows
     . . exported "SH"."CAL_MONTH_SALES_MV"                   6.382 KB      48 rows
     . . exported "ORDS_METADATA"."ORDS_SCHEMA_VERSION"       5.953 KB       1 rows
     . . exported "HR"."REGIONS"                              5.546 KB       4 rows
     . . exported "HRREST"."REGIONS"                          5.546 KB       4 rows
     . . exported "OE"."PROMOTIONS"                           5.570 KB       2 rows

La table de test "ZZSYSAUX"."T_TBSSYSAUX" est exportée.
     . . exported "ZZSYSAUX"."T_TBSSYSAUX"                    5.492 KB       1 rows

     . . exported "HR"."ZZ_TAB_EXPORT_PLAN"                   5.148 KB       2 rows
     . . exported "HR"."ZZTEST"                               5.085 KB       6 rows

La table de test "SYSTEM"."T_SYSTEM" est aussi exportée.
     . . exported "SYSTEM"."T_SYSTEM"                         5.054 KB       1 rows

     . . exported "DBJSON"."CREATE$JAVA$LOB$TABLE"                0 KB       0 rows
     . . exported "IX"."AQ$_ORDERS_QUEUETABLE_G"                  0 KB       0 rows
     . . exported "IX"."AQ$_ORDERS_QUEUETABLE_L"                  0 KB       0 rows
     . . exported "IX"."AQ$_ORDERS_QUEUETABLE_T"                  0 KB       0 rows
     . . exported "IX"."AQ$_STREAMS_QUEUE_TABLE_C"                0 KB       0 rows
     . . exported "IX"."AQ$_STREAMS_QUEUE_TABLE_G"                0 KB       0 rows
     . . exported "IX"."AQ$_STREAMS_QUEUE_TABLE_H"                0 KB       0 rows
     . . exported "IX"."AQ$_STREAMS_QUEUE_TABLE_I"                0 KB       0 rows
     . . exported "IX"."AQ$_STREAMS_QUEUE_TABLE_L"                0 KB       0 rows
     . . exported "IX"."AQ$_STREAMS_QUEUE_TABLE_T"                0 KB       0 rows
     . . exported "IX"."ORDERS_QUEUETABLE"                        0 KB       0 rows
     . . exported "IX"."STREAMS_QUEUE_TABLE"                      0 KB       0 rows
     . . exported "ORDS_METADATA"."OAUTH_APPROVALS"               0 KB       0 rows
     . . exported "ORDS_METADATA"."OAUTH_APPROVAL_PRIVS"          0 KB       0 rows
     . . exported "ORDS_METADATA"."OAUTH_CLIENTS"                 0 KB       0 rows
     . . exported "ORDS_METADATA"."OAUTH_CLIENT_PRIVILEGES"       0 KB       0 rows
     . . exported "ORDS_METADATA"."OAUTH_CLIENT_ROLES"            0 KB       0 rows
     . . exported "ORDS_METADATA"."OAUTH_PENDING_APPROVALS"       0 KB       0 rows
     . . exported "ORDS_METADATA"."OAUTH_SESSIONS"                0 KB       0 rows
     . . exported "ORDS_METADATA"."ORDS_HANDLERS"                 0 KB       0 rows
     . . exported "ORDS_METADATA"."ORDS_MODULES"                  0 KB       0 rows
     . . exported "ORDS_METADATA"."ORDS_PARAMETERS"               0 KB       0 rows
     . . exported "ORDS_METADATA"."ORDS_TEMPLATES"                0 KB       0 rows
     . . exported "ORDS_METADATA"."ORDS_WORKSPACE_SCHEMAS"        0 KB       0 rows
     . . exported "ORDS_METADATA"."SEC_AUTHENTICATORS"            0 KB       0 rows
     . . exported "ORDS_METADATA"."SEC_ORIGINS_ALLOWED_MODULES"      0 KB       0 rows
     . . exported "ORDS_METADATA"."SEC_PRIVILEGE_AUTHS"           0 KB       0 rows
     . . exported "ORDS_METADATA"."SEC_PRIVILEGE_MODULES"         0 KB       0 rows
     . . exported "ORDS_METADATA"."SEC_ROLE_MAPPINGS"             0 KB       0 rows
     . . exported "ORDS_METADATA"."SEC_SESSIONS"                  0 KB       0 rows
     . . exported "SH"."COSTS":"COSTS_1995"                       0 KB       0 rows
     . . exported "SH"."COSTS":"COSTS_1996"                       0 KB       0 rows
     . . exported "SH"."COSTS":"COSTS_H1_1997"                    0 KB       0 rows
     . . exported "SH"."COSTS":"COSTS_H2_1997"                    0 KB       0 rows
     . . exported "SH"."COSTS":"COSTS_Q1_2002"                    0 KB       0 rows
     . . exported "SH"."COSTS":"COSTS_Q1_2003"                    0 KB       0 rows
     . . exported "SH"."COSTS":"COSTS_Q2_2002"                    0 KB       0 rows
     . . exported "SH"."COSTS":"COSTS_Q2_2003"                    0 KB       0 rows
     . . exported "SH"."COSTS":"COSTS_Q3_2002"                    0 KB       0 rows
     . . exported "SH"."COSTS":"COSTS_Q3_2003"                    0 KB       0 rows
     . . exported "SH"."COSTS":"COSTS_Q4_2002"                    0 KB       0 rows
     . . exported "SH"."COSTS":"COSTS_Q4_2003"                    0 KB       0 rows
     . . exported "SH"."SALES":"SALES_1995"                       0 KB       0 rows
     . . exported "SH"."SALES":"SALES_1996"                       0 KB       0 rows
     . . exported "SH"."SALES":"SALES_H1_1997"                    0 KB       0 rows
     . . exported "SH"."SALES":"SALES_H2_1997"                    0 KB       0 rows
     . . exported "SH"."SALES":"SALES_Q1_2002"                    0 KB       0 rows
     . . exported "SH"."SALES":"SALES_Q1_2003"                    0 KB       0 rows
     . . exported "SH"."SALES":"SALES_Q2_2002"                    0 KB       0 rows
     . . exported "SH"."SALES":"SALES_Q2_2003"                    0 KB       0 rows
     . . exported "SH"."SALES":"SALES_Q3_2002"                    0 KB       0 rows
     . . exported "SH"."SALES":"SALES_Q3_2003"                    0 KB       0 rows
     . . exported "SH"."SALES":"SALES_Q4_2002"                    0 KB       0 rows
     . . exported "SH"."SALES":"SALES_Q4_2003"                    0 KB       0 rows
     . . exported "XDBEXT"."IMAGE_METADATA_TABLE"                 0 KB       0 rows
     . . exported "XDBEXT"."REPOSITORY_EVENTS_TABLE"          59.81 KB     115 rows
     . . exported "XDBPM"."SQLLDR_REPOSITORY_TABLE"               0 KB       0 rows
     . . exported "XDBPM"."XDBPM_INDEX_DDL_CACHE"                 0 KB       0 rows
     . . exported "XFILES"."DOCUMENT_UPLOAD_TABLE"                0 KB       0 rows
     . . exported "XFILES"."LOG_RECORD_QUEUE_TABLE"               0 KB       0 rows
     . . exported "XFILES"."TREE_STATE_TABLE"                     0 KB       0 rows
     . . exported "XFILES"."XFILES_DOCUMENT_STAGING"              0 KB       0 rows
     . . exported "XFILES"."XFILES_RESULT_CACHE"                  0 KB       0 rows
     . . exported "XFILES"."XFILES_WIKI_TABLE"                    0 KB       0 rows
     Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
     ******************************************************************************
     Dump file set for SYS.SYS_EXPORT_FULL_01 is:
       /u01/app/oracle/admin/orclcdb/dpdump/8A34DEF16CD55C76E0530100007F040C/EXP_FULL.dmp
     Job "SYS"."SYS_EXPORT_FULL_01" completed with 1 error(s) at Sun Feb 13 15:40:03 2022 elapsed 0 00:05:39