Update qui prend plus de 7h ! [Résolu]

[Battosaiii]

Bonjour,

J'ai une base de données RADIOLOGIE avec 565 000 données.
La base temporaire rados_mediweb contient 107 000 données.

Il y a un index sur la table rados_mediweb

Voila ma requete SQL :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
 
UPDATE radiologie r
		SET r.id_demande =(SELECT m.remissnr
							FROM rados_mediweb m
							WHERE r.id_demande2 = m.remissnr
							AND r.type_examen = m.undtyp
							AND r.date_prescription = m.date_presc)			 
		WHERE EXISTS (  SELECT m.remissnr
						FROM rados_mediweb m
						WHERE  r.id_demande2 = m.remissnr					 
						AND r.type_examen = m.undtyp
						AND r.date_prescription = m.date_presc);

Voici l'explan plan de cette requete sql :

Operation Object Name

UPDATE STATEMENT Optimizer Mode=CHOOSE
UPDATE MEDIWEB_OPE.RADIOLOGIE
FILTER
TABLE ACCESS FULL MEDIWEB_OPE.RADIOLOGIE
TABLE ACCESS FULL MEDIWEB_OPE.RADOS_MEDIWEB
TABLE ACCESS FULL MEDIWEB_OPE.RADOS_MEDIWEB

Comment puis je améliorer la requete SQL ?

Merci

[Rei Ichido]

Code :

1
2
3
4
5
6
7
8
9
 
MERGE INTO radiologie r
    USING rados_mediweb m
      ON (       r.id_demande2 = m.remissnr
	    AND r.type_examen = m.undtyp
	    AND r.date_prescription = m.date_presc
          )
   WHEN MATCHED THEN 
            UPDATE SET r.id_demande = m.remissnr

[Waldar]

Et vous noterez que cette solution vous a été proposée il y a presque deux mois :
http://www.developpez.net/forums/d11...te-update-sql/

[mnitu]

Déjà ajouter un index sur (id_demande2,type_examen, date_prescription) pourrait aider.

[Mohamed.Houri]

Vous n'êtes pas dans la bonne direction. Selon mon humble avis, il faut d'abord prendre les bonnes habitudes de diagnostique des problèmes de performance. L'un de nous devrait écrire un article dans lequel sera orienté tout intervenant confronté à un problème de performance. Je pense à particulièrement ce genre d’article

http://forums.oracle.com/forums/thre...63295&tstart=0
http://forums.oracle.com/forums/thre...812597#1812597

Pour en revenir à votre question, que signifie pour nous autres lecteurs de votre question ceci:

Il y a un index sur la table rados_mediweb

Quelle est la définition de cet index ?
Vous semblez avoir la conclusion que les responsables des 7 heures de votre update sont les selects sur la table radio_mediweb ? Avez-vous une preuve où des indications comme des traces files confirmant ceci? Est-ce que la table radiologie possède des triggers qui eux, peut-être, font un traitement lourd ? Est-ce que la table radiologie possède des contraintes d’intégrités nécessitant des vérifications avant update. Est-ce que la table radiologie possède des indexes ? Est-ce que cet update est fait pendant un temps où la base de données est très active (la table radio_mediweb est en train de bouger pendant l’update).

Activez les traces (10046 events) et regardez qui consomme le plus de temps dans votre update

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
ALTER session SET events '10046 trace name context forever, level 12';
UPDATE radiologie r
		SET r.id_demande =(SELECT m.remissnr
				FROM rados_mediweb m
							WHERE r.id_demande2 = m.remissnr
							AND r.type_examen = m.undtyp
							AND r.date_prescription = m.date_presc)			 
		WHERE EXISTS (  SELECT m.remissnr
						FROM rados_mediweb m
						WHERE  r.id_demande2 = m.remissnr					 
						AND r.type_examen = m.undtyp
						AND r.date_prescription = m.date_presc);
 
ALTER session SET events '10046 trace name context off';

20-30 minutes de trace pourraient déjà vous donner une indication

[Waldar]

J'ajouterai que la requête est de plus mal écrite.
Je ne l'avais pas vu hier, mais à mon avis on peut l'écrire ainsi :

Code :

1
2
3
4
5
6
7
UPDATE radiologie r
   SET r.id_demande = r.id_demande2             
 WHERE EXISTS (SELECT NULL
                 FROM rados_mediweb m
                WHERE m.remissnr   = r.id_demande2                     
                  AND m.undtyp     = r.type_examen 
                  AND m.date_presc = r.date_prescription);

[mnitu]

Envoyé par Waldar

J'ajouterai que la requête est de plus mal écrite.

Comme d’habitude bien analyser la requête avant de lancer la grosse artillerie c’est imbattable.

[orafrance]

Envoyé par Waldar

[code]
SET r.id_demande = r.id_demande2

Bien vu

[Battosaiii]

Merci de vos réponses,

En fait j'avais déjà le même index que mnitu préconise.

De plus je dois faire 2 updates sur 2 champs. Je ne peux pas faire comme waldar préconise.

Voila la requete finale que je dois améliorer :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
 
 
UPDATE radiologie r
		SET (r.id_demande, r.id_examen) =(SELECT m.remissnr, m.lopnr
							FROM rados_mediweb m
							WHERE r.id_demande2 = m.remissnr
							AND r.type_examen = m.undtyp
							AND r.date_prescription = m.date_presc)			 
		WHERE EXISTS (  SELECT 1
						FROM rados_mediweb m
						WHERE  r.id_demande2 = m.remissnr					 
						AND r.type_examen = m.undtyp
						AND r.date_prescription = m.date_presc);

A cause de r.id_examen à updater avec m.lopnr je ne peux pas faire l' update simplifiée de waldar.


J'ai trouvé un article sur l'optimisation des requetes sql.
http://sqlpro.developpez.com/cours/optimiser/

Cet article préconise d'utiliser "join on" plutot que la jointure sur "where". J'obtiens donc cette requete suivante :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
 
		UPDATE radiologie r
		SET (r.id_demande, r.id_examen) =(SELECT m.remissnr, m.lopnr
							FROM rados_mediweb m
							LEFT JOIN radiologie r ON (r.id_demande2 = m.remissnr
							AND r.type_examen = m.undtyp
							AND r.date_prescription = m.date_presc))			 
		WHERE EXISTS (  SELECT 1
						FROM rados_mediweb m
						LEFT JOIN radiologie r ON (r.id_demande2 = m.remissnr					 
						AND r.type_examen = m.undtyp
						AND r.date_prescription = m.date_presc));

D'apres l explain plan cette requete semble encore + consommatrice que la requete au dessus! Mais je ne sais pas trop analyser explain plan donc j'ai peut etre tord :

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

UPDATE STATEMENT Optimizer Mode=CHOOSE 2 2
UPDATE RADIOLOGIE
FILTER
TABLE ACCESS FULL N.RADIOLOGIE 2 18 2
HASH JOIN OUTER 82 3 K 5
TABLE ACCESS FULL N.RADOS_MEDIWEB 82 1 K 2
TABLE ACCESS FULL N.RADIOLOGIE 2 44 2
HASH JOIN OUTER 82 3 K 5
TABLE ACCESS FULL N.RADOS_MEDIWEB 82 2 K 2
TABLE ACCESS FULL N.RADIOLOGIE 2 44 2

Avant de tester ma requete sur ma base de test je prefere faire des optimisations. En effet les tests sont tres longs .

[Waldar]

Envoyé par Battosaiii

Voila la requete finale que je dois améliorer

Erf... à éviter.

Envoyé par Battosaiii

J'ai trouvé un article sur l'optimisation des requetes sql.
http://sqlpro.developpez.com/cours/optimiser/

Cet article préconise d'utiliser "join on" plutot que la jointure sur "where".

C'est la simple implémentation de la norme SQL:92, rien de plus, et ça n'a strictement rien à voir avec votre problème.
La meilleure solution à implémenter, c'est celle de la première réponse.

[mnitu]

Envoyé par Battosaiii

...J'ai trouvé un article sur l'optimisation des requetes sql.
http://sqlpro.developpez.com/cours/optimiser/

....

Le problème avec son article est qu'il ne précise pas le SGBD et qu'il oublie de exposer ses tests pouvant démontrer ses affirmations.
Donc avec Oracle oubliez-le.

[Battosaiii]

Merci waldar de ta réponse rapide !

EN fait j'ai une erreur "ORA-00905: Mot-clé absent" lorsque j’exécute la solution avec merge. J'ai regardé si il ne manquait pas un espace ou autre mais cela me semble ok :

Code :

1
2
3
4
5
6
7
 
MERGE 
	INTO radiologie r
    USING rados_mediweb m
      ON ( r.id_demande2 = m.remissnr AND r.type_examen = m.undtyp AND r.date_prescription = m.date_presc )
WHEN MATCHED THEN
    UPDATE SET r.id_demande = m.remissnr

Peut être je ne peux pas éxécuter MERGE sur Oracle9i Enterprise Edition Release 9.2.0.6.0 ?

En faite il me semble qu'on utilise des bases 8i sur nos serveurs en production. C'est pourquoi je ne peux pas utiliser MERGE malheureusement !

[mnitu]

Merge en Oracle 9 impose la présence de 2 clauses
When Matched...
When Not Matched ...

[Battosaiii]

En faite il me semble qu'on utilise des bases 8i sur nos serveurs en production. Or Merge est supporté uniquement à partir de la serveur 9i. C'est pourquoi je ne peux pas utiliser MERGE malheureusement !

[mnitu]

Alors pour l’instant faite l’update corrélé. Avec les bons indexes et vu la volumétrie que vous avez donné ça ne peut pas dure 7H.
Si c’est le cas fait une trace comme Mohammed Houri a indiqué.