Dégradation drastique des performances avec WHERE ROWNUM

**Logan Mauzaize** · 10/07/2012, 12h32

Bonjour à tous,

Dans un premier temps, je voudrais savoir si d'autres personnes avaient déjà expérimenté le même problème. Lorsque je souhaite utilisé des Top-N query avec WHERE ROWNUM pour faire de la pagination, je passe de quelques secondes d'utilisation à plusieurs minutes (là ca fait une heure que ca tourne et toujours pas de résultat).
En revanche cela fonctionne très bien si je stocke le rownum dans une première TOP query, et que je filtre en utilisant une autre TOP query.

Pour schématiser :

Code :

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

SELECT * FROM (SELECT * FROM A ORDER BY A.1, A.2, ...);

Environ 4 secondes

Code :

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

SELECT * FROM (SELECT * FROM A ORDER BY A.1, A.2, ...) WHERE ROWNUM <= 1;

Environ ??? secondes?/minutes?/heures?

Code :

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

SELECT * FROM (SELECT B.*, ROWNUM NUM FROM (SELECT * FROM A ORDER BY A.1, A.2, ...) B) WHERE NUM <= 1;

Environ 4 secondes

Quelques informations supplémentaires:

Code :

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

1
2
3
4
5
6
7
8
select * from v$version;
BANNER                                                                           
-------------------------------------------------------------------------------- 
Oracle Database 11g Release 11.1.0.6.0 - 64bit Production                        
PL/SQL Release 11.1.0.6.0 - Production                                           
CORE	11.1.0.6.0	Production                                                         
TNS for Solaris: Version 11.1.0.6.0 - Production                                 
NLSRTL Version 11.1.0.6.0 - Production

Pour information la requête est en fait d'une complexité moyenne (GROUP BY et MAX) qui elle-même repose sur une vue plus complexe (jointure sur 4 tables avec comptage d'élément qui relie les tables 2 à 2). Je pourrais éventuellement faire une schéma mais ca me prendra un peu de temps ^^

**ojo77** · 10/07/2012, 12h40

a mon avis il n'y a pas de différence de plan entre

Code :

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

SELECT * FROM (SELECT * FROM A ORDER BY A.1, A.2, ...) WHERE ROWNUM <= 1;

et

Code :

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

SELECT * FROM (SELECT B.*, ROWNUM NUM FROM (SELECT * FROM A ORDER BY A.1, A.2, ...) B) WHERE NUM <= 1;

En effet, l'optimiseur devrait au final faire une réécriture sur la seconde et retrouver la première pour donc au final ne travailler que sur une des deux requêtes.
Un petit explain plan sur les deux requêtes ?

Ensuite : Les deux requêtes ont-elles été passées dans les mêmes conditions ?

**Logan Mauzaize** · 10/07/2012, 13h05

Le plan d'exécution n'est pas le même .... Par contre, je le génère avec SQL*Developper, comment je fais pour l'avoir en "plain text" ?

Yes dans les mêmes conditions avec SQL*Developper avec la même connection. Je répète toujours les mêmes depuis ce matin.

Tout à l'heure je vais essayer d'installer sur une Oracle 11gR2 sous Linux.

**ojo77** · 10/07/2012, 13h07

Pour l'avoir en texte plein depuis SQL*DEV ça devrait fonctionner

Code :

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

select * from table (sys.dbms_xplan.display() )

ou, pour les gourmands

Code :

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

select * from table (sys.dbms_xplan.display(null,null,'advanced') )

**Logan Mauzaize** · 10/07/2012, 13h26

Voilà les plans d'exécution, j'ai essayé de mettre des noms parlants à mes tables

Pour la version avec rownum qui fonctionne (double TOP + ROWNUM en select)

Code text :

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
Succès de l'élément plan FOR.
PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
Plan hash value: 2490705766                                                                                                                                                                                                                                                                                  
 
----------------------------------------------------------------------------------------------------------------                                                                                                                                                                                             
| Id  | Operation                   | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                                                                                                                                                             
----------------------------------------------------------------------------------------------------------------                                                                                                                                                                                             
|   0 | SELECT STATEMENT            |                          | 58136 |    20M|       |  5895   (3)| 00:01:11 |                                                                                                                                                                                             
|*  1 |  VIEW                       |                          | 58136 |    20M|       |  5895   (3)| 00:01:11 |                                                                                                                                                                                             
|   2 |   COUNT                     |                          |       |       |       |            |          |                                                                                                                                                                                             
|   3 |    VIEW                     |                          | 58136 |    20M|       |  5895   (3)| 00:01:11 |                                                                                                                                                                                             
|   4 |     SORT GROUP BY           |                          | 58136 |    13M|    27M|  5895   (3)| 00:01:11 |                                                                                                                                                                                             
|*  5 |      HASH JOIN RIGHT OUTER  |                          | 58136 |    13M|       |  2907   (4)| 00:00:35 |                                                                                                                                                                                             
|   6 |       VIEW                  |                          | 12314 |   216K|       |    14  (22)| 00:00:01 |                                                                                                                                                                                             
|   7 |        HASH GROUP BY        |                          | 12314 | 61570 |       |    14  (22)| 00:00:01 |                                                                                                                                                                                             
|   8 |         INDEX FAST FULL SCAN| IX2_EXTERNAL_SERVICE     | 12314 | 61570 |       |    11   (0)| 00:00:01 |                                                                                                                                                                                             
|*  9 |       HASH JOIN RIGHT OUTER |                          | 58136 |    12M|  6320K|  2890   (4)| 00:00:35 |                                                                                                                                                                                             
|  10 |        VIEW                 |                          |   196K|  4020K|       |  1479   (5)| 00:00:18 |                                                                                                                                                                                             
|  11 |         HASH GROUP BY       |                          |   196K|  3445K|    10M|  1479   (5)| 00:00:18 |                                                                                                                                                                                             
|* 12 |          HASH JOIN          |                          |   196K|  3445K|       |   324   (6)| 00:00:04 |                                                                                                                                                                                             
|  13 |           TABLE ACCESS FULL | VERSION                  | 27081 |   238K|       |   104   (2)| 00:00:02 |                                                                                                                                                                                             
|  14 |           TABLE ACCESS FULL | LIEN_VERSION_CLIENT      |   196K|  1722K|       |   214   (5)| 00:00:03 |                                                                                                                                                                                             
|  15 |        VIEW                 |                          | 58136 |    10M|       |   516   (6)| 00:00:07 |                                                                                                                                                                                             
|* 16 |         HASH JOIN           |                          | 58136 |  7323K|       |   516   (6)| 00:00:07 |                                                                                                                                                                                             
|  17 |          TABLE ACCESS FULL  | PRODUIT                  |  9822 |   594K|       |    35   (3)| 00:00:01 |                                                                                                                                                                                             
|* 18 |          HASH JOIN          |                          | 58136 |  3803K|       |   479   (6)| 00:00:06 |                                                                                                                                                                                             
|* 19 |           TABLE ACCESS FULL | CLIENT                   |  2016 | 20160 |       |     5   (0)| 00:00:01 |                                                                                                                                                                                             
|* 20 |           HASH JOIN         |                          | 58251 |  3242K|  1152K|   472   (5)| 00:00:06 |                                                                                                                                                                                             
|* 21 |            TABLE ACCESS FULL| VERSION                  | 21741 |   891K|       |   105   (3)| 00:00:02 |                                                                                                                                                                                             
|* 22 |            TABLE ACCESS FULL| LIEN_VERSION_CLIENT      | 68659 |  1005K|       |   218   (7)| 00:00:03 |                                                                                                                                                                                             
----------------------------------------------------------------------------------------------------------------                                                                                                                                                                                             
 
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
---------------------------------------------------                                                                                                                                                                                                                                                          
 
   1 - filter("NUM"<=1)                                                                                                                                                                                                                                                                                      
   5 - access("COUNT_EXS"."VERSION_REF"(+)="from$_subquery$_010"."VERSION_ID")                                                                                                                                                                                                                             
   9 - access("COUNT_HISTORY"."CLIENT_REF"(+)="CLIENT"."CLIENT_ID" AND                                                                                                                                                                                                                           
              "COUNT_HISTORY"."PRODUIT_REF"(+)="PRODUIT"."PRODUIT_ID")                                                                                                                                                                                                                               
  12 - access("R2C"."VERSION_REF"="R"."VERSION_ID")                                                                                                                                                                                                                                                        
  16 - access("VERSION"."PRODUIT_REF"="PRODUIT"."PRODUIT_ID")                                                                                                                                                                                                                         
  18 - access("LIEN_VERSION_CLIENT"."CONTRACT_REF"="CLIENT"."CLIENT_ID")                                                                                                                                                                                                                      
  19 - filter("CLIENT"."SOURCE"='APPLICATION')                                                                                                                                                                                                                                                     
  20 - access("LIEN_VERSION_CLIENT"."VERSION_REF"="VERSION"."VERSION_ID")                                                                                                                                                                                                              
  21 - filter("VERSION"."STATUS"='DIFFUSED' OR "VERSION"."STATUS"='NEW')                                                                                                                                                                                                       
  22 - filter("LIEN_VERSION_CLIENT"."IS_LAST"=1)                                                                                                                                                                                                                                                    
 
 44 lignes sélectionnées

Et la version classique qui ne fonctionne pas (avec une simple TOP avec WHERE ROWNUM)

Code text :

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
Succès de l'élément plan FOR.
PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
Plan hash value: 4185169469                                                                                                                                                                                                                                                                                  
 
--------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                   
| Id  | Operation                             | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                                                                                                                                                   
--------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                   
|   0 | SELECT STATEMENT                      |                          |     1 |   361 |       |     7  (15)| 00:00:01 |                                                                                                                                                                                   
|*  1 |  COUNT STOPKEY                        |                          |       |       |       |            |          |                                                                                                                                                                                   
|   2 |   VIEW                                |                          |     1 |   361 |       |     7  (15)| 00:00:01 |                                                                                                                                                                                   
|*  3 |    SORT GROUP BY STOPKEY              |                          |     1 |   236 |       |     7  (15)| 00:00:01 |                                                                                                                                                                                   
|   4 |     NESTED LOOPS OUTER                |                          |     1 |   236 |       |     6   (0)| 00:00:01 |                                                                                                                                                                                   
|   5 |      NESTED LOOPS OUTER               |                          |     1 |   215 |       |     6   (0)| 00:00:01 |                                                                                                                                                                                   
|   6 |       VIEW                            |                          |     1 |   197 |       |     6   (0)| 00:00:01 |                                                                                                                                                                                   
|   7 |        NESTED LOOPS                   |                          |       |       |       |            |          |                                                                                                                                                                                   
|   8 |         NESTED LOOPS                  |                          |     1 |   775 |       |     6   (0)| 00:00:01 |                                                                                                                                                                                   
|   9 |          NESTED LOOPS                 |                          |     1 |   458 |       |     5   (0)| 00:00:01 |                                                                                                                                                                                   
|  10 |           NESTED LOOPS                |                          |     2 |   832 |       |     4   (0)| 00:00:01 |                                                                                                                                                                                   
|* 11 |            TABLE ACCESS FULL          | LIEN_VERSION_CLIENT      |     4 |   812 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                   
|* 12 |            TABLE ACCESS BY INDEX ROWID| CLIENT                   |     1 |    10 |       |     1   (0)| 00:00:01 |                                                                                                                                                                                   
|* 13 |             INDEX UNIQUE SCAN         | PK_CLIENT                |     1 |       |       |     0   (0)| 00:00:01 |                                                                                                                                                                                   
|* 14 |           TABLE ACCESS BY INDEX ROWID | VERSION                  |     1 |   245 |       |     1   (0)| 00:00:01 |                                                                                                                                                                                   
|* 15 |            INDEX UNIQUE SCAN          | PK_VERSION               |     1 |       |       |     0   (0)| 00:00:01 |                                                                                                                                                                                   
|* 16 |          INDEX UNIQUE SCAN            | PK_PRODUIT               |     1 |       |       |     0   (0)| 00:00:01 |                                                                                                                                                                                   
|  17 |         TABLE ACCESS BY INDEX ROWID   | PRODUIT                  |     1 |  1233 |       |     1   (0)| 00:00:01 |                                                                                                                                                                                   
|* 18 |       VIEW                            |                          |     1 |    18 |       |            |          |                                                                                                                                                                                   
|  19 |        SORT GROUP BY                  |                          |     1 |     5 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                   
|  20 |         INDEX FULL SCAN               | IX1_EXTERNAL_SERVICE     | 12314 | 61570 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                   
|* 21 |      VIEW                             |                          |     1 |    21 |       |            |          |                                                                                                                                                                                   
|  22 |       SORT GROUP BY                   |                          |   196K|  3445K|    10M|  1479   (5)| 00:00:18 |                                                                                                                                                                                   
|* 23 |        HASH JOIN                      |                          |   196K|  3445K|       |   324   (6)| 00:00:04 |                                                                                                                                                                                   
|  24 |         TABLE ACCESS FULL             | VERSION                  | 27081 |   238K|       |   104   (2)| 00:00:02 |                                                                                                                                                                                   
|  25 |         TABLE ACCESS FULL             | LIEN_VERSION_CLIENT      |   196K|  1722K|       |   214   (5)| 00:00:03 |                                                                                                                                                                                   
--------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                   
 
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
---------------------------------------------------                                                                                                                                                                                                                                                          
 
   1 - filter(ROWNUM<=1)                                                                                                                                                                                                                                                                                     
   3 - filter(ROWNUM<=1)                                                                                                                                                                                                                                                                                     
  11 - filter("LIEN_VERSION_CLIENT"."IS_LAST"=1)                                                                                                                                                                                                                                                    
  12 - filter("CLIENT"."SOURCE"='APPLICATION')                                                                                                                                                                                                                                                     
  13 - access("LIEN_VERSION_CLIENT"."CLIENT_REF"="CLIENT"."CLIENT_ID")                                                                                                                                                                                                                      
  14 - filter("VERSION"."STATUS"='DIFFUSED' OR "VERSION"."STATUS"='NEW')                                                                                                                                                                                                       
  15 - access("LIEN_VERSION_CLIENT"."VERSION_REF"="VERSION"."VERSION_ID")                                                                                                                                                                                                              
  16 - access("VERSION"."PRODUIT_REF"="PRODUIT"."PRODUIT_ID")                                                                                                                                                                                                                         
  18 - filter("COUNT_EXS"."VERSION_REF"(+)="from$_subquery$_009"."VERSION_ID")                                                                                                                                                                                                                             
  21 - filter("COUNT_HISTORY"."CLIENT_REF"(+)="CLIENT"."CLIENT_ID" AND                                                                                                                                                                                                                           
              "COUNT_HISTORY"."PRODUIT_REF"(+)="PRODUIT"."PRODUIT_ID")                                                                                                                                                                                                                               
  23 - access("R2C"."VERSION_REF"="R"."VERSION_ID")                                                                                                                                                                                                                                                        
 
 48 lignes sélectionnées

Il y a une notice pour comprendre tout ce bazard ???

**mnitu** · 10/07/2012, 14h06

Pourriez-vous exécuter sous sqlplus connecté comme dba ou autre utilisateur ayant les privilèges nécessaires et afficher le résultat

Code :

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

1
2
 
SQL> show parameter optimizer

**Logan Mauzaize** · 10/07/2012, 14h21

J'espère qu'il faut pas toucher à ces paramètres :s Ceci est notre base de développement.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
NAME                                               TYPE        VALUE                                                                                                
-------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------- 
optimizer_capture_sql_plan_baselines               boolean     FALSE                                                                                                
optimizer_dynamic_sampling                         integer     2                                                                                                    
optimizer_features_enable                          string      11.1.0.6                                                                                             
optimizer_index_caching                            integer     0                                                                                                    
optimizer_index_cost_adj                           integer     100                                                                                                  
optimizer_mode                                     string      ALL_ROWS                                                                                             
optimizer_secure_view_merging                      boolean     TRUE                                                                                                 
optimizer_use_invisible_indexes                    boolean     FALSE                                                                                                
optimizer_use_pending_statistics                   boolean     FALSE                                                                                                
optimizer_use_sql_plan_baselines                   boolean     TRUE

Pour information voici les paramètres de la production (version 10.2.0.5)

Code :

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

1
2
3
4
5
6
7
8
NAME                                               TYPE        VALUE                                                                                                
-------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------- 
optimizer_dynamic_sampling                         integer     2                                                                                                    
optimizer_features_enable                          string      10.2.0.5                                                                                             
optimizer_index_caching                            integer     0                                                                                                    
optimizer_index_cost_adj                           integer     100                                                                                                  
optimizer_mode                                     string      ALL_ROWS                                                                                             
optimizer_secure_view_merging                      boolean     TRUE

Pour info, non je ne peux pas tester en production, j'ai uniquement accès en lecture à ces bases.

**mnitu** · 10/07/2012, 14h44

Sans le rownum la requête ramène combien des enregistrements ?

**Logan Mauzaize** · 10/07/2012, 14h56

7536, oui je sais c'est précis :p Autant dire pas grand chose.

Je vais essayer de générer une version "simpliste" de mon domaine et de ma requête.

**mnitu** · 10/07/2012, 15h01

La zone LIEN_VERSION_CLIENT"."IS_LAST"=1 est-il une zone calculée dans une vue ?
Je pense que le problème n'est pas lié à la structure de la requête mais il se cache à l'intérieur de la vue "A" dans

Code :

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

SELECT * FROM A ORDER BY A.1, A.2, ...)

**ojo77** · 10/07/2012, 15h03

Envoyé par Nemek

Il y a une notice pour comprendre tout ce bazard ???

Oui : http://www.oracle.com/technetwork/da...011-393674.pdf

**Mohamed.Houri** · 10/07/2012, 15h25

Je pense que vous souffrez du problème de l'optimisateur basculant en mode FIRST_ROWS à cause de la clause where rownum <= 1

Ce qui me met dans cette piste c’est ceci:

Code :

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

1
2
 
|  20 |         INDEX FULL SCAN               | IX1_EXTERNAL_SERVICE     | 12314 | 61570 |       |     2   (0)| 00:00:01 |

Lorsque le CBO se positionne en mode FIRST_ROWS (entre autres à cause de la clause where rownum <= 1) il a tendance à privilégier un INDEX FULL SCAN afin d’éviter de faire un tri (ORDER BY) et ceci quelque soit le coût de l’opération FULL INDEX SCAN.

Vous pouvez vérifier, en utilisant l’event 10053, que dans le deuxième cas le CBO est bien passé en mode FIRST_ROWS ce qui a généré cette couteuse opération INDEX FULL SCAN. De plus cette opération se trouvant dans

Code :

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

1
2
3
4
 
|* 18 |       VIEW                            |                          |     1 |    18 |       |            |          |                                                                                                                                                                                   
|  19 |        SORT GROUP BY                  |                          |     1 |     5 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                   
|  20 |         INDEX FULL SCAN               | IX1_EXTERNAL_SERVICE     | 12314 | 61570

ne peut transférer ces données qu’une fois toute l’opération SORT GROUP BY finie. Autant dire une fois que tout l’indexe soit lu dans son entièreté.

C’est toujours le même problème qui se répète avec le ROWNUM.

order by and first_rows

Une autre remarque, sélectionner ROWNUM dans une sous requête empêche le CBO de faire un merge de cette sous-requête avec la requête mère qui l’invoque. Ceci doit être pris en considération aussi.

Complément:

Wrong Estimation = Wrong Plan = Wrong execution time

Observez bien qu'Oracle estime que la majorité des opérations retourne 1 seul enregistrement. Par exemple Il opte pour une NESTED LOOP

Code :

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

1
2
3
4
 
|  10 |           NESTED LOOPS                |                          |     2 |   832 |       |     4   (0)| 00:00:01 |                                                                                                                                                                                   
|* 11 |            TABLE ACCESS FULL          | LIEN_VERSION_CLIENT      |     4 |   812 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                   
|* 12 |            TABLE ACCESS BY INDEX ROWID| CLIENT                   |     1 |    10 |

qu'il pense être conduite(la LOOP) par la table directrice LIEN_VERSION_CLIENT ayant 4 enregistrements lorsque le filtre suivant lui est appliqué

Code :

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

  11 - filter("LIEN_VERSION_CLIENT"."IS_LAST"=1)

Mais observez bien le premier plan d'execution pour la même table

Code :

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

1
2
3
4
5
 
|* 20 |           HASH JOIN         |                          | 58251 |  3242K|  1152K|   472   (5)| 00:00:06 |                                                                                                                                                                                             
|* 21 |            TABLE ACCESS FULL| VERSION                  | 21741 |   891K|       |   105   (3)| 00:00:02 |                                                                                                                                                                                             
|* 22 |            TABLE ACCESS FULL| LIEN_VERSION_CLIENT      | 68659 |  1005K|       |   218   (7)| 00:00:03 |                                                                                                                                                                                             
----------------------------------------------------------------------------------------------------------------

Cette fois la table LIEN_VERSION_CLIENT lorsque le même filtre lui est appliquée retourne 68659 enregistrements!!! Mon Dieu quelle difference

Code :

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

1
2
 
22 - filter("LIEN_VERSION_CLIENT"."IS_LAST"=1)

**Logan Mauzaize** · 10/07/2012, 15h39

Mon domaine se compose d'une liste de produit. Chaque produit est décliné en plusieurs versions. Chaque client possède une ou plusieurs versions. Enfin, une version est liée à un ou plusieurs service externe. Un même service externe ne peut être liée qu'à une seule version.

Voici le code de la vue, utilisée par la requête A.

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
SELECT
    "VERSION"."VERSION_ID",
    "CLIENT"."FAMILY" "CLIENT_FAMILY",
    "CLIENT"."CODE" "CLIENT_CODE",
    "CLIENT"."NAME" "CLIENT_NAME",
    "CLIENT"."TYPE" "CLIENT_TYPE",
    "PRODUIT"."NAME" "PRODUIT_NAME",
    "PRODUIT"."FAMILY" "PRODUIT_FAMILY",
    "PRODUIT"."DOMAIN" "PRODUIT_DOMAIN",
    "PRODUIT"."CUSTOM" "PRODUIT_CUSTOM",
    "PRODUIT"."TYPE" "PRODUIT_TYPE",
    "VERSION"."DATE" "VERSION_DATE",
    "VERSION"."NUMERO" "VERSION_NUMERO",
    "VERSION"."NEXT" "VERSION_NEXT",
    "PRODUIT"."NORM" "PRODUIT_NORM",
    "PRODUIT"."MODEL" "PRODUIT_MODEL",
    "PRODUIT"."KEY" "PRODUIT_KEY",
    NVL(COUNT_EXS.NB,0) AS COUNT_EXS,
    "LIEN_VERSION_CLIENT"."LINK_SD",
    "LIEN_VERSION_CLIENT"."IS_LAST",
    NVL(COUNT_HISTORY.NB,0) AS COUNT_HISTORY
  FROM "PRODUIT"
  JOIN "VERSION"
  ON "VERSION"."PRODUIT_REF" = "PRODUIT"."PRODUIT_ID"
  AND "VERSION"."STATUS"   IN ('NEW', 'DIFFUSED')
  JOIN "LIEN_VERSION_CLIENT"
  ON "LIEN_VERSION_CLIENT"."VERSION_REF" = "VERSION"."VERSION_ID"
  JOIN "CLIENT"
  ON "CLIENT"."CLIENT_ID"      = "LIEN_VERSION_CLIENT"."CLIENT_REF"
  AND "CLIENT"."SOURCE" = 'APPLICATION'
  LEFT JOIN
    (SELECT r.PRODUIT_REF,
      r2c.CLIENT_REF,
      COUNT(*) nb
    FROM VERSION r
    JOIN LIEN_VERSION_CLIENT r2c
    ON r.VERSION_ID=r2c.version_ref
    GROUP BY r.PRODUIT_REF,
      r2c.CLIENT_REF
    ) COUNT_HISTORY ON COUNT_HISTORY."PRODUIT_REF" = "PRODUIT"."PRODUIT_ID"
  AND COUNT_HISTORY."CLIENT_REF"                = "CLIENT"."CLIENT_ID"
  LEFT JOIN
    (SELECT version_ref,
      COUNT(*) nb
    FROM external_service
    GROUP BY version_ref
    ) COUNT_EXS
  ON COUNT_EXS."version_ref" = "VERSION"."VERSION_ID";

Voici ensuite le code de la requête A:

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
 
select
  'constante',
  PRODUIT_FAMILY,
  PRODUIT_NAME,
  PRODUIT_DOMAIN,
  PRODUIT_CUSTOM ,
  PRODUIT_TYPE,
  VERSION_DATE,
  VERSION_NUMERO,
  VERSION_NEXT,
  max(LINK_SD),
  PRODUIT_MODEL,
  PRODUIT_NORM,
  max(VERSION_ID),
  max(COUNT_EXS),
  MAX(COUNT_HISTORY)
from
  mavue
where
  IS_LAST=1
group by
  'constante',
  PRODUIT_FAMILY,
  PRODUIT_NAME,
  PRODUIT_DOMAIN,
  PRODUIT_CUSTOM,
  PRODUIT_TYPE,
  VERSION_DATE,
  VERSION_NUMERO,
  VERSION_NEXT,
  PRODUIT_MODEL,
  PRODUIT_NORM 
order by
  PRODUIT_FAMILY asc,
  PRODUIT_TYPE ASC,
  PRODUIT_CUSTOM ASC

**Logan Mauzaize** · 10/07/2012, 16h00

Envoyé par Mohamed.Houri

Je pense que vous souffrez du problème de l'optimisateur basculant en mode FIRST_ROWS à cause de la clause where rownum <= 1

Si je comprends bien c'est "normal" ? Il y a moyen de forcer le mode "ALL_ROWS" ? Au moins pour tester.

Pour le moment, j'ai contourné le problème en faisant une récupération globale. Et je gère la "pagination" côté Java. Je n'ai pas vraiment la main sur la requête.
1) Elle est générée par Hibernate (framework de mapping SQL-Java)
2) Les ordres Hibernate sont générés par un pseudoframework interne. Le but de cette requête étant de générer un "cube" (au sens décisionnel) selon la sélection de l'utilisateur.

J'ai surtout la main sur la vue, les index, etc. J'ai également la possibilité de modifier le générateur de requête pour utiliser la solution double TOP avec SELECT ROWNUM, mais je voulais être sûr de pas louper quelque chose ...

**mnitu** · 10/07/2012, 16h35

Envoyé par Nemek

Si je comprends bien c'est "normal" ? Il y a moyen de forcer le mode "ALL_ROWS" ? Au moins pour tester.

Pour le moment, j'ai contourné le problème en faisant une récupération globale. ...

Code :

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

1
2
 
alter session set "_optimizer_rownum_pred_based_fkr" = false;

et puis dans la même session testez votre requête.

**Mohamed.Houri** · 10/07/2012, 16h46

Envoyé par mnitu

Code :

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

1
2
 
alter session set "_optimizer_rownum_pred_based_fkr" = false;

et puis dans la même session testez votre requête.

Ceci est "Ok" pour un environnement de test.

Mais comme c'est un "undocument parameter" ou un paramètre avec un under score (_), il ne faudrait pas l'envisager en PRODUCTION sans l'accord d'Oracle.

**mnitu** · 10/07/2012, 17h00

Envoyé par Nemek

... Et je gère la "pagination" côté Java. ...

Bref, ce n’est pas une bonne décision. Java ne peut gère la pagination que après avoir faire transitée tout le résultat de la requête via le réseau. C‘est moins efficace surtout quand vous cherchez la deuxième, troisième, N-ème page.

**mnitu** · 10/07/2012, 17h02

Envoyé par Mohamed.Houri

Ceci est "Ok" pour un environnement de test.

Mais comme c'est un "undocument parameter" ou un paramètre avec un under score (_), il ne faudrait pas l'envisager en PRODUCTION sans l'accord d'Oracle.

Tout à fait d'accord. C'est juste pour voir si c'est ça. Personellement je doute et je pense que le problème est dans la (requête de la) vue.

**Logan Mauzaize** · 10/07/2012, 17h50

Envoyé par mnitu

Bref, ce n’est pas une bonne décision. Java ne peut gère la pagination que après avoir faire transitée tout le résultat de la requête via le réseau. C‘est moins efficace surtout quand vous cherchez la deuxième, troisième, N-ème page.

En l'état je mets 4 secondes pour tout rapatrier contre 1H pour récupérer uniquement la première ligne ... Pour la méthode du double TOP avec SELECT ROWNUM, je met qu'une seconde de moins.

Je testerai tout de même le "ALTER SESSION", au moins pour valider l'origine du problème.

**mnitu** · 11/07/2012, 10h04

Envoyé par Nemek

En l'état je mets 4 secondes pour tout rapatrier contre 1H pour récupérer uniquement la première ligne ... Pour la méthode du double TOP avec SELECT ROWNUM, je met qu'une seconde de moins.
...

Je n’en doute pas de vos tests mais réfléchissez un peu à la différence entre faire la pagination sur le serveur et la faire dans le client : java ou autre.

Dégradation drastique des performances avec WHERE ROWNUM

Oracle

Discussions similaires

Partager

Partager