DBMS_XPLAN.DISPLAY_CURSOR 03 : ajouter des champs, les ordres DM et DDL traités - add fields, DML and DDL orders processed
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
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
/http%3A%2F%2Fstorage.canalblog.com%2F34%2F03%2F1515248%2F131304568_o.jpg)
/https%3A%2F%2Fprofilepics.canalblog.com%2Fprofilepics%2F1%2F5%2F1555182.jpg)