Discussion :

[LEK]

Bonsoir,
je fais des tests simples pour en apprendre plus sur plans d'exécution/optim,etc... Et je tombes sur un cas qui me semble curieux (mais je ne suis pas sûr de ma compréhension des plans d'exécution suivants) :
Je possède deux tables de tests :
- modele (id_modele varchar2(20), libelle_modele(varchar2(20)))
=> dont le champ id_modele varchar2(20) est la PK
- vehicule (id_vehicule number(3), id_modele (varchar2(20), libelle_vehicule(varchar2(20))))
=> qui possede une fk sur la table modele via le champ id_modele

Un index est donc créé pour la PK de la table modele et autre pour la FK de la table vehicule... (j'utilise une clé varchar2 pour simuler une clé naturelle)

J'insere dans ma table "modele" 23 modeles fictifs de véhicules.
J'insère dans ma table "vehicule" 284 véhicules fictifs qui pointent aléatoirement sur un des 30 modèles créés précédemment.

Je lance la commande suivante dans l'espoir de collecter des stats fraîches :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
begin  
	DBMS_STATS.GATHER_DATABASE_STATS();  
end;  
/

Je fais une jointure simple de ce type :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
 select m.libelle_modele,v.id_modele
from vehicule v, modele m
where m.id_modele = v.id_modele;

Le plan pour cette requête donne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
--------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------
|   0 | SELECT STATE.. |                 |    22 |  2750 |    7  (15)| 00:0|
|*  1 |  HASH JOIN         |                 |    22 |  2750 |     7  (15)| 00: |
|   2 |   TABLE ACCESS FULL| MODELE |    23|   782 |     3   (0)| 00:0
|   3 |   TABLE ACCESS FULL| VEHICULE |   284 | 25844 |     3   (0)| 00:0
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("M"."ID_MODELE"="V"."ID_MODELE")
 
 
Statistiques
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         18  consistent gets
          0  physical reads
          0  redo size
       5080  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         23  rows processed

Je me dis que l'optimiseur à jugé qu'il était inutile d'utiliser l'index et qu'un acces full scan était le plus optimal...
(Mais à quoi correspond l'utilisation du hash join dans ce cas ?)

Je change la requête ainsi :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
 select v.id_vehicule,
	(select m.libelle_modele from modele m where m.id_modele = v.id_modele)
from vehicule v;

et maintenant le résultat devient :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
 
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |                 |   284 | 25844 |     3   (0)| 00:00
|*  1 |  INDEX RANGE SCAN | FK_ID_MODELE  |     1 |    20 |     1   (0)| 
|   2 |  TABLE ACCESS FULL| VEHICULE |   284 | 25844 |     3   (0)| 00:00
--------------------------------------------------------------------------
 
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("M"."ID_MODELE"=:B1)
 
Statistiques
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         18  consistent gets
          0  physical reads
          0  redo size
       5080  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         23  rows processed

Sauriez-vous répondre à ces questions :
Pourquoi le côut est-il moins élevé dans ce cas (divisé par 2)?
Pourquoi l'optimiseur n'a-t-il pas utilisé l'index via un range scan dans la première requête?

Merci d'avance pour votre aide,
Lek.

[FSiebert]

Bonjour,

Quelques éléments de réponses

Tout d'abord, vous devriez utiliser la syntaxe qui est la norme depuis 20 ans pour vos jointures : http://sqlpro.developpez.com/cours/sqlaz/jointures/

Concernant le coût, il n'y a aucun sens à le comparer entre deux requêtes différentes. Pour une requête l'optimiseur va déterminer le coût de différentes méthodes et choisir la moins coûteuse mais ce nombre n'est pas comparable avec le coût d'une autre requête.

Pour votre première requête, l'optimiseur a en effet dû estimer qu'il était plus performant de lire directement la table que de passer par l'index. Vous récupérez de toute façon toutes les lignes de la table, autant les lire directement plutôt que via un index. Vous pouvez ajouter un HINT pour forcer l'utilisation de l'index et voir ce que ça donne.

[LEK]

Merci pour votre réponse.
Je n'utilise jamais effectivement ce type de syntaxe pour mes jointures (inner join...) : cela fait-il une réelle différence ? Ou bien est-ce dans le cas où oracle n'assure plus un jour la compatibilité ascendante (ce qui m'étonnerait fortement) ?
Pour ce qui est des requêtes différentes, sur le principe je suis d'accord, mais
alors pourquoi se force-t-on a faire des jointures tables à tables alors que vraisemblablement il est plus performant de faire des requêtes inline ?

[FSiebert]

Je n'utilise jamais effectivement ce type de syntaxe pour mes jointures (inner join...) : cela fait-il une réelle différence ? Ou bien est-ce dans le cas où oracle n'assure plus un jour la compatibilité ascendante (ce qui m'étonnerait fortement) ?

C'est surtout pour une question de lisibilité (et ça aide aussi un peu l'optimiseur). Et au-delà de ces points, les normes sont faites pour être respectées
Edit : Ou ça n'aide pas, voir ci-dessous l'explication de Mohamed.Houri.

Pour ce qui est des requêtes différentes, sur le principe je suis d'accord, mais alors pourquoi se force-t-on a faire des jointures tables à tables alors que vraisemblablement il est plus performant de faire des requêtes inline ?

Qui a dit que c'est plus performant ?
Comme je l'ai dit, comparer les coûts de ces deux requêtes ne veut rien dire. Par ailleurs les statistiques (par exemple les consistent gets) sont les mêmes. Vos deux requêtes semblent équivalentes.
Enfin, essayez éventuellement ces deux requêtes avec des vrais volumes (quelques millions de lignes), ça devrait être plus significatif

[mnitu]

Plusieurs remarques :

1. Ne fait jamais une marotte de l’écriture AINSI pour les requêtes Oracle. A part quelques bugs de plus concernant l’écriture AINSI l’impact sur l’optimisation est nul contrairement à ce que SQL*PRO crois. Personnellement je la préfère parce qu’elle met mieux en évidence les conditions de jointures en les séparant des filtres ce qui permet une meilleur compréhension de la façon dans lesquelles les tables sont liées.
2. Ne comparez jamais le coût des deux requêtes qui ne sont pas équivalentes. En fait la requête qui utilise une sous-requête scalaire est équivalente avec une jointure externe.
3. N’utilisez jamais les hints que pour des tests, sauf exception notable bien documentée
4. En plus de regarder le coût il est utile de regarder aussi les statistiques d'exécution. Dans votre exemple elles sont identiques ce qui est une bonne indication que quelque chose cloche. En fait dans le deuxième cas le cout de la sous-requête scalaire n’est pas correctement intégré dans l’affichage du plan.
5. Le coût est une estimation du temps que la requête prendra et il est légitime de comparer les divers plan d’exécution d’une même requête ou des requêtes différentes. Mais attention ça reste une estimation et il y a des cases où l’optimiseur se trompe (quoi que de moins en moins).
6. Fournissez toujours un jeu d’essai complet quand vous voulez discuter des décisions prises par l’optimiseur. Cella permettra aux autres de reproduire et de (in)valider vos propos.

[nocay]

Bonjour,

Merci pour votre réponse.
Je n'utilise jamais effectivement ce type de syntaxe pour mes jointures (inner join...) : cela fait-il une réelle différence ? Ou bien est-ce dans le cas où oracle n'assure plus un jour la compatibilité ascendante (ce qui m'étonnerait fortement) ?

Simplement car elle est plus standard et plus lisible dans la plupart des cas

Merci pour votre réponse.
Pour ce qui est des requêtes différentes, sur le principe je suis d'accord, mais
alors pourquoi se force-t-on a faire des jointures tables à tables alors que vraisemblablement il est plus performant de faire des requêtes inline ?

Si nous sommes pragmatique, c'est vrai que, ce qui importe, est le résultat de ta requête, quelle que soit sa forme, et donc quelles performance pour obtenir ce résultat.
Pour cela l’optimiser joue un rôle mais aussi la forme de ta requête. Et là je te dis, à mon grand désespoir, tout dépend de la taille de tes tables, du retour de tes sous requêtes, des index et j'en passe.

Une requête inline ne sera pas forcement plus performante, cela va dépendre du nombre de données quelle doit brasser pour te retourner un résultat.

Si tu rajoutes un v.id=X sur tes 2 requêtes tu auras sans doute un cout comparable.
P.S. : Dans tout les cas l'explain plan dépend de tes données en cours et ne présage en rien des performances que tu auras avec l'augmentation des données de tes tables donc si tu doit passer du temps a faire de l'optimisation de requête il vaux mieux que ta base soit en rythme de croisière.

P.P.S. : j'ai loupé juste avant le mien

[SQLpro]

Comparons les index SQL à la vraie vie....

Si vous cherchez le mot "anticonstitutionnel" dans un ouvrage de droit administratif, vous allez dans l'index et trouverez dans cet index les quelques pages à scruter.
Si maintenant vous cherchez le mot "avec"... vous ne le trouverez pas dans l'index, car on a jugé inutile de l'y mettre, car il y aurait trop de pages référencées, voire toute !
En conclusion, quand vous cherchez "anticonstitutionnel" vous opérez un SEEK (recherche) et dans l'autre cas "avec", vous balayez toutes les pages de l'ouvrage donc un SCAN).
Tout dépend donc de la fréquence d'apparition de la référence dans l'index, mesuré en fait par la sélectivité du critère, ce pourquoi il existe des statistiques cachées derrière les index !
L'optimiseur choisit donc sa stratégie de traitement en fonction de la distribution des données, voire d'autres éléments comme la présence de certaines contraintes....
A +

[SQLpro]

Plusieurs remarques :

1. Ne fait jamais une marotte de l’écriture AINSI pour les requêtes Oracle. A part quelques bugs de plus concernant l’écriture AINSI l’impact sur l’optimisation est nul contrairement à ce que SQL*PRO crois.

Puisque vous me mettez en cause, permettez moi de vous répondre...
Je n'ai pas testé sur oracle, mais sur MS SQL Server, l'optimiseur met un peu moins de temps à trouver un bon plan (sauf cas d'abandon avant exploration totale) du fait qu'il reconnais immédiatement ce qui est du ressort de la jointure (donc des algo de jointure) et ce qui est de la restriction (algo de lecture).
En conclusion, le temps de traitement global est moins long (de très peu...quelques ms) du fait que l'optimiseur met moins de temps et à plus de chance de trouver le meilleur plan !

A +

[Mohamed.Houri]

Pour ceux ou celles qui veulent reproduire le cas présenté ici, ils (elles) trouveront le modèle dans le fichier sql attaché.

Ce sont deux requêtes différentes et donc potentiellement produisant deux plans d’exécution différents. Par contre, il est clair qu’en dehors de certain bugs et de certains paramétrages spécifiques, le seul caprice qu’a l’optimisateur c’est d’avoir de bonnes statistiques à sa disposition. S’il arrive à faire de bonnes estimations il génèrera le meilleur plan d’exécution possible.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
 
SQL> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE	10.2.0.4.0	Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
 
SQL> SELECT
  2     m.libelle_modele
  3    ,v.id_modele
  4  FROM
  5       vehicule v
  6     , modele m
  7  WHERE
  8      m.id_modele = v.id_modele;
 
284 rows selected.
 
SQL> start c:\dispcursor
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
SQL_ID  g6aft1zu1y1a4, child number 0
-------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
|   1 |  MERGE JOIN                  |            |      1 |    284 |    284 |00:00:00.01 |       5 |       |       |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| MODELE     |      1 |     23 |     23 |00:00:00.01 |       4 |       |       |          |
|   3 |    INDEX FULL SCAN           | MOD_PK     |      1 |     23 |     23 |00:00:00.01 |       2 |       |       |          |
|*  4 |   SORT JOIN                  |            |     23 |    284 |    284 |00:00:00.01 |       1 | 22528 | 22528 |20480  (0)|
|   5 |    INDEX FULL SCAN           | VEH_IND_FK |      1 |    284 |    284 |00:00:00.01 |       1 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("M"."ID_MODELE"="V"."ID_MODELE")
       filter("M"."ID_MODELE"="V"."ID_MODELE")
 
SQL> SELECT v.id_vehicule,
  2  	(SELECT m.libelle_modele FROM modele m WHERE m.id_modele = v.id_modele)
  3  FROM vehicule v;
 
284 rows selected.
 
SQL> start c:\dispcursor
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  duw0m50jczzt6, child number 0
Plan hash value: 542114372
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| MODELE   |     23 |      1 |     23 |00:00:00.01 |      25 |
|*  2 |   INDEX UNIQUE SCAN         | MOD_PK   |     23 |      1 |     23 |00:00:00.01 |       2 |
|   3 |  TABLE ACCESS FULL          | VEHICULE |      1 |    284 |    284 |00:00:00.01 |      16 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("M"."ID_MODELE"=:B1)

Les estimations étant très bonnes dans les deux cas, je pense donc qu’il n’y a rien à reprocher ici au CBO

[Mohamed.Houri]

C'est surtout pour une question de lisibilité (et ça aide aussi un peu l'optimiseur).

Je serai ravi que vous nous montriez comment une écriture en ANSI JOIN (ISO JOIN) peut aider l'optimisateur.

Lorsque je sais que le CBO transforme les jointures ANSI en jointure Oracle avant d'établir son meilleur plan d'exécution, je trouve cette réponse étrange un peu.

D'avance merci.

[mnitu]

@SQLPro,

Je dois faire mea culpa parce que je viens de commettre la même erreur que vous : j’ai généralisé sans me rendre compte le comportement de l’optimiseur d’Oracle à des autres bases. Je vous dois donc des excuses et je précise que pour une autre base qu’Oracle l’écriture ANSI pourrait avoir un impact important dans l’élaboration des plans d’exécution.

[LEK]

Hé, voilà un modeste post qui suscite des réactions !
Je ne voulais en aucun cas être polémique; comme je l'ai dit je suis néophyte et essaie simplement de comprendre : mes remarques manquent sûrement de pertinence du fait que je ne sache pas lire correctement un plan d'exécution.
Pour ce qui des jointures avec la syntaxe ISO/ANSI JOIN, si je ne les utilise pas c'est comme je l'ai indiqué par habitude de codage : j'a

[pachot]

Bonjour Lek,

Je me dis que l'optimiseur à jugé qu'il était inutile d'utiliser l'index et qu'un acces full scan était le plus optimal...
(Mais à quoi correspond l'utilisation du hash join dans ce cas ?)

Vu qu'on a besoin de toutes les lignes, le full scan est le mieux effectivement.
Le hash join, c'est ici:
1. Je lis toute la table MODEL (full scan)
2. J'en fais une table de hachage sur la colonne de jointure
3. Je lis tout VEHICULE, et pour chaque ligne je vais voir la table de hachage

Par rapport à l'accès par index, on met un peu plus de temps au début (pour construire la table de hachage) mais on va beaucoup plus vite ensuite car une table de hachage, sourtout si elle loge en mémoire, est beaucoup plus rapide à interroger que de parcourir un arbre d'index.

Pourquoi le côut est-il moins élevé dans ce cas (divisé par 2)?
Pourquoi l'optimiseur n'a-t-il pas utilisé l'index via un range scan dans la première requête?

Bizarre ce plan. Il semble qu'il ne va pas voir la table modèle alors où trouve-t-il le libellé ?

Est-ce que c'est possible de fournir un plan d'exécution de la manière suivante:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
alter session set statistic_level=all;
set autotrace off serveroutput off pagesize 1000 linesize 200
 SELECT v.id_vehicule,
	(SELECT m.libelle_modele FROM modele m WHERE m.id_modele = v.id_modele)
FROM vehicule v;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST ADVANCED'));

Ca donnera beaucoup plus d'infos...

Merci,
Franck.

[FSiebert]

@Mohamed.Houri, j'ai projeté un peu vite l'article de SQLpro sur Oracle parce que ça me paraissait logique. J'ignorais que le CBO transforme les jointures ANSI en jointure Oracle (je pensais que c'était l'inverse, il est vrai sans argument). Un mea culpa de ma part également

Reste l'argument de la lisibilité qui pour moi justifie pleinement cette syntaxe.

En tout cas, merci d'avoir relevé mon erreur et d'avoir expliqué le fonctionnement du CBO. Dans quelle partie de la doc Oracle se trouve ces informations ? J'aimerai en lire plus.

[Mohamed.Houri]

@Mohamed.Houri, j'ai projeté un peu vite l'article de SQLpro sur Oracle parce que ça me paraissait logique. J'ignorais que le CBO transforme les jointures ANSI en jointure Oracle (je pensais que c'était l'inverse, il est vrai sans argument). Un mea culpa de ma part également

Reste l'argument de la lisibilité qui pour moi justifie pleinement cette syntaxe.

En tout cas, merci d'avoir relevé mon erreur et d'avoir expliqué le fonctionnement du CBO. Dans quelle partie de la doc Oracle se trouve ces informations ? J'aimerai en lire plus.

Cher Florent,

Je n'ai pas de documentation en vue mais c'est surtout au travers de quelques traces 10053 que j'ai remarqué cela.

Et vous avez raison pour l'argument de la lisibilité. Je pense qu'il faut simplement déconseiller un mélange des deux types de jointures dans la même requête et quelques autres règles exposées par exemple ici

[FSiebert]

Un peu trop de contenu pour lire tout ça maintenant, mais je sauvegarde le lien, merci beaucoup.
Il faudrait aussi que j'étudie quelques traces pour en apprendre plus.

Bonne continuation.

[LEK]

Hé, voilà un modeste post qui suscite bien des réactions !
Je ne voulais en aucun cas être polémique : comme je l'ai indiqué je me pose là en néophyte et si mes remarques manquent de pertinence c'est probablement que je ne sais pas encore correctement lire (ni quoi lire visiblement) dans un plan d'exécution...
En fait je suis parti d'une requête relativement complexe que j'essaye d'optimisé (le coût de plus de 340 000 et les 4 secondes pour obtenir le résultat me semblaient effectivement exagéré) : donc je l'ai décomposé en sous-ensemble plus gérables et j'essaye de comprendre en recréant des tables de test...
Bon c'est une requête dont j'ai "hérité" et il est vrai qu'après suppression des produits cartésiens qu'elle contenait et d'autres points dans ce goût là j'arrive à une réponse plus satisfaisante... mais pris dans la lancée je me disais que je pouvais aller plus loin (en fait je manipule des données dans 4/6 tables qui possèdent au plus 200/300 lignes donc les perf désastreuses annoncées m'étonnaient au plus haut point! )

A priori, ce n'est pas la bonne méthode, je devrais si je comprend bien ne travailler que sur les tables d'origine (relativement au volume, à l'indexation, l'état des stats)... Je devrais comparer ce qui est comparable...
J'aurais voulu poster mes données réelles de tests mais dû à des contraintes de licences je ne peux malheureusement pas.