Index non utilisé dans une requête

**tibal** · 05/05/2010, 16h48

Bonjour,
J'ai une requête utilisant une fonction (voir plus bas). Afin d'améliorer les performances, j'ai ajouter un indexe basé sur cette fonction, mais le moteur Oracle ne l'utilise pas, et lorsque je force l'utilisation de cet indexe via un HINT, je vois que le moteur Oracle a raison mais ne comprend pas. Voici le détail :
Voici le 1er plan :

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
explain plan for SELECT
  NVL(DOSSIER,'VIDE' ) AS T1
FROM
  MA_TABLE
GROUP BY
   NVL(DOSSIER,'VIDE')
ORDER BY
   T1 ASC;
 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 4111865712
 
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |   364 |  2912 | 13002  (10)| 00:02:37 |
|   1 |  SORT ORDER BY      |                            |   364 |  2912 | 13002  (10)| 00:02:37 |
|   2 |   HASH GROUP BY     |                            |   364 |  2912 | 13002  (10)| 00:02:37 |
|   3 |    TABLE ACCESS FULL| MA_TABLE                   |  2235K|    17M| 12256   (4)| 00:02:28 |
--------------------------------------------------------------------------------------------------

Second plan avec HINT sur l'indexe de fonction :

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
explain plan for SELECT /*+ INDEX(MA_TABLE IDX_NVL_MA_TABLE_ASC) */
  NVL(DOSSIER,'VIDE' ) AS T1
FROM
  MA_TABLE
GROUP BY
   NVL(DOSSIER,'VIDE')
ORDER BY
   T1 ASC;
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
Plan hash value: 1625393906
 
------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                        |   364 |  2912 |   373K  (1)| 01:14:48 |
|   1 |  SORT ORDER BY        |                        |   364 |  2912 |   373K  (1)| 01:14:48 |
|   2 |   SORT GROUP BY NOSORT|                        |   364 |  2912 |   373K  (1)| 01:14:48 |
|   3 |    INDEX FULL SCAN    | IDX_NVL_MA_TABLE_ASC   |  2193K|       |  6773   (2)| 00:01:22 |
------------------------------------------------------------------------------------------------

De plus, mon indexe de fonction est déja trié.
Vu que je ne souhaite obtenir que les informations d'un colonne et que cette dernière est indexée, pourquoi Oracle ne l'utilise pas et pourquoi ce plan qui prend 1h12' de plus ?

Ps : taille de la table 472 Mo et 60 416 blocks
taille de l'indexe 56 Mo et 7 168 blocks

Merci d'avance pour votre aide.

**Waldar** · 05/05/2010, 17h11

Qu'est-ce que ça donne avec cette requête :

Code :

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

1
2
SELECT DISTINCT NVL(DOSSIER,'VIDE' ) AS T1
  FROM MA_TABLE;

**mnitu** · 05/05/2010, 19h40

Si vous examinez les deux plans il est facile de voir que la principale différence est donnée par la méthode utilisé pour l'agrégation: SORT GROUP BY (NOSORT) vs HASH GROUP BY.

**tibal** · 06/05/2010, 08h39

Bonjour,

Waldar, voici le résultat de la requête :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT DISTINCT NVL(DOSSIER,'VIDE' ) AS T1
  FROM MA_TABLE;
T1
--------------------
TOTO
TATA
TITI
.
VIDE
.
.
967 ligne(s) sÚlectionnÚe(s).

Mnitu :

Si vous examinez les deux plans il est facile de voir que la principale différence est donnée par la méthode utilisé pour l'agrégation: SORT GROUP BY (NOSORT) vs HASH GROUP BY.

Oui, ça je l'ai bien vu mais ça ne m'explique pas, ni le choix, ni le chiffrage de l'optimiseur.

D'ailleurs, j'ai relancé la requête avec le HINT en la traçant. Et curieusement, le résultat n'est pas du tout celui prédit par

Code :

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

EXPLAIN PLAN FOR ...

Voici le résultat de la trace dans TKPROF :

Code :

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

1
2
3
4
5
6
7
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       66      2.36       5.83       6683       6683          0         967
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       68      2.38       5.84       6683       6683          0         967

Quelqu'un peut t'il m'expliquer pourquoi la prévision de l'optimiseur est à ce point erronnée ?

**Waldar** · 06/05/2010, 10h00

Envoyé par tibal

Waldar, voici le résultat de la requête :

Je parlais de l'explain plan et du temps d'exécution bien sûr

**tibal** · 06/05/2010, 10h08

Voici Waldar :

Code :

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

1
2
3
explain plan for 
SELECT DISTINCT NVL(DOSSIER,'VIDE' ) AS T1
  FROM MA_TABLE;

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
Plan hash value: 2166119174
 
--------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                        |  2193K|    16M|  1520   (4)| 00:00:19 |
|   1 |  PX COORDINATOR         |                        |       |       |            |          |
|   2 |   PX SEND QC (RANDOM)   | :TQ10000               |  2193K|    16M|  1520   (4)| 00:00:19 |
|   3 |    PX BLOCK ITERATOR    |                        |  2193K|    16M|  1520   (4)| 00:00:19 |
|   4 |     INDEX FAST FULL SCAN| IDX_NVL_MA_TABLE_ASC   |  2193K|    16M|  1520   (4)| 00:00:19 |
--------------------------------------------------------------------------------------------------

**mnitu** · 06/05/2010, 10h26

Envoyé par tibal

...
Mnitu :

Oui, ça je l'ai bien vu mais ça ne m'explique pas, ni le choix, ni le chiffrage de l'optimiseur.

Si vous voulez plus des informations tracer l'optimiseur via l'événement 10053.

Envoyé par tibal

...
...
D'ailleurs, j'ai relancé la requête avec le HINT en la traçant. Et curieusement, le résultat n'est pas du tout celui prédit par EXPLAIN PLAN FOR ...

Quelqu'un peut t'il m'expliquer pourquoi la prévision de l'optimiseur est à ce point erronnée ?

Le plan ne dépend pas seulement de la requête mais aussi de pas mal des autres choses: les paramètres de la session, les statistiques, etc.

**Waldar** · 06/05/2010, 10h49

Tibal, ok, mais il faut en tirer les conclusions !
L'explain plan paraît bien, il utilise l'index et il n'y a pas besoin de hint.

La conclusion c'est qu'une requête correctement écrite est meilleure qu'une requête mal écrite.
Utiliser group by pour faire un distinct, c'est très rarement justifiable !

**mnitu** · 06/05/2010, 11h32

Il ne faut pas non plus tirer trop vite des conclusions (mais, je suis tout à fait d'accord Waldar avec ta remarque). Que est-ce que ça donne

Code :

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

1
2
3
4
5
 
EXPLAIN plan FOR 
SELECT DISTINCT NVL(DOSSIER,'VIDE' ) AS T1
  FROM MA_TABLE
ORDER BY T1

**tibal** · 10/05/2010, 15h29

Waldar :

Tibal, ok, mais il faut en tirer les conclusions !
L'explain plan paraît bien, il utilise l'index et il n'y a pas besoin de hint.

Oui, mais pas dans la requête initiale, et c'est celle-ci qui est importante.

La conclusion c'est qu'une requête correctement écrite est meilleure qu'une requête mal écrite.

Je n'ai pas la main, c'est un applicatif client qui en est à l'origine.

Utiliser group by pour faire un distinct, c'est très rarement justifiable !

Il n'y a pas de group by et un distinct dans ma requête initiale.

Mnitu, voici le résulta demandé :

Code :

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

1
2
3
4
EXPLAIN plan FOR 
SELECT DISTINCT NVL(DOSSIER,'VIDE' ) AS T1
  FROM MA_TABLE
ORDER BY T1

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
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
Plan hash value: 3763606681
 
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                        |  2193K|    16M|       |  9797   (5)| 00:01:58 |
|   1 |  PX COORDINATOR            |                        |       |       |       |            |       |
|   2 |   PX SEND QC (ORDER)       | :TQ10001               |  2193K|    16M|       |  9797   (5)| 00:01:58 |
|   3 |    SORT ORDER BY           |                        |  2193K|    16M|    67M|  9797   (5)| 00:01:58 |
|   4 |     PX RECEIVE             |                        |  2193K|    16M|       |  1520   (4)| 00:00:19 |
|   5 |      PX SEND RANGE         | :TQ10000               |  2193K|    16M|       |  1520   (4)| 00:00:19 |
|   6 |       PX BLOCK ITERATOR    |                        |  2193K|    16M|       |  1520   (4)| 00:00:19 |
|   7 |        INDEX FAST FULL SCAN| IDX_T_DOSSIER_USER_ASC |  2193K|    16M|       |  1520   (4)| 00:00:19 |
-------------------------------------------------------------------------------------------------------------

Merci encore à tous les deux.

Index non utilisé dans une requête

Administration Oracle

Discussions similaires

Partager

Partager