Salut

J'ai un problème de temps d'exécution sur un traitement que je n'ai PAS développé moi-même (n'étant pas très doué en développement).

Le traitement consiste en un select puis un certain nombre d'update et d'insert.

Les update et insert sont générés par un outil (que je ne connais ni ne possède : Informatica) à partir des résultats du select.
D'après mon contact il semblerait que l'outil génère 15000+ update et les envoie un à un puis fasse de même pour les inserts.

Le select en question :
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
SELECT STOCK.STK_EXTERNALID, STOCK.STK_DOSSIER_STE, STOCK.STK_DOSSIER_NUM, STOCK.STK_DOSSIER_TYPE, STOCK.STK_DOSSIER_LIGNE, STOCK.STK_VTEP_DISPO, F563301.PLLITM, F563301.PLITM, F563301.PLFY, F563301.PLPN, F563301.PL$SRES, F563301.PLUORG, F563301.PLKIT, F563301.PL$VTEP, F563301.PLUPMJ, F563301.PL$NB1, F563301.PL$NB2, F563301.PL$NB3, F563301.PL$NB4, F563301.PL$NB5, F563301.PL$NB6, F563301.PL$NB7, F563301.PL$NB8, F563301.PL$NB9, F563301.PL$NB10, F564406.RDFY, F564406.RDPN, F564406.RDKIT, F564406.RDUPMJ, DATE_REFERENCE.DATE_APPLICATION, STOCK.STK_DATE
FROM
 yield.STOCK, KOGITO_SAS.F563301, KOGITO_SAS.F564406, yield.DATE_REFERENCE
WHERE
 F564406.RDDOC(+)=0
AND
 F563301.PLITM=STOCK.STK_ID_RESEAU
and
F563301.PLFY=STOCK.STK_ANNEE
and
F563301.PLPN=STOCK.STK_SEMAINE
and
F563301.PLITM=F564406.RDKIT(+)
and
F563301.PLFY=F564406.RDFY(+)
and
F563301.PLPN=F564406.RDPN(+)
and
STOCK.STK_DATE_GENE=DATE_REFERENCE.DATE_APPLICATION
Lorsque le traitement s execute dans son ensemble (select + update) j'ai la trace suivante :
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
TKPROF: Release 9.2.0.8.0 - Production on Ma Jul 27 16:09:44 2010
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
Trace file: kgtprod_ora_1824.trc
Sort options: execnt  fchcnt  
********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************
 
UPDATE STOCK SET STK_NBFACESTHEORIQUE = :1 
WHERE
 STK_EXTERNALID = :2 AND STK_DATE_GENE = :3
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute  14858      0.79       0.82          0      44574      23835       14858
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    14860      0.79       0.82          0      44574      23835       14858
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 117  (YIELD)
error during execute of EXPLAIN PLAN statement
ORA-00942: Table ou vue inexistante
 
parse error offset: 78
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                   14858        0.00          0.01
  SQL*Net message from client                 14858       59.99       1123.79
  undo segment extension                        242        0.00          0.00
  buffer busy waits                               4        0.00          0.00
 
 
Trace file: kgtprod_ora_1824.trc
Trace file compatibility: 9.02.00
Sort options: execnt  fchcnt  
       1  session in tracefile.
       2  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       2  SQL statements in trace file.
       1  unique SQL statements in trace file.
   44960  lines in trace file.
Ce qui me laisse penser que ce traitement est "mal écrit"

Ce matin on m'a donc envoyé la requete Select et je l ai executé directement sur le serveur de BD en tracant la session

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
TKPROF: Release 9.2.0.8.0 - Production on Me Jul 28 10:38:25 2010
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
Trace file: kgtprod_ora_3128.trc
Sort options: execnt  fchcnt  
********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************
 
SELECT STOCK.STK_EXTERNALID, STOCK.STK_DOSSIER_STE, STOCK.STK_DOSSIER_NUM, STOCK.STK_DOSSIER_TYPE, STOCK.STK_DOSSIER_LIGNE, STOCK.STK_VTEP_DISPO, F563301.PLLITM, F563301.PLITM, F563301.PLFY, F563301.PLPN, F563301.PL$SRES, F563301.PLUORG, F563301.PLKIT, F563301.PL$VTEP, F563301.PLUPMJ, F563301.PL$NB1, F563301.PL$NB2, F563301.PL$NB3, F563301.PL$NB4, F563301.PL$NB5, F563301.PL$NB6, F563301.PL$NB7, F563301.PL$NB8, F563301.PL$NB9, F563301.PL$NB10, F564406.RDFY, F564406.RDPN, F564406.RDKIT, F564406.RDUPMJ, DATE_REFERENCE.DATE_APPLICATION, STOCK.STK_DATE
FROM
 yield.STOCK, KOGITO_SAS.F563301, KOGITO_SAS.F564406, yield.DATE_REFERENCE
WHERE
 F564406.RDDOC(+)=0
AND
 F563301.PLITM=STOCK.STK_ID_RESEAU
and
F563301.PLFY=STOCK.STK_ANNEE
and
F563301.PLPN=STOCK.STK_SEMAINE
and
F563301.PLITM=F564406.RDKIT(+)
and
F563301.PLFY=F564406.RDFY(+)
and
F563301.PLPN=F564406.RDPN(+)
and
STOCK.STK_DATE_GENE=DATE_REFERENCE.DATE_APPLICATION 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      349   1700.76    1706.85       7881  144000691          0        5221
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      351   1700.76    1706.85       7881  144000691          0        5221
 
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     350        0.00          0.00
  db file scattered read                        252        0.06          0.37
  db file sequential read                      4479        0.26          5.57
  SQL*Net more data to client                     1        0.00          0.00
  SQL*Net message from client                   349        0.90         96.68
  latch free                                      2        0.00          0.00
********************************************************************************
Trace file: kgtprod_ora_3128.trc
Trace file compatibility: 9.02.00
Sort options: execnt  fchcnt  
       1  session in tracefile.
       1  user  SQL statements in trace file.
       1  internal SQL statements in trace file.
       2  SQL statements in trace file.
       2  unique SQL statements in trace file.
    5877  lines in trace file.
Et la valeur de QUERY m a fait bondir

Veriez-vous un moyen de réecrire cette requete pour palier aux soucis sachant que des index sont posés sur chaque colonnes concernées par la jointure (+) sauf la colonne RDKIT ?

Que les statistiques passent chaque nuit sur l'ensemble des schémas et que j'ai reconstruit les index avant de lancer le select.