Temps de reponse sur Select avec Jointure

**Guigsounet** · 28/07/2010, 11h08

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.

**Waldar** · 28/07/2010, 11h27

Ok pour les TKPROF, mais il manque le plan d'exécution du select.

**Guigsounet** · 28/07/2010, 14h07

Merci de votre réponse rapide

Je dois avoir un pb de config pour l outil tkprof effectivement

sinon j'ai mis en pièce jointe le plan généré par OEM

**Waldar** · 28/07/2010, 14h13

Ok, quelques questions supplémentaires.
Quelles sont les volumétries des quatre tables ?

Si vos statistiques sont bien à jour, c'est 176000 pour stock et 2 pour date_reference, mais les deux autres ?

Que couvrent les index IDX_F563301 et F564406_3 ?

**Guigsounet** · 28/07/2010, 14h29

Alors a l'heure actuelle nous avons :
yield.STOCK : 220136
yield.DATE_REFERENCE : 1
KOGITO_SAS.F563301 : 1536799
KOGITO_SAS.F564406 : 1748428

Quant aux indexes :
IDX_F563301 : Colonnes PLITM, PLFY PLPN et PL$TLIGP de la table F563301
F564406_3 : Colonnes RDDOC, RDDCT, RDKCO, RDEDLN de la table F5644

**Waldar** · 28/07/2010, 14h54

Bizarre que l'estimation du nombre de ligne de stock soit si éloignée de la réalité.

Essayez de créer cet index :

Code :

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

1
2
3
CREATE INDEX kogito_sas.IDX_F564406_4
ON kogito_sas.f564406 (rdkit, rdfy, rdpn, rddoc, rdupmj)
COMPUTE STATISTICS;

Celui utilisé actuellement ne me paraît pas performant.

Si la critère de date sur stock est très restrictif, vous pouvez aussi créer un index dessus :

Code :

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

1
2
3
CREATE INDEX YIELD.IDX_STOCK_DT_GENE
ON YIELD.STOCK (stk_date_gene)
COMPUTE STATISTICS;

J'ai réécrit la requête de façon normative, mais ça ne va pas changer le plan d'exécution :

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
select st.stk_externalid,
       st.stk_dossier_ste,
       st.stk_dossier_num,
       st.stk_dossier_type,
       st.stk_dossier_ligne,
       st.stk_vtep_dispo,
       f1.pllitm,
       f1.plitm,
       f1.plfy,
       f1.plpn,
       f1.pl$sres,
       f1.pluorg,
       f1.plkit,
       f1.pl$vtep,
       f1.plupmj,
       f1.pl$nb1,
       f1.pl$nb2,
       f1.pl$nb3,
       f1.pl$nb4,
       f1.pl$nb5,
       f1.pl$nb6,
       f1.pl$nb7,
       f1.pl$nb8,
       f1.pl$nb9,
       f1.pl$nb10,
       f2.rdfy,
       f2.rdpn,
       f2.rdkit,
       f2.rdupmj,
       dr.date_application,
       st.stk_date
  from yield.stock st
       inner join yield.date_reference dr
         on dr.date_application = st.stk_date_gene
       inner join kogito_sas.f563301 f1
         on f1.plitm = st.stk_id_reseau
        and f1.plfy  = st.stk_annee
        and f1.plpn  = st.stk_semaine
       left outer join kogito_sas.f564406 f2
         on f2.rdkit = f1.plitm
        and f2.rdfy  = f1.plfy
        and f2.rdpn  = f1.plpn
        and f2.rddoc = 0;

Temps de reponse sur Select avec Jointure

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager