Requête à optimiser

Version imprimable

Requête à optimiser

Bonjour,

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

Code:

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:

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:

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:

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:

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

24/03/2009, 13h11
Pomalaix

Citation:

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.
24/03/2009, 14h18
mnitu

Citation:

1 recursive calls
...

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

Citation:

88 consistent gets

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

Mais ce qui est choquante est

Citation:

Il s'agit d'une requête qui est exécutée dans une boucle plusieurs centaine de milliers de fois.
24/03/2009, 14h24
salim11
salut,

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

1 2 sql > alter system flush shared_pool;
24/03/2009, 15h10
laurentschneider

Citation:

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? :aie:

D'abord, une vue en ligne, c'est une sous-requête, non?
24/03/2009, 15h12
laurentschneider
Citation:
Envoyé par salim11

salut,

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

Code:

1 2 sql > alter system flush shared_pool;
non non non...

Code:

alter system flush buffer_cache

shared pool, c'est pour la shared pool, pas le cache du buffer...
24/03/2009, 15h14
PpPool

Citation:

Envoyé par laurentschneider

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

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
24/03/2009, 20h24
farenheiit

Citation:

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 ?

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:

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:

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)

25/03/2009, 09h35
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 » ?
25/03/2009, 10h11
farenheiit

Citation:

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.

Citation:

Envoyé par Waldar

Code:

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:

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

25/03/2009, 14h20
Waldar

Citation:

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 ?
25/03/2009, 14h42
farenheiit

Citation:

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.

Citation:

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
25/03/2009, 16h04
mnitu

Citation:

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.
25/03/2009, 16h18
farenheiit

Citation:

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
25/03/2009, 17h17
mnitu

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.