[oracle 8.0.5] insert très (trop) lent

Version imprimable

Voir 40 message(s) de cette discussion en une page

09/10/2006, 11h35
icoons

[oracle 8.0.5] insert très (trop) lent

Parmis les quelques milliers de requêtes qui sont executées chaque jour sur notre base de données (oracle 8.0.5), une seule me pose de réels soucis.

> Chaque jour, une requête insert (environ 16000 lignes, 23 colonnes, 10 Mo) dans une table temporaire. Cette insertion, somme toute légère, est extrèmement longue et peut durer plusieurs heures alors que la requête qui l'alimente prend, au plus, quelques minutes.

> La table cible est préalablement vidée (truncate ou delete, j'ai essayé les deux), et les segments rollbacks sont largement suffisant pour supporter les données de cette transaction. La table est construite avec les options suivantes :NOLOGGING,NOCACHE,NOPARALLEL

> Aucune autre requête n'est susceptible de verrouiller cette table puisque elle n'est utilisée que dans ce traitement et que, par ailleurs , c'est l'un des premiers traitement à être lancé, la nuit, après le redémarrage de nos bases.

> J'ajoute que le poids des index est réduit : une clé primaire et un index sur trois colonnes chacun (les mêmes mais dans un ordre différent)

> Le seul moyen que j'ai cru trouver pour limiter le temps d'execution est de procéder à un rebuild de la table et des index. Je ne suis pas sûr cependant que ce ne soit pas dû à un heureux concours de circonstance et que mon rebuild ne soit aussi efficace qu'un rituel vaudoux.

Qu'est-ce qui, selon vous, pourrait ralentir à ce point une commande insert ?
09/10/2006, 11h41
orafrance

tu l'as mis en trace pour voir les événements d'attente ?
09/10/2006, 11h58
McM

la dernière fois qu'un problème comme ceci (insert trop long pour une requete rapide) a été posée sur le forum, c'était un problème d'extent de la table d'insertion ou d'index qui étaient à quelques Ko.
Donne les desc de tes tables et indexes.
09/10/2006, 12h16
Oraman

il s'agit d'un script d'insertion ?
un echantillion si c'est possible ?
09/10/2006, 12h38
Oraman

voici le une dicussion similaire sur qui a déjà eu lieu sur le Forum
http://www.developpez.net/forums/sho...d.php?t=209124

voici la requête d'insertion :

1 >

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
INSERT INTO alm.tb_wdo_vte
            (id_wdo_vte_ref_cla, id_wdo_vte_pvt, id_wdo_vte_emp,
id_wdo_vte_mci_jou_heu, nb_wdo_vte_prp, nb_wdo_vte_tkv,
dc_wdo_vte_mnt_ca_net_ht, dc_wdo_vte_mnt_ca_brt_ini,
dc_wdo_vte_mnt_ca_brt_bas, dc_wdo_vte_mnt_ca_net_ttc,
dc_wdo_vte_mnt_mrg, dc_wdo_vte_mnt_rab_rem,    dc_wdo_vte_mnt_rem,
             dc_wdo_vte_vnd_heu, nb_wdo_vte_rmb, nb_wdo_vte_ech,
             nb_wdo_vte_ret_cli, nb_wdo_vte_qte, dc_wdo_vte_mnt_ret_cli,
             dc_wdo_vte_mnt_ech, dc_wdo_vte_mnt_rmb, dc_wdo_vte_amp_hor,
             lb_wdo_vte_sta, nb_wdo_vte_cli)
   SELECT   g_t4.id_ref, g_t3.id_pvt, g_t3.id_emp,
            TO_NUMBER (TO_CHAR (g_t3.dt_ent_tkv, 'yyyymmddHH24')), 0, 0,
            SUM ((  (  (g_t1.dc_lig_tkv_prx_ven_brt * g_t1.nb_lig_tkv_qt)
                     - g_t1.dc_lig_tkv_mnt_rem
                    )
                  - g_t1.dc_lig_tkv_mnt_tva
                 )
                ),
            SUM ((g_t2.dc_prx_brt_ini * g_t1.nb_lig_tkv_qt)),
            SUM ((g_t1.nb_lig_tkv_qt * g_t2.dc_prx_brt_bas)),
            SUM ((  (g_t1.dc_lig_tkv_prx_ven_brt * g_t1.nb_lig_tkv_qt)
                  - g_t1.dc_lig_tkv_mnt_rem
                 )
                ),
            (  SUM ((  (  (g_t1.dc_lig_tkv_prx_ven_brt * g_t1.nb_lig_tkv_qt)
                        - g_t1.dc_lig_tkv_mnt_rem
                       )
                     - g_t1.dc_lig_tkv_mnt_tva
                    )
                   )
             - SUM ((g_t1.nb_lig_tkv_qt * g_t2.dc_prmp))
            ),
            (  SUM ((  (g_t2.dc_prx_brt_bas - g_t1.dc_lig_tkv_prx_ven_brt)
                     * g_t1.nb_lig_tkv_qt
                    )
                   )
             + SUM (g_t1.dc_lig_tkv_mnt_rem)
            ),
            SUM (g_t1.dc_lig_tkv_mnt_rem), 0, 0, 0, 0,
            SUM (g_t1.nb_lig_tkv_qt), 0, 0, 0, 0, 'AA', 0
       FROM ods.tb_o_lig_tkv@xdwa g_t1,
            alm.tb_wdo_prx_ref_jou@xdwa g_t2,
            ods.tb_o_ent_tkv@xdwa g_t3,
            ods.tb_o_uvc@xdwa g_t4
      WHERE g_t3.id_tps_jou = g_t2.id_jou
        AND g_t1.id_uvc = g_t4.id_uvc
        AND g_t3.id_ent_tkv = g_t1.id_ent_tkv
        AND g_t3.id_tps_jou >= TO_NUMBER (TO_CHAR (:1, 'yyyymmdd'))
        AND g_t1.cd_lig_tkv_typ <> 'MVTFI'
        AND g_t4.id_ref = g_t2.id_uvc
   GROUP BY TO_NUMBER (TO_CHAR (g_t3.dt_ent_tkv, 'yyyymmddHH24')),
            g_t4.id_ref,
            g_t3.id_pvt,
            g_t3.id_emp

2 > Le script de création de la table et des index sont les suivants :

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
CREATE TABLE ALM.TB_WDO_VTE
(
  ID_WDO_VTE_REF_CLA         NUMBER(15),
  ID_WDO_VTE_PVT             NUMBER(15),
  ID_WDO_VTE_MCI_JOU_HEU     NUMBER(15),
  NB_WDO_VTE_PRP             NUMBER(38),
  ID_WDO_VTE_EMP             NUMBER(15),
  NB_WDO_VTE_TKV             NUMBER(38),
  DC_WDO_VTE_MNT_CA_NET_HT   NUMBER(16,6),
  DC_WDO_VTE_MNT_CA_BRT_INI  NUMBER(16,6),
  DC_WDO_VTE_MNT_CA_BRT_BAS  NUMBER(16,6),
  DC_WDO_VTE_MNT_CA_NET_TTC  NUMBER(16,6),
  DC_WDO_VTE_MNT_MRG         NUMBER(16,6),
  DC_WDO_VTE_MNT_RAB_REM     NUMBER(16,6),
  DC_WDO_VTE_MNT_REM         NUMBER(16,6),
  DC_WDO_VTE_VND_HEU         NUMBER(12,2),
  NB_WDO_VTE_RMB             NUMBER(38),
  NB_WDO_VTE_ECH             NUMBER(38),
  NB_WDO_VTE_RET_CLI         NUMBER(38),
  NB_WDO_VTE_QTE             NUMBER(38),
  DC_WDO_VTE_MNT_RET_CLI     NUMBER(16,6),
  DC_WDO_VTE_MNT_ECH         NUMBER(16,6),
  DC_WDO_VTE_MNT_RMB         NUMBER(16,6),
  DC_WDO_VTE_AMP_HOR         NUMBER(12,2),
  LB_WDO_VTE_STA             VARCHAR2(2),
  NB_WDO_VTE_CLI             NUMBER(15)
)
TABLESPACE XDWAE09
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          10M
            NEXT             2M
            MINEXTENTS       1
            MAXEXTENTS       255
            PCTINCREASE      20
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOLOGGING 
NOCACHE
NOPARALLEL;
 
CREATE INDEX ALM.IX_WDO_VTE_JOU_PVT ON ALM.TB_WDO_VTE
(ID_WDO_VTE_MCI_JOU_HEU, ID_WDO_VTE_REF_CLA, ID_WDO_VTE_PVT)
NOLOGGING
TABLESPACE XDWAI06
PCTFREE    20
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          5M
            NEXT             500K
            MINEXTENTS       1
            MAXEXTENTS       255
            PCTINCREASE      20
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
 
 
CREATE INDEX ALM.IX_WDO_VTE_PVT_EMP_JOU ON ALM.TB_WDO_VTE
(ID_WDO_VTE_PVT, ID_WDO_VTE_EMP, ID_WDO_VTE_MCI_JOU_HEU)
NOLOGGING
TABLESPACE XDWAI06
PCTFREE    20
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          5M
            NEXT             500K
            MINEXTENTS       1
            MAXEXTENTS       255
            PCTINCREASE      20
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

3 > Lorsque je trace la session (avec toad) je ne vois rien, mais je ne sais pas voir non plus : l'explain plan semble bon témoigne que la requête select utilise tous les index. j'ai lancé une trace mais je ne suis pas super bilingue en tkprof, malgré l'aide des faqs et tutoriels trouvés ici bas.

09/10/2006, 15h08
orafrance

regarde du coté de v$session_wait pendant l'exécution

voilà pour v$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
     SID       SEQ# EVENT                                                     
---------- ---------- ----------------------------------------------------            1      10428 pmon timer                                                
         2      14849 rdbms ipc message                                         
         3      15886 rdbms ipc message                                         
         4      48073 rdbms ipc message                                         
         7         34 rdbms ipc message                                         
         5      21090 rdbms ipc message                                         
        67      11322 db file sequential read                                   
         6       1554 smon timer                                                
         8      19229 SQL*Net message from client                               
        11         46 SQL*Net message from client                               
        13         66 SQL*Net message from client                               
        16         38 SQL*Net message from client                               
        17         78 SQL*Net message from client                               
        18         46 SQL*Net message from client                               
        21        516 SQL*Net message from client                               
        38      16269 SQL*Net message from client                               
        39      11718 SQL*Net message from client                               
        93        577 SQL*Net message from client                               
        87       2337 SQL*Net message from client                               
        84        275 SQL*Net message from client                               
        82          7 SQL*Net message from client                               
        78       4100 SQL*Net message from client                               
        76       1608 SQL*Net message from client                               
        69       1384 SQL*Net message from client                               
        42       2319 SQL*Net message from client                               
        46      12950 SQL*Net message from client                               
        47        326 SQL*Net message from client                               
        55        322 SQL*Net message from client                               
        57       3883 SQL*Net message from client                               
        58         76 SQL*Net message from client                               
        59        852 SQL*Net message from client                               
        65          8 SQL*Net message from client                               
32 rows selected

éloquent, non ?

et aussi...

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
EVENT                                                            TOTAL_WAITS TOT
---------------------------------------------------------------- ----------- ---
Null event                                                                 1    
latch free                                                              4835    
pmon timer                                                             10456    
process startup                                                            7    
rdbms ipc reply                                                         1401    
rdbms ipc message                                                      61605    
enqueue                                                                    9    
control file sequential read                                             363    
control file parallel write                                            10486    
refresh controlfile command                                                1    
free buffer waits                                                        196    
write complete waits                                                      84    
buffer busy waits                                                       6021    
log file sequential read                                                  20    
log file single write                                                     30    
log file parallel write                                                20035    
log buffer space                                                          21    
log file switch completion                                                 7    
log file sync                                                           5065    
db file sequential read                                              1500284    
db file scattered read                                                511163    
db file parallel write                                                  6998    
direct path read                                                       62155    
direct path write                                                      58540    
sort segment request                                                       1    
instance state change                                                      2    
smon timer                                                               106    
file identify                                                             66    
file open                                                               2254    
SQL*Net message to client                                             889126    
SQL*Net message to dblink                                                 21    
SQL*Net more data to client                                           139571    
SQL*Net message from client                                           889095    
SQL*Net more data from client                                          43259    
SQL*Net message from dblink                                               21    
SQL*Net more data from dblink                                             38    
SQL*Net break/reset to client                                            142    
37 rows selected

[/SIZE]

Je pense que ca ne vient pas de l'insert, mais plutot du select.(avec SUM , etc)
Ca donne quoi en lançant juste :

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
 
 SELECT g_t4.id_ref, g_t3.id_pvt, g_t3.id_emp,
TO_NUMBER (TO_CHAR (g_t3.dt_ent_tkv, 'yyyymmddHH24')), 0, 0,
SUM (( ( (g_t1.dc_lig_tkv_prx_ven_brt * g_t1.nb_lig_tkv_qt)
- g_t1.dc_lig_tkv_mnt_rem
)
- g_t1.dc_lig_tkv_mnt_tva
)
),
SUM ((g_t2.dc_prx_brt_ini * g_t1.nb_lig_tkv_qt)),
SUM ((g_t1.nb_lig_tkv_qt * g_t2.dc_prx_brt_bas)),
SUM (( (g_t1.dc_lig_tkv_prx_ven_brt * g_t1.nb_lig_tkv_qt)
- g_t1.dc_lig_tkv_mnt_rem
)
),
( SUM (( ( (g_t1.dc_lig_tkv_prx_ven_brt * g_t1.nb_lig_tkv_qt)
- g_t1.dc_lig_tkv_mnt_rem
)
- g_t1.dc_lig_tkv_mnt_tva
)
)
- SUM ((g_t1.nb_lig_tkv_qt * g_t2.dc_prmp))
),
( SUM (( (g_t2.dc_prx_brt_bas - g_t1.dc_lig_tkv_prx_ven_brt)
* g_t1.nb_lig_tkv_qt
)
)
+ SUM (g_t1.dc_lig_tkv_mnt_rem)
),
SUM (g_t1.dc_lig_tkv_mnt_rem), 0, 0, 0, 0,
SUM (g_t1.nb_lig_tkv_qt), 0, 0, 0, 0, 'AA', 0
FROM ods.tb_o_lig_tkv@xdwa g_t1,
alm.tb_wdo_prx_ref_jou@xdwa g_t2,
ods.tb_o_ent_tkv@xdwa g_t3,
ods.tb_o_uvc@xdwa g_t4
WHERE g_t3.id_tps_jou = g_t2.id_jou
AND g_t1.id_uvc = g_t4.id_uvc
AND g_t3.id_ent_tkv = g_t1.id_ent_tkv
AND g_t3.id_tps_jou >= TO_NUMBER (TO_CHAR (:1, 'yyyymmdd'))
AND g_t1.cd_lig_tkv_typ <> 'MVTFI'
AND g_t4.id_ref = g_t2.id_uvc
GROUP BY TO_NUMBER (TO_CHAR (g_t3.dt_ent_tkv, 'yyyymmddHH24')),
g_t4.id_ref,
g_t3.id_pvt,
g_t3.id_emp

09/10/2006, 15h39
remi4444

T'es sur que c'est pas plutot le SELECT qui est long ??

Pour tester tu devrais le faire en 2 temps:
- remplissage d'une table temporaire à l'image de l'autre sans index ni contraintes avec le SELECT
- Insert into TABLE_DEFINITIVE select * TABLE_TEMPORAIRE
09/10/2006, 15h52
icoons

le select n'est pas spécialement court en effet mais, sans l'insert, ça dure une dizaine de minute; vingt tout au plus dans les mauvais jours.
avec l'insert (et sans le rebuild) j'en ai pour des heures carrées.

L'idée de remi4444 de fractionner le travail est sans doute une solution (si je ne trouve rien d'autre) mais elle n'est pas super satisfaisante : Insérer moins de 20000 lignes dans une table, en une seule fois, en 2006 et même avec une 8.05, ce devrait être possible.
09/10/2006, 15h57
orafrance

A mon avis tu as un problème de TEMP trop petit. Le SELECT fait du tri -> consomme du TEMP et l'insert met à jour les indexes -> consomme du TEMP.

Essaye d'augmenter la SORT_AREA_SIZE via un alter session avant de lancer ta requête.
09/10/2006, 16h11
icoons

je vais essayer ça;
je pensais que le sort_area_size ne servait qu'à réserver de l'espace pour effectuer des tris. Il est aussi utilisé dans un insert ?
09/10/2006, 16h12
orafrance

SUM = tri, MAJ d'index = tri ;)
09/10/2006, 16h23
icoons

bah oui, forcément :oops:

je vais un test en passant le sort_area_size à 16777216 (j'étais à 8388608).
09/10/2006, 16h24
orafrance

je pense que tu peux carrément charger la mule genre à 64 ou 128Mo ;)
09/10/2006, 16h26
remi4444

Citation:

Envoyé par icoons

le select n'est pas spécialement court en effet mais, sans l'insert, ça dure une dizaine de minute; vingt tout au plus dans les mauvais jours.

Avec quoi as-tu mesuré que le select ne durait qu'une dizaine de minutes? méfie toi des outils comme TOAD qui te donne le temps de récupération des 20 premières lignes affichées et pas de l'ensemble des données.
09/10/2006, 16h41
icoons

Citation:

Envoyé par Fred_D

je pense que tu peux carrément charger la mule genre à 64 ou 128Mo ;)

ok je recommence !

Citation:

Envoyé par remi4444

Avec quoi as-tu mesuré que le select ne durait qu'une dizaine de minutes?

Tout simplement en lançant la requête et en attendant qu'elle se termine. Je ne fais dans ce cas qu'un select (à l'instar de ce que demande bouyao un peu plus haut).
Je mesure le temps d'execution du select seul et celui du select + insert.
Je vais aussi tout insérer dans une table temporaire puis réinjecter les données dans la table cible (avec les index) pour mesurer l'influence des index et de la construction de la table cible dans cet insert.
09/10/2006, 18h12
remi4444

Citation:

Envoyé par icoons

Tout simplement en lançant la requête et en attendant qu'elle se termine.

Justement qu'est-ce que tu entends par "terminé" parcequ'il y a 2 chrono dans la mesure d'une requête, le temps de récupération de la première ligne (ouverture du curseur) et le temps total de récupération de toutes les lignes du curseur.

On se fait souvent avoir dans TOAD parceque lorsqu'il rend la main en affichant les premières ligne de la requête, il se peux que le temps affiché soit très court et pourtant la requête est loin d'être terminée. Par contre quand on insère les lignes dans une table temporaire, là on est sur que c'est le vrai temps total, et il peut être monstrueusement plus long...

Voir 40 message(s) de cette discussion en une page