Problème lenteur d'update

**Nico02** · 22/07/2013, 11h51

Bonjour à tous,

J'ai un petit soucis concernant un UPDATE et je ne sais pas trop pourquoi.

Suite à une mise à jour de la base, j'ai certaines infos qui n'ont pas étaient rapatriées.
Le code suivant est récupère des status dans 2 tables et les réaffecte dans une 3eme dans le cas où ce status n'existe pas.

J'ai donc 2 curseurs qui font mes select et je fais un update dessus.

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
DECLARE
  CURSOR c_update_wo IS
    SELECT wowo_user_status FROM t_work_order, t_job_request WHERE wowo_code = jrjr_work_order 
    AND jrjr_wo_user_status IS NULL 
    AND wowo_user_status IS NOT NULL;
 
  CURSOR c_update_h_wo IS
    SELECT hwowo_user_status FROM t_h_work_order, t_job_request WHERE hwowo_code = jrjr_work_order  
    AND jrjr_wo_user_status IS NULL 
    AND hwowo_user_status IS NOT NULL;
 
  status CHAR;
 
BEGIN
  OPEN c_update_wo;
  LOOP   
    FETCH c_update_wo INTO status;
    EXIT WHEN c_update_wo%NOTFOUND;
    UPDATE t_job_request SET jrjr_wo_user_status = status;
  END LOOP;
  CLOSE c_update_wo;
 
  OPEN c_update_h_wo;
  LOOP
    FETCH c_update_h_wo INTO status;
    EXIT WHEN c_update_wo%NOTFOUND;
    UPDATE t_job_request SET jrjr_wo_user_status = status;
  END LOOP;
  CLOSE c_update_h_wo;
END;

Le problème c'est que c'est TRES LONG !

Les 2 select mettent à peine plus de 1s en tout (pour ~10,000 enregistrements) quand je les exécute séparément, alors que le block Pl/SQL n'en finit pas..

N’étant pas un spécialiste SQL j'aimerai donc comprendre pourquoi cette différence et si cela vient de mon code..

Cdt.

**Nico02** · 22/07/2013, 12h01

Je me réponds tout seul, j'ai oublié de mettre le WHERE dans le update donc il mettait tout à jour

Code :

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

UPDATE t_job_request SET jrjr_wo_user_status = req.wowo_user_status WHERE jrjr_work_order = req.wowo_code;

Méaculpa

**al1_24** · 22/07/2013, 12h26

Tu n'es pas obligé de passer par un curseur pour ce type de mise à jour.

Tu peux utiliser un UPDATE ensembliste :

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
UPDATE  t_job_request   jrq
SET     jrq.jrjr_wo_user_status 
        =   (   SELECT  wowo_user_status
                FROM    t_work_order    wor
                WHERE   wor.wowo_code = jrq.jrjr_work_order
                    AND wor.wowo_user_status IS NOT NULL
            )
WHERE   jrq.jrjr_wo_user_status IS NULL
    AND EXISTS
        (   SELECT  NULL
            FROM    t_work_order    wor
            WHERE   wor.wowo_code = jrq.jrjr_work_order
                AND wor.wowo_user_status IS NOT NULL
        )
;

(Attention à ne pas oublier le EXISTS, sinon les lignes de jrq qui n'ont pas dé correspondance sont mises à jour aussi, avec la valeur NULL

)

Ou, plus lisible et souvent plus efficace, une commande MERGE :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
MERGE INTO  t_job_request   tgt
USING   (   SELECT  jrq.jrjr_work_order
                ,   wor.wowo_user_status 
            FROM    t_work_order    wor
                INNER JOIN
                    t_job_request   jrq 
                    ON  wor.wowo_code = jrq.jrjr_work_order 
            WHERE   jrq.jrjr_wo_user_status IS NULL 
                AND wor.wowo_user_status IS NOT NULL
        )   src
    ON  (   tgt.jrjr_work_order = src.jrjr_work_order   )
WHEN MATCHED THEN UPDATE
    SET tgt.jrjr_wo_user_status = src.jrjr_wo_user_status
;

**Waldar** · 22/07/2013, 14h23

On ne pourrait pas réduire le merge comme ceci :

Code :

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

1
2
3
4
5
6
7
 MERGE INTO t_job_request tgt
 USING t_work_order       src
    ON (tgt.jrjr_work_order = src.wowo_code)
  WHEN MATCHED THEN UPDATE
   SET tgt.jrjr_wo_user_status = src.jrjr_wo_user_status
 WHERE tgt.jrjr_wo_user_status IS NULL
   AND src.wowo_user_status IS NOT NULL;

C'est une vraie question, j'ai toujours un doute sur le WHERE et j'ai la flemme de chercher !

**pacmann** · 22/07/2013, 15h57

Envoyé par Waldar

C'est une vraie question, j'ai toujours un doute sur le WHERE et j'ai la flemme de chercher !

J'aurais jamais cru entendre ça de ta part

Comme tu te fais un peu vieux, je vais faire le test qu'aurais fait (au minimum) le Waldar dans sa jeunesse :

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
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
 
SQL> CREATE TABLE test1 AS SELECT level l, level l2 FROM DUAL CONNECT BY LEVEL <= 1000000;
 
Table created.
 
Elapsed: 00:00:01.21
SQL> CREATE TABLE test2 AS SELECT * FROM test1;
 
Table created.
 
Elapsed: 00:00:00.48
 
SQL> set autot traceonly statistics explain
 
SQL> MERGE INTO test1
  2  USING test2
  3  ON (test1.l = test2.l)
  4  WHEN MATCHED THEN UPDATE SET test1.l2 = test2.l2
  5  WHERE test2.l2 <= 1;
 
1 row merged.
 
Elapsed: 00:00:00.82
 
Execution Plan
----------------------------------------------------------
Plan hash value: 520388833
 
--------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT      |       |   811K|    40M|       |  4643   (1)| 00:00:56 |
|   1 |  MERGE               | TEST1 |       |       |       |            |          |
|   2 |   VIEW               |       |       |       |       |            |          |
|*  3 |    HASH JOIN         |       |   811K|    49M|    29M|  4643   (1)| 00:00:56 |
|   4 |     TABLE ACCESS FULL| TEST2 |   811K|    20M|       |   473   (2)| 00:00:06 |
|   5 |     TABLE ACCESS FULL| TEST1 |   938K|    33M|       |   474   (3)| 00:00:06 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("TEST1"."L"="TEST2"."L")
 
Note
-----
   - dynamic sampling used for this statement
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          3  db block gets
       4159  consistent gets
          0  physical reads
          0  redo size
        745  bytes sent via SQL*Net to client
        896  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
SQL> MERGE INTO test1
  2  USING (select * from test2 where test2.l2 <= 1) test2
  3  ON (test1.l = test2.l)
  4  WHEN MATCHED THEN UPDATE SET test1.l2 = test2.l2
  5  WHERE test2.l2 <= 1;
 
1 row merged.
 
Elapsed: 00:00:00.23
 
Execution Plan
----------------------------------------------------------
Plan hash value: 520388833
 
------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | MERGE STATEMENT      |       |    23 |  1196 |   956   (3)| 00:00:12 |
|   1 |  MERGE               | TEST1 |       |       |            |          |
|   2 |   VIEW               |       |       |       |            |          |
|*  3 |    HASH JOIN         |       |    23 |  1472 |   956   (3)| 00:00:12 |
|*  4 |     TABLE ACCESS FULL| TEST2 |    23 |   598 |   476   (3)| 00:00:06 |
|   5 |     TABLE ACCESS FULL| TEST1 |   938K|    33M|   474   (3)| 00:00:06 |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("TEST1"."L"="TEST2"."L")
   4 - filter("TEST2"."L2"<=1)
 
Note
-----
   - dynamic sampling used for this statement
 
 
Statistics
----------------------------------------------------------
         15  recursive calls
          3  db block gets
       4400  consistent gets
          0  physical reads
          0  redo size
        746  bytes sent via SQL*Net to client
        939  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed

=> Tu peux mettre ça où tu veux, par contre il vaut mieux filtrer avant de matcher (tu le vois dans notre cas sur la taille de la hash table à construire)

Par contre, il me semble aussi (lu rapidement, j'ai juste tilté sur Waldar fatigué) qu'on doit pouvoir se passer de la jointure dans le USING.

**Nico02** · 23/07/2013, 12h58

Oula je ne pensais pas défrayer la chronique à ce point avec ce topic

Le script est déjà parti chez le client donc tant pis, (c'était juste un script correctif, il ne sera surement plus utilisé) mais je prends note de toute vos réponses

Problème lenteur d'update

PL/SQL Oracle

Discussions similaires

Partager

Partager