Update qui prend plus de 7h !

Battosaiii · 12/09/2011, 17h49

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 :

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

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** · 12/09/2011, 18h16

Code :

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

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** · 12/09/2011, 18h56

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** · 13/09/2011, 09h19

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

**Mohamed.Houri** · 13/09/2011, 10h10

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 :

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

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** · 13/09/2011, 12h26

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 :

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

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** · 13/09/2011, 14h00

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** · 13/09/2011, 14h17

Envoyé par Waldar

[code]
SET r.id_demande = r.id_demande2

Bien vu

Battosaiii · 13/09/2011, 16h08

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 :

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

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 :

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

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** · 13/09/2011, 16h14

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** · 13/09/2011, 16h31

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 · 13/09/2011, 16h32

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 :

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

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** · 13/09/2011, 16h34

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

Battosaiii · 13/09/2011, 16h35

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** · 13/09/2011, 16h44

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é.

Update qui prend plus de 7h !

SQL Oracle

Discussions similaires

Partager

Partager