Discussion :

[Mr_Coinche]

Bonjour,

Voila, lorsque que j'exécute une requête sur une vue que j'ai créée, la requête met plus de 2 minute à s'exécuter.

Il n'y a pas de jointure sur la requête, les jointures sont effectuées dans la vue.

L'exécution est rapide si dans la requête je ne mets que deux clauses where, par contre à partir de quatre clauses where, j'ai un MERGE JOIN CARTESIAN et le nombre de consistant gets augmente radicalement.
Sans compter les Table access full, je sais pas si c'est très optimal.

Plus surprenant lorsque j'effectue un dump de ma base et que je l'envoie à un collègue qui effectue la même requête, sur la base qu'il a créé à partir de mon dump, lui il obtient le résultat en moins de deux secondes, même en mettant les quatre clauses where.

On a effectué un trace (avec set autot trace) sur la même requête chacun de notre côté. Les résultats sont radicalement différents.

On utilise la même version d'Oracle.

Voici ma trace :

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
Plan d exécution
----------------------------------------------------------                      
Plan hash value: 2198388969                                                     
 
------------------------------------------------------------------------------------------------------------------                                              
| Id  | Operation                           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |                                              
------------------------------------------------------------------------------------------------------------------                                              
|   0 | SELECT STATEMENT                    |                            |     1 |   359 |  3362   (1)| 00:00:41 |                                              
|   1 |  SORT ORDER BY                      |                            |     1 |   359 |  3362   (1)| 00:00:41 |                                              
|   2 |   VIEW                              |                            |     1 |   359 |  3361   (1)| 00:00:41 |                                              
|   3 |    HASH UNIQUE                      |                            |     1 |   278 |  3361   (1)| 00:00:41 |                                              
|   4 |     NESTED LOOPS                    |                            |     1 |   278 |  3360   (1)| 00:00:41 |                                              
|   5 |      MERGE JOIN CARTESIAN           |                            |    59 | 13511 |   103   (2)| 00:00:02 |                                              
|*  6 |       HASH JOIN                     |                            |     1 |   211 |    18  (12)| 00:00:01 |                                              
|   7 |        NESTED LOOPS                 |                            |     1 |   196 |    14   (8)| 00:00:01 |                                              
|   8 |         NESTED LOOPS                |                            |     1 |   163 |    13   (8)| 00:00:01 |                                              
|*  9 |          HASH JOIN                  |                            |     1 |   144 |    12   (9)| 00:00:01 |                                              
|* 10 |           HASH JOIN                 |                            |     7 |   833 |     9  (12)| 00:00:01 |                                              
|* 11 |            TABLE ACCESS FULL        | LEX_COM_ARD                |     1 |    17 |     3   (0)| 00:00:01 |                                              
|* 12 |            TABLE ACCESS FULL        | PRG_OPERATION              |    84 |  8568 |     5   (0)| 00:00:01 |                                              
|* 13 |           TABLE ACCESS FULL         | PRG_LEX_PROGRAMME          |     1 |    25 |     3   (0)| 00:00:01 |                                              
|* 14 |          TABLE ACCESS BY INDEX ROWID| PRG_LEX_ETAT               |     1 |    19 |     1   (0)| 00:00:01 |                                              
|* 15 |           INDEX UNIQUE SCAN         | PRG_LEX_ETAT_IDX           |     1 |       |     0   (0)| 00:00:01 |                                              
|  16 |         TABLE ACCESS BY INDEX ROWID | PRG_LEX_SOUS_PROGRAMME     |     1 |    33 |     1   (0)| 00:00:01 |                                              
|* 17 |          INDEX UNIQUE SCAN          | PRG_LEX_SOUS_PROGRAMME_IDX |     1 |       |     0   (0)| 00:00:01 |                                              
|  18 |        TABLE ACCESS FULL            | LEX_COM_CE                 |    28 |   420 |     3   (0)| 00:00:01 |                                              
|  19 |       BUFFER SORT                   |                            |  2183 | 39294 |   100   (2)| 00:00:02 |                                              
|  20 |        TABLE ACCESS FULL            | SIR_GL_CLASSEMENT_ABCD     |  2183 | 39294 |    85   (0)| 00:00:02 |                                              
|* 21 |      VIEW                           |                            |     1 |    49 |    55   (0)| 00:00:01 |                                              
|* 22 |       TABLE ACCESS FULL             | PRG_TRONCON_OPERATION      |     1 |    49 |    55   (0)| 00:00:01 |                                              
--------------------------------------------------------------------------------                         
 
 
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
 
   6 - access("PRG_OPERATION"."ID_LEX_COM_CE"="LEX_COM_CE"."ID_LEX_COM_CE")     
   9 - access("PRG_OPERATION"."ID_PRG_LEX_PROGRAMME"="PRG_LEX_PROGRAMME"."ID_PRG_LEX_PROGRAMME")                                                                
  10 - access("PRG_OPERATION"."ID_LEX_COM_ARD"="LEX_COM_ARD"."ID_LEX_COM_ARD")  
  11 - filter("LEX_COM_ARD"."NOM_ARD"='Rennes')                                 
  12 - filter("PRG_OPERATION"."ANNEE_OPERATION"=2010 AND "PRG_OPERATION"."ID_PRG_LEX_SOUS_PROGRAMME" IS                                                         
               	NOT NULL AND "PRG_OPERATION"."ID_PRG_LEX_PROGRAMME" IS NOT NULL AND "PRG_OPERATION"."ID_LEX_COM_CE" IS                                            
               	NOT NULL AND "PRG_OPERATION"."ID_LEX_COM_ARD" IS NOT NULL AND "PRG_OPERATION"."ID_PRG_LEX_ETAT" IS NOT NULL)                                                             
  13 - filter("PRG_LEX_PROGRAMME"."LIBELLE"='Grosses réparations')              
  14 - filter("PRG_LEX_ETAT"."LIBELLE"='Voté')                                  
  15 - access("PRG_OPERATION"."ID_PRG_LEX_ETAT"="PRG_LEX_ETAT"."ID_PRG_LEX_ETAT")
  17 - access("PRG_OPERATION"."ID_PRG_LEX_SOUS_PROGRAMME"="PRG_LEX_SOUS_PROGRAMME"."ID_PRG_LEX_SOUS_PROGRAMME")                                                             
  21 - filter("PRG_OPERATION"."CODE_ITIN"="A"."CODE_ITIN")                      
  22 - filter("A"."ROUTE"="B"."ROUTE" AND ("B"."LOCAL1">="A"."LOCAL1" OR "B"."LOCAL2">"A"."LOCAL1") AND 
		("B"."LOCAL1"<"A"."LOCAL2" OR "B"."LOCAL2"<="A"."LOCAL2"))        
 
 
Statistiques
----------------------------------------------------------                      
          0  recursive calls                                                    
          0  db block gets                                                      
    9444161  consistent gets                                                    
          0  physical reads                                                     
          0  redo size                                                          
       5035  bytes sent via SQL*Net to client                                   
        267  bytes received via SQL*Net from client                             
          6  SQL*Net roundtrips to/from client                                  
          2  sorts (memory)                                                     
          0  sorts (disk)                                                       
         61  rows processed

Et voici la sienne :

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
Plan d exécution
----------------------------------------------------------                      
Plan hash value: 3186314313                                                     
 
------------------------------------------------------------------------------------------------------------------                                              
| Id  | Operation                           | Name                       | Rows | Bytes | Cost (%CPU)| Time     |                                              
------------------------------------------------------------------------------------------------------------------                                              
|   0 | SELECT STATEMENT                    |                            |     1 |   359 | 87955   (1)| 00:17:36 |                                            
|   1 |  SORT ORDER BY                      |                            |     1 |   359 | 87955   (1)| 00:17:36 |                                              
|   2 |   VIEW                              |                            |     1 |   359 | 87954   (1)| 00:17:36 |                                              
|   3 |    HASH UNIQUE                      |                            |     1 |   677 | 87954   (1)| 00:17:36 |                                              
|*  4 |     HASH JOIN                       |                            |    27 | 18279 | 87953   (1)| 00:17:36 |                                              
|   5 |      TABLE ACCESS FULL              | LEX_COM_CE                 |    28 |   700 |     3   (0)| 00:00:01 |                                              
|   6 |      NESTED LOOPS                   |                            |    27 | 17604 | 87949   (1)| 00:17:36 |                                              
|   7 |       NESTED LOOPS                  |                            |    27 | 16200 | 87948   (1)| 00:17:36 |                                              
|   8 |        NESTED LOOPS                 |                            |   115 | 63020 | 87947   (1)| 00:17:36 |                                              
|   9 |         NESTED LOOPS                |                            |   692 |   335K| 87946   (1)| 00:17:36 |                                              
|* 10 |          HASH JOIN                  |                            |  2077 |   900K| 87944   (1)| 00:17:36 |                                              
|* 11 |           TABLE ACCESS FULL         | PRG_OPERATION              |   112 | 27664 |     5   (0)| 00:00:01 |                                              
|  12 |           NESTED LOOPS              |                            |  2077 |   399K| 87939   (1)| 00:17:36 |                                              
|  13 |            TABLE ACCESS FULL        | SIR_GL_CLASSEMENT_ABCD     |  2077 | 76849 |    85   (2)| 00:00:02 |                                              
|  14 |            VIEW                     |                            |     1 |   160 |    42   (0)| 00:00:01 |                                              
|* 15 |             TABLE ACCESS FULL       | PRG_TRONCON_OPERATION      |     1 |   160 |    42   (0)| 00:00:01 |                                              
|* 16 |          TABLE ACCESS BY INDEX ROWID| PRG_LEX_PROGRAMME          |     1 |    52 |     1   (0)| 00:00:01 |                                              
|* 17 |           INDEX UNIQUE SCAN         | PRG_LEX_PROGRAMME_IDX      |     1 |       |     0   (0)| 00:00:01 |                                              
|* 18 |         TABLE ACCESS BY INDEX ROWID | LEX_COM_ARD                |     1 |    52 |     1   (0)| 00:00:01 |                                              
|* 19 |          INDEX UNIQUE SCAN          | LEX_COM_ARD_IDX            |     1 |       |     0   (0)| 00:00:01 |                                              
|* 20 |        TABLE ACCESS BY INDEX ROWID  | PRG_LEX_ETAT               |     1 |    52 |     1   (0)| 00:00:01 |                                              
|* 21 |         INDEX UNIQUE SCAN           | PRG_LEX_ETAT_IDX           |     1 |       |     0   (0)| 00:00:01 |                                              
|  22 |       TABLE ACCESS BY INDEX ROWID   | PRG_LEX_SOUS_PROGRAMME     |     1 |    52 |     1   (0)| 00:00:01 |                                              
|* 23 |        INDEX UNIQUE SCAN            | PRG_LEX_SOUS_PROGRAMME_IDX |     1 |       |     0   (0)| 00:00:01 |                                              
------------------------------------------------------------------------------------------------------------------                                              
 
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
 
   4 - access("PRG_OPERATION"."ID_LEX_COM_CE"="LEX_COM_CE"."ID_LEX_COM_CE")     
  10 - access("PRG_OPERATION"."CODE_ITIN"="A"."CODE_ITIN")                      
  11 - filter("PRG_OPERATION"."ANNEE_OPERATION"=2010)                           
  15 - filter("A"."ROUTE"="B"."ROUTE" AND ("B"."LOCAL1">="A"."LOCAL1" OR "B"."LOCAL2">"A"."LOCAL1")
		AND ("B"."LOCAL1"<"A"."LOCAL2" OR "B"."LOCAL2"<="A"."LOCAL2"))        
  16 - filter("PRG_LEX_PROGRAMME"."LIBELLE"='Grosses réparations')              
  17 - access("PRG_OPERATION"."ID_PRG_LEX_PROGRAMME"="PRG_LEX_PROGRAMME"."ID_PRG_LEX_PROGRAMME")                                                                
  18 - filter("LEX_COM_ARD"."NOM_ARD"='Rennes')                                 
  19 - access("PRG_OPERATION"."ID_LEX_COM_ARD"="LEX_COM_ARD"."ID_LEX_COM_ARD")  
  20 - filter("PRG_LEX_ETAT"."LIBELLE"='Voté')                                  
  21 - access("PRG_OPERATION"."ID_PRG_LEX_ETAT"="PRG_LEX_ETAT"."ID_PRG_LEX_ETAT") 
  23 - access("PRG_OPERATION"."ID_PRG_LEX_SOUS_PROGRAMME"="PRG_LEX_SOUS_PROGRAMME"."ID_PRG_LEX_SOUS_PROGRAMME")                                                             
 
Note                                                                            
-----                                                                           
   - dynamic sampling used for this statement                                   
 
 
Statistiques
----------------------------------------------------------                      
       1978  recursive calls                                                    
          0  db block gets                                                      
     413861  consistent gets                                                    
          0  physical reads                                                     
          0  redo size                                                          
       6002  bytes sent via SQL*Net to client                                   
        425  bytes received via SQL*Net from client                             
          6  SQL*Net roundtrips to/from client                                  
         64  sorts (memory)                                                     
          0  sorts (disk)                                                       
         61  rows processed

Si quelqu'un a une idée, je suis preneur.

Merci d'avance...

[remi4444]

On ne peut pas répondre à priori surtout sans connaitre ni la requête, ni l'état des stats, ni les index etc...

Le plan d'exécution est différent. Quand il y a cette instabilité c'est que l'optimiseur à le choix entre plusieurs mauvaises solutions et qu'il ne trouve pas de chemin évident pour déduire un plan stable. La moindre différence au niveau des stats du serveur le fera basculer d'un coté ou d'un autre...

[remi4444]

Code :

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

1
2
3
 
("B"."LOCAL1">="A"."LOCAL1" OR "B"."LOCAL2">"A"."LOCAL1") AND 
("B"."LOCAL1"<"A"."LOCAL2" OR "B"."LOCAL2"<="A"."LOCAL2"))

Petite apparté sur cette clause qui ressemble à un test de chevauchement de période entre 2 entité.

A supposer que vous avez toujours la contrainte B.LOCAL1 < B.LOCAL2 ainsi que la contrainte A.LOCAL1 < A.LOCAL2 (le début toujours inférieur à la fin...) alors le filtrage de chevauchement est plus simple:

Code :

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

("B"."LOCAL1" < "A"."LOCAL2" AND  "B"."LOCAL2">"A"."LOCAL1")

[mnitu]

...
Si quelqu'un a une idée, je suis preneur.

Merci d'avance...

Note
-----
- dynamic sampling used FOR this statement

[Mr_Coinche]

Bonjour à vous deux,

Petit retour un peu tardif suite au w end de 4 jours.

J'ai recalculé les stats de toutes les tables et de tous les index qui entraient en jeu dans la requete sur la vue. Et le temps d'execution est passé de plus de 2' a environ 5". C'est encore un peu long a mon gout, mais acceptable vue l'utilisation de la requete...

Je devrais pouvoir l'optimiser plus, mais je n'ai malheureusement pas le temps.


Voila, merci a vous.

[Mr_Coinche]

Petite apparté sur cette clause qui ressemble à un test de chevauchement de période entre 2 entité.

A supposer que vous avez toujours la contrainte B.LOCAL1 < B.LOCAL2 ainsi que la contrainte A.LOCAL1 < A.LOCAL2 (le début toujours inférieur à la fin...) alors le filtrage de chevauchement est plus simple:

Code :

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

("B"."LOCAL1" < "A"."LOCAL2" AND  "B"."LOCAL2">"A"."LOCAL1")

Re !

Je regarde la simplification que tu ma proposé, efferctivement local1 est tjrs inferieur a local2 dans les 2 tables.

Par contre comme simplification je trouve l'inverse de toi :

Code :

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

1
2
 
("B"."LOCAL1">="A"."LOCAL1" AND "B"."LOCAL2"<="A"."LOCAL2")

C'est moi qui ai tord ? ou c'est toi qui t'es trompé ?

Merci

[remi4444]

Euh... il me semble que ce soit ma formule la bonne si j'ai bien compris que le test était de savoir si la période B chevauchait la période A d'une manière ou d'une autre.

Ton test, c'est de savoir si la période B est entièrement incluse dans la période A. Si c'est la cas, alors ta formule simplifiée est bonne mais c'est la formule originelle qui était éronnée...

Bon, je peux me tromper, mais si on fait un petit exercice de logique ensembliste on pourrait poser le pb ainsi:

Code :

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

1
2
3
4
Ensemble A: population qui vérifie : "B"."LOCAL1">="A"."LOCAL1"
Ensemble B: population qui vérifie : "B"."LOCAL2"> "A"."LOCAL1"
Ensemble C: population qui vérifie : "B"."LOCAL1"< "A"."LOCAL2"
Ensemble D: population qui vérifie : "B"."LOCAL2"<="A"."LOCAL2"

Ton test intial consiste à constituer l'ensemble

(A union B ) intersection (C union D)
Or si on considère la contrainte supplémentaire qui est que LOCAL2 est forcément supérieur à LOCAL1 alors ça veux dire que :
- Qui dit "A" dit forcément "B"
- Qui dit "D" dit forcément "C"

Autrement dit, A est inclus dans B et D est inclus dans C. et donc :
A union B = B
C union D = C
La formule se simplifie donc en
B intersection C
ça donne donc bien

Code :

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

"B"."LOCAL2"> "A"."LOCAL1" AND "B"."LOCAL1"< "A"."LOCAL2"

Pour en avoir le coeur net, pose tous les cas de périodes sur un dessin et vérifie

Cette simplification n'est pas si anecdotique qu'elle en a l'air, car les "OR" dans les requêtes c'est toujours périlleux question perf car l'optimiseur a du mal à se sortir des plan qui font l'union de plusieurs ensembles potentiellement gros...