DBMS_XPLAN.DISPLAY_CURSOR 02 : appels simples et complexes - DBMS_XPLAN.DISPLAY_CURSOR 02: simple and complex calls

7 juin 2022

DBMS_XPLAN.DISPLAY_CURSOR 02 : appels simples et complexes - DBMS_XPLAN.DISPLAY_CURSOR 02: simple and complex calls

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

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

     ...
     SQL_ID 9420hwvnq6jsj, child number 1
     -------------------------------------
     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"