Discussion :

[android59]

Bonjour,

j'ai une grosse requête qui génère environ 400000 resultats ( et qui augmente chaque mois, bref) et je me demandais si c'était possible d'optimiser ce genre de requête, afin qu'elle aille plus rapidement , voici 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
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
 
SELECT distinct identifiant as MATRICULE,
nom as NOM,
prenom as PRENOM,
datnais as DATE_NAISSANCE,
lieu as LIEU_NAISSANCE,
RUE  as RUE,
champs,
champs,
champs,,
champs,
champs,
champs,
champs,
champs,
champs,
champs,
champs,
champs,
champs,
champs,
champs,
champs,
champs,
champs,
champs,
champs,
champs,
champs,
champs,
champs,
champs,
champs,
champs,
champs,
champs,
champs,
champs,
champs,
champs,
champs,
champs,
champs,
champs,
champs
FROM table1 t1
inner join .......
inner join .......
inner join .......
inner join .......
left outer join ....... and champs = to_date('2019-01-31','YYYY-MM-DD')
left outer join ....... and champs = to_date('2019-01-31','YYYY-MM-DD')
left outer join .......
left outer join .......
where condition= 0003;

Car ma requête je l'ai lancé dans une base mais ça fais 2h qu'elle tourne et j'ai aucun résultat, comment ça se fait ?.

Merci pour vos conseils

[JeanYvette]

Bonjour bonjour,

Tout d'abord, il est bien de penser à l'anonymisation du code, mais pas trop non plus. Ce que je veux dire ici, c'est que l'on a même pas le contenue de ton "ON" sur les jointures. Tu peux nous mettre tes jointures en remplaçant par "macolonneA" "macolonneB" ? (à part si il n'y a pas de traitement sur les colonnes style substr et tout le reste)...

Ensuite, il nous faudrait surtout le plan d'exécution de ta requête, voir si les index sont correctement utilisés, si le parcours de tes tables n'est pas trop couteux ou fait bizarre.

Globalement, si ça augmente avec le temps, il y a moyen que le problème soit "tout simplement" une grande augmentation de la quantité des données et là il faudrait potentiellement soit purger, soit historisé.

En tout cas, à l'heure actuelle, je ne suis pas sûr que l'on dispose d'assez d'information pour pouvoir t'aider entièrement

Bisous bisous

[al1_24]

Ajouter des index sur les conditions de jointure ne fait jamais de mal...

[McM]

Un distinct qui ramène 400000 lignes et je ne sais combien de colonnes .. le TEMP va exploser
Vaut revoir la requête étape par étape pour voir ce qui est optimisable.

[android59]

Merci pour vos retour,
pour être concret , voici un autre exemple :

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
36
 
SELECT distinct t1.identifiantpersonne as idpersonne,
T5.nom as NOM,
t5.prenom as PRENOM,
t5.x530_datnais as DATE_NAISSANCE,
t5.lieunais as LIEU_NAISSANCE,
t6.RUE as RUE,
t6.cpost, as CODE_POSTAL,
ville as VILLE,
PAYS as PAYS,
t14.champs1 as champs1,
t14.champs2 as champs2,
to_char(t2.datedebu,'dd/mm/yyyy') as debut,
to_char(t2.datefin,'dd/mm/yyyy') as fin,
t3.identifiant as ENTREPRISE,
t4.raisonsociale as RAISON_SOCIALE,
NVL(t11.champs3,' ') as champs3,
NVL(t11.champs4,' ') as champs4,
NVL(t12.mail,' ') as ADRESSE_ELECTRONIQUE,
NVL(t2.champs5,' ') as champs5,
NVL(fonction1(-1, t2.id, 'reference1'),'INDEFINI') as champs,
NVL(fonction1(-1, t2.id, 'reference2'),' ') as champs,
.....
.....
..... //21 fonction en plus, idem a NVL(fonction1(-1, t2.id, 'reference2'),' ') as champs, en changement le nom du champs
.....
FROM table1 t1
inner join tableRelation t2 on t2.idref_2=t1.id and t2.idrefAutretable=4
inner join table1 t3 on t3.id=t2.idref_1
inner join societe t4 on t4.idrefPersonne=t3.idrefpersonne
inner join table5 t5 on t5.idrefpersonne=t1.idrefpersonne
left outer join table6 t6 on t1.idrefpersonne=t6.idrefpersonne and t6.datefin = to_date('2019-01-31','YYYY-MM-DD')
left outer join table11 t11 on t1.idrefpersonne=t11.idrefpersonne and t11.datefin = to_date('2019-01-31','YYYY-MM-DD')
left outer join coordonneselectronique t12 on t12.idrefpersonne=t1.idrefpersonne
left outer join Personne t14 on t14.id=t1.idrefPersonne
where t1.identifiant=444;

cela fais 4h que j'ai lancé ma requete, pourtant auparavant ça n'a jamais fait ça, habituellement ça met 10mn max, mais en fait je suis sur un e nouvelle base de données, y aurait il un rapport ?

J'ai pas très bien saisie quand on parle de purger ? ou d'historiser?
Y aurais t-il un rapport entre la purge , l'historisation et le temps trop long?
Merci

[android59]

J'ai fais le plan d'execution , mais le hic je ne le comprend pas

[McM]

Nouvelle base de données ? Est-ce que les stats sont à jour ? Est-ce que les structures des tables (colonnes, index, etc...) sont les mêmes ?
Est-ce que la bdd est la même, avec la même configuration ?
Ca peut aussi venir de l'OS (I/O des disques, etc..)..

Voir l'explain plan de la requête.

[Mohamed.Houri]

Sans plan d’exécution ni un rapport SQL monitor on ne peut que donner des pistes qui semblent être bonnes à suivre. Par exemple vous appelez plusieurs fonctions en utilisant le paramètre t2.id puis vous appliquez un distinct sur le résultat.
Il serait peut-être judicieux de n’invoquer les fonctions que pour les t2.id distincts. Pour cela, peut-être que vous feriez mieux de sortir les fonctions du distinct et de les appeler uniquement après avoir éliminé tous les doublons.

Et pour, éventuellement bénéficier de l’effet ‘’scalar subquery cache’’, englobez les appels aux fonctions autour d’un SELECT FROM DUAL.

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
SELECT  
  idpersonne,
  NOM,
  ../..
  (SELECT NVL(fonction1(-1, t2.id, 'reference1'),'INDEFINI') FROM DUAL) as champs,
  (SELECT NVL(fonction1(-1, t2.id, 'reference2'),' ') FROM DUAL)      as champs
  ../..
FROM 
(
SELECT 
   DISTINCT 
		t1.identifiantpersonne 	as idpersonne,
		T5.nom 					as NOM,
		t5.prenom 				as PRENOM,
		t5.x530_datnais 		as DATE_NAISSANCE,
		t5.lieunais 			as LIEU_NAISSANCE,
		t6.RUE 					as RUE,
		t6.cpost			    as CODE_POSTAL,
 
		NVL(t11.champs3,' ') as champs3,
		NVL(t11.champs4,' ') as champs4,
		NVL(t12.mail,' ') as ADRESSE_ELECTRONIQUE,
		NVL(t2.champs5,' ') as champs5,
 
../..
		FROM table1 t1
		inner join tableRelation t2 on t2.idref_2=t1.id and t2.idrefAutretable=4
		inner join table1 t3 on t3.id=t2.idref_1
		inner join societe t4 on t4.idrefPersonne=t3.idrefpersonne
		inner join table5 t5 on t5.idrefpersonne=t1.idrefpersonne
		left outer join table6 t6 on t1.idrefpersonne=t6.idrefpersonne and 
		where t1.identifiant=444
		);

Bien Cordialement
Mohamed Houri

[JeanYvette]

Après il y a toujours la méthode de "fourmis", afficher dans l'ancienne base tous les index que vous avez sur les tables de la requêtes. Faire de même pour la nouvelle base, et y intégrer les index manquants si il y en a ...

[tbc92]

En général, quand j'ai ce genre de problème, j'avance étape par étape.
J'exécute chacune des requêtes ci-dessous, pour voir à quel moment ça dérape :

Code :

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

1
2
3
4
5
 
SELECT 
*
FROM table1 t1
where t1.identifiant=444;

Code :

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

1
2
3
4
5
6
 
SELECT 
*
FROM table1 t1
inner join tableRelation t2 on t2.idref_2=t1.id and t2.idrefAutretable=4
where t1.identifiant=444;

Code :

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

1
2
3
4
5
6
7
 
SELECT 
*
FROM table1 t1
inner join tableRelation t2 on t2.idref_2=t1.id and t2.idrefAutretable=4
inner join table1 t3 on t3.id=t2.idref_1
where t1.identifiant=444;

Code :

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

1
2
3
4
5
6
7
8
 
SELECT 
*
FROM table1 t1
inner join tableRelation t2 on t2.idref_2=t1.id and t2.idrefAutretable=4
inner join table1 t3 on t3.id=t2.idref_1
inner join societe t4 on t4.idrefPersonne=t3.idrefpersonne
where t1.identifiant=444;

etc...
A chaque étape j'ajoute une des tables.

Par ailleurs, et ici, il faut peut-être commencer par ça, quand je vois des requêtes avec la clause 'DISTINCT' et qui ont des problèmes de performances, j'ai très souvent constaté que le DISTINCT était là pour 'masquer' un autre problème. Le type a fait la requête, il y avait des lignes en doubles, le programmeur a donc ajouté un mot DISTINCT. Alors que le vrai remède était peut-être de rajouter une clause on table2.id_periode=table1.id_periode .

Vérifie pourquoi tu as ce DISTINCT, c'est toujours suspect d'utiliser DISTINCT.

[Ikebukuro]

Est-ce que ta boite a payé le Diagnostic Pack et le Tuning Pack?
Si oui, lance le SQL Tuning Advisor et le SQL Access Advisor pendant 30 minutes à chaque fois, tu devrais avoir (peut-être) un meilleur plan d'exécution.

[pachot]

j'ai une grosse requête qui génère environ 400000 resultats

1. Dans quel but est exécutée cette requête? 400000, ce n'est ni pour un écran, ni pour une imprimante. C'est une extraction pour un fichier?
2. Un SELECT DISTINCT est toujours louche. Si on fait une aggregation, on a souvent besoin de mesures en face des dimensions et alors on fait un group by. L'ajout d'un DISTINCT ici voudrait dire qu'on fait des jointures one-to-many mais qu'on a pas vraiement besoin des informations qui viennent de cette table.

Il faudrait d'abord vérifier s'il n'y a pas une erreur de logique dans la requête.
Ensuite voir le plan d'exécution. Vu le nombre de lignes retournées On peut s'attendre a avoir des Hash Join entre des petites tables et une grosse. S'il y a plusieurs grosses tables, il sera interssant qu'elle soient partitionnées sur la clé de jointure.

[android59]

Bonjour,

en fait c'est une requête toute basique, si je peux m'exprimer ainsi, seulement je sélectionne une vingtaine de champs, dont la plupart d'entre elle fait appel à une fonction, pour le reste on a des jointures basiques 4/5 jointures c'est pas énorme et une condition.

D'une manière général, il sélectionne une liste de personne avec toutes les informations qui les caractérise : nom, prenom, adresse, etc.. et il nous concocte un fichier excel de 400 000 lignes .

Le distinct sert à ce que nous ayons pas de doublon, par exemple une personne peut avoir plusieurs contrat ( date de debut et de fin différente) donc elle peut apparaitre 3 fois dans le fichier mais avec des informations différentes.
Par contre le distinct nous permet d'enlever les lignes en double.

Je précise que c'est une requête qui a été établie longtemps avant mon arrivé et j'ai repris le flambeau depuis .

Voila un peu pour les explications

[android59]

Est-ce que ta boite a payé le Diagnostic Pack et le Tuning Pack?
Si oui, lance le SQL Tuning Advisor et le SQL Access Advisor pendant 30 minutes à chaque fois, tu devrais avoir (peut-être) un meilleur plan d'exécution.

pas à ma connaissance et après vérification je dirais que non .

Je sais pu si je l'ai précisé mais en quelques sorte je suis ce qu'il se rapprocherais le plus d'un dba dans la boite et je tend à le devenir officiellement d'ailleur, maisp our l'instant je suis dba, developpeur d'application ( parcours initiale) et je me débrouille en réseau

[tbc92]

Le distinct est bien une des causes de lenteur dans ton cas.

En gros, faisons une hypothèse :

SELECT * FROM table1 t1 where t1.identifiant=444 --> 100 000 lignes.

SELECT * FROM table1 t1 inner join tableRelation t2 on t2.idref_2=t1.id and t2.idrefAutretable=4 where t1.identifiant=444; --> 2 Millions de lignes (hypothèse..)

Pour chacune des 2 Millions de lignes, on va aller chercher les autres informations dans les autres tables (nom, adresse ... ) Donc 2 Millions de recherches.
Je pars du principe que ces recherches ne changent pas le nombre de lignes, on va chercher le nom, ou l'adresse, et on trouve une seule ligne à chaque fois.
Et ensuite seulement, au tout dernier moment, Oracle fait le Distinct.

Organise ta requête différemment :

Code :

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

1
2
3
4
5
6
With x1 as (
SELECT  Distinct t1.aaa , t1.bbb, t2.ccc, t3.ddd 
 FROM table1 t1 inner join tableRelation t2 on t2.idref_2=t1.id and t2.idrefAutretable=4  where t1.identifiant=444
)
select * from X1
inner join table1 t3 on t3.id=X1.idref_1;

Ainsi, Oracle ira chercher les informations dans les autres tables uniquement 400 000 fois, au lieu de 2 millions de fois.
Idem, tu as une fonction fonction1() qui est évaluée 2 millions de fois... alors que 400 000 fois devraient normalement suffire.

A adapter bien sûr pour ton cas.

Je suis aussi très sceptique sur le fait d'utiliser une fonction dans ce type de requête. Mais, d'une part, je ne suis pas suffisament pointu, d'autre part, il faudrait être en mesure de proposer une solution différente

[Scriuiw]

Le distinct sert à ce que nous ayons pas de doublon, par exemple une personne peut avoir plusieurs contrat ( date de debut et de fin différente) donc elle peut apparaitre 3 fois dans le fichier mais avec des informations différentes.
Par contre le distinct nous permet d'enlever les lignes en double.

Je rejoins l'avis des autres personnes du forum.
Essayez de vous passer de la clause DISTINCT a tout prix.

Dans le cas des contrats, faite en sorte de sélectionner le dernier contrat par exemple ou alors de brider le résultat avec un ROWNUM=1 dans une sous-requête si les informations ne vous servent pas (mais dans ce cas pourquoi joindre la table...)

[pachot]

S'il n'y a pas moyen d'éviter le DISTINCT parce que très mauvais design des tables, alors il faut s'assurer qu'il ait assez de mémoire pour ne pas passer plusieurs fois sur disque: Parallel Query et/ou manual workarea policy avec un sort_area_size assez grand.