Blog d'un DBA sur le SGBD Oracle et SQL

18 juin 2017

Le produit cartésien n'est pas le MAL mais quand peut-on l'utiliser?

         
Introduction
On vous a dit, re-dit et re-re-dit que le produit cartésien (ou produit en croix) c'était le mal, que c'était à éviter à tout prix car cette absence de jointure retourne des enregistrements en trop et que ça monopolisait trop de ressources serveur, au point de parfois le mettre à genoux.

OK, mais dans quel cas peut-on faire un produit cartésien sachant que ce n'est pas interdit en SQL et qu'il y a même un opérateur dédié à cette opération, appelé CROSS JOIN?

C'est le but de cet article!



Points d'attention
N'utilisez le produit cartésien qu'en toute connaissance de cause, en sachant que le résultat peut être sans commune mesure avec la volumétrie des tables jointes et que vos SELECT peuvent mettre des heures pour s'exécuter et donc faire tomber votre serveur.



Base de tests
N'importe quelle base Oracle.

 



Exemples

============================================================================================
Comment faire un produit cartésien.

============================================================================================
Pour rappels :
          - le produit cartésien est l'absence de jointure entre deux tables, que ce soit volontaire ou, le plus souvent, un oubli
          - le résultat du produit cartésien est la combinatoire entre les deux tables, soit pour deux tables ayant N et M enregistrements, le résultat sera N*M enregistrements alors qu'une équijointure (jointure avec le signe = ) aurait pu ramener 1 000 fois moins d'enregistrements dans un cas classique

Jeu d'essai
On utilise deux tables avec deux enregistrements chacune pour éviter les problèmes de volumétrie.

Notez que les noms des colonnes sont différents entre les deux tables.

         SQL> select * from emp;
         NOM          PRENOM          AGE
         ------------------------------ ------------------------------ ----------
         DUPONT          TOM          40
         DURAND          TOM          50

         SQL> select * from clients;
         NOM_CLIENT          PRENOM_CLIENT          AGE_CLIENT
         ------------------------------ ------------------------------ -------------------------
         DUPONT                   TOM                                     40
         DUPONT                   DAVID                                  40


Syntaxe propriétaire Oracle

Dans le cas de la syntaxe classique Oracle, le produit cartésien a lieu en ne faisant aucune jointure entre les tables du FROM.

Si on fait le produit cartésien, voilà le résultat : toute la combinatoire possible et imaginable, soit 2 x 2 = 4 enregistrements dans notre exemple dont 3 mauvais.
         SQL> select *
         from emp, clients
;

         NOM          PRENOM          AGE          NOM_CLIENT          PRENOM_CLIENT          AGE_CLIENT
         ------------------------------ ------------------------------ ---------- ------------------------------ ------------------------------ ----------
         DUPONT          TOM          40                   DUPONT                   TOM                            40
         DUPONT          TOM          40                   DUPONT                   DAVID                          40
         DURAND          TOM          50                   DUPONT                   TOM                            40
         DURAND          TOM          50                   DUPONT                   DAVID                          40

Si on avait fait la jointure : 1 seul enregistrement au lieu de 4 mais c'est le bon!
         SQL> select *
                  from emp e, clients c
                  where e.nom = c.nom_client and e.prenom = c.prenom_client;
         NOM          PRENOM          AGE          NOM_CLIENT          PRENOM_CLIENT          AGE_CLIENT
         ------------------------------ ------------------------------ ---------- ------------------------------ ------------------------------ ----------
         DUPONT          TOM          40                   DUPONT                   TOM                            40


Syntaxe ANSI92
Dans le cas de la syntaxe ANSI92, le produit cartésien se fait avec le mot clé CROSS JOIN ou bien, attention c'est un piège de cette syntaxe, avec NATURAL JOIN dans le cas où aucune colonne entre les tables jointes n'a de nom en commun.

         SQL> select * from emp CROSS JOIN clients;
         NOM          PRENOM          AGE          NOM_CLIENT          PRENOM_CLIENT          AGE_CLIENT
         ------------------------------ ------------------------------ ---------- ------------------------------ ------------------------------ ----------
         DUPONT          TOM          40                   DUPONT                   TOM                            40
         DUPONT          TOM          40                   DUPONT                   DAVID                          40
         DURAND          TOM          50                   DUPONT                   TOM                            40
         DURAND          TOM          50                   DUPONT                   DAVID                         40

         SQL> select * from emp NATURAL JOIN clients;
         NOM          PRENOM          AGE          NOM_CLIENT          PRENOM_CLIENT          AGE_CLIENT
         ------------------------------ ------------------------------ ---------- ------------------------------ ------------------------------ ----------
         DUPONT          TOM          40                   DUPONT                   TOM                            40
         DUPONT          TOM          40                   DUPONT                   DAVID                         40
         DURAND          TOM          50                   DUPONT                   TOM                           40
         DURAND          TOM          50                   DUPONT                   DAVID                         40


============================================================================================
Pourquoi faire un produit cartésien.

============================================================================================

Pour répondre à certains besoins, il est possible, voir obligatoire, de faire un produit cartésien.

Tests de volumétrie avec des données réelles dans les tables de tests
Quand on fait des tests de volumétrie, il faut disposer de tables avec des données en quantité suffisamment importante. Il existe diverses méthodes pour remplir des tables mais une des plus intéressante est de faire un produit cartésien. Prenons comme exemple DBA_OBJECTS, une vue qui renferme près de 80 000 enregistrements dans une nouvelle base Oracle 12. Faire un produit cartésien dessus signifie créer 80000 * 80000 soit 6 400 000 000 d'enregistrements... oui, 6 milliards et demi! 

OK, c'est trop mais on pourra toujours faire une sélection sur le type des objets ou utiliser rownum pour filtrer et avoir des tables avec quelques millions de rows. L'autre gros intérêt à faire un produit en croix depuis des tables système existantes, c'est l'assurance d'avoir des données dont les valeurs ne sont pas bidon et qui permettront ensuite de faire des tests pertinents. Entre générer deux millions de noms composés d'un libellé fixe comme AA concaténé à une séquence ou utiliser DBMS_RANDOM, je préfère de loin utiliser les données de DBA_OBJECTS car elles ont un sens.
        SQL> select count(*) from dba_objects;
        COUNT(*)
        ----------
        78398

        SQL> select 80000 * 80000 from dual;
        80000*80000
        -----------
        6400000000

On vérifie qu'on a bien 6 milliards et demi d'enregistrements avec ce cross join.
        SQL> select count(*)
         from dba_objects o1, dba_objects o2;

        COUNT(*)
        ----------
        6146246404

En utilisant des filtres, nous avons presque 34 millions d'enregistrements, ce qui est plus gérable pour Oracle.
        SQL> select count(*)
        from dba_objects o1, dba_objects o2
        where o1.owner <> 'SYS' and o2.owner <> 'SYS' and o1.object_type = 'TABLE';
        COUNT(*)
        ----------
        33557060

On peut donc utiliser ce filtre pour créer une table avec des données nombreuses et faisant sens.
         SQL> create table TEST_OBJECTS AS
         SELECT o1.OWNER, o1.object_name, o2.object_type, o2.status
         from dba_objects o1, dba_objects o2
         where o1.owner <> 'SYS' and o2.owner <> 'SYS' and o1.object_type = 'TABLE';


Afficher toute la combinatoire possible.
Il est parfois nécessaire d'afficher tous les enregistrements possibles et imaginables entre deux tables. Dans ce cas là, la solution est le produit cartésien.
Par exemple une entreprise a 10 employés et 100 clients réguliers qu'elle a fréquemment au téléphone. Elle décide d'affecter certains employés à certains clients, plutôt de la même classe d'âge car ils partagent le même vocabulaire, les mêmes valeurs... MAIS cela n'est pas une obligation, la RH se réserve le droit de réfléchir et d'affecter telle employé à tel client, ce qui fait qu'il y a un élément humain de décision dans le process, chose qui ne peut pas se traduire par un filtre SQL. Par classe d'âge on entends plus ou moins 10 ans par exemple. On fera donc un produit cartésien pour afficher tous les cas possibles puis ensuite la RH va affecter manuellement tel employé à tels clients. Nous aurons 1000 enregistrements pour voir tous les cas possibles.

Pour que ce test soit plus lisible, j'ai ajouté des employés et des clients par rapport aux tests précédents.
         SQL> select e.nom || ' ' || e.prenom || ' ' || TO_CHAR(e.age) AS "EMPLOYE",
                   c.nom_client || ' ' || c.prenom_client || ' ' || TO_CHAR(c.age_client) AS "CLIENT",
                   e.age - c.AGE_CLIENT AS "DIFFERENCE AGE"
                   from emp e, clients c
                  
order by "EMPLOYE", "DIFFERENCE AGE";

        EMPLOYE                                CLIENT                  DIFFERENCE AGE
        ------------------------------ ------------------------------ -----------------------------------------
        DURAND TOM 45               MIMON DELPHINE 70             -25
        DURAND TOM 45               VRAMO DAVID 40                     5
        DURAND TOM 45               PAPIN MARCEL 40                   5
        DURAND TOM 45               MARTIN SOPHIE 25                20
        MARCIAL PAUL 40            MIMON DELPHINE 70             -30
        MARCIAL PAUL 40            VRAMO DAVID 40                     0
        MARCIAL PAUL 40            PAPIN MARCEL 40                    0
        MARCIAL PAUL 40            MARTIN SOPHIE 25                 15
        8 rows selected.


Tester le gestionnaire de ressources.
Je tiens à remercier le DBA de pseudo Pomalaix qui sur le lien suivant explique qu'on peut empêcher une requête trop longue de s'exécuter via des profils ou le Resource Manager. Avec des profils, cette requête va certes commencer à s'exécuter mais elle va s'interrompre dès qu'on aura dépassé un seuil de ressources alloué au user. Avec le gestionnaire de ressources, c'est carrément le lancement de la requête qui n'aura pas lieu si celle-ci devait consommer trop de ressources.
https://www.developpez.net/forums/d1601098/bases-donnees/oracle/administration/produit-croix-requete-lente-blocage-oracle/

Et où intervient le produit cartésien là-dedans? Dans la création des requêtes pour tester le profil ou le plan de gestion des ressources :-) Si on ne veut pas qu'une requête de plus de 15 minutes puisse s'exécuter, il faut tester avec une requête mettant au minimum ce temps : un produit cartésien entre dba_objects et dba_tab_cols sera parfait pour cela.

Pomalaix nous donne même un exemple.
"Par exemple, en jouant sur une directive avec une limite MAX_EST_EXEC_TIME fixée à 10 secondes de temps CPU :
          select count(*) from dba_objects, dba_users;
          ERREUR à la ligne 1 :
          ORA-07455: temps d'exécution prévu (105 sec.) hors limites (10 sec.)"

Je ne décrirai pas ici comment tester cette partie car créer un plan de ressource n'est pas simple et c'est hors périmètre de cet article.


Afficher sur une seule ligne le contenu de deux tables avec ou sans lien.
Ce cas là est plus subtil :-)
Nous pouvons également utiliser le produit cartésien lorsque nous voulons afficher sur une seule ligne des informations venant de deux tables ayant ou non des colonnes en commun MAIS OBLIGATOIREMENT si ces tables n'ont qu'un seul enregistrement!


Vous avez deviné pourquoi? Parce qu'un produit cartésien crée un résultat de Table1.Nb_rows * Table2.Nb_rows enregistrements et, quand on a 1 des deux côtés, le résultat est 1 * 1 = 1, donc on ne peut pas avoir d'enregistrement parasite dans ce cas!

Il est possible d'obtenir le même résultat avec un autre ordre SQL mais je veux montrer que le produit cartésien est ici possible.

Premier exemple : jointure entre deux tables sans colonne en commun et ayant chacune un seul enregistrement : V$INSTANCE et USER_USERS.
         SQL>select COLUMN_NAME
                 from dba_tab_cols
                 where table_name = 'V_$INSTANCE'
         INTERSECT
                 select COLUMN_NAME
                 from dba_tab_cols
                 where table_name = 'USER_USERS';
         no rows selected

Le test est simple : j'affiche en une ligne des infos sur ma base et sur le nom de mon user.
         SQL> select d.dbid, d.name, d.created, u.username from v$database d, user_users u;
         DBID                NAME       CREATED     USERNAME
         ---------- --------- --------- ------------------------------------------------
         768045447       ORCL12C   02-MAR-17    HR

Deuxième exemple : jointure entre deux tables avec une ou des colonnes en commun mais ayant à nouveau chacune un seul enregistrement : V$INSTANCE et V$DATABASE.
         SQL>select COLUMN_NAME
                 from dba_tab_cols
                 where table_name = 'V_$INSTANCE'
         INTERSECT
                 select COLUMN_NAME
                 from dba_tab_cols
                 where table_name = 'V_$DATABASE';
         COLUMN_NAME
         ----------------------------------------------------------------------------------------------------
         CON_ID

On va comparer le résultat sans et avec jointure sur la colonne commune : même résultat, comme je m'y attendais!
Select sans jointure.
         SQL> select d.dbid, d.name, d.created, i.instance_name, i.status
         from v$database d, v$instance i;
         DBID            NAME        CREATED     INSTANCE_NAME    STATUS
         ---------- --------- --------- ---------------- -----------------------------------------------------
         768045447    ORCL12C   02-MAR-17    orcl12c                     OPEN

Select avec jointure.
         SQL> select d.dbid, d.name, d.created, i.instance_name, i.status
         from v$database d, v$instance i
         where d.con_id = i.con_id;
         DBID            NAME        CREATED     INSTANCE_NAME    STATUS
         ---------- --------- --------- ---------------- -----------------------------------------------------
         768045447    ORCL12C   02-MAR-17    orcl12c                     OPEN



Voilà, comme je vous le disais, le produit cartésien peut parfois grandement vous simplifier la vie.


Posté par David DBA à 11:31 - - Commentaires [0] - Permalien [#]
Tags : , ,


08 juin 2017

Plan d'exécution : ce n'est pas la ligne la plus indentée qui est exécutée en premier


Introduction
Un plan d'exécution Oracle peut rapidement devenir incompréhensible dès qu'il comporte des dizaines de lignes. On peut s'aider de Google ou de livres d'administration Oracle pour le décrypter mais, curieusement, même les DBA les plus connus ne sont pas d'accord sur l'ordre dans lequel Oracle exécute les différentes opérations de ce plan!

Pour rappel, il est fondamental de comprendre l'exécution de ce plan pour tuner un ordre SQL! Si on ne sait pas quelle est la première opération exécutée, il est impossible de mettre en place une stratégie pour diminuer dès le début le nombre d'enregistrements qu'Oracle va devoir traiter.

En résumé il existe deux théories expliquant quelles sont les opérations exécutées en premier :
          - théorie 1 : ce sont les opérations qui sont les plus indentées
          - théorie 2 : ce sont les opérations feuilles (opérations sans sous-opération) de Id le plus faible



Points d'attention
N/A.



Base de tests
N'importe quelle base Oracle mais avoir Toad d'installé.
 



Exemples
============================================================================================
Les deux théories dans la littérature

============================================================================================

Voici des extraits de deux livres écrits par des DBA réputés dans le monde Oracle décrivant ces théories .


Sam, Darl, Bill, Christian, if you want me to remove your text, please contact me with the canalblog formular and I will do it.

Première théorie : un livre écrit par Darl Kuhn disant que ce sont les opérations les plus indentées qui s'exécutent en premier, "Oracle Database 11g Performance Tuning Recipes" par Sam Alapati, Darl Kuhn, Bill Padfield. Voir pages 307 et 308.

Canalblog DBA Oracle Plan execution Toad04

"Once you have an explain plan to interpret, you can tell which steps are executed first because the innermost or most indented steps are executed first, and are executed from the inside out, in top-down order. In the foregoing query, we are joining the EMP and DEPT tables. Here are the steps of how the query is processed based on the execution plan:
          1. The PK_DEPT index is scanned (ID 3).
          2. All EMP table rows are scanned (ID 5).
          3. Rows are retrieved from the DEPT table based on the matching entries in the PK_DEPT index (ID 2).
          4. Resulting data from the EMP table is sorted (ID 4).
          5. Data from the EMP and DEPT tables are then joined via a MERGE JOIN (ID 1).
          6. The resulting data from the query is returned to the user (ID 0)."


Deuxième théorie : Christian Antognini dit que ce sont les feuilles qui s'exécutent en premier dans son excellent livre "Troubleshooting Oracle performance"; par feuille on entend une opération qui n'a pas besoin d'opération fille pour ramener des données. Voir pages 228 et 229.
          SELECT *
          FROM emp, dept
          WHERE emp.deptno = dept.deptno
          AND emp.comm IS NULL
          AND dept.dname != 'SALES';

Le plan d'exécution a deux opérations filles, celles d'Id 2 et 4 et une opération la plus indentée, celle d'Id 4. Laquelle s'exécute en premier?
          ------------------------------------------------------------------
          | Id | Operation | Name | Starts | A-Rows |
          ------------------------------------------------------------------
          | 1 |   NESTED LOOPS | | 1 | 8 |
          |* 2 |      TABLE ACCESS FULL | EMP | 1 | 10 |
          |* 3 |      TABLE ACCESS BY INDEX ROWID| DEPT | 10 | 8 |
          |* 4 |            INDEX UNIQUE SCAN | DEPT_PK | 10 | 10 |
          ------------------------------------------------------------------
          2 - filter("EMP"."COMM" IS NULL)
          3 - filter("DEPT"."DNAME"<>'SALES')
          4 – access("EMP"."DEPTNO"="DEPT"."DEPTNO"


Christian construit ensuite un arbre des opérations et explique comment Oracle exécute celui-ci.

Canalblog DBA Oracle Plan execution Toad03

1. Operation 1 has two children (2 and 3), and among them, operation 2 is the first in ascending order. Therefore, the execution starts with operation 2 (the outer loop).

2. Operation 2 scans the table emp, applies the filter predicate "EMP"."COMM" IS NULL, and returns the data of ten rows to its parent operation (1).

3. For each row returned by operation 2, the second child of the operation NESTED LOOPS, the inner loop, is executed once. This is confirmed by comparing the column A-Rows of operation 2 with the column Starts of operations 3 and 4.

4. The inner loop is composed of two stand-alone operations. Based on the rules that apply to this type of operation, operation 4 is executed before operation 3.

5. Operation 4 scans the index dept_pk by applying the access predicate "EMP"."DEPTNO"= "DEPT"."DEPTNO". In doing so, it extracts ten rowids from the index over the ten executions and passes them to its parent operation (3).

6.
Operation 3 accesses the table dept through the ten rowids passed from operation 4. For each rowid, a row is read. Then it applies the filter predicate "DEPT"."DNAME"<>'SALES'. This filter leads to the exclusion of two rows. It passes the data of the remaining eight rows to its
parent operation (1).

7.
Operation 1 sends the data of eight rows to the caller.



============================================================================================
Conclusion : le test avec Toad et ses plans d'exécution personnalisés
============================================================================================

Les deux théories sont pour moi incompatibles dès que nous avons des opérations feuilles qui ne sont pas les plus indentées, comme dans le deuxième exemple ci-dessus. Dans le cas contraire, les deux théories décrivent la même réalité.


Ne sachant plus à quel saint me vouer, j'ai décidé de faire mon propre test pour en avoir le coeur net! Le problème est : comment valider quelle théorie est la bonne? Je n'ai pas accès au code source d'Oracle, les traces 10053 ou 10046 ne semblent pas utiles... et puis, un jour, j'ai découvert une astuce dans le logiciel Toad. Dans cette outil, dans la fenêtre affichant le plan d'exécution, il faut faire clic droit et choisir Display mode pour afficher le plan d'exécution dans un mode plus ou moins lisible AVEC des informations complémentaires ajoutées par Toad.


Ma requête de test est on ne peut plus basique.
          SQL> SELECT table_name FROM dba_tables;

Voici le résultat avec le mode d'affichage par défaut, celui de DBMS_XPLAN; à afficher avec la police "Courier New" pour avoir une indentation plus lisible.
----------------------------------------------------------------------------------------

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |  3103 |   424K|   669   (1)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT OUTER      |          |  3103 |   424K|   669   (1)| 00:00:01 |
|   2 |   TABLE ACCESS FULL         | SEG$     |  7158 | 78738 |    58   (0)| 00:00:01 |
|*  3 |   HASH JOIN RIGHT OUTER     |          |  2662 |   335K|   611   (1)| 00:00:01 |
|   4 |    INDEX FULL SCAN          | I_USER2  |    78 |   312 |     1   (0)| 00:00:01 |
|*  5 |    HASH JOIN OUTER          |          |  2662 |   324K|   610   (1)| 00:00:01 |
|*  6 |     HASH JOIN OUTER         |          |  2662 |   304K|   548   (1)| 00:00:01 |
|*  7 |      HASH JOIN              |          |  2662 |   291K|   485   (1)| 00:00:01 |
|   8 |       INDEX FULL SCAN       | I_USER2  |    78 |   312 |     1   (0)| 00:00:01 |
|*  9 |       HASH JOIN             |          |  2662 |   280K|   484   (1)| 00:00:01 |
|* 10 |        HASH JOIN            |          |  2662 |   192K|   380   (1)| 00:00:01 |
|  11 |         MERGE JOIN CARTESIAN|          |    29 |  1276 |    11  (10)| 00:00:01 |
|* 12 |          HASH JOIN          |          |     1 |    41 |     1 (100)| 00:00:01 |
|* 13 |           FIXED TABLE FULL  | X$KSPPI  |     1 |    28 |     1 (100)| 00:00:01 |
|  14 |           FIXED TABLE FULL  | X$KSPPCV |  3350 | 43550 |     1 (100)| 00:00:01 |
|  15 |          BUFFER SORT        |          |    29 |    87 |    11  (10)| 00:00:01 |
|  16 |           TABLE ACCESS FULL | TS$      |    29 |    87 |    10   (0)| 00:00:01 |
|* 17 |         TABLE ACCESS FULL   | TAB$     |  2662 | 79860 |   369   (1)| 00:00:01 |
|* 18 |        TABLE ACCESS FULL    | OBJ$     | 22479 |   746K|   104   (1)| 00:00:01 |
|  19 |      INDEX FAST FULL SCAN   | I_OBJ1   | 23308 |   113K|    62   (0)| 00:00:01 |
|  20 |     INDEX FAST FULL SCAN    | I_OBJ1   | 23308 |   182K|    62   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Donc la question qui se pose est : quelle opération est exécutée en premier, celle d'Id 2, 13 ou 14?

Pour Toad, c'est la 2! Si vous changez le mode d'affichage du plan d'exécution, vous avez le mode Graphic donnant l'arbre ci-dessous avec, dans chaque bloc, le numéro d'ordre dans lequel Oracle exécute l'opération. Regardez sur la troisième ligne, Toad a ajouté un 1 pour le FULL TABLE ACCESS de SYS.SEG$ qui est l'opération d'Id 2 ci-dessus.

Canalblog DBA Oracle Plan execution Toad05


Le mode Graphic.

Canalblog DBA Oracle Plan execution Toad01



Le mode MS Graphic est encore plus lisible.

Canalblog DBA Oracle Plan execution Toad02


Vous pouvez aussi afficher le mode "Plain english", qui liste les opérations de façon séquentielle dans un tableau avec des commentaires additionnels de Toad qui sont bien intéressants!



Si on part du principe que Toad a raison, la conclusion est : c'est la théorie 2 qui est juste, c'est l'opération feuille (opération sans sous-opération) de Id le plus faible qui s'exécute en premier.


Posté par David DBA à 21:33 - - Commentaires [0] - Permalien [#]
Tags : ,