DBMS_XPLAN.DISPLAY_CURSOR 04 : pièges et points d’attention - DBMS_XPLAN.DISPLAY_CURSOR 04 : pitfalls and points of attention

30 juin 2022

DBMS_XPLAN.DISPLAY_CURSOR 04 : pièges et points d’attention - DBMS_XPLAN.DISPLAY_CURSOR 04 : pitfalls and points of attention

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

============================================================================================
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

============================================================================================
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
     -------------------------------------
     ...