Temps de reponse sur Select avec Jointure

Version imprimable

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:

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:

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:

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.

28/07/2010, 11h27
Waldar

Ok pour les TKPROF, mais il manque le plan d'exécution du select.
28/07/2010, 14h07
Guigsounet

1 pièce(s) jointe(s)

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
28/07/2010, 14h13
Waldar

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 ?
28/07/2010, 14h29
Guigsounet

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

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:

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:

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:

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;

28/07/2010, 14h59
Guigsounet

merci, je teste et vous fait un retour :)

Nous avons un index sur STK_DATE_GENE :
PK_STOCK sur STK_DATE_GEN et STK_EXTERNALID

Voici le TKprof sur l execution (en cours) de la requete avec le nouvel index IDX_F564406_4

Ca semble depiler bien plus vite :

Code:

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
 
TKPROF: Release 9.2.0.8.0 - Production on Me Jul 28 15:32:54 2010
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
Trace file: kgtprod_ora_2016.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     2873      1.06       3.38       2419     123098          0       43081
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2875      1.06       3.38       2419     123098          0       43081
 
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                    2873        0.00          0.00
  db file scattered read                        415        0.03          0.43
  db file sequential read                      1106        0.11          2.01
  SQL*Net more data to client                     1        0.00          0.00
  SQL*Net message from client                  2872        0.61        891.31
 
 
 
********************************************************************************
 
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
 
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     2873      1.06       3.38       2419     123098          0       43081
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2875      1.06       3.38       2419     123098          0       43081
 
Misses in library cache during parse: 1
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                    2874        0.00          0.00
  SQL*Net message from client                  2873        6.20        897.52
  db file scattered read                        415        0.03          0.43
  db file sequential read                      1106        0.11          2.01
  SQL*Net more data to client                     1        0.00          0.00
 
 
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0
 
Misses in library cache during parse: 0
 
    1  user  SQL statements in session.
    0  internal SQL statements in session.
    1  SQL statements in session.
    0  statements EXPLAINed in this session.
********************************************************************************
Trace file: kgtprod_ora_2016.trc
Trace file compatibility: 9.02.00
Sort options: execnt  fchcnt  
       1  session in tracefile.
       1  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       1  SQL statements in trace file.
       1  unique SQL statements in trace file.
   10187  lines in trace file.

28/07/2010, 15h56
Waldar

Il manque toujours le plan d'exécution !
Il y a une option pour qu'il soit présent dans le TKPROF, ça dépend comment vous tracez votre session.

Dans quel sens est construite la PK_STOCK :
(STK_DATE_GEN, STK_EXTERNALID) ou (STK_EXTERNALID, STK_DATE_GEN) ?

Est-ce que la valeur de STK_DATE_GEN est vraiment discriminante ?
En effet, Oracle a choisi de ne pas utiliser cette PK alors qu'il n'y a qu'une seule date - celle de date_reference - qui pourrait constituer un bon filtre.

1 pièce(s) jointe(s)

20 - 25 mins au lieu de 5h belle perf

le plan a legerement changé (je le joins)

mon attention est maintenant reporté sur la différence observée dans le tkprof entre le cpu et l elapsed

EDIT (nos posts se sont croisés :)

Qu entendez vous par discriminante ?
Code:

1 2 3 4 5 6 CREATE UNIQUE INDEX "YIELD"."PK_STOCK" ON "YIELD"."STOCK" ("STK_EXTERNALID", "STK_DATE_GENE") TABLESPACE "YIELD_IDX" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 25640K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) LOGGING

28/07/2010, 16h04
Waldar
Discriminante, ça veut dire qu'il y a peu de lignes qui ont une seule valeur de date_gene.

Que donne cette requête :
Code:

1 2 select count(*), count(distinct STK_DATE_GENE) from YIELD.STOCK;

Code:

1
2
3
  COUNT(*) COUNT(DISTINCTSTK_DATE_GENE)
---------- ----------------------------
    220136                            6

Ok, peu de valeurs donc.

Et celle-ci :

Code:

1
2
3
4
select count(*)
  from yield.stock st
       inner join yield.date_reference dr
         on dr.date_application = st.stk_date_gene

28/07/2010, 16h41
Guigsounet

42735
28/07/2010, 17h06
Waldar

Il faudrait quand même essayer avec l'index sur STK_DATE_GENE, créez-en un supplémentaire (juste pour le test), ça réduit quand même significativement la volumétrie.
30/07/2010, 10h29
Guigsounet

Je met ce conseil dans un petit coin, merci encore pour votre disponibilité :)