optimisation requête avec jointures externes

**beurtom** · 16/10/2006, 11h31

Bonjour je travaille sur une evo d'une requête ayant des jointures externes. Je ne peux les enlever parce que les utilisateurs ont besoin des infos envoyer grâce à ces jointures. Lorsque je lance la nouvelle requête elle met énormément de temps (après 3h pas de réponses). Avez vous un moyen de l'optimiser? le "where" de ma requête est:

where T1."KSESSION"=T2."KSESSION"(+) and T1."KSTAGE"=T2."KSTAGE"(+) and T1."KSESSION"=T4."KSESSION"(+) and T1."KSTAGE"=T4."KSTAGE"(+)
and T4."KSOC"=T5."KSOC"(+) and T4."NMAT"=T5."NMAT"(+) and T4."KUOSAL"=T6."KUO"(+) and T4."KSESSION"=T7."KSESSION"(+) and T4."KSTAGE"=T7."KSTAGE"(+)
and T4."NMAT"=T7."NMAT"(+) and T1."EASSUR"=T3."EASSUR" and T1."KUOGES"=T8."KUOGES" and T4."KUOSAL"=T9."KUOSAL"
and T1."DDEBSES">=to_date('1930-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and T1."DFINSES"<=to_date('2999-12-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and T1."KSTAGE" like '%' and T8."APERIMCSP"='CSP' and T9."APERIMCSP"='CSP'
order by T1."KUOGES" asc, T1."KSTAGE" asc, T1."KSESSION" asc

En rouge je vous ai mis les nouvelles tables que j'ai créé.

Merci d'avance

**orafrance** · 16/10/2006, 11h57

Essaye de supprimer l'ORDER BY. Quel est le plan d'exécution (recherche explain plan) ? Y a-t-il des indexes ?

**remi4444** · 16/10/2006, 12h12

C'est marrant ça une requête qui commence par "where"....

- Assure toi que les stats sont bien passées

- si oui, il faut absoluement editer le plan d'exécution de la requête c'est par là que tout commence....

**beurtom** · 16/10/2006, 13h46

Tout dabord je vous remercie pour m'avoir répondu aussi rapidement.

J'ai des indexes sur toutes les tables et toutes les colonnes me servant pour les jointures. J'ai regardé l'explain plan et j'ai les tables T1, T4 et une table qui est utilisée pour créer T8 et T9 qui sont en access full.
Je précise que les tables T8 et T9 sont des vues qui sont créées à partir de plusieurs tables. J'ai aussi enlever le order by. J'ai éssayé de créer plutôt des tables à la place des vues mais cela n'a rien changé, la requête est toujours aussi longue à s'exécuter. Je vous met tout le script, il est assez long, c'est la raison pour laquelle tout à l'heure je n'ai mis que le where.

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
 
select T1."KSTAGE", T1."ASTAGEL", T1."KSESSION", T1."ASESSIONC", T1."KUOGES", T1."AUOGESC", 
T1."AETATSESC", T1."DDEBSES", T1."DFINSES", T1."QHRESES", T1."QSMAXSES"-T1."QSINSSES", T1."QSINSSES", 
T1."QSATTSES", T2."AORGL", T3."AASSUR", T4."NMAT", T4."ANOM", T4."KSEXE", T5."KMESSAG", T5."ALACC", 
T5."ALIEUC", T4."KUOSAL", T4."AENTITEC", T6."KUO4", T6."AUOC4", T6."KUO3", T6."AUOC3", T4."KCLASS", T4."ACLASSC", 
T4."KMETRATP", T4."AMETRATPC", T4."KPLAN", T4."EINEXSAL", T4."AETINSSESC", T4."DETINSSES", T4."QDURPRES", T4."IDPFS", 
T4."IDIDFS", T4."IDDDS", T4."AFRATPC", T4."KOBJDEP", T4."AOBJDEP", T4."AOBJFIN", T4."AIMFS", T4."KPOINTA", T4."ECCNV", 
T4."KDEMANDE", T4."ADEMANDEC", T4."DDEMANDE", T4."NMATID", T4."DVDFSES", T7."ATYPCOUC", T7."DEFFCOU", T7."QMONENGA", T7."KTYPCOU", 
T7."QMONBRUT", T1."KETATSES", T7."NVENTIL", T7."NIMPUTA", T4."IDSTR"
from "INFO"."FASSUR" T3, "INFO"."V_UOGES_CSPENT" T8, "INFO"."V_UOSAL_CSPENT" T9, "FSESSION" T1, "FORGSESS" T2, 
"FPARSESS" T4, "INFO"."FAGENTS" T5, "TUOH" T6, "FCOUSAL" T7
where T1."KSESSION"=T2."KSESSION"(+) and T1."KSTAGE"=T2."KSTAGE"(+) and T1."KSESSION"=T4."KSESSION"(+) and T1."KSTAGE"=T4."KSTAGE"(+) 
and T4."KSOC"=T5."KSOC"(+) and T4."NMAT"=T5."NMAT"(+) and T4."KUOSAL"=T6."KUO"(+) and T4."KSESSION"=T7."KSESSION" and T4."KSTAGE"=T7."KSTAGE" 
and T4."NMAT"=T7."NMAT" and T1."EASSUR"=T3."EASSUR" and T1."KUOGES"=T8."KUOGES" and T4."KUOSAL"=T9."KUOSAL" 
and T1."DDEBSES">=to_date('1930-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and T1."DFINSES"<=to_date('2999-12-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS') 
and T1."KSTAGE" like '%' and T8."APERIMCSP"='CSP' and T9."APERIMCSP"='CSP'
order by T1."KUOGES" asc, T1."KSTAGE" asc, T1."KSESSION" asc

**beurtom** · 16/10/2006, 14h00

Je vous met aussi l'explain plain si cela peut vous aider à comprendre, merci:

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE
SORT ORDER BY
MERGE JOIN
SORT JOIN
MERGE JOIN
SORT JOIN
NESTED LOOPS OUTER
NESTED LOOPS OUTER
NESTED LOOPS OUTER
FILTER
NESTED LOOPS OUTER
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS FULL INFO.FCOUSAL
TABLE ACCESS BY INDEX ROWID INFO.FSESSION
INDEX RANGE SCAN INFO.FSESSION_I04
TABLE ACCESS BY INDEX ROWID INFO.FASSUR
INDEX UNIQUE SCAN INFO.PK_FASSUR
TABLE ACCESS BY INDEX ROWID INFO.FPARSESS
INDEX RANGE SCAN INFO.FPARSESS_I02
TABLE ACCESS BY INDEX ROWID INFO.TUOH
INDEX UNIQUE SCAN INFO.PK_TUOH
TABLE ACCESS BY INDEX ROWID INFO.FAGENTS
INDEX UNIQUE SCAN INFO.PK_FAGENTS
TABLE ACCESS BY INDEX ROWID INFO.FORGSESS
INDEX RANGE SCAN INFO.FORGSESS_I01
SORT JOIN
VIEW INFO.V_UOSAL_CSPENT
SORT UNIQUE
UNION-ALL
FILTER
TABLE ACCESS FULL INFO.FPARSESS
NESTED LOOPS
TABLE ACCESS FULL INFO.TUO_CSP
TABLE ACCESS BY INDEX ROWID INFO.TUOH
INDEX RANGE SCAN INFO.TUOH_I18
SORT JOIN
VIEW INFO.V_UOGES_CSPENT
SORT UNIQUE
UNION-ALL
FILTER
TABLE ACCESS FULL INFO.FSESSION
TABLE ACCESS FULL INFO.TUO_CSP
NESTED LOOPS
TABLE ACCESS FULL INFO.TUO_CSP
TABLE ACCESS BY INDEX ROWID INFO.TUO
INDEX RANGE SCAN INFO.TUO_I01

**orafrance** · 16/10/2006, 14h08

Pour la deuxiéme fois, merci de respecter les régles du forum.

Merci aux autres d'attendre que beurtom ait édité ses messages avant de répondre

**remi4444** · 16/10/2006, 14h16

- As tu passé les stats sur ton schéma (on ne voit rien apparaitre dans l'explain plan) ?

- Combien de lignes renvoi ta requêtes en gros ?

- Si il y a très peu de lignes renvoyées par rapport au lignes des plus grosses tables, quel est le critère (ou l'ensemble de critères) le plus discriminant ?

- Si ta requête s'est mise à ètre longue quand tu as rajouté des vues, il est très important d'avoir la définitions de ces vues en question.

**remi4444** · 16/10/2006, 14h16

Envoyé par Fred_D

Merci aux autres d'attendre que beurtom ait édité ses messages avant de répondre

Désolé j'ai répondu trop vite... mais promis ça se reproduira plus

**beurtom** · 16/10/2006, 14h34

Merci Rémi,

Je débute dans l'optimisation des requêtes, je ne sais pas comment passer les stats. Peux tu me donner cette info? J'utilise Toad si cela peut t'aider à me répondre.
Pour rappel la requête se lance sans erreur, c'est juste qu'elle met hyper longtemps pour me donner les résultats, j'ai attendu 3h et j'avais toujours pas de résultat.

Je te donne aussi le script de crétion des vues:

#1ère vue

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
 
CREATE VIEW V_UOGES_CSPENT 
AS 
(
	(
		select distinct 'ENTREPRISE' as APERIMCSP,T1.kuoges as KUOGES from FSESSION T1
	)
	union
	(
		select distinct 'CSP' as APERIMCSP , kuo as KUOGES from tuo_csp
	) 
 	union
	(
		select distinct 'CSP' as APERIMCSP, TA.kuo as KUOGES 
		from tuo TA ,tuo_csp TB
		where TA.kuomere = TB.kuo 
		--and TA.kuo like ('4%')
		--and TA.ksitu='ACT'
	)
);

#2ème vue

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
CREATE VIEW V_UOSAL_CSPENT 
AS 
(
	(select distinct 'ENTREPRISE' as APERIMCSP,T1.KUOSAL as KUOSAL from FPARSESS T1)
	union
 	(
 		select distinct 'CSP' as APERIMCSP, T1.KUO as KUOSAL from TUOH T1, TUO_CSP T2
  		where T1.KUO3=T2.KUO 
  		--and T1.KUO like'5%'	 	
 	)
);

**remi4444** · 16/10/2006, 14h40

J'ai bien une petite idée, mais j'ai pas le droit de te répondre avant que tu réédite ton post pour metre les balises CODE /CODE ......

**beurtom** · 16/10/2006, 14h45

C'est peut être ça que tu veux?

Merci Rémi,

Je débute dans l'optimisation des requêtes, je ne sais pas comment passer les stats. Peux tu me donner cette info? J'utilise Toad si cela peut t'aider à me répondre.
Pour rappel la requête se lance sans erreur, c'est juste qu'elle met hyper longtemps pour me donner les résultats, j'ai attendu 3h et j'avais toujours pas de résultat.

Je te donne aussi le script de crétion des vues:

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
 
 
CREATE VIEW V_UOGES_CSPENT 
AS 
(
	(
		select distinct 'ENTREPRISE' as APERIMCSP,T1.kuoges as KUOGES from FSESSION T1
	)
	union
	(
		select distinct 'CSP' as APERIMCSP , kuo as KUOGES from tuo_csp
	) 
 	union
	(
		select distinct 'CSP' as APERIMCSP, TA.kuo as KUOGES 
		from tuo TA ,tuo_csp TB
		where TA.kuomere = TB.kuo 
		--and TA.kuo like ('4%')
		--and TA.ksitu='ACT'
	)
);

2ème vue

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
CREATE VIEW V_UOSAL_CSPENT 
AS 
(
	(select distinct 'ENTREPRISE' as APERIMCSP,T1.KUOSAL as KUOSAL from FPARSESS T1)
	union
 	(
 		select distinct 'CSP' as APERIMCSP, T1.KUO as KUOSAL from TUOH T1, TUO_CSP T2
  		where T1.KUO3=T2.KUO 
  		--and T1.KUO like'5%'	 	
 	)
);

**orafrance** · 16/10/2006, 15h06

UNION fait déjà un DISTINCT alors c'est soit UNION soit DISTINCT mais pas les 2

Par ailleurs, pour V_UOSAL_CSPENT tu peux remplacer UNION par UNION ALL, les lignes du 1° select étant forcément distinct des lignes du 2° (ENTREPRISE pour les 1° et CSP pour la 2°

Je confirme ce que dit Rémi, il manque les stats sinon on aurait le coût de la requête. Pour plus d'info, merci d'utiliser le moteur de recherche dans le forum

PS : merci de lire ATTENTIVEMENT le post suivant : http://www.developpez.net/forums/showthread.php?t=28716

**remi4444** · 16/10/2006, 15h20

Tu peux aussi ré-éditer (et non rajouter) tes messages précédents en y mettant les balises codes...

L'optimisation est une discipline à part entière, il ne suffit malheureuselment pas de mettre un paramètre oracle du style "ALTER SESSION SET SLOW_REQUEST = FALSE" pour que ça roule...

Mais il faut comprendre le fonctionnement interne, l'implantation des données, le principe des index, les différentes formes de jointures, union etc...
Oracle cependant utilise un module tres puissant nommé "Cost Based Optimizer" qui est une forme "d'intelligence artificielle" en ce sens qu'il cherche tout seul le meilleur algorhitme d'accès au données.

http://download-uk.oracle.com/docs/c...a96533/toc.htm

Lorsqu'on lance une requête il y a plusieurs phases:

- Parsing de la requête.
- Recherche du meilleur algorhytme d'accès au données (l'algo s'appelle l'EXPLAIN PLAN ou PLAN D'EXECUTION)
- compilation de l'explain plan
- exécution de l'explain plan avec les constantes propres à la requête.

Le CBO intervient dans la 2ieme phase, cependant il n'y a pas de miracle, il faut qu'il ait des statitistiques sur ta répartitions des données pour qu'il puisse travailler correctement. On peut faire calculer à oracle des statistiques plus ou moins fines, pour ce faire, on utilise généralement le package DBMS_STATS.

http://download-uk.oracle.com/docs/c...s.htm#ARPLS059

Par exemple pour passer les stats de base sur un schéma:

Code :

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

1
2
3
4
 
 BEGIN
   DBMS_STATS.GATHER_SCHEMA_STATS ('TON_SCHEMA');
  END;

C'est la première chose à faire.

Si ça ne suffit pas, il faut alors plonger plus profond dans la résolution. J'ai un principe personnel pour la recherche, c'est que je me met à la place du CBO et j'essaye de savoir si je trouverais moi un algorhitme rapide et j'essaye de comprendre ensuite pourquoi le CBO s'est "trompé".

J'ai quand meme une remarque par rapport à la définition de tes vues, les "distinct" et les "union" (le premier étant redondant dans le second) sous-entendent un dédoublonnage, donc un tri, ce qui va forcement etre trés gourmand en ressources si les table impliquées sont grosses. Si c'est possible, il faut éviter les distinct et utiliser les "UNION ALL" plutot que les "UNION".

EDIT: j'ai croisé avec le message de Fred... désolé pour la redite...

**orafrance** · 16/10/2006, 15h32

les grands esprits se rencontrent

Bravo pour se message très détaillé

beurtom

regarde dans v$session_wait les attentes générées pendant l'exécution de ta requête aussi, tu peux éventuellement fait une trace de niveau 8.

**beurtom** · 16/10/2006, 16h50

désolé pour mes erreurs lors des posts de mes mess précédents, cela ne se reproduira plus.

J'ai lu quelques tuto et quelques sujets déjà traité sur l'optimisation pour me mettre un peu plus à niveau.
J'ai modifier mes vues en enlevant les distincts mais le "UNION ALL" pour ma vue V_UOSAL_CSPENT me donne beaucoup plus de ligne que le UNION; Du coup pour cette vue j'utilise le UNION.
Au niveau des stats n'ayant pas de résultats je ne peux vous les procurer. La table Explain plain n'étant pas rempli. Il en est de même pour la vue v$session_wait qui me donne pas d'informations utilisables. J'utilise avec toad l'onglet explain plain pour avoir le plan d'exécution de ma requête.
Pour info et pour rattraper mon erreur je suis une version Oracle8i.

Vous avez été très détaillé dans vos réponses, pouvez vous l'être encore car je suis débutant?

Merci

optimisation requête avec jointures externes

Oracle

Discussions similaires

Partager

Partager