Requête ralentie par "order by" [Résolu]

[olivier]]

Bonjour.

Je travaille sur une base 10.2.0.5.0 sur Win2003 SP2.

Voici mon problème :

La requête

Code :

1
2
 
SELECT * FROM TABLE3 WHERE CLE2 IN ( SELECT CLE2 FROM TABLE2 WHERE CLE1 = 354063);

ramène 40 lignes en une seconde voir moins.
Si je rajoute la clause ORDER BY j'obtiens un temps de réponse à pleurer !

TABLE2 compte près de 3 millions de lignes (9 champs num(10), 1 champ date)
TABLE3 compte près de 32 million de lignes (4 champs num (10), 1 champ date)

J'ai exécuté un explain plan des 2 requêtes dont voici le résultat.

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
 
SQL> EXPLAIN plan FOR SELECT * FROM TABLE3 WHERE CLE2 IN ( SELECT CLE2 FROM TABLE2 WHERE CLE1 = 354063);
 
Explicité.
 
SQL> SELECT * FROM TABLE(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3324095648
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                    |    68 |  2176 |    18   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID  | TABLE3             |    14 |   294 |     3   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |                    |    68 |  2176 |    18   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| TABLE2             |     5 |    55 |     4   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IDX_TABLE2_CLE1    |     5 |       |     3   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | PK_TABLE3          |    14 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   4 - access("CLE1"=354063)
   5 - access("CLE2"="CLE2")
 
18 ligne(s) sélectionnée(s).

et

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
 
SQL> EXPLAIN plan FOR SELECT * FROM TABLE3 WHERE CLE2 IN ( SELECT CLE2 FROM TABLE2 WHERE CLE1 = 354063) ORDER BY CLE_TRI;
 
Explicité.
 
SQL> SELECT * FROM TABLE(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3634225011
 
-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                        |    68 |  2176 |    69M  (1)|233:01:59 |
|   1 |  NESTED LOOPS                |                        |    68 |  2176 |    69M  (1)|233:01:59 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TABLE3                 |    31M|   638M|  6161K  (1)| 20:32:24 |
|   3 |    INDEX FULL SCAN           | TABLE4TABLE3           |    31M|       | 64280   (1)| 00:12:52 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| TABLE2                 |     1 |    11 |     2   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN         | PK_TABLE2              |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   4 - filter("CLE1"=354063)
   5 - access("CLE2"="CLE2")
 
18 ligne(s) sélectionnée(s).

Quelqu'un pourrait-il m'expliquer pourquoi j'ai une telle variation de temps de réponse?

[mnitu]

Votre première requête réécrite comme :

Code :

1
2
3
4
5
6
7
8
9
 
SELECT TABLE3.*
  FROM TABLE3
	JOIN
	(SELECT DISTINCT cle2 AS cle2
                FROM TABLE2
              WHERE cle1 = 'valeur'
           )
     ON TABLE3.cle2 = TABLE2.cle2

est une jointure entre les deux tables avec une seule condition de filtrage sur la table TABLE2. Le plan choisi par l'optimiseur est d'identifier les enregistrements de la table TABLE2 via l'index IDX_TABLE2_CLE1 et par la suite d'utiliser la colonne CLE2 pour accéder via l'index PK_TABLE3 aux enregistrements de la table TABLE3.

Le fait d'ajouter le tri sur une colonne indexée de la table TABLE3: CLE_TRI ouvre une autre possibilité: accéder d'abord aux enregistrements de la table TABLE3 via l'index TABLE4TABLE3 (!?) et éviter ainsi l'étape de tri imposé par la présence de la clause SQL ORDER BY, trouver via la CLE2 l'enregistrement de la table TABLE2 et filtrer par rapport à la condition de filtrage sur CLE1.

Maintenant, la vrai la question est pourquoi cela se produit vu qu'à priori vos statistiques semble correctes (à vérifier) et que j'ai du mal à m'imaginer pourquoi la solution basée sur le premier plan avec en plus le tri effectué en dernière étape lui semble plus coûteuse que le plan 2 ?

Avez vous plus de détails à nous donner ?

[olivier]]

Bonjour.

Je me permet juste d'ajouter, pour ceux qui liront peut être cette discussion, un petit correctif (c'est du moins ce que j'ai dû faire) :

Code :

1
2
3
4
5
6
7
8
SELECT TABLE3.*
  FROM TABLE3
	JOIN
	(SELECT DISTINCT cle2 AS cle2
                FROM TABLE2
              WHERE cle1 = 'valeur'
           ) as TABLE2
     ON TABLE3.cle2 = TABLE2.cle2

Ceci étant dit, comment puis-je vérifier l'exactitude des mes statistiques?
Quels détails puis-je vous fournir qui permettraient de pousser l'analyse?

[mnitu]

Merci, j'avais l'intime conviction d'avoir corrigé la requête!

Essayez de suivre le démarche proposé par Mohamed.Houri dans ses intervention (#4 ou #8) pour comparer les statistiques estimés par rapport aux statistiques réelles.

[olivier]]

Je regarde ces 2 points et je vous tiens au courant.

[olivier]]

Bonjour.

Désolé d'avoir interrompu cette conversation aussi longtemps.
J'ai pu exécuter ce que vous m'aviez demandé et voici les résultats :

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
SQL> ALTER session SET statistics_level = ALL;
 
Session modifiée.
 
SQL> SELECT * FROM TABLE3 WHERE CLE2 IN ( SELECT CLE2 FROM TABLE2 WHERE CLE1 = 354063);
 
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6nwkss5c38wqf, child number 1
-------------------------------------
SELECT * FROM TABLE3 WHERE CLE2 IN ( SELECT CLE2 FROM TABLE2 WHERE CLE1 = 354063)
 
Plan hash value: 3324095648
 
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                    |      0 |        |      0 |00:00:00.01 |       0 |      0 |
|   1 |  TABLE ACCESS BY INDEX ROWID  | TABLE3             |      1 |     14 |     39 |00:00:00.03 |      28 |      6 |
|   2 |   NESTED LOOPS                |                    |      1 |     65 |     44 |00:00:00.02 |      23 |      4 |
|   3 |    TABLE ACCESS BY INDEX ROWID| TABLE2             |      1 |      5 |      4 |00:00:00.01 |       9 |      2 |
|*  4 |     INDEX RANGE SCAN          | IDX_TABLE2_CLE1    |      1 |      5 |      4 |00:00:00.01 |       6 |      2 |
|*  5 |    INDEX RANGE SCAN           | PK_TABLE3          |      4 |     14 |     39 |00:00:00.01 |      14 |      2 |
-----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   4 - access("CLE1"=354063)
   5 - access("CLE2"="CLE2")
 
 
23 ligne(s) sélectionnée(s).

et

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
SQL> SELECT * FROM TABLE3 WHERE CLE2 IN ( SELECT CLE2 FROM TABLE2 WHERE CLE1 = 354063) ORDER BY 2;
 
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9ksd6bb3xfcdc, child number 1
-------------------------------------
SELECT * FROM TABLE3 WHERE CLE2 IN ( SELECT CLE2 FROM TABLE2 WHERE CLE1 = 354063) ORDER BY CLE_TRI
 
Plan hash value: 3634225011
 
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                        |      1 |        |     39 |00:15:38.05 |     102M|   5223K|
|   1 |  NESTED LOOPS                |                        |      1 |     65 |     39 |00:15:38.05 |     102M|   5223K|
|   2 |   TABLE ACCESS BY INDEX ROWID| TABLE3                 |      1 |     32M|     32M|00:10:11.26 |    6445K|   5194K|
|   3 |    INDEX FULL SCAN           | TABLE4TABLE3           |      1 |     32M|     32M|00:00:32.18 |   65951 |  65869 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| TABLE2                 |     32M|      1 |     39 |00:04:30.91 |      96M|  28267 |
|*  5 |    INDEX UNIQUE SCAN         | PK_TABLE2              |     32M|      1 |     32M|00:02:36.58 |      64M|  24850 |
--------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   4 - filter("CLE1"=354063)
   5 - access("CLE2"="CLE2")
 
 
23 ligne(s) sélectionnée(s).

Comment dois-je interpréter ces résultats?

[mnitu]

Il y a quelque chose qui m’interpelle : la première requête ne ramène rien et la deuxième ramène 39 enregistrements ! Est-ce ce le cas ?

[olivier]]

C'est vrai que c'est surprenant. Je n'avais pas vu que la première ligne du tableau renvoie A-ROWS=0.
Pourtant, la requête me restitue bien les mêmes 39 lignes que la deuxième requête. Je viens de la relancer à l'instant

[mnitu]

Pourriez-vous exécuter aussi cette commande (vous devez avoir accès à la table sys.v$parameter)

Code :

1
2
3
4
5
6
7
8
9
10
11
 
SQL> SHOW parameter optimizer
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      10.2.0.4
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      first_rows
optimizer_secure_view_merging        BOOLEAN     TRUE

[olivier]]

Code :

1
2
3
4
5
6
7
8
9
10
SQL> SHOW parameter optimizer;
 
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      FIRST_ROWS
optimizer_secure_view_merging        BOOLEAN     TRUE

[Mohamed.Houri]

Envoyé par olivier]

Code :

Votre problème est là. Changer en ALL_ROWS et donnez nous des nouvelles

C'est très intéressant de le confirmer ou d'infirmer

[olivier]]

Heu.... Comment je change ce parametre?

[Mohamed.Houri]

Envoyé par olivier]

Heu.... Comment je change ce parametre?

Code :

1
2
 
ALTER session SET optimizer_mode=ALL_ROWS;

[mnitu]

Envoyé par Mohamed.Houri

...
C'est très intéressant de le confirmer ou d'infirmer

C'est vrai que First_Rows est mauvais mais j'en doute que jusqu'à ce point. Mais comme vous dites à confirmer/infirmer!

[olivier]]

j'ai un peu oublié GooGle sur ce coup-la!

Ceci étant dit, avec les bons paramètres, ça va tout de suite mieux.

temps d'execution
sans tri 00: 00: 00.09
avec tri 00: 00: 00.12

Merci infiniment de votre aide à tous les deux.

[Mohamed.Houri]

Envoyé par olivier]

j'ai un peu oublié GooGle sur ce coup-la!

Ceci étant dit, avec les bons paramètres, ça va tout de suite mieux.

temps d'execution
sans tri 00: 00: 00.09
avec tri 00: 00: 00.12

Merci infiniment de votre aide à tous les deux.

Et maintenant je vais vous expliquer pourquoi?

C’est un bug dans le hint first_rows qu’Oracle maintient uniquement pour raison de ''backward compatibility'' en gros pour que le passé ne soit pas dépaysé.

Il ne faut plus l’utiliser dans les nouvelles versions et optez plutot si besoin est au first_rows_n.

Sous ce mode d’optimisation le CBO, s’il voit qu’il peut utiliser un index pour éviter un ORDER BY, alors il le fera quelque soit le cout de l’accès à cet index.

Observez bien que dans votre second ''explain plan'', il n’y a aucune opération du type ORDER BY alors que votre requête contient bien une instruction ORDER BY. Par contre, on constate bien une opération INDEX FULL SCAN très couteuse et très volumineuse 32M

Code :

1
2
 
3 |    INDEX FULL SCAN           | TABLE4TABLE3           |      1 |     32M|     32M|00:00:32.18 |

C’est cette opération qui a été privilégiée à un simple ORDER BY de 39 records.

Une remarque : dans des plans d’exécution plus compliqués on aurait observé une opération du type sort (order by) nosort montrant qu’il y a bien eu un order by mais sans passer par un tri supplémentaire.

[olivier]]

Il ne faut plus l’utiliser dans les nouvelles versions et optez plutot si besoin est au first_rows_n.

Est-ce un paramètre que je peux modifier au niveau de la base pour que toutes les sessions (SQLPlus ou via des programmes) le prennent en compte ou bien dois-je le faire à chaque session SQLPlus que j'ouvre?

[mnitu]

Envoyé par Mohamed.Houri

...C’est un bug dans le hint first_rows qu’Oracle maintient uniquement pour raison de ''backward compatibility'' en gros pour que le passé ne soit pas dépaysé.
...

Si vous me permettez quelques précisions :
Ici first rows n’est pas un hint SQL mais un mode de fonctionnement de l’optimiseur légal, initialisé via un paramètre. Il existe bien aussi un hint SQL qui porte ce nom.
Il n’y a pas de bug, tout simplement il fonctionne comme ça, d’après la documentation il utilise un mix de coût et règles heuristiques pour trouver un bon plan. De plus la documentation indique clairement que parfois il se trompe et elle recommande de ne plus l’utiliser

FIRST_ROWS The optimizer uses a mix of cost and heuristics to find a best plan for fast delivery of the first few rows.
Note: Using heuristics sometimes leads the query optimizer to generate a plan with a cost that is significantly larger than the cost of a plan without applying the heuristic. FIRST_ROWS is available for backward compatibility and plan stability; use FIRST_ROWS_n instead

Bravo à vous pour avoir bien vu !

[olivier]]

C'est bon, j'ai trouvé la réponse à ma dernière question

Code :

ALTER system SET optimizer_mode = FIRST_ROWS_1000;

Encore merci tant pour les réponses que pour les compléments que je vais garder précieusement.

[mnitu]

Ajoutez scope=both si vous avez un spfile ou modifiez aussi le fichier initXXX.ora.
Sinon au prochain démarrage de l’instance vous allez avoir une surprise.