Discussion :

[olivier]]

Bonjour.

Je travaille sur une base 10.2.0.5.0 sur Win2003 SP2.

Voici mon problème :

La requête

Code :

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

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 :

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

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

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

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 :

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

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 :

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

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