Discussion :

[jeremzzz]

Bonjour à tous.

Actuellement je travaille sur plusieurs base de données différentes (allant de 6i à 10g) et je me pose la question suivante : Un index est-il vraiment utile sur certaines de mes tables, qui ont des problèmes de performances.

Je vous explique :

Mon entreprise utilise pas mal de tables temporaires pour un peu tout, notamment l'édition des reports.

Ayant parfois plusieurs utilisateurs, nous utilisons un numéro d'identifiant unique par utilisateur qui n'est pas clé primaire sur la table puisque, dans le cas d'une commande par exemple, un utilisateur peut avoir x lignes de commande donc x fois ce numéro.

Ces tables sont donc soumises à de forts changements très fréquents, passant parfois de plusieurs centaines de milliers à 0 ligne en quelques minutes à longueur de journée.

Donc faut-il vraiment qu'il y ait un index sur nos tables temporaires, où cela ralentit-il au contraire les opérations sur les tables ?

Je précise aussi que nous avons parfois plusieurs index, mais ce numéro unique par utilisateur est toujours présent dans chaque index.

Merci d'avance pour votre aide

[ldiaz]

Bonjour,

ton index sera util si la selectivité est bonne sur ta table. On entend par selectivité le fait qu'il y est beaucoup d'ID differents. Exemple.
Si dans une table tu fais un index sur "genre" ici homme ou femme. Ta selectivité risque d'etre naze vu que sur 100 000 lignes tu peux avoir je sais pas: 40 000 hommes et 60 000 femmes.
Donc si tu filtre ta requete par

select blabla, blibli from table
where genre ='homme';

Tu risques d'avoir un full scan et donc une query lente.
Tel que tu explique, cet ID est unique, donc la selectivité sera bonne, donc pas de full scan, sauf si ta table au moment de la requete n'a que tres peu de ligne. Dans ce cas l'optimizeur d'oracle risque de preferer le full scan.

Donc je ferais un index sur la table.

D'autre part pense a executer des stats regulieres d'autant plus si la quantite de ligne change souvent.

Aussi tu peux sortir les snap de toute une journée et regarder le top 5 events avec index et sans index. La probablement tu verras une diference.

J'espere que cela pourra t'aider.

Ciao

[jeremzzz]

Vu le client dont il s'agit, j'ai très rarement peu de lignes, ça tourne souvent à plus de 5000 et ça monte facilement à 100 000 voire même 400 000 parfois (s'il n'y a qu'un seul utilisateur qui utilise le programme)

Merci pour les informations. Je laisse donc mes index.

Pour le moment, j'ai mis en place un script qui recréé la table tous les week end. C'est pas le mieux, mais au moins je n'ai plus mes problèmes de performances.

[ldiaz]

Tu peux aussi faire un simple test

Tu fais une requete avec une conditions where qui utilise un champ indexé (ou pas).
Avant la requete tu fais un set autotrace on
comme ça tu verras les consistent gets et phsysical reades.
Tu test avec et sans index et tu verras les differences.
L'objectif etant d'avoir un minimum de physical reads vu qu'il s'agit des lecture a disque, les plus couteuse, les consistent gets etant les acces aux block de données en memoire (plus rapide).

Bonne chasse

[pachot]

Bonjour,

Ayant parfois plusieurs utilisateurs, nous utilisons un numéro d'identifiant unique par utilisateur qui n'est pas clé primaire sur la table puisque, dans le cas d'une commande par exemple, un utilisateur peut avoir x lignes de commande donc x fois ce numéro.

Il pourrait par contre faire partie de la clé peut-être, même s'il nêst pas identifiant à lui tout seul.

(allant de 6i à 10g)

6i ça n'existe pas.
Est-ce que tu utilise des global Temporary Tables ? Ce serait bien si chaque session ne doit voir que ses données.

Cordialement,
Franck.

[carreau]

bonjour,

De manière similaire et en complément de la discussion,

j'interroge des tables non indexées avec une requete très peu performante
lorsque j'ajoute des index, je divise grosso modo le cout de ma requête par 10

Mais je me pose la question de savoir si l'ajout des index va pénaliser l'insertion

Je ne sais pas comment mesurer la perte de temps et le cout des index à l'insertion
Pourriez vous m'indiquer une méthode ? des outils ?

Merci d'avance
Jerome

[FSiebert]

Bonjour,

Un index pénalise l'insertion d'environ 25%, 33% mais ce n'est pas pour ça qu'un index n'est pas une bonne idée

À lire (par exemple) cet article : http://blog.developpez.com/sqlpro/p7..._d_index_ni_de

[ldiaz]

Salut

et bien oui si la table possede un ou plusieurs index, l'insertion sera plus couteuse. Ce que tu peux faire c'est creer ta table avec l'option NOLOGING, mettre la base en NOARCHIVE, pour le reste tu dois pouvoir decider si l'impacte sur l'insert est plus penalisant que ne pas avoir les index au moment de faire les select. (ufff ma phrase est tordue non?)

[mnitu]

Quels sont vos problèmes de performances et comment se manifestent-ils ? Avez-vous fait une trace SQL étendue ou autre analyse des traitements concernées ? C’est plutôt de cette manière que vous pouvez envisager une bonne solution et moins sur le bout du nez et divers conseils trouvés sur internet.

Le fait d’utiliser des versions différents allant de 8i à 10g c’est embêtant parce qu’il y a des différences énormes en termes d’optimisation entre ces deux versions.

La forte variation en nombre des enregistrements des tables appelle pour l'échantillonnage dynamique (dynamic sampling) mais il faut bien voir de que on parle. Aujourd’hui lire 50 000 enregistrements en balayage complet de la table (full table scan) c’est rien.

Vos problèmes des performances peuvent être tout à fait liés à la manière dans laquelle vous utilisez la base, c’est fous ce que les gens peuvent faire parfois. Du ce point de vue la seule démarche correct est d’analyser les traitements qui posent des problèmes de performance, de comprendre l’origine de ces problèmes ce qui permettra d’identifier et évaluer les solutions à mettre en ouvre !

[StringBuilder]

Je me pose une question à propos du "coût" d'un index en termes d'insertion.

Si j'ai une contrainte d'unicité sur une colonne ou un tuple (typiquement, la clé primaire), lors d'un insert, le SGBD n'aura-t-il pas un avantage à avoir un index sur le tuple, afin d'identifier immédiatement la présence ou non de doublon ?

En effet, dans une table sans index ni contrainte, effectivement, l'insert se contente d'ajouter comme un bourrin les données dans la table sans contrôle, et sera donc plus rapide sans index.

Mais à partir du moment où une contrainte apparaît, je me demande si la présence d'un index ne permettra pas d'accélérer les insertion : entre le temps d'ajouter une entrée dans un index, et le temps à faire un full-scan sur une table entière pour vérifier l'unicité, je pense que l'avantage est à l'utilisation de l'index.

[pachot]

Bonjour,

Je ne sais pas comment mesurer la perte de temps et le cout des index à l'insertion
Pourriez vous m'indiquer une méthode ? des outils ?

Ca c'est une très bonne idée de le mesurer, parce que se baser sur des pourcentages données au pif ne signifie rien.

Sur la théorie:
- l'insert dans la table va modifier en principe un seul block
- pour chaque index il y a au moins un block feuille modifié, parfois plus.
- en plus pour chaque index il a fallu le parcourir, donc lire 2 ou 3 blocs suivant sa hauteur
- chaque block modifié va générer de l'undo (faible pour des inserts) et du redo

Donc le premier index va beaucoup diminuer les perfs par rapport à une table sans index. Mais de toute façon il y en a au moins déjà un pour la PK.
C'est sûr que s'il y a déjà 10 index, le 11ème ne va pas changer grand chose.

Et pour le 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
 
SQL> create table test as select rownum a,rownum b,rownum c,rownum d from dual connect by level <1000;
 
Table created.
 
SQL> insert /*+ append */ into test select t1.a,t1.b,t1.c,t1.d from test t1, test t2;
 
998001 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> select count(*) from test;
 
  COUNT(*)
----------
    999000
 
SQL>

Là on a une table avec presque un million de lignes.

Je vais créer à chaque fois un index de plus, et utiliser autotrace pour voir combien de blocs en plsu l'insert doit toucher.

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
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
111
112
113
114
115

SQL> set autotrace on stat
SQL> insert into test values (0,0,0,0);

1 row created.


Statistics
----------------------------------------------------------
          0  recursive calls
          1  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        722  bytes sent via SQL*Net to client
        487  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> create index test_a on test(a);

Index created.

SQL> insert into test values (0,0,0,0);

1 row created.


Statistics
----------------------------------------------------------
          1  recursive calls
          6  db block gets
          1  consistent gets
          2  physical reads
          0  redo size
        722  bytes sent via SQL*Net to client
        487  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> create index test_b on test(b);

Index created.

SQL> insert into test values (0,0,0,0);

1 row created.


Statistics
----------------------------------------------------------
          1  recursive calls
          9  db block gets
          1  consistent gets
          2  physical reads
          0  redo size
        722  bytes sent via SQL*Net to client
        487  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> create index test_c on test(c);

Index created.

SQL> insert into test values (0,0,0,0);

1 row created.


Statistics
----------------------------------------------------------
          1  recursive calls
         12  db block gets
          1  consistent gets
          2  physical reads
          0  redo size
        722  bytes sent via SQL*Net to client
        487  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> create index test_d on test(d);

Index created.

SQL> insert into test values (0,0,0,0);

1 row created.


Statistics
----------------------------------------------------------
          1  recursive calls
         15  db block gets
          1  consistent gets
          2  physical reads
          0  redo size
        722  bytes sent via SQL*Net to client
        487  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autotrace off

Je regarde surtout les 'db block gets', ceux qui sont nécessaire à la modification de la table ou des index.
pas d'index -> 1 block touché
1 index -> 6 blocks
2 index -> 9 blocks
3 index -> 12 blocks
4 index -> 15 blocks

A chaque index, c'est 3 blocks de plus, donc probablement 2 branches et une feuille. On vérifie la hauteur des index:

Code :

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

1
2
3
4
5
6
7
8
SQL> select index_name,blevel from user_indexes where index_name like 'TEST__';
 
INDEX_NAME                         BLEVEL
------------------------------ ----------
TEST_A                                  2
TEST_B                                  2
TEST_C                                  2
TEST_D                                  2

C'est bien ça...

Donc maintenant vous savez comment tester l'impact sur les performances de l'insert.

Cordialement,
Franck.

[StringBuilder]

Je vois pas en quoi faire un trigger qui effectue un contrôle et provoque une erreur si le contrôle n'est pas vérifié ne pourrait pas marcher ! Vous dites n'importe quoi mnitu !

Ensuite, soit un table "tableA" avec les colonnes "col1", "col2", "col3", "col4" de type int.

Soit les règles suivantes :
- Quand "col1" = 1 alors le tuple (col1, col2, col3) doit être unique
- Quand "col1" = 2 alors aucun contrôle d'unicité est fait
- Quand "col1" prend une autre valeur, alors le tuple (col1, col2) doit être unique

On aura ce genre de situation dans une table de travail pour un rapport statistique par exemple. On recopie dans la table des informations sur des commandes (col1 = 1, col2 = commande.id, col3 = lignecommande.numero). On peut aussi y stocker des appels téléphoniques, à ce moment, col2 = appel.id et il n'y a pas de "lignes" dans un appel). Etc.

Impossible de mettre une simple contrainte check ou de créer un index unique.
Si on veut blinder l'alimentation de la table, il faudra alors faire un trigger qui s'occupera de vérifier les règles, à grand coup de count(*) sur les colonnes col1, col2 et éventuellement col3.
Je vois pas ce qui pourrait poser le moindre problème à Oracle.

En revanche, en l'absence d'index, le trigger va mettre trois plombes à contrôler les règles de gestion.

[Waldar]

C'est votre vocabulaire qui n'était pas précis. J'ai compris et je pense que mnitu me suivra que vous parliez d'une contrainte ALTER TABLE ADD CONSTRAINT... et pas d'une règle de gestion.

Et encore, votre règle de gestion est tout à fait portable dans les règles classique avec une modélisation type héritage,

[skuatamad]

Vous dites n'importe quoi mnitu !

Je ne crois pas...

On recopie dans la table des informations sur des commandes (col1 = 1, col2 = commande.id, col3 = lignecommande.numero). On peut aussi y stocker des appels téléphoniques, à ce moment, col2 = appel.id et il n'y a pas de "lignes" dans un appel). Etc.

Je ne suis pas particulièrement convaincu par le modèle évoqué mais je pense que l'idée était plutôt d'évoquer un contexte avec des contraintes complexes.

Je vois pas ce qui pourrait poser le moindre problème à Oracle.

MUTATING TABLE ! en gros insert into values <=> OK mais insert into select <=> impossible...
Donc tout dépend du contexte, exemple (je n'ai codé que la 1ere contrainte et que en mode insert):

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
SQL> create table t (c1 number, c2 number, c3 number, c4 number)
  2  /
 
Table created.
 
SQL> 
SQL> create or replace trigger trig before insert on t
  2  for each row
  3  begin
  4    if (:new.c1 = 1) then
  5      for c in (select 1
  6                  from t
  7                 where c1 = :new.c1
  8                   and c2 = :new.c2
  9                   and c3 = :new.c3
 10                   and rownum = 1)
 11      loop
 12        raise_application_error(-20001,'tuple c1,c2,c3 already exist');
 13      end loop;
 14    end if;
 15  end;
 16  /
 
Trigger created.
 
SQL> show err
No errors.
SQL> insert into t values (1,1,1,1);
 
1 row created.
 
SQL> insert into t values (1,2,2,2);
 
1 row created.
 
SQL> insert into t values (1,2,2,3);
insert into t values (1,2,2,3)
            *
ERROR at line 1:
ORA-20001: tuple c1,c2,c3 already exist
ORA-06512: at "SKUATAMAD.TRIG", line 10
ORA-04088: error during execution of trigger 'SKUATAMAD.TRIG'

Ca a l'air de marcher mais :

Code :

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

1
2
3
4
5
6
7
SQL> insert into t select 1,2,3,4  from dual;
insert into t select 1,2,3,4  from dual
            *
ERROR at line 1:
ORA-04091: table SKUATAMAD.T is mutating, trigger/function may not see it
ORA-06512: at "SKUATAMAD.TRIG", line 3
ORA-04088: error during execution of trigger 'SKUATAMAD.TRIG'

Impossible de mettre une simple contrainte check ou de créer un index unique.

FBI ! Le MVCC implémenté par Oracle (et bien d'autres) présente de nombreux avantages concernant l'accès aux données (et les locks associés) mais présente des inconvénients sur les contraintes implémentées par trigger.
Par contre Oracle offre également d'autres fonctionnalités :

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
74
75
76
77
78
SQL> drop table t
  2  /
 
Table dropped.
 
SQL> create table t (c1 number, c2 number, c3 number, c4 number)
  2  /
 
Table created.
 
SQL> 
SQL> create unique index idx1_t on t (
  2    case when c1 = 1 then c1 end,
  3    case when c1 = 1 then c2 end,
  4    case when c1 = 1 then c3 end)
  5  /
 
Index created.
 
SQL> create unique index idx2_t on t (
  2    case when c1 not in (1, 2) then c1 end,
  3    case when c1 not in (1, 2) then c2 end)
  4  /
 
Index created.
 
SQL> insert into t select 1,2,3,4  from dual;
 
1 row created.
 
SQL> insert into t select 1,2,3,4  from dual;
insert into t select 1,2,3,4  from dual
*
ERROR at line 1:
ORA-00001: unique constraint (SKUATAMAD.IDX1_T) violated
 
 
SQL> insert into t select 1,3,3,4  from dual;
 
1 row created.
 
SQL> insert into t select 2,2,2,2  from dual;
 
1 row created.
 
SQL> insert into t select 2,2,2,2  from dual;
 
1 row created.
 
SQL> insert into t select 3,2,2,2  from dual;
 
1 row created.
 
SQL> insert into t select 3,2,2,2  from dual;
insert into t select 3,2,2,2  from dual
*
ERROR at line 1:
ORA-00001: unique constraint (SKUATAMAD.IDX2_T) violated
 
 
SQL> insert into t select 3,3,2,2  from dual;
 
1 row created.
 
SQL> select * from t;
 
        C1         C2         C3         C4
---------- ---------- ---------- ----------
         1          2          3          4
         1          3          3          4
         2          2          2          2
         2          2          2          2
         3          2          2          2
         3          3          2          2
 
6 rows selected.
 
SQL>

Bon inutile de dépenser de l'energie à chercher des cas où les FBI unique ne sont pas suffisant, nous savons tous qu'ils existent, mais en l'occurrence (sauf erreur de ma part sur mes tests plutôt succints) l'exemple choisi n'avait rien d'impossible à être traité en mode déclaratif.

[mnitu]

Je vois pas en quoi faire un trigger qui effectue un contrôle et provoque une erreur si le contrôle n'est pas vérifié ne pourrait pas marcher ! Vous dites n'importe quoi mnitu !

Peut être qu'en lisant un peu la doc et faisant quelques tests pourrait vous aider à mieux comprendre pourquoi votre solution ne marche pas.

[Mohamed.Houri]

Je vois pas en quoi faire un trigger qui effectue un contrôle et provoque une erreur si le contrôle n'est pas vérifié ne pourrait pas marcher ! Vous dites n'importe quoi mnitu !

A chaque fois, dans ce genre de situation, je rappelle un principe fondamental que tout utilisateur de la base de données doit avoir à l’esprit à savoir : vous n’êtes pas tout seul à utiliser la base de données. Pensez aux accès concurrents, pensez à deux utilisateurs différents lançant le même trigger au même moment et faisant des vérifications simultanées. L’un peut ne pas voir les changements de l’autre et vice versa.

De plus, pensez un instant à l’avantage que peut apporter un index ou une contrainte au CBO par rapport au trigger ; non seulement la contrainte accomplie bien son rôle de vérification en mode multiutilisateurs mais en plus elle fourni, d’importantes informations au CBO lui permettant de choisir un plan d’exécution adéquat (moyennant d’autres informations bien sûr telles que des statistiques à jour et représentatives, etc…)

Trigger, vérifications et accès concurrents ne font pas bon ménage.