Problème étrange de performances avec clause "ORDER BY"

**StringBuilder** · 03/06/2014, 11h00

Bonjour,

J'ai une requête assez complexe (plusieurs dizaines de lignes) qui a pour point d'entrée une table qui contient, entre autres, deux colonnes : code produit et code dépôt.

Lorsque j'exécute la requête avec "order by code produit", elle dure moins de 2 secondes.
Lorsque j'éxécute la requête avec "order by code dépôt", elle dure plus de 15 minutes.

Le volume retourné est faible (moins de 100 lignes).

Le plan d'exécution réel (constaté dans la console Oracle) est le même.

Qu'est-ce qui peut expliquer une telle différence de performances ?

**mnitu** · 03/06/2014, 16h01

Bref j'ai du mal à croire que le plan d'exécution est le même donc postez le plan d'exécution des deux requêtes en utilisant dbms_plan.display_cursor (voir les exemples fournis ou Finding Plans) et en affichant les estimations, le nombre réel de lignes et les prédicats. Ajoutez aussi le paramétrage de l'optimiseur.

**StringBuilder** · 04/06/2014, 09h53

Voici ce que j'ai dans la console Oracle :

Requête 1 :

Code SQL :

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
SELECT MevDsk.CODSOC, MevDsk.CODENT, MevDsk.SEGMENT, MevDsk.CODSOC_PHY, JDsk.SIGDEP, JDsk.CODPRO, JDsk.LIBSTK, JDsk.CODEMP, JDsk.CODUNI, JDsk.DATCRE, JDsk.DATPER, JDsk.DATPMP, 
JDsk.PUMP, JDsk.VALPUMP, JDsk.DATDPA, JDsk.DPA, JDsk.VALDPA, JDsk.CODREA, JDsk.CODCRB, JDsk.CSS, JDsk.CSO, JDsk.CPC, JDsk.CQE, JDsk.PERINV, JDsk.NUMINV, JDsk.DATINV, JDsk.DATPIN, 
JDsk.C01, JDsk.C02, JDsk.C03, JDsk.C04, JDsk.C05, JDsk.C06, JDsk.C07, JDsk.C08, JDsk.C09, JDsk.C10, JDsk.C11, JDsk.C12, JDsk.C13, JDsk.C14, JDsk.DATMSK, JDsk.NUMMSK, JDsk.DATMOD, 
JDsk.UTIMOD, JDsk.QTEMIN, JDsk.QOTITE, JDsk.TYPAFF, JDsk.CODSOC AS CODSOC1, JDsk.SUISTK, JDsk.CODSOC_PHY AS CODSOC_PHY1, JDsk.STATUT, JDsk.NOMPOR, JDsk.LMODCOL, JDsk.CLSINV, 
JDsk.MODENT, JDsk.MODSOR, JDsk.GESNUMLOT, JDsk.GESNUMSER, JDsk.TYPFIFO, JDsk.TOLFIFO, JDsk.UNTTOLFIFO, JDsk.TOLPDS, JDsk.RETENTION, JDsk.DURDLV, JDsk.UNTDURDLV, JDsk.DURDLUO, 
JDsk.UNTDURDLUO, JDsk.DURDLC, JDsk.UNTDURDLC, JDsk.CODEMP1, JDsk.CODEMP2, JDsk.NBREMP, JDsk.GESPICK, JDsk.OCCMAX1, JDsk.CNTTYP, JDsk.CNTCOD, JDsk.CLAVOL, JDsk.LCODMAG, JDsk.NBREMP1, 
JDsk.MCALCVC, JDsk.NBJCVC, JDsk.NIVROT, JDsk.MODAPPRO, JDsk.SIGTIE_SER, JDsk.DELREA, JDsk.DELSUP, JDsk.DELPRE, JDsk.DELSEC, JDsk.CLAABC, JDsk.CLAABCP, JDsk.TYPTIE_SER, JDsk.FAMLOG, 
JDsk.SFALOG, JDsk.SSFLOG, JDsk.RECOND, JDsk.DATMAR, JDsk.C15, JDsk.C16, JDsk.C17, JDsk.C18, JDsk.C19, JDsk.C20, JDsk.C21, JDsk.C22, JDsk.C23, JDsk.C24, JDsk.C25, JDsk.C26, JDsk.C27, 
JDsk.C28, JDsk.C29, JDsk.C30, MevAsk.CODSOC AS CODSOC2, MevAsk.CODENT AS CODENT1, MevAsk.SEGMENT AS SEGMENT1, MevAsk.CODSOC_PHY AS CODSOC_PHY2, JAsk.CODASK, JAsk.LIBASK, JAsk.LIRASK, 
JAsk.CODCSK1, JAsk.CODCSK2, JAsk.CODCSK3, JAsk.CODCSK4, JAsk.CODCSK5, JAsk.CODCSK6, JAsk.CODCSK7, JAsk.CODCSK8, JAsk.CODCSK9, JAsk.CODCSK10, JAsk.CODCSK11, JAsk.CODCSK12, JAsk.CODCSK13, 
JAsk.CODCSK14, JAsk.DATMOD AS DATMOD1, JAsk.UTIMOD AS UTIMOD1, JAsk.CODSOC AS CODSOC3, JAsk.CODUNI AS CODUNI1, JAsk.FILASK FROM DSK JDsk, MEV MevDsk, ASK JAsk, MEV MevAsk 
WHERE MevDsk.CODSOC = MevAsk.CODSOC and MevDsk.CODENT = 'DSK' and MevDsk.SEGMENT = ' ' and JDsk.CODSOC = MevDsk.CODSOC_PHY and MevAsk.CODENT = 'ASK' and MevAsk.SEGMENT = ' ' 
and JAsk.CODSOC = MevAsk.CODSOC_PHY AND (MevDsk.Codsoc = 1 AND NOT (JDsk.coduni = ' ') AND Codask = 'COSK' AND (Sigdep IN (SELECT DISTINCT (Cleque2) FROM Hque, Mev 
WHERE Mev.Codent = 'HQUE' AND Segment = 'VHDP' AND Mev.Codsoc = MevDsk.Codsoc AND Codsoc_phy = Hque.Codsoc AND Achvte = 'V' AND Codtli = 'HDP' AND Typque1 = '90' AND Cleque1 = 'INFO') 
OR 'INFO' IN (SELECT DISTINCT (Cleque1) FROM Hque, Mev WHERE Mev.Codent = 'HQUE' AND Segment = 'VHDP' AND Mev.Codsoc = MevDsk.Codsoc AND Codsoc_phy = Hque.Codsoc AND Achvte = 'V' 
AND Codtli = 'HDP' AND Typque1 = '90' AND Typque2 = '5')) AND Codpro IN (SELECT Codpro FROM Pro, Mev WHERE Mev.Codent = 'PRO' AND Mev.Codsoc = MevDsk.Codsoc 
AND Pro.Codsoc = Mev.Codsoc_phy AND NOT (suistk = 'N') AND Pro.Codzn2 = 'M') AND (JDsk.Sigdep IN (SELECT zod.clezod FROM mev Mevzod, zod WHERE Mevzod.codent = 'ZOD' 
AND Mevzod.SEGMENT = 'DEP' AND Mevzod.codsoc_phy = zod.codsoc AND Mevzod.codsoc = MevDsk.Codsoc AND zod.typzod = 'DEP' AND zod.numzod = '203' AND zod.valzod = 'B08'))) ORDER BY Sigdep

Hashage du plan d'exécution : 2863121916

Plan d'exécution :
SELECT STATEMENT

35

2855

SORT ORDER BY

34 1 0,428 2855 35 91537522 2851
FILTER

33

TABLE ACCESS BY INDEX ROWID DSK TABLE 24 1 0,267 2547 31 46946367 2545
NESTED LOOPS

23 1 0,428 2846 35 67260936 2843
NESTED LOOPS

16 2 0,322 12 1 106767 12
NESTED LOOPS

8 1 0,141 6 1 47799 6
NESTED LOOPS

5 1 0,049 5 1 39057 5
TABLE ACCESS BY INDEX ROWID MEV TABLE 2 1 0,022 3 1 22664 3
INDEX UNIQUE SCAN MEV_IDX1 INDEX (UNIQUE) 1 1
2 1 15293 2
TABLE ACCESS BY INDEX ROWID MEV TABLE 4 1 0,026 2 1 16393 2
INDEX UNIQUE SCAN MEV_IDX1 INDEX (UNIQUE) 3 1
1 1 9021 1
TABLE ACCESS BY INDEX ROWID ASK TABLE 7 1 0,092 1 1 8741 1
INDEX UNIQUE SCAN ASK_IDX1 INDEX (UNIQUE) 6 1
0
1050 0
VIEW VW_SQ_1 VIEW 15 2 0,041 6 1 58969 6
SORT UNIQUE

14 1 0,054

NESTED LOOPS

13 1 0,054 6 1 58969 6
TABLE ACCESS BY INDEX ROWID MEV TABLE 10 1 0,019 3 1 22664 3
INDEX UNIQUE SCAN MEV_IDX1 INDEX (UNIQUE) 9 1
2 1 15293 2
TABLE ACCESS BY INDEX ROWID ZOD TABLE 12 1 0,035 4 1 36304 4
INDEX RANGE SCAN ZOD_IDX1 INDEX (UNIQUE) 11 1
3 1 28914 3
INDEX RANGE SCAN DSK_IDX2 INDEX 22 4130
287 4 20207802 286
TABLE ACCESS BY INDEX ROWID MEV TABLE 21 1 0,014 4 1 30236 4
NESTED LOOPS

20 1 0,029 8 1 61246 8
TABLE ACCESS BY INDEX ROWID PRO TABLE 18 1 0,016 4 1 31010 4
INDEX SKIP SCAN PRO_IDX1 INDEX (UNIQUE) 17 1
3 1 21564 3
INDEX RANGE SCAN MEV_IDX1 INDEX (UNIQUE) 19 2
2 1 15493 2
NESTED LOOPS

28 1 0,046 5 1 38007 5
TABLE ACCESS BY INDEX ROWID MEV TABLE 26 1 0,019 3 1 22664 3
INDEX UNIQUE SCAN MEV_IDX1 INDEX (UNIQUE) 25 1
2 1 15293 2
INDEX RANGE SCAN HQUE_IDX1 INDEX (UNIQUE) 27 1 0,027 2 1 15343 2
NESTED LOOPS

32 1 0,043 5 1 37957 5
TABLE ACCESS BY INDEX ROWID MEV TABLE 30 1 0,019 3 1 22664 3
INDEX UNIQUE SCAN MEV_IDX1 INDEX (UNIQUE) 29 1
2 1 15293 2
INDEX RANGE SCAN HQUE_IDX1 INDEX (UNIQUE) 31 1 0,024 2 1 15293 2

Et pour la requête 2 :

Code sql :

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
SELECT MevDsk.CODSOC, MevDsk.CODENT, MevDsk.SEGMENT, MevDsk.CODSOC_PHY, JDsk.SIGDEP, JDsk.CODPRO, JDsk.LIBSTK, JDsk.CODEMP, JDsk.CODUNI, JDsk.DATCRE, JDsk.DATPER, JDsk.DATPMP, 
JDsk.PUMP, JDsk.VALPUMP, JDsk.DATDPA, JDsk.DPA, JDsk.VALDPA, JDsk.CODREA, JDsk.CODCRB, JDsk.CSS, JDsk.CSO, JDsk.CPC, JDsk.CQE, JDsk.PERINV, JDsk.NUMINV, JDsk.DATINV, JDsk.DATPIN, 
JDsk.C01, JDsk.C02, JDsk.C03, JDsk.C04, JDsk.C05, JDsk.C06, JDsk.C07, JDsk.C08, JDsk.C09, JDsk.C10, JDsk.C11, JDsk.C12, JDsk.C13, JDsk.C14, JDsk.DATMSK, JDsk.NUMMSK, JDsk.DATMOD, 
JDsk.UTIMOD, JDsk.QTEMIN, JDsk.QOTITE, JDsk.TYPAFF, JDsk.CODSOC AS CODSOC1, JDsk.SUISTK, JDsk.CODSOC_PHY AS CODSOC_PHY1, JDsk.STATUT, JDsk.NOMPOR, JDsk.LMODCOL, JDsk.CLSINV, 
JDsk.MODENT, JDsk.MODSOR, JDsk.GESNUMLOT, JDsk.GESNUMSER, JDsk.TYPFIFO, JDsk.TOLFIFO, JDsk.UNTTOLFIFO, JDsk.TOLPDS, JDsk.RETENTION, JDsk.DURDLV, JDsk.UNTDURDLV, JDsk.DURDLUO, 
JDsk.UNTDURDLUO, JDsk.DURDLC, JDsk.UNTDURDLC, JDsk.CODEMP1, JDsk.CODEMP2, JDsk.NBREMP, JDsk.GESPICK, JDsk.OCCMAX1, JDsk.CNTTYP, JDsk.CNTCOD, JDsk.CLAVOL, JDsk.LCODMAG, JDsk.NBREMP1, 
JDsk.MCALCVC, JDsk.NBJCVC, JDsk.NIVROT, JDsk.MODAPPRO, JDsk.SIGTIE_SER, JDsk.DELREA, JDsk.DELSUP, JDsk.DELPRE, JDsk.DELSEC, JDsk.CLAABC, JDsk.CLAABCP, JDsk.TYPTIE_SER, JDsk.FAMLOG, 
JDsk.SFALOG, JDsk.SSFLOG, JDsk.RECOND, JDsk.DATMAR, JDsk.C15, JDsk.C16, JDsk.C17, JDsk.C18, JDsk.C19, JDsk.C20, JDsk.C21, JDsk.C22, JDsk.C23, JDsk.C24, JDsk.C25, JDsk.C26, JDsk.C27, 
JDsk.C28, JDsk.C29, JDsk.C30, MevAsk.CODSOC AS CODSOC2, MevAsk.CODENT AS CODENT1, MevAsk.SEGMENT AS SEGMENT1, MevAsk.CODSOC_PHY AS CODSOC_PHY2, JAsk.CODASK, JAsk.LIBASK, JAsk.LIRASK, 
JAsk.CODCSK1, JAsk.CODCSK2, JAsk.CODCSK3, JAsk.CODCSK4, JAsk.CODCSK5, JAsk.CODCSK6, JAsk.CODCSK7, JAsk.CODCSK8, JAsk.CODCSK9, JAsk.CODCSK10, JAsk.CODCSK11, JAsk.CODCSK12, JAsk.CODCSK13, 
JAsk.CODCSK14, JAsk.DATMOD AS DATMOD1, JAsk.UTIMOD AS UTIMOD1, JAsk.CODSOC AS CODSOC3, JAsk.CODUNI AS CODUNI1, JAsk.FILASK FROM DSK JDsk, MEV MevDsk, ASK JAsk, MEV MevAsk 
WHERE MevDsk.CODSOC = MevAsk.CODSOC and MevDsk.CODENT = 'DSK' and MevDsk.SEGMENT = ' ' and JDsk.CODSOC = MevDsk.CODSOC_PHY and MevAsk.CODENT = 'ASK' and MevAsk.SEGMENT = ' ' 
and JAsk.CODSOC = MevAsk.CODSOC_PHY AND (MevDsk.Codsoc = 1 AND NOT (JDsk.coduni = ' ') AND Codask = 'COSK' AND (Sigdep IN (SELECT DISTINCT (Cleque2) FROM Hque, Mev 
WHERE Mev.Codent = 'HQUE' AND Segment = 'VHDP' AND Mev.Codsoc = MevDsk.Codsoc AND Codsoc_phy = Hque.Codsoc AND Achvte = 'V' AND Codtli = 'HDP' AND Typque1 = '90' AND Cleque1 = 'INFO') 
OR 'INFO' IN (SELECT DISTINCT (Cleque1) FROM Hque, Mev WHERE Mev.Codent = 'HQUE' AND Segment = 'VHDP' AND Mev.Codsoc = MevDsk.Codsoc AND Codsoc_phy = Hque.Codsoc AND Achvte = 'V' 
AND Codtli = 'HDP' AND Typque1 = '90' AND Typque2 = '5')) AND Codpro IN (SELECT Codpro FROM Pro, Mev WHERE Mev.Codent = 'PRO' AND Mev.Codsoc = MevDsk.Codsoc 
AND Pro.Codsoc = Mev.Codsoc_phy AND NOT (suistk = 'N') AND Pro.Codzn2 = 'M') AND (JDsk.Sigdep IN (SELECT zod.clezod FROM mev Mevzod, zod WHERE Mevzod.codent = 'ZOD' 
AND Mevzod.SEGMENT = 'DEP' AND Mevzod.codsoc_phy = zod.codsoc AND Mevzod.codsoc = MevDsk.Codsoc AND zod.typzod = 'DEP' AND zod.numzod = '203' AND zod.valzod = 'B08'))) ORDER BY Codpro

(Y'a que le "ORDER BY" qui change)

Hashage du plan d'exécution : 2863121916

Plan d'exécution :
SELECT STATEMENT

35

2855

SORT ORDER BY

34 1 0,428 2855 35 91537522 2851
FILTER

33

TABLE ACCESS BY INDEX ROWID DSK TABLE 24 1 0,267 2547 31 46946367 2545
NESTED LOOPS

23 1 0,428 2846 35 67260936 2843
NESTED LOOPS

16 2 0,322 12 1 106767 12
NESTED LOOPS

8 1 0,141 6 1 47799 6
NESTED LOOPS

5 1 0,049 5 1 39057 5
TABLE ACCESS BY INDEX ROWID MEV TABLE 2 1 0,022 3 1 22664 3
INDEX UNIQUE SCAN MEV_IDX1 INDEX (UNIQUE) 1 1
2 1 15293 2
TABLE ACCESS BY INDEX ROWID MEV TABLE 4 1 0,026 2 1 16393 2
INDEX UNIQUE SCAN MEV_IDX1 INDEX (UNIQUE) 3 1
1 1 9021 1
TABLE ACCESS BY INDEX ROWID ASK TABLE 7 1 0,092 1 1 8741 1
INDEX UNIQUE SCAN ASK_IDX1 INDEX (UNIQUE) 6 1
0
1050 0
VIEW VW_SQ_1 VIEW 15 2 0,041 6 1 58969 6
SORT UNIQUE

14 1 0,054

NESTED LOOPS

13 1 0,054 6 1 58969 6
TABLE ACCESS BY INDEX ROWID MEV TABLE 10 1 0,019 3 1 22664 3
INDEX UNIQUE SCAN MEV_IDX1 INDEX (UNIQUE) 9 1
2 1 15293 2
TABLE ACCESS BY INDEX ROWID ZOD TABLE 12 1 0,035 4 1 36304 4
INDEX RANGE SCAN ZOD_IDX1 INDEX (UNIQUE) 11 1
3 1 28914 3
INDEX RANGE SCAN DSK_IDX2 INDEX 22 4130
287 4 20207802 286
TABLE ACCESS BY INDEX ROWID MEV TABLE 21 1 0,014 4 1 30236 4
NESTED LOOPS

20 1 0,029 8 1 61246 8
TABLE ACCESS BY INDEX ROWID PRO TABLE 18 1 0,016 4 1 31010 4
INDEX SKIP SCAN PRO_IDX1 INDEX (UNIQUE) 17 1
3 1 21564 3
INDEX RANGE SCAN MEV_IDX1 INDEX (UNIQUE) 19 2
2 1 15493 2
NESTED LOOPS

28 1 0,046 5 1 38007 5
TABLE ACCESS BY INDEX ROWID MEV TABLE 26 1 0,019 3 1 22664 3
INDEX UNIQUE SCAN MEV_IDX1 INDEX (UNIQUE) 25 1
2 1 15293 2
INDEX RANGE SCAN HQUE_IDX1 INDEX (UNIQUE) 27 1 0,027 2 1 15343 2
NESTED LOOPS

32 1 0,043 5 1 37957 5
TABLE ACCESS BY INDEX ROWID MEV TABLE 30 1 0,019 3 1 22664 3
INDEX UNIQUE SCAN MEV_IDX1 INDEX (UNIQUE) 29 1
2 1 15293 2
INDEX RANGE SCAN HQUE_IDX1 INDEX (UNIQUE) 31 1 0,024 2 1 15293 2

Plan strictement identique.
En revanche, les statistiques ne sont absolument pas les mêmes.

La première requête :
- 38% attente E/S
- 62% UC

Et la seconde :
- 100% UC

Ce qui est incompréhensible, c'est que vu qu'il y a réutilisation de données en cache (puisqu'il n'y a pas d'IO) ça devrait être plus rapide non ????

-- Attendre la suite avant de répondre, je viens de relancer la seconde requête, et même si c'est pas fini, je vois des choses complètement différentes cette fois !
(autre plan, autres statistiques, etc.) A n'y plus rien comprendre... Mais c'est toujours aussi lent par contre...

**StringBuilder** · 04/06/2014, 10h01

Bon, désolé, je comprends rien.
La console Oraclce "invente" des requêtes.

Ma requête numéro 2, finalement n'est pas du tout la même.
Et du coup il y a un plan différent, mais surtout, un accès à une table très peu performante (lié au modèle des données, table fourre-tout de plusieurs centaines de millions de lignes).

J'arrive pas à comprendre comment, avec les données en cache, Oracle s'en sort en découpant la requête en deux... Car la requête avec "order by codpro", elle existe juste pas dans le code, donc c'est Oracle qui l'a écrite...

Vu que l'accès à la table "lente" est dans une clause "in", j'imagine qu'il s'est dit "tiens, je vais réutiliser les données de la précédente requête en cache (qui contient les mêmes données, qui sont simplement à filtrer) les réordonner (changement du order by) et application d'un filtre ni vu ni connus...

Ou alors je fume des trucs pas clairs à mon insi.

En tout cas, problème "résolu". J'ai enfin le plan de la vraie requête et l'explication du pourquoi du comment elle est si lente. Reste plus qu'à trouver comment l'optimiser.