pb performance requete avec différence dates

**marion782** · 06/12/2006, 09h06

Bonjour,
J'ai un problème de performance et je ne comprends pas pourquoi mais il est vrai que je ne suis pas une experte bdd alors je fais appel à vous.
J'ai créé une 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
create or replace view v_lignes_di as
(select id_da_ligne as id_da_ligne, da_ligne.id_da_entete as id_da_entete, 
  da_ligne.id_organisation_logistique as id_organisation_logistique, da_ligne.id_article as id_article,
  date_du_besoin as date_besoin, date_approbation as date_approbation, delai_appro as delai_appro,
  case when date_du_besoin-date_approbation < delai_appro then 'O' else 'N' end as flg_di_anticip
from
da_entete, da_ligne,
(select id_article, delai_appro, id_organisation_logistique
from article_secondaire
where id_article in(select distinct id_article
from da_entete, da_ligne
where DA_LIGNE.ID_DA_ENTETE=DA_ENTETE.ID_DA_ENTETE 
and DA_ENTETE.ID_TYPE_DOCUMENT = 19 )) temp
where 
DA_LIGNE.ID_DA_ENTETE=DA_ENTETE.ID_DA_ENTETE
and DA_ENTETE.ID_TYPE_DOCUMENT = 19
and da_ligne.id_article = temp.id_article 
and da_ligne.id_organisation_logistique = temp.id_organisation_logistique)

Quand je fais un select * je mets environ 113.5sec, si je rajoute une condition sur la date d'approbation, je mets 17.719s, tout cela est OK.
Le problème vient quand je mets une condition sur mon flag O/N

Code :

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

1
2
3
select * from v_lignes_di
where to_char(date_approbation,'MM/YYYY') = '05/2006'
and flg_di_anticip = 'N'

Ma requete n'en fini pas!!
Pourquoi??? Merci d'avance pour toute idée

**orafrance** · 06/12/2006, 09h15

sans plan d'exécution c'est impossible de répondre

**Marc Musette** · 06/12/2006, 10h57

pense à créer un functional index sur date_approbation voire un bitmap index sur ton flag

**orafrance** · 06/12/2006, 11h05

avant de créer de nouveaux indexes, je pense qu'il faut qu'on comprenne pourquoi l'ajout d'un critère pose tant de problème

**marion782** · 06/12/2006, 11h53

Je ne sais pas si cela peut vous aider mais ci joint l'explain plan que me fourni PL/SQL Developper... Merci d'avance

**orafrance** · 06/12/2006, 12h09

il n'y a pas d'index sur article_secondaire.article_id ?

et pourquoi tu fais :

Code :

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

1
2
3
4
5
6
7
8
9
 FROM da_entete,
       da_ligne,
       (SELECT id_article, delai_appro, id_organisation_logistique
          FROM article_secondaire
         WHERE id_article IN (
                  SELECT DISTINCT id_article
                             FROM da_entete, da_ligne
                            WHERE da_ligne.id_da_entete = da_entete.id_da_entete
                              AND da_entete.id_type_document = 19)) temp

et pas :

Code :

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

1
2
3
4
  FROM da_entete,
       da_ligne,
	   article_secondaire
 WHERE article_secondaire.id_article = da_entete.id_article

tout simplement

**McM** · 06/12/2006, 12h17

Tu peux nous donner les PK, Index et Volumétries de tes tables.
J'ai un sentiment bizarre sur la sous requete "temp"

Sinon, le CASE, tu peux le remplacer et voir ce que ça donne

Code :

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

CASE WHEN date_du_besoin - date_approbation < delai_appro THEN 'O' ELSE 'N' END AS flg_di_anticip

Code :

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

DECODE( SIGN(date_du_besoin - date_approbation - delai_appro), -1 , 'O', 'N') AS flg_di_anticip

**McM** · 06/12/2006, 12h28

Fred, t'as oublié une ligne

Code :

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

1
2
3
4
5
 FROM DA_ENTETE e,
     DA_LIGNE l,
	 ARTICLE_SECONDAIRE s
WHERE s.id_article = l.id_article
AND l.id_organisation_logistique = s.id_organisation_logistique

**marion782** · 06/12/2006, 13h47

Mes réponses dans l'ordre :
- il y a bien un index sur article_secondaire.article_id
- je suis passée par une table temp dans ma requete car la table article_secondaire contient + de 2300000 lignes! donc sans limiter mes articles secondaires ma requete était impossible (à se finir)
- da_entete : pk : id_da_entete ; 11239 lignes
- da_ligne : pk : id_da_ligne ; 76720 lignes
- article_secondaire : pk id_article_secondaire ; 2308949 lignes

Je veux bien essayer avec le decode mais je ne pense que cela resoude mon pb de la requete de selection des lignes à Oui...

Merci

**Marc Musette** · 06/12/2006, 14h10

si tu regardes l'explain plan, tu vois qu'il fait un full table scan sur ARTICLE_SECONDAIRE

peux tu refaire un calcul de tes stats ? elles semblent ne pas être à jour

aussi je crois que ce n'est pas une bonne idée de mettre un IN supplémentaire ; autant optimiser tout de suite la requête définitive ...

**marion782** · 06/12/2006, 14h23

je viens de modifier ma requete (suppression du IN), elle repond plus vite quand je fait un select * mais toujours pas de réponse quand j'ajoute mon test de compraison entre les 2 dates et le délai (ou quand je passe par la vue et mon "where flg_di_anticip = 'O'")

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
create or replace view v_lignes_di as(
select id_da_ligne, temp.id_organisation_logistique, temp.id_article,
  date_du_besoin, date_approbation, delai_appro, 
  decode(sign(date_du_besoin - date_approbation - delai_appro),-1,'O','N') as flg_di_anticip 
from
(select id_da_ligne, date_approbation, date_du_besoin, id_article, id_organisation_logistique
from da_entete, da_ligne
where DA_LIGNE.ID_DA_ENTETE=DA_ENTETE.ID_DA_ENTETE 
and DA_ENTETE.ID_TYPE_DOCUMENT = 19 )temp , 
article_secondaire 
where article_secondaire.id_article = temp.id_article
and article_secondaire.id_organisation_logistique = temp.id_organisation_logistique)

**Marc Musette** · 06/12/2006, 14h31

donne nous l'explain plan de ta requ^te modifiée

est que tu peux recalculer les stats ?

**orafrance** · 06/12/2006, 14h53

Envoyé par McM

Fred, t'as oublié une ligne

Code :

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

1
2
3
4
5
 FROM DA_ENTETE e,
     DA_LIGNE l,
	 ARTICLE_SECONDAIRE s
WHERE s.id_article = l.id_article
AND l.id_organisation_logistique = s.id_organisation_logistique

c'était un extrait parce que toi aussi t'as oublié des lignes

**orafrance** · 06/12/2006, 14h55

Envoyé par marion782

- je suis passée par une table temp dans ma requete car la table article_secondaire contient + de 2300000 lignes! donc sans limiter mes articles secondaires ma requete était impossible (à se finir)

c'est une très mauvaise idée puisque tu fais 2 fois appel aux 2 autres tables. Tu n'as pas dû comprendre pourquoi tu avais ces problèmes de temps de réponse.

D'ailleurs si on lit :

Code :

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

and da_ligne.id_article = temp.id_article

tu vois qu'en fait ça revient à

Code :

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

and da_ligne.id_article = da_ligne.id_article

Vérifie les stats de tes indexes, je ne me rappelle pas que tu as donné la version d'Oracle non plus

**orafrance** · 06/12/2006, 14h58

j'aimerai bien voir l'explain plan de ça :

Code :

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

1
2
3
4
5
SELECT id_article, delai_appro, id_organisation_logistique
  FROM article_secondaire, da_entete, da_ligne
 WHERE article_secondaire.id_article = da_ligne.id_article
   AND da_ligne.id_da_entete = da_entete.id_da_entete
   AND da_entete.id_type_document = 19

en espérant évidemment qu'on à des indexes sur les colonnes da_entete.id_type_document, da_ligne.id_da_entete et article_secondaire.id_article

**marion782** · 06/12/2006, 15h07

voici l'explain plain pour la requete que tu m'as donné.
Toutefois attention car tu n'as pas fait la jointure entre da_ligne et article_secondaire. (cette jointure est impérative pour récupérer le bon délai d'appro car dans article_secondaire un même id_article peut être associé à plusieurs id_organisation_logistique et donc plusieurs délai_appro différents)

**marion782** · 06/12/2006, 15h13

j'ai oublié, j'utilise oracle 8 en dev (mais je crois que cela sera en prod avec oracle 9)

**orafrance** · 06/12/2006, 15h31

Envoyé par marion782

voici l'explain plain pour la requete que tu m'as donné.
Toutefois attention car tu n'as pas fait la jointure entre da_ligne et article_secondaire. (cette jointure est impérative pour récupérer le bon délai d'appro car dans article_secondaire un même id_article peut être associé à plusieurs id_organisation_logistique et donc plusieurs délai_appro différents)

c'était bien la requête de la vue que je donnais... ce qui donne à la place de :

Code :

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

SELECT * FROM v_lignes_di WHERE to_char(date_approbation,'MM/YYYY') = '05/2006' AND flg_di_anticip = 'N'

ceci :

Code :

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

1
2
3
4
5
6
7
SELECT id_article, delai_appro, id_organisation_logistique
  FROM article_secondaire, da_entete, da_ligne
 WHERE article_secondaire.id_article = da_ligne.id_article
   AND da_ligne.id_da_entete = da_entete.id_da_entete
   AND da_entete.id_type_document = 19
   AND TO_CHAR (date_approbation, 'MM/YYYY') = '05/2006'
   AND flg_di_anticip = 'N'

je ne vois pas de lien entre ligne et article dans la requête d'origine

Le plan d'exécution parait très étrange... t'a pas d'index sur da_ligne.id_da_entete ? Les stats sont à jour ?

A part ça, c'est pas très malin d'avoir un décalage de version entre dév et prod

**McM** · 07/12/2006, 13h53

Décidemment Fred, tu l'aimes pas cette ligne :

SELECT id_article, delai_appro, id_organisation_logistique
FROM article_secondaire, da_entete, da_ligne
WHERE article_secondaire.id_article = da_ligne.id_article
AND da_ligne.id_da_entete = da_entete.id_da_entete
AND da_entete.id_type_document = 19
AND TO_CHAR (date_approbation, 'MM/YYYY') = '05/2006'
AND flg_di_anticip = 'N'
AND da_ligne.id_organisation_logistique = article_secondaire.id_organisation_logistique

Bon, ça donne quoi l'EXPLAIN de cette requete ?