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?

**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)

**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...

Requête à optimiser

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager