Requête à optimiser

**farenheiit** · 24/03/2009, 11h51

Bonjour,

On me demande d'optimiser la requête suivante:

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
SELECT P.IDINPS, NVL(TO_CHAR(P.DARSEF, 'DD-MON-YY'), 'XXXXXXXXX')
  2  FROM PSTEGF P, CTINGF C, (SELECT MAX(PS.IDINPS) as IDINPS
  3  	 		  FROM PSTEGF PS, CTINGF CT
  4  			  WHERE
  5  			  PS.IDEXP1 = 'A115332'
  6  			  	AND PS.TYPSTE = 'ARP'
  7  			  	AND
  8  				PS.NUCTIN = CT.NUCTIN
  9  				AND CT.CDOFIN = 'ARPEGE_OPT'
 10  				AND PS.DATEAC IS NULL
 11  			  ) ID_MAX
 12  WHERE
 13  P.IDEXP1 = 'A115332'
 14  AND P.TYPSTE = 'ARP'
 15  AND
 16  P.NUCTIN = C.NUCTIN
 17  AND C.CDOFIN = 'ARPEGE_OPT'
 18  AND P.DATEAC IS NULL
 19  AND C.DATEAC IS NULL
 20  AND ID_MAX.IDINPS = P.IDINPS;

Voici ce que donne l'autotrace:

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
70
71
72
73
74
75
76
77
78
79
80
Ecoulé : 00 :00 :00.07
 
Plan d'exécution
----------------------------------------------------------                      
          0                                                                     
SELECT STATEMENT Optimizer=CHOOSE (Cost=53 Card=1 Bytes=68)                     
 
 
          1                  0                                                  
  TABLE ACCESS (BY INDEX ROWID) OF 'CTINGF' (TABLE) (Cost=6 Card=1 Bytes=19)    
 
 
          2                  1                                                  
    NESTED LOOPS (Cost=53 Card=1 Bytes=68)                                      
 
 
          3                  2                                                  
      NESTED LOOPS (Cost=47 Card=1 Bytes=49)                                    
 
 
          4                  3                                                  
        VIEW (Cost=27 Card=1 Bytes=7)                                           
 
 
          5                  4                                                  
          SORT (AGGREGATE)                                                      
 
 
          6                  5                                                  
            TABLE ACCESS (BY INDEX ROWID) OF 'CTINGF' (TABLE) (Cost=6 Card=1 Byt
es=13)                                                                          
 
 
          7                  6                                                  
              NESTED LOOPS (Cost=27 Card=1 Bytes=52)                            
 
 
          8                  7                                                  
                TABLE ACCESS (BY INDEX ROWID) OF 'PSTEGF' (TABLE) (Cost=21 Card=
1 Bytes=39)                                                                     
 
 
          9                  8                                                  
                  INDEX (RANGE SCAN) OF 'PSTEGF_FK7' (INDEX) (Cost=3 Card=18)   
 
 
         10                  7                                                  
                INDEX (RANGE SCAN) OF 'CTINGF_FK5' (INDEX) (Cost=2 Card=3)      
 
 
         11                  3                                                  
        TABLE ACCESS (BY INDEX ROWID) OF 'PSTEGF' (TABLE) (Cost=20 Card=1 Bytes=
42)                                                                             
 
 
         12                 11                                                  
          INDEX (RANGE SCAN) OF 'PSTEGF_FK6' (INDEX) (Cost=2 Card=17)           
 
 
         13                  2                                                  
      INDEX (RANGE SCAN) OF 'CTINGF_FK5' (INDEX) (Cost=2 Card=3)                
 
 
 
 
 
 
Statistiques
----------------------------------------------------------                      
          1  recursive calls                                                    
          0  db block gets                                                      
         88  consistent gets                                                    
          0  physical reads                                                     
          0  redo size                                                          
        299  bytes sent via SQL*Net to client                                   
        275  bytes received via SQL*Net from client                             
          4  SQL*Net roundtrips to/from client                                  
          0  sorts (memory)                                                     
          0  sorts (disk)                                                       
          1  rows processed

Voyez-vous quelque chose de choquant dans cette requête ?
Il s'agit d'une requête qui est exécutée dans une boucle plusieurs centaine de milliers de fois.

en remplacant la vue en ligne suivante:

Code :

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

1
2
3
4
5
6
7
8
9
10
(SELECT MAX(PS.IDINPS) as IDINPS
	 		  FROM PSTEGF PS, CTINGF CT
			  WHERE 
			  PS.IDEXP1 = 'A115332'    
			  	AND PS.TYPSTE = 'ARP'     
			  	AND 
				PS.NUCTIN = CT.NUCTIN
				AND CT.CDOFIN = 'ARPEGE_OPT'
				AND PS.DATEAC IS NULL
			  ) ID_MAX

par une sous requête dans la clause where :

Code :

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

1
2
3
4
5
6
7
8
AND  P.IDINPS = (select max(PS.IDINPS) FROM PSTEGF PS, CTINGF CT
				  WHERE 
				  PS.IDEXP1 = 'A115332'    
				  	AND PS.TYPSTE = 'ARP'     
				  	AND 
					PS.NUCTIN = CT.NUCTIN
					AND CT.CDOFIN = 'ARPEGE_OPT'
					AND PS.DATEAC IS NULL  )

j'obtiens le plan d'exécution 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
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
Ecoulé : 00 :00 :00.01
 
Plan d'exécution
----------------------------------------------------------                      
          0                                                                     
SELECT STATEMENT Optimizer=CHOOSE (Cost=39 Card=1 Bytes=61)                     
 
 
          1                  0                                                  
  TABLE ACCESS (BY INDEX ROWID) OF 'CTINGF' (TABLE) (Cost=6 Card=1 Bytes=19)    
 
 
          2                  1                                                  
    NESTED LOOPS (Cost=12 Card=1 Bytes=61)                                      
 
 
          3                  2                                                  
      TABLE ACCESS (BY INDEX ROWID) OF 'PSTEGF' (TABLE) (Cost=6 Card=1 Bytes=42)
 
 
          4                  3                                                  
        BITMAP CONVERSION (TO ROWIDS)                                           
 
 
          5                  4                                                  
          BITMAP AND                                                            
 
 
          6                  5                                                  
            BITMAP CONVERSION (FROM ROWIDS)                                     
 
 
          7                  6                                                  
              INDEX (RANGE SCAN) OF 'PSTEGF_FK6' (INDEX) (Cost=3 Card=17)       
 
 
          8                  7                                                  
                SORT (AGGREGATE)                                                
 
 
          9                  8                                                  
                  TABLE ACCESS (BY INDEX ROWID) OF 'CTINGF' (TABLE) (Cost=6 Card
=1 Bytes=13)                                                                    
 
 
         10                  9                                                  
                    NESTED LOOPS (Cost=27 Card=1 Bytes=52)                      
 
 
         11                 10                                                  
                      TABLE ACCESS (BY INDEX ROWID) OF 'PSTEGF' (TABLE) (Cost=21
 Card=1 Bytes=39)                                                               
 
 
         12                 11                                                  
                        INDEX (RANGE SCAN) OF 'PSTEGF_FK7' (INDEX) (Cost=3 Card=
18)                                                                             
 
 
         13                 10                                                  
                      INDEX (RANGE SCAN) OF 'CTINGF_FK5' (INDEX) (Cost=2 Card=3)
 
 
         14                  5                                                  
            BITMAP CONVERSION (FROM ROWIDS)                                     
 
 
         15                 14                                                  
              INDEX (RANGE SCAN) OF 'PSTEGF_FK7' (INDEX) (Cost=3 Card=17)       
 
 
         16                  2                                                  
      INDEX (RANGE SCAN) OF 'CTINGF_FK5' (INDEX) (Cost=2 Card=3)                
 
 
 
 
 
 
Statistiques
----------------------------------------------------------                      
          0  recursive calls                                                    
          0  db block gets                                                      
         91  consistent gets                                                    
          0  physical reads                                                     
          0  redo size                                                          
        299  bytes sent via SQL*Net to client                                   
        275  bytes received via SQL*Net from client                             
          4  SQL*Net roundtrips to/from client                                  
          0  sorts (memory)                                                     
          0  sorts (disk)                                                       
          1  rows processed

A priori c'est meilleur car je passe de 7 secondes à une seconde mais pourquoi?? J'avais lu qu'il fallait toujours préférer les vues en ligne au sous requête

**Pomalaix** · 24/03/2009, 13h11

Envoyé par farenheiit

A priori c'est meilleur car je passe de 7 secondes à une seconde ...

Ces valeurs sont-elles reproductibles si vous exécutez les deux variantes alternativement, et plusieurs fois ?
Car il faut se méfier de l'effet de cache comme on le rappelle souvent.

**mnitu** · 24/03/2009, 14h18

1 recursive calls
...

Ca pourrait dire hard parse pour la première requête!

88 consistent gets

C'est plus de 91 de la deuxième et donc probablement plus cher.

Mais ce qui est choquante est

Il s'agit d'une requête qui est exécutée dans une boucle plusieurs centaine de milliers de fois.

**salim11** · 24/03/2009, 14h24

salut,

pour éviter le problème du cache(bien sûr si tu as les droits)

Code :

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

1
2
 
sql > alter system flush shared_pool;

**laurentschneider** · 24/03/2009, 15h10

J'avais lu qu'il fallait toujours préférer les vues en ligne au sous requête

C'était quoi comme bouquin, MS Access pour les nuls?

D'abord, une vue en ligne, c'est une sous-requête, non?

**laurentschneider** · 24/03/2009, 15h12

Envoyé par salim11

salut,

pour éviter le problème du cache(bien sûr si tu as les droits)

Code :

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

1
2
 
sql > alter system flush shared_pool;

non non non...

Code :

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

alter system flush buffer_cache

shared pool, c'est pour la shared pool, pas le cache du buffer...

**PpPool** · 24/03/2009, 15h14

Envoyé par laurentschneider

C'était quoi comme bouquin, MS Access pour les nuls?

D'abord, une vue en ligne, c'est une sous-requête, non?

notre ami faisait allusion certainement aux INLINE VIEW dans la clause FROM préférable aux sous-requetes imbriquées dans la clause WHERE

pour faire avancer le schmilblick, je vois bien déjà déplacer le calcul de l'ID MAX dans un bloc WITH

**farenheiit** · 24/03/2009, 20h24

Envoyé par PpPool

notre ami faisait allusion certainement aux INLINE VIEW dans la clause FROM préférable aux sous-requetes imbriquées dans la clause WHERE

pour faire avancer le schmilblick, je vois bien déjà déplacer le calcul de l'ID MAX dans un bloc WITH

c'est exactement ça !!! j'avais lu ça dans un bouquin d'optimisation de requetes.

Je ne savais pas qu'on pouvait flusher le buffer cache !!!!!
C'est une nouveauté 10G ???

C'est quoi un recursive call et un hard parse ?

**Waldar** · 24/03/2009, 20h36

Est-ce normal que le prédicat "C.DATEAC IS NULL" ne soit pas dans la requête qui calcule l'id max ?

Si c'est un oubli vous pouvez probablement vous en sortir avec une seule passe. Je n'ai pas d'Oracle sous la main donc il y a quelques tests à faire :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT -- DISTINCT -- test1
    MAX(PS.IDINPS),
    FIRST_VALUE(NVL(TO_CHAR(P.DARSEF, 'DD-MON-YY'), 'XXXXXXXXX')) OVER(ORDER BY MAX(PS.IDINPS) DESC)
FROM
    PSTEGF P
    INNER JOIN CTINGF C
      ON C.NUCTIN = P.NUCTIN
WHERE
    P.IDEXP1 = 'A115332'
AND P.TYPSTE = 'ARP'
AND P.DATEAC IS NULL
AND C.CDOFIN = 'ARPEGE_OPT'
AND C.DATEAC IS NULL
-- GROUP BY NVL(TO_CHAR(P.DARSEF, 'DD-MON-YY'), 'XXXXXXXXX') -- test 2

Si ce n'est pas un oubli comme vous récupérez un ID unique vous avez déjà énormément de prédicats qui sont résolus par le calcul de l'id max :

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
SELECT
    P.IDINPS,
    NVL(TO_CHAR(P.DARSEF, 'DD-MON-YY'), 'XXXXXXXXX')
FROM
    PSTEGF P
    INNER JOIN CTINGF C
      ON C.NUCTIN = P.NUCTIN
WHERE
    C.DATEAC IS NULL
AND P.IDINPS = (SELECT MAX(PS.IDINPS) AS IDINPS
                FROM
                    PSTEGF P
                    INNER JOIN CTINGF C
                      ON C.NUCTIN = P.NUCTIN
		WHERE
                    PS.IDEXP1 = 'A115332'
		AND PS.TYPSTE = 'ARP'
                AND CT.CDOFIN = 'ARPEGE_OPT'
                AND PS.DATEAC IS NULL)

**mnitu** · 25/03/2009, 09h35

Je ne pense pas que j’ai bien expliqué mon point de vue : si la requête après optimisation prendras seulement 0,1 secondes, l’exécuter 100 000 fois en boucle prendras presque 3 heures. Donc je m’interroge plutôt sur l’algorithme utilisé. Pour quoi devez vous l’exécuter « plusieurs centaine des milliers de fois dans une boucle » ?

**farenheiit** · 25/03/2009, 10h11

Envoyé par mnitu

Je ne pense pas que j’ai bien expliqué mon point de vue : si la requête après optimisation prendras seulement 0,1 secondes, l’exécuter 100 000 fois en boucle prendras presque 3 heures. Donc je m’interroge plutôt sur l’algorithme utilisé. Pour quoi devez vous l’exécuter « plusieurs centaine des milliers de fois dans une boucle » ?

Ce n'est pas mon code ni mon appli.
Il s'agit d'un batch en pro-C que je découvre. Apparremment l'appli traite de facturation de prestation telecom. Et pour chacune de ces prestations l'appli effectue un certain nombre de traitement dont la requête en question.

**farenheiit** · 25/03/2009, 10h27

Envoyé par Waldar

Est-ce normal que le prédicat "C.DATEAC IS NULL" ne soit pas dans la requête qui calcule l'id max ?

Si c'est un oubli vous pouvez probablement vous en sortir avec une seule passe. Je n'ai pas d'Oracle sous la main donc il y a quelques tests à faire :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT -- DISTINCT -- test1
    MAX(PS.IDINPS),
    FIRST_VALUE(NVL(TO_CHAR(P.DARSEF, 'DD-MON-YY'), 'XXXXXXXXX')) OVER(ORDER BY MAX(PS.IDINPS) DESC)
FROM
    PSTEGF P
    INNER JOIN CTINGF C
      ON C.NUCTIN = P.NUCTIN
WHERE
    P.IDEXP1 = 'A115332'
AND P.TYPSTE = 'ARP'
AND P.DATEAC IS NULL
AND C.CDOFIN = 'ARPEGE_OPT'
AND C.DATEAC IS NULL
-- GROUP BY NVL(TO_CHAR(P.DARSEF, 'DD-MON-YY'), 'XXXXXXXXX') -- test 2

Si ce n'est pas un oubli comme vous récupérez un ID unique vous avez déjà énormément de prédicats qui sont résolus par le calcul de l'id max :

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
SELECT
    P.IDINPS,
    NVL(TO_CHAR(P.DARSEF, 'DD-MON-YY'), 'XXXXXXXXX')
FROM
    PSTEGF P
    INNER JOIN CTINGF C
      ON C.NUCTIN = P.NUCTIN
WHERE
    C.DATEAC IS NULL
AND P.IDINPS = (SELECT MAX(PS.IDINPS) AS IDINPS
                FROM
                    PSTEGF P
                    INNER JOIN CTINGF C
                      ON C.NUCTIN = P.NUCTIN
		WHERE
                    PS.IDEXP1 = 'A115332'
		AND PS.TYPSTE = 'ARP'
                AND CT.CDOFIN = 'ARPEGE_OPT'
                AND PS.DATEAC IS NULL)

Ma requête me retournait une seule ligne.
Avec ta requête j'ai 26 lignes

**Waldar** · 25/03/2009, 14h20

Envoyé par farenheiit

Ma requête me retournait une seule ligne.
Avec ta requête j'ai 26 lignes

J'ai mis deux requêtes, mais j'imagine que c'est la première qui renvoie plus de lignes. Avez-vous essayé les tests (en supprimant les commentaires) ?

Pouvez-vous répondre à la première question ?

**farenheiit** · 25/03/2009, 14h42

Envoyé par Waldar

J'ai mis deux requêtes, mais j'imagine que c'est la première qui renvoie plus de lignes. Avez-vous essayé les tests (en supprimant les commentaires) ?

Pouvez-vous répondre à la première question ?

Non j'ai essayé la 2ème.

Envoyé par Waldar Voir le message
Est-ce normal que le prédicat "C.DATEAC IS NULL" ne soit pas dans la requête qui calcule l'id max ?

je ne sais pas si c'est normal ou pas car ce n'est pas mon application. J'interviens juste pour essayer de comprendre pourquoi le batch dure 30 heures au lieu de 2 heures depuis le passage de la 8i à la 10G. Le soucis c'est qu'il n'est pas possible de relancer le traitement batch avant mi-avril du coup j'essaye d'analyser le code et les requêtes qui me semblent suspectes.
De plus il n ' y a plus rien dans les vues dynamiques car la abse a été redémarrée depuis.
Autant dire que je galère

**mnitu** · 25/03/2009, 16h04

Envoyé par farenheiit

Ce n'est pas mon code ni mon appli.
Il s'agit d'un batch en pro-C que je découvre. Apparremment l'appli traite de facturation de prestation telecom. Et pour chacune de ces prestations l'appli effectue un certain nombre de traitement dont la requête en question.

Si l’application est un Pro*C alors très probablement qu’elle utilise des variables de liaison et non pas de littéraux en dur comme dans votre requête. Si c’est le cas, vous devez utiliser des variables de liaison dans vos tests et explain plans et non pas des valeurs littéraux.
Pour avancer dans vos démarche je vous suggère d’exécuter le programme en mode trace sql sur une base de test, à la limite sur un échantion des données répresentatif. L’avantage est qu’en suite vous saurez ce qui ne va pas et non plus deviner, comme vous le fait actuellement.

**farenheiit** · 25/03/2009, 16h18

Envoyé par mnitu

Si l’application est un Pro*C alors très probablement qu’elle utilise des variables de liaison et non pas de littéraux en dur comme dans votre requête. Si c’est le cas, vous devez utiliser des variables de liaison dans vos tests et explain plans et non pas des valeurs littéraux.
Pour avancer dans vos démarche je vous suggère d’exécuter le programme en mode trace sql sur une base de test, à la limite sur un échantion des données répresentatif. L’avantage est qu’en suite vous saurez ce qui ne va pas et non plus deviner, comme vous le fait actuellement.

c'est ce que j'aimerais faire mais en Dév la volumétrie est faible par rapport à la prod et en homologation ils ont un pb avec un daemon qui empêche de pouvoir lancer la batch. j'avais exposé ce problème ici mais sans succès

**mnitu** · 25/03/2009, 17h17

Si je comprends bien vous avez un problème de lenteur mais vous n’avez pas d’idée d’ou ça viens ni qui est le responsable.
Concernant le batch il devrait être possible de l’exécuter « à la main » en lui passant les bonnes paramètres si le daemon ne peut pas le faire. Ajoutez une trace sql dans ce batch et regardez que est-ce que ça donne.
Concernant le daemon j’ai regardé dans la doc et si le pipe ‘'STD$PIPE$EXEC'’ n’existe pas le daemon attendra. Essayez d’instrumenter la procédure pour savoir si le problème est la. Il n’est pas clair non plus ce qui se passe ensuite parce que le daemon reçoit la commande et exécute ensuite un fichier des commandes batch.
En fin vérifiez aussi le nombre des daemons qui tourne simultanément. Et instrumentez aussi le daemon pour avoir une idée où le temps passe.
Seulement après avoir trouvé ou ça coince vous pouvez optimiser.

Requête à optimiser

SQL Oracle

Discussions similaires

Partager

Partager