Discussion :

[aline]

Bonjour à tous,

je me pose quelques questions sur les triggers au niveau des performances.
Je parle de tables dans lesquelles ont fait des insert à très grande gréquence.

Dans ce type de tables, j'ai évidement une séquence comme identifiant unique.

Ma question:
-Poser un trigger qui se charge de gerer la séquence est-il plus couteux en terme de performance que de rajouter dans la requete insert la colonne avec sa sequence correspondante?

(Je pense que oui, mais je peux difficilement mettre en oeuvre la deuxième solution, n'étant pas maitre des codes d'insertion. C'est pour cela que je veux le verifier).

Et une deuxième subsidiaire.

Est-ce qu'un trigger sur un update ou un delete peux ralentir un insert (même d'un milliardième de seconde!)?

[orafrance]

une surcharge de code est toujours pénalisante donc oui un trigger pour la séquence ralenti l'insert mais c'est négligeable et surtout plus pratique

Pour la deuxiéme question, ça me surprendrait mais Oracle recèle encore quelques secrets bien gardé

[lalystar]

Est-ce qu'un trigger sur un update ou un delete peux ralentir un insert (même d'un milliardième de seconde!)?

J'aurais dis non, évidemment Mais les résultats m'ont complètement surpris ! Voici un petit test :

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
 
drop table SANS_TRIGGER;
drop table AVEC_TRIGGER;
 
create table SANS_TRIGGER as select * from all_objects where 1=0;
 
create table AVEC_TRIGGER as select * from all_objects where 1=0;
 
create or replace trigger tu_avec_trigger before update on AVEC_TRIGGER
begin
   null;
end;
/
 
alter session set sql_trace=true;
alter session set timed_statistics=true;
 
insert into SANS_TRIGGER
select   *
from     ALL_OBJECTS;
 
commit;
 
insert into AVEC_TRIGGER
select   *
from     ALL_OBJECTS;
 
commit;
 
 
alter session set sql_trace=false;
alter session set timed_statistics=false;

Et le résultat avec TkProf :

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
 
insert into SANS_TRIGGER
select   *
from     ALL_OBJECTS
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.08       0.09          0          0          0           0
Execute      1      1.58       1.61        688      16098        482        3337
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      1.66       1.70        688      16098        482        3337
 
 
insert into AVEC_TRIGGER
select   *
from     ALL_OBJECTS
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.09       0.08          0          0          0           0
Execute      1      1.50       1.56        682      16091        481        3337
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      1.59       1.64        682      16091        481        3337

Le temps total est inférieur pour l'insertion dans la table AVEC trigger. Il y a même légèrement moins de bloc lu dans le second cas.

Moralité : mettez partout des triggers qui servent à rien ! (A prendre avec humour bien sûr).

Quelle est l'erreur ?
Au niveau du temps je pense que c'est parce que la seconde requête a profité du cache (lecture sur disque 682 blocs contre 688) mais elle fait quand même moins de logical I/O : 16572 contre 16580...

Quelqu'un a des résultats similaires ? Si oui, comment l'interprétez vous ?



Laly.

[orafrance]

C'est pas bon de faire un INSERT AS SELECT, parce que tu ne sais pas si tu testes l'insert ou le select

Pour faire un test cohérent il faut insérer des valeurs... je te laisse le soin de faire un milliard d'ordre insert pour le test

[lalystar]

Oui, bien sûr ! Bien vu Orafrance 8) je refais le test !


Laly.

[lalystar]

Nouveau test :

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
 
begin
   for i in 1..10000
   loop
      insert into AVEC_TRIGGER
      values (1);
   end loop;
end;
/
 
 
begin
   for i in 1..10000
   loop
      insert into SANS_TRIGGER
      values (1);
   end loop;
end;
/

Voici le nouveau résultat :

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
 
INSERT INTO SANS_TRIGGER 
VALUES
 ( 1  )
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute  10000      2.77       2.79          2         31      10479       10000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    10001      2.78       2.79          2         31      10479       10000
 
 
 
INSERT INTO AVEC_TRIGGER 
VALUES
 ( 1  )
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute  10000      3.03       2.89          5         31      10481       10000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    10001      3.04       2.90          5         31      10481       10000

La morale est sauve mais on note quand même un ralentissement dans l'insert sur la table avec trigger (1 dixième de secondes en plus)!


Laly.

[orafrance]

In Oracle we trust

[aline]

donc, 1/100000eme de seconde avec le trigger.
C'est plus que raisonnable!

[aline]

Je viens de faire un test pas très surprenant

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
 
create table avec_trigger( seq1 number, a varchar2(1));
alter table AVEC_TRIGGER  add constraint seq_pk primary key (SEQ1)
 
 
create table sans_trigger( seq2 number, a varchar2(1));
alter table sans_TRIGGER  add constraint seq2_pk primary key (SEQ2)
 
create sequence seq1 start with 1 increment by 1;
 
create sequence seq2 start with 1 increment by 1;
 
 
 
alter session set sql_trace=true;
alter session set timed_statistics=true;
 
 
create or replace trigger seq_avec_trigger
before insert on avec_trigger
for each row
begin
    select seq1.nextval into :new.seq1 from dual;
end;
/
 
 
 
begin
   for i in 1..10000
   loop
      insert into AVEC_TRIGGER (a)
      values ('X');
   end loop;
end;
/
 
 
begin
   for i in 1..10000
   loop
      insert into SANS_TRIGGER
      values (i,'X');
   end loop;
end;
/ 
 
 
alter session set sql_trace=false;
alter session set timed_statistics=true;
 
INSERT into SANS_TRIGGER
      values (:b1,'X')
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000      0.78       1.13          0         74      30960       10000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    10001      0.78       1.14          0         74      30960       10000
 
 
INSERT into AVEC_TRIGGER (a)
      values ('X')
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000      1.76       1.86          0         90      10398       10000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    10001      1.76       1.86          0         90      10398       10000

Donc, il semblerait qu'il vaut mieux inserer une sequence sans trigger!

[lalystar]

Aline,


J'ai continué à partir de ton test en faisant la même chose mais avec un CACHE de 50 pour la séquence, voici les résultats :

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
 
SELECT SEQ_CACHE.NEXTVAL   
FROM
 DUAL
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute  10000      1.34       1.20          0          0          0           0
Fetch    10000      4.82       5.12          4      10000      40200       10000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    20001      6.16       6.33          4      10000      40200       10000
 
 
SELECT SEQ_NOCACHE.NEXTVAL   
FROM
 DUAL
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute  10000      1.71       1.59          0          0          0           0
Fetch    10000     29.28      29.75          3      10000      50000       10000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    20001     31.00      31.35          3      10000      50000       10000

Diminution du temps d'un facteur 5 avec le cas : impressionant !


Laly.

[aline]

C'est bien ca!
Mais c'est combien le cache par défaut dans une séquence?
20 je crois?!

[lalystar]

Oui tu as raison, la valeur par défaut c'est CACHE 20 (en tout en 8.1.7.4).


Laly.

[aline]

Dans ce cas, il serait judicieux de savoir quelle valeur de cache pourrait être optimale et si on arrive à approcher l'insertion directe sans utiliser de trigger.
Je fais deux trois tests pour voir tout cela.