[Oracle 9.2.0.6] Explain Plan : question de débutant

Version imprimable

22/06/2005, 12h31
BenCar

[Oracle 9.2.0.6] Explain Plan : question de débutant

Bonjour à tous

J'essaie actuellement d'optimiser quelques requêtes. Pour cela, je me sers de l'explain plan.
Voilà mon problème, j'ai une requête avec un explain plan propre. Je passe bien par les index, j'ai uniquement un access full sur un table (normal, j'ai un count distinct sur une de ses colonnes). L'explain plan me renvoie un cost de 1300.
Dans ce cas, la requête me retourne le résultat en 2 minutes.

En suite, je reprend ma requête. Je modifie ma clause where (je change les join pour prendre un autre chemin).
A ce moment là, l'explain plan me dit : 5 access full et uniquement 1 index unique scan et un cost de 8700. Compte tenu de ma modif, c'est normal car le chemin est plus long.

MAIS, quand j'exécute la requête avec ces modifs, j'ai le résultat au bout de 20 secondes !!!!

Ma question est la suivante : ai je un problème ;-) ? Jusqu'où peut on faire confiance à l'explain plan ?

Je ne suis pas DBA (et ne compte pas le devenir) mais j'aimerais comprendre.

Merci à tous pour vos contributions.
Cordialement

Benoit
22/06/2005, 12h33
booth

Re: [Oracle 9.2.0.6] Explain Plan : question de débutant

Citation:

Envoyé par BenCar

Bonjour à tous

J'essaie actuellement d'optimiser quelques requêtes. Pour cela, je me sers de l'explain plan.
Voilà mon problème, j'ai une requête avec un explain plan propre. Je passe bien par les index, j'ai uniquement un access full sur un table (normal, j'ai un count distinct sur une de ses colonnes). L'explain plan me renvoie un cost de 1300.
Dans ce cas, la requête me retourne le résultat en 2 minutes.

En suite, je reprend ma requête. Je modifie ma clause where (je change les join pour prendre un autre chemin).
A ce moment là, l'explain plan me dit : 5 access full et uniquement 1 index unique scan et un cost de 8700. Compte tenu de ma modif, c'est normal car le chemin est plus long.

MAIS, quand j'exécute la requête avec ces modifs, j'ai le résultat au bout de 20 secondes !!!!

Ma question est la suivante : ai je un problème ;-) ? Jusqu'où peut on faire confiance à l'explain plan ?

Je ne suis pas DBA (et ne compte pas le devenir) mais j'aimerais comprendre.

Merci à tous pour vos contributions.
Cordialement

Benoit

montre tes deux requetes et tes explain plans ...

il se peut que tes statistiques ne soient pas mises à jour ....

Voici la requête :

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
SELECT   COUNT (DISTINCT CASE
                   WHEN t32385.opty_wid > 0
                      THEN t32385.opty_wid
                END) AS c1,
         t32577.stg_name AS c2, t43873.camp_name AS c3,
         t220742.attrib_01 AS c4, t43873.camp_src_num AS c5,
         t43873.camp_type AS c6, t220742.attrib_21 AS c7
    FROM w_opty_d t31547,
         w_revn_f t32385,
         w_sstage_d t32577,
         w_source_d t43873,
         w_opty_dx t204383,
         w_source_dx t220742
   WHERE (    t32385.source_wid = t43873.row_wid
          AND
              --T32385.OPTY_WID = T204383.ROW_WID and
              t31547.row_wid = t32385.opty_wid
          AND t31547.row_wid = t204383.row_wid
          AND
              --T32385.SOURCE_WID=T220742.ROW_WID  and
              t43873.row_wid = t220742.row_wid
          AND t32385.curr_sstage_wid = t32577.row_wid
         )
GROUP BY t32577.stg_name,
         t43873.camp_name,
         t43873.camp_src_num,
         t43873.camp_type,
         t220742.attrib_21,
         t220742.attrib_01

Cette requête est celle qui s'éxécute en moins de 20 secondes. Pour avoir l'autre, il suffit d'enlever les commentaires dans la clause where.
Voici son explain plan :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT STATEMENT Optimizer Mode=CHOOSE		594 K	 	8690  	 	      	             	 
SORT GROUP BY		594 K	44 M	8690  	 	      	              
NESTED LOOPS		594 K	44 M	1386  	 	      	              
NESTED LOOPS		596 K	42 M	1386  	 	      	              
HASH JOIN		596 K	39 M	1386  	 	      	             	 
TABLE ACCESS FULL	SIEBEL.W_SSTAGE_D	7  	63  	2  	         
HASH JOIN		596 K	34 M	1380  	 	      	             	 
HASH JOIN		2 K	121 K	25  	 	      	             	 
TABLE ACCESS FULL	SIEBEL.W_SOURCE_DX	2 K	34 K	2  	 
TABLE ACCESS FULL	SIEBEL.W_SOURCE_D	2 K	86 K	22  	  
TABLE ACCESS FULL	SIEBEL.W_REVN_F	596 K	6 M	1334  	  
INDEX UNIQUE SCAN	SIEBEL.W_OPTY_D_P1	1  	5  	 	 
INDEX UNIQUE SCAN	SIEBEL.W_OPTY_DX_P1	1  	5

L'explain plan de la requête de base (celle qui prend 2 mn) est le suivant :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT STATEMENT Optimizer Mode=CHOOSE		1  	 	1342  	 	      	             	 
SORT GROUP BY		1  	79  	1342  	 	      	             
NESTED LOOPS		1  	79  	1339  	 	      	             
NESTED LOOPS		1  	70  	1338  	 	      	             
NESTED LOOPS		2  	70  	1336  	 	      	             
NESTED LOOPS		2  	42  	1334  	 	      	             
NESTED LOOPS		596 K	9 M	1334  	 	      	             
TABLE ACCESS FULL	SIEBEL.W_REVN_F	596 K	6 M	1334  	 
INDEX UNIQUE SCAN	SIEBEL.W_OPTY_DX_P1	1  	5  	 	 
INDEX UNIQUE SCAN	SIEBEL.W_OPTY_D_P1	1  	5  	 	 
TABLE ACCESS BY INDEX ROWID	SIEBEL.W_SOURCE_DX	1  	14  	1
INDEX UNIQUE SCAN	SIEBEL.W_SOURCE_DX_P1	1  	 	 
TABLE ACCESS BY INDEX ROWID	SIEBEL.W_SOURCE_D	1  	35  	1
INDEX UNIQUE SCAN	SIEBEL.W_SOURCE_D_P1	1  	 	 
TABLE ACCESS BY INDEX ROWID	SIEBEL.W_SSTAGE_D	1  	9  	1 
INDEX UNIQUE SCAN	SIEBEL.W_SSTAGE_D_P1	1

Toutes les statistiques sont à jour. J'ai un programme qui lance les analyses tous les matins.

22/06/2005, 13h33
LeoAnderson

Merci d'éditer votre dernier message pour y intégrer les balises [ code ] et [ quote ] (sans les espaces) ;-)

Sinon, je ne vois pas en quoi ça vous surprend que 5 ACCESS FULL soient plus lents qu'un seul ??? :?
22/06/2005, 13h38
BenCar

Désolé pour les balises. C'est mon premier message. je ne suis pas encore habitué.

Ce qui me gêne, c'est que justement c'est le contraire. La requête est plus rapide avec 5 access full qu'avec un seul !
22/06/2005, 13h44
orafrance

il y a 2 problèmes : le TBS sur W_REVN_F et le tri

est-ce que tu as un index sur (t32385.source_wid,t32385.curr_sstage_wid) ?
22/06/2005, 13h50
BenCar

Oui, j'ai deux index :
W_REVN_F_F15 sur CURR_SSTAGE_WID
et
W_REVN_F_F29 sur SOURCE_WID

Qu'entends tu par :
"il y a 2 problèmes : le TBS sur W_REVN_F et le tri" ?
22/06/2005, 14h03
orafrance

TFS et pas TBS :aie: :arrow: Table Full Scan ;)

Essaye d'ajouter un index avec les 2 colonnes et si ça ne suffit pas ajoute un hint : /*+ indexes(tab,ind) */ :arrow: :recherch:

Pour le tri, bah c'est le GROUP BY qui consomme énormémement aussi... regarde les couts pour chacune des lignes pour repérer celles où le coùt est le plus important ;)

J'ai ajouté l'index mais il ne le prend pas en compte même avec un hint.
Par contre, j'en ai créé un avec les 3 colonnes suivante : SOURCE_WID,OPTY_WID ET CURR_SSTAGE_WID.
Là, il passe bien par ce nouvel index et j'obtiens un cost de 9.
Voici l'explain plan
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 SELECT STATEMENT Optimizer Mode=CHOOSE 1 9 SORT GROUP BY 1 75 9 NESTED LOOPS 1 75 6 NESTED LOOPS 2 122 4 NESTED LOOPS 2 52 2 NESTED LOOPS 596 K 11 M 2 NESTED LOOPS 596 K 9 M 2 TABLE ACCESS FULL SIEBEL.W_SSTAGE_D 7 63 2 INDEX FULL SCAN SIEBEL.TEST_DEV 85 K 582 K INDEX UNIQUE SCAN SIEBEL.W_OPTY_D_P1 1 5 INDEX UNIQUE SCAN SIEBEL.W_OPTY_DX_P1 1 5 TABLE ACCESS BY INDEX ROWID SIEBEL.W_SOURCE_D 1 35 1 INDEX UNIQUE SCAN SIEBEL.W_SOURCE_D_P1 1 TABLE ACCESS BY INDEX ROWID SIEBEL.W_SOURCE_DX 1 14 1 INDEX UNIQUE SCAN SIEBEL.W_SOURCE_DX_P1 1
La requête est rapide (9 secondes).
Merci de ton aide.
Je vais encore la travailler pour voir si je peux éliminer le dernier Table Access Full.

Jusqu'où peut on faire confiance à l'explain plan.
Maintenant, il est logique dans le sens où le côut annoncé est faible et que son temps d'exécution est plus rapide.

Mais avant j'avais le contraire.
Quelques conseils ??

Encore merci de ton aide.

22/06/2005, 14h45
bouyao

Citation:

Envoyé par BenCar

Jusqu'où peut on faire confiance à l'explain plan.
Maintenant, il est logique dans le sens où le côut annoncé est faible et que son temps d'exécution est plus rapide.

c'est faux, un cout de explain plan faible ne veut pas dire un temps d'executions plus rapide.
22/06/2005, 14h48
BenCar

OK
Alors à quoi faut il être attentif sur l'explain plan ?

Dans mon cas, j'avais un plan avec 5 table access full qui était plus rapide que celui avec 1 seul table access full pour le même résultat.

Merci pour vos éclaircissements.
22/06/2005, 15h00
orafrance

Citation:

Envoyé par bouyao

Citation:

Envoyé par BenCar

Jusqu'où peut on faire confiance à l'explain plan.
Maintenant, il est logique dans le sens où le côut annoncé est faible et que son temps d'exécution est plus rapide.

c'est faux, un cout de explain plan faible ne veut pas dire un temps d'executions plus rapide.

Certes mais c'est quand même très souvent le cas ;)

Le coût est calculé selon le nombre de bloc à lire pour récupérer l'info il me semble. En gros, meilleur est le cout, meilleur est le plan d'exécution mais un index mal choisi peut leurrer Oracle.

Le plan d'exécution permet justement de mettre en lumière des indexes peu intéressant comme c'était ton cas ;)
22/06/2005, 15h03
BenCar

Merci orafrance pour ton aide.
23/06/2005, 10h56
Jaouad

Citation:

c'est faux, un cout de explain plan faible ne veut pas dire un temps d'executions plus rapide.

Exactement , cela fait partie des idées recu ,
comme le fait qu'un FTS est plus rapide qu'un accées indéxes .

Si vous connaissez vos données , et que l'explain plan vous montre un FTS sur une grosse table dans ce cas OK , sinon le juge de paix reste en réghle génèral le temps d'éxècution .