requête correlée lente

**assahdo** · 03/06/2010, 16h29

Bonjour,
J'ai une requete corrélée qui est trop lente à cause du not exists et la correlation dont le code est le suivant:

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
 
SELECT e.VEHICLE_SEQNO
 
      FROM GF_TRACING_EVENTS_WRK e, OB_JFO_PROCOP p
 
     WHERE e.JFO_SEQNO = p.JFO_SEQNO
 
       AND p.OPERATION_STATUS = 'EX'
 
       AND p.PROCOP_KIND IN ('DEC', 'SDL', 'TDL', 'PXD', 'DAT', 'WRK')
 
       AND NOT EXISTS (SELECT NULL
 
                         FROM OB_TRACE_EVENT e, OB_DEFAULT_EVENT de
 
                        WHERE e.EVENT_ID = de.EVENT_ID
 
                          AND e.EVENT_SUB_ID = de.EVENT_SUB_ID
 
                          AND SUBSTR(de.DEFAULT_EVENT_KIND, 1, 4) = 'END_'
 
                          AND e.PROCOP_SEQNO = p.PROCOP_SEQNO)

Est ce qu'on peut réecrire cette requête en évitant la corrélation???

Merci d'avance.

**McM** · 03/06/2010, 16h32

Il faut l'explain plan pour qu'on sache ce qui va pas.

**assahdo** · 03/06/2010, 16h37

Oui, le plan que j'ai eu avec l'autotrace traceonly est le suivant:

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
 
Elapsed: 00:09:21.87
 
Execution Plan
----------------------------------------------------------                                                                             
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=7283 Card=73004 Byte                                                                 
          s=3358184)                                                                                                                   
 
   1    0   NESTED LOOPS (Cost=7283 Card=73004 Bytes=3358184)                                                                          
   2    1     TABLE ACCESS (FULL) OF 'GF_TRACING_EVENTS_WRK' (Cost=40                                                                  
          Card=72423 Bytes=1882998)                                                                                                    
 
   3    1     INLIST ITERATOR                                                                                                          
   4    3       INDEX (RANGE SCAN) OF 'JFOPROCOP_OPTIM_SB01' (NON-UNIQ                                                                 
          UE) (Cost=1 Card=1 Bytes=20)                                                                                                 
 
   5    4         TABLE ACCESS (BY INDEX ROWID) OF 'OB_DEFAULT_EVENT'                                                                  
          (Cost=2 Card=1 Bytes=32)                                                                                                     
 
   6    5           NESTED LOOPS (Cost=3 Card=1 Bytes=55)                                                                              
   7    6             TABLE ACCESS (BY INDEX ROWID) OF 'OB_TRACE_EVENT                                                                 
          ' (Cost=2 Card=4 Bytes=92)                                                                                                   
 
   8    7               INDEX (RANGE SCAN) OF 'TRACEEVENT_PROCOP' (NON                                                                 
          -UNIQUE) (Cost=4 Card=3)                                                                                                     
 
   9    6             INDEX (RANGE SCAN) OF 'DFLTEVENT_SUBEVENT_FK' (N                                                                 
          ON-UNIQUE) (Cost=1 Card=1)                                                                                                   
 
 
 
 
 
Statistics
----------------------------------------------------------                                                                             
         12  recursive calls                                                                                                           
          0  db block gets                                                                                                             
    1502367  consistent gets                                                                                                           
      17503  physical reads                                                                                                            
       3796  redo size                                                                                                                 
        139  bytes sent via SQL*Net to client                                                                                          
        228  bytes received via SQL*Net from client                                                                                    
          1  SQL*Net roundtrips to/from client                                                                                         
          1  sorts (memory)                                                                                                            
          0  sorts (disk)                                                                                                              
          0  rows processed

**mnitu** · 03/06/2010, 17h00

Le formatage de votre plan est difficile à lire. Essayez :

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
 
SELECT e.VEHICLE_SEQNO 
      FROM GF_TRACING_EVENTS_WRK e  
     WHERE e.JFO_SEQNO In (Select p.JFO_SEQNO 
                             From OB_JFO_PROCOP p
                            where p.OPERATION_STATUS = 'EX' 
                              AND p.PROCOP_KIND IN ('DEC', 'SDL', 'TDL', 'PXD', 'DAT', 'WRK')
                              AND NOT EXISTS (SELECT NULL
                                                FROM OB_TRACE_EVENT e, OB_DEFAULT_EVENT de
                                               WHERE e.EVENT_ID = de.EVENT_ID
                                                 AND e.EVENT_SUB_ID = de.EVENT_SUB_ID
                                                 AND SUBSTR(de.DEFAULT_EVENT_KIND, 1, 4) = 'END_'
                                                 AND e.PROCOP_SEQNO = p.PROCOP_SEQNO)
                           )                       
/

**assahdo** · 03/06/2010, 17h19

Merci mnitu pour votre reponse, mais elle prend toujours un temps pas possible.

**Waldar** · 03/06/2010, 17h45

Dans votre première requête vous utilisez deux fois le même alias e.
Impossible de savoir ce que vous cherchez à faire vraiment.

C'est peut-être ça, peut-être pas ça :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT e.VEHICLE_SEQNO
  FROM GF_TRACING_EVENTS_WRK e
       INNER JOIN OB_JFO_PROCOP p
         ON p.JFO_SEQNO = e.JFO_SEQNO
 WHERE p.OPERATION_STATUS = 'EX'
   AND p.PROCOP_KIND IN ('DEC', 'SDL', 'TDL', 'PXD', 'DAT', 'WRK')
   AND NOT EXISTS (SELECT NULL
                     FROM OB_TRACE_EVENT te
                          INNER JOIN OB_DEFAULT_EVENT de
                            ON de.EVENT_ID     = te.EVENT_ID
                           AND de.EVENT_SUB_ID = te.EVENT_SUB_ID
                    WHERE de.DEFAULT_EVENT_KIND like 'END_%'
                      AND te.PROCOP_SEQNO = p.PROCOP_SEQNO);

**assahdo** · 03/06/2010, 18h19

Merci waldar,
je vais essayer, c peut etre ça!! je signale aussi que les tables OB_TRACE_EVENT ,OB_JFO_PROCOP sont tres volumineuses > 1 millard de lignes chacune, la table GF_TRACING_EVENTS_WRK fait 600 Milles.

**Waldar** · 03/06/2010, 18h32

Vos tables sont-elles partitionnées ?
Sont-elles compressées ?
Quels sont les index dessus ?

Vous êtes sûr de vos volumétrie ? D'après votre explain plan votre table GF_TRACING_EVENTS_WRK ne fait que 72.000 lignes, pas 600.000.
Vos statistiques sont-elles à jour ?

**mnitu** · 04/06/2010, 09h17

Envoyé par Waldar

Vos tables sont-elles partitionnées ?
Sont-elles compressées ?
Quels sont les index dessus ?

Vous êtes sûr de vos volumétrie ? D'après votre explain plan votre table GF_TRACING_EVENTS_WRK ne fait que 72.000 lignes, pas 600.000.
Vos statistiques sont-elles à jour ?

Statistiques non représentatives => Estimation erronées des cardinalités => Chemin d’accès dégradé => Méthodes de jointures dégradées => Ordre de jointure dégradé => Temps d’exécution dégradés

**assahdo** · 04/06/2010, 09h34

Effectivement, vous avez raison, les stats sont obsolètes, sinon y'a-t-il une autre façon d'eviter la correlation en faisant une jointure par exemple ou autre ???
Merci à tous pour votre coup de main

**assahdo** · 04/06/2010, 10h51

Bonjour Waldar,

En fait j'ai testé votre ré-écriture, et ça marche mieux qu'avant.

Merci.

Envoyé par Waldar

Dans votre première requête vous utilisez deux fois le même alias e.
Impossible de savoir ce que vous cherchez à faire vraiment.

C'est peut-être ça, peut-être pas ça :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT e.VEHICLE_SEQNO
  FROM GF_TRACING_EVENTS_WRK e
       INNER JOIN OB_JFO_PROCOP p
         ON p.JFO_SEQNO = e.JFO_SEQNO
 WHERE p.OPERATION_STATUS = 'EX'
   AND p.PROCOP_KIND IN ('DEC', 'SDL', 'TDL', 'PXD', 'DAT', 'WRK')
   AND NOT EXISTS (SELECT NULL
                     FROM OB_TRACE_EVENT te
                          INNER JOIN OB_DEFAULT_EVENT de
                            ON de.EVENT_ID     = te.EVENT_ID
                           AND de.EVENT_SUB_ID = te.EVENT_SUB_ID
                    WHERE de.DEFAULT_EVENT_KIND like 'END_%'
                      AND te.PROCOP_SEQNO = p.PROCOP_SEQNO);

**Waldar** · 04/06/2010, 10h54

Il n'y a aucun soucis à écrire une requête corrélée.
Mais dès lors que vos stats sont trop loin de la réalité, n'importe quelle requête peut se mettre à mouliner sans raison.

On peut écrire la même requête en utilisant une jointure externe, mais les plans sont quasi-similaires.

Bref : rafraîchissez vos statistiques !

Quant à ma requête, je ne suis pas sûr que ce soit ce que vous vouliez faire, j'ai modifié les deux alias "e", je ne suis pas certain d'avoir fait les bons choix.

**assahdo** · 04/06/2010, 11h10

En fait c'est une requête qui provient d'un progiciel, je ne sais pas ce qu'elle fait, et on me demande de la tuner sans toucher au stats. c'est spécial!!
cdt.

**pacmann** · 04/06/2010, 12h03

Salut !

Si c'est un problème des statistiques et que tu n'as pas le droit de toucher aux statistiques :
- Rafraîchis les stats sur ton environnement de test (si tu veux, tu peux sauvegarder les anciennes)
- Exécuter la requête, et regarde le nouveau plan
- Mets des hints dans ta requête pour que ça fasse la même chose avec les stats pourries...

(c'est sale, mais vu que tu as un peu les poings liés...)

Tu peux aussi tenter le dynamic sampling ?

PS : oh Waldar, t'es devenu modérateur ! Félicitations

**assahdo** · 04/06/2010, 12h28

Merci pour tes précisions, (tenter le dynamic sampling) ça sert à quoi exactement, et en plus je ne sais pas faire !!!

**pacmann** · 04/06/2010, 13h27

Le dynamic sampling, c'est le fait d'échantilloner les tables soit parce que tes statistiques n'existent pas, soit parce que le CBO utilise des hypothèses inappropriées pour estimer la cardinalité résultant d'un ou plusieurs prédicats, ...

Tu as un paramètre que tu peux modifier (en alter session par exemple) :
ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING=2

Ou utiliser un hint :
/*+dynamic_sampling(t,2)*/

Le "2", c'est un paramètre qui indique dans quels cas ce sera fait, et combien il va échantillonner.

Je te laisse chercher un peu et te documenter sur le net, par exemple :
http://www.oracle.com/technology/ora...o19asktom.html

**assahdo** · 07/06/2010, 11h43

Merci pacmann.

requête correlée lente

SQL Oracle

Discussions similaires

Partager

Partager