Discussion :

[farenheiit]

Bonjour,

Qu'est ce qui pourrait expliquer qu'un insert select dure très longtemps alors que le select s'execute très rapidement. Il n' y'a pas de trigger sur la table mais il y'a 3 indexes B-TREE.
Le select s'execute en 1 minute et retourne 500 000 lignes (test effectué avec create table as select) mais l'insert+select dure 40 minutes.

Est-ce que le fait que les indexes soient fragmentées (plus de 50% de feuilles vides pour 2 des 3 indexes) peut jouer?
peut-il y'avoir d'autres causes?

il s'agit d'une Base 10G R2

[pachot]

Bonjour,

Un create table as select, donc sans indexes, peut être très rapide, surtout sur une base en noarchivelog mode (ou sur une table en nologging) car c'est un accès direct sans passer par le buffer cache, sans génération d'undo et sans génération de redo.
La mise à jour des 3 indexes, une dizaine de blocks à aller voir pour chaque enregistrement, les modifiers en buffer cache, généréer du redo, de l'undo et du redo pour le undo...
Donc un facteur 40 ne me parait pas hallucinant.

Le fait qu'il y ait de la place vide dans les feuilles est plutot pas mal. L'insert a besoin de place vide et s'il n'y en avait pas il devrait faire des splits de blocs, beaucoup plus coûteux.

En fonction du volume de donnée qu'il y a avant dans la tables, il peut être interressant de mettre les index unusable plus de les rebuilder à la fin.

Cordialement,
Franck.

[farenheiit]

pas compris l'histoire des dizaines de block à aller voir par enregistrement.
Il me semblait que dans un index les feuilles vides n'étaient pas comblées et restaient toujours vides???

[pachot]

La dizaine de blocs, c'est parce qu'il y a 3 indexes, qu'il faut descendre 1 ou 2 branches, et mettre à jour 1 feuille.

Il me semblait que dans un index les feuilles vides n'étaient pas comblées et restaient toujours vides???

Oh non ! Imagine que ce soit le cas ... les indexes grossiraient à vue d'oeuil

Non, la place libérée dans une feuille peut être réutilisée dès que la transaction qui l'a libéré est commitée. Bien sur, pas par n'importe quel insert. Une feuille correspond à un tranche de valeur pour les colonnes indexées. Donc on ne peut y mettre que des enregistrements qui sont dans la même tranche.
Par contre, lorsque la feuille est totalement vide, là elle peut être réutilisée n'importe où.

Un peu de place libre partout, c'est le meilleur moyen de pouvoir insérer des données rapidement. C'est pareil à la bibliothèque. Chaque étagère de chaque rayon a un peu de place vide. Donc facile d'ajouter un livre quelque part.

Imagine que l'on compacte tout sur quelques étagères complètement pleines: au premier nouveau livre, il faudra tout réaménager ! Celui qui aura fait la réorganisation sera content de lui (gain de place) mais ceux qui vont bosser après lui vont galérer. C'est souvent ce qu'il se passe lorsqu'on réorganise les indexes un peu trop régulièrement...

Cordialement,
Franck.

[farenheiit]

Ba dans ce cas à quoi sert le rebuild alors?
T'as pas une preuve de ce que tu avances par hasard?

[farenheiit]

Je répond à ta place: en fait le rebuild sert surtout pour les scan des indexes car en reconstruisant on a un arbre plus compact donc moins de blocks à parcourir. c'est ça?

[pachot]

Ba dans ce cas à quoi sert le rebuild alors?

Il y a de rares cas où celà peut être utile si l'on purge des données et que l'on sait qu'on ne va jamais inserer de nouveux enregistrements dans les mêmes valeurs.

Par exemple, index sur date.
Si on purge 100% des données antérieures au 1er janvier, là les feuilles correspondantes seront complètement vies et pourront être réutilisées par n'importe que nouvel insert.
Mais si l'on purge 90% des données antérieures au 1er janvier là les feuilles correspondantes auront 10% d'espace libre, réutilisables seulement par des dates antérieures au 1er janvier. Alors si l'on est surs de ne pas inserer à nouveu des dates comme celles là, il peut être interressant d'amélioere celà. Mais un COALESCE est suffisant (et se fait online facilement) pas nécessairement besoin de faire un rebuild.

Cordialement,
Franck.

[farenheiit]

si j'ai 3 indexes sur la table avec une profondeur de 3.
ma requête insère 500 000 lignes.
Est-ce qu'on peut dire que chaque insert va mettre à jour les 3 indexes et donc chaque insert va engendrer environ 10 logical reads et qu'au final l'insertion des 500 000 lignes va engendrer 10 * 500 000 = 5 millions de logical reads????

[pachot]

Effectivement un scan d'index aura moins de feuilles à lire si elles sont très compactes. Si tout d'un coup la table devient read only, alors c'est une bonne idée de tout compacter, tout comme on peut reconstruire la table avec pctfree=0

Et pour la preuve que les feuilles vides ne restent pas vides (vite fait):

Création d'une table avec index:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL> create table TEST_TABLES as select * from dba_objects;
Table created.
 
SQL> create index TEST_INDEX on TEST_TABLES ( object_type ) pctfree 50;
Index created.
 
SQL> analyze index TEST_INDEX compute statistics;
Index analyzed.
 
SQL> select index_name,leaf_blocks from user_indexes where index_name='TEST_INDEX';
 
INDEX_NAME                     LEAF_BLOCKS
------------------------------ -----------
TEST_INDEX                              69

69 feuilles. On supprime tout et on ré-insert tout:

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
SQL> delete from TEST_TABLES ;
24587 rows deleted.
 
SQL> commit;
Commit complete.
 
SQL> insert into TEST_TABLES select * from dba_objects ;
24588 rows created.
 
SQL> commit;
Commit complete.
 
SQL>
SQL> analyze index TEST_INDEX compute statistics;
 
Index analyzed.
 
SQL> select index_name,leaf_blocks from user_indexes where index_name='TEST_INDEX';
 
INDEX_NAME                     LEAF_BLOCKS
------------------------------ -----------
TEST_INDEX                              75

Seulement quelques blocs en plus. Si l'espace n'etait pas reutilisé, on en aurait le double !

On recommance plusieurs fois:

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
SQL> delete from TEST_TABLES ;
24588 rows deleted.
 
SQL> commit;
Commit complete.
 
SQL> insert into TEST_TABLES select * from dba_objects ;
24588 rows created.
 
SQL> commit;
Commit complete.
 
SQL> delete from TEST_TABLES ;
24588 rows deleted.
 
SQL> commit;
Commit complete.
 
SQL> insert into TEST_TABLES select * from dba_objects ;
24588 rows created.
 
SQL> commit;
Commit complete.
 
SQL> delete from TEST_TABLES ;
24588 rows deleted.
 
SQL> commit;
Commit complete.
 
SQL> insert into TEST_TABLES select * from dba_objects ;
24588 rows created.
 
SQL> commit;
Commit complete.
 
SQL> delete from TEST_TABLES ;
24588 rows deleted.
 
SQL> commit;
Commit complete.
 
SQL> insert into TEST_TABLES select * from dba_objects ;
24588 rows created.
 
SQL> commit;
Commit complete.
 
SQL> delete from TEST_TABLES ;
24588 rows deleted.
 
SQL> commit;
Commit complete.
 
SQL> insert into TEST_TABLES select * from dba_objects ;
24588 rows created.
 
SQL> commit;
Commit complete.
 
SQL> analyze index TEST_INDEX compute statistics;
Index analyzed.
 
SQL> select index_name,leaf_blocks from user_indexes where index_name='TEST_INDEX';
 
INDEX_NAME                     LEAF_BLOCKS
------------------------------ -----------
TEST_INDEX                              75

L'index a atteint sa taille optimale. cela ne bouge plus. Alors on n'y touche plus tant que l'e comportement de l'appli ne change pas.
S'il y a de purges, des changements importants dans les données et dans leur utilisation, alors un coalese peut faire du bien, voire un rebuild.
Mais trais souvent dans ce cas, on va aussi réorganiser la table, alors le rebuild des index suivra.

Cordialement,
Franck.

[farenheiit]

si j'ai 3 indexes sur la table avec une profondeur de 3.
ma requête insère 500 000 lignes.
Est-ce qu'on peut dire que chaque insert va mettre à jour les 3 indexes et donc chaque insert va engendrer environ 10 logical reads et qu'au final l'insertion des 500 000 lignes va engendrer 10 * 500 000 = 5 millions de logical reads????

Je ne sais pas si t'avais vu ce message alors je le remet.

Concernant l'arbre B-TREE. Comment est-il possible que sa taille peut être supérieur à 3? Est-ce lié à ce qu'on appelle le split des blocs? Comment juge t'on qu'un arbre est déséquilibré ?

[pachot]

Oui on peut dire ça. Sans compter des splits de blocs eventuels, ni tout les blocks d'undo générés par tout ça. Un cout x10 pour mettre à jour les indexes n'est pas hallucinant du tout. C'est un peu pour celà qu'on évite de créer des indexes à tout va, c'est bien pour aller chercher des données, mais ça fait plus de boulot lors des mises à jour.

Une taille supérieur à 3, oui, pourquoi pas. Oui, c'est lié au split de blocs.

Plus de place dans une feuille => split de la feuille + rajout de l'info dans la branche supérieure
Et si plus de place dans la branche => split de la branche + rajout de l'info dans la branche supérieure
Etc...
Et si plus de place tout en haut (la racine) => split de la racine dans 2 nouvelles branches de niveau inférieur + nouvelle racine pour pointer sur celles là. Et là, l'index prends un niveau en plus.

Mais bien sur, plus de 3 niveaux, ca veut dire beaucoup beaucoup de feuilles...

Si déséquilibré signifie qu'il est plus profond pour certaines valeurs que pour d'autres, alors il est toujours equilibré (vu qu'un ajout de niveau ne se fait qu'à la racine). Dans un B-tree, la profondeur sera toujours la même, quelle que soit la valeur qu'on va chercher.

Cordialement,
Franck.

[farenheiit]

merci franck,

tes explications sont super par contre ton dernier paragraphe sur l'équilibre des indexes je n'ai pas trop compris ce que t'as voulu dire. pour moi un arbre B-TREE peut être déséquilibré si par exemple t'as une purge effectuée sur les feuilles de gauches alors qu'à droite tu rajoutes des donnés ca peut génèrer des splits de blocks et tu te retrouves avec un index plus profond du côté droit non?

Sinon pr en revenir à mon problème:
j'ai supprimé les 3 indexes et l'insert s'effectue en 14 secondes pour 500 000 lignes.
J'ai recrée le 1er indexe et l'insert se fait en 22 secondes.
Par contre quand je crée le 2ème indexe je passe à 22 minutes.

Voici les stats sur mes colonnes indexées:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
 select column_name, num_distinct, num_nulls, last_analyzed 
 from user_tab_columns 
 where table_name = 'ITPCRO' and column_name in ('DAHIS','CTCRO','NUBCR','NUFCR','NTUBX');
 
COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS LAST_ANAL
------------------------------ ------------ ---------- ---------
CTCRO                                    29          0 28-JUN-09
DAHIS                                    63          0 28-JUN-09
NTUBX                                   259          0 28-JUN-09
NUBCR                               2127935      48030 28-JUN-09
NUFCR                                   316      48030 28-JUN-09

l'index 1 est basé sur les champs DAHIS(date) et CTCRO (varchar2(2))
l'indexe 2 est basé sur les champs NUBCR(number(10)) et NUFCR(number(10)).
l'indexe 3 est basé sur NTUBX (number(6))

qu'est ce qui explique que l'indexe numéro 2 a un impact si enorme alors que le premier n'en a pas ?

Comment expliquer

[pachot]

et tu te retrouves avec un index plus profond du côté droit non?

Et non: un split ne block fait que 1 bloc se transforme en 2 blocs, mais au même niveau. donc pas de profondeur.
Le seul moment ou il y a ajout d'un niveau, c'est lors de split du block racine. Car un block racine, il ne peut y en avoir qu'un. Donc si on le dédouble, il faut rajouter un bloc racine au dessus.

Pour test 3 indexes, la taille des entrées d'index peut jouer beaucoup: une taille 2 fois plus grande, c'est 2 fois plus de feuilles, et ça peut bien faire qu'il y ait un niveau de plus.

Cordialement,
Franck.

[farenheiit]

Et non: un split ne block fait que 1 bloc se transforme en 2 blocs, mais au même niveau. donc pas de profondeur.
Le seul moment ou il y a ajout d'un niveau, c'est lors de split du block racine. Car un block racine, il ne peut y en avoir qu'un. Donc si on le dédouble, il faut rajouter un bloc racine au dessus.

Donc on ne peut pas avoir plus de 4 comme niveau de profondeur si seul le bloc racine peut spliter.

Pour test 3 indexes, la taille des entrées d'index peut jouer beaucoup: une taille 2 fois plus grande, c'est 2 fois plus de feuilles, et ça peut bien faire qu'il y ait un niveau de plus.

Qu'entends tu par taille des entrées d'indexes?
je viens d'essayer l'insert avec juste l'index numéro 3 et je passe à 1 minute.
Donc c'est bien mon index numéro 2 qui fout la merde.
Est-ce que le nombre de valeur distinctes et le nombre de valeurs NULLS peuvent jouer?

[pachot]

Donc on ne peut pas avoir plus de 4 comme niveau de profondeur si seul le bloc racine peut spliter.

Puorquoi ? si tu as 4 niveaux et plus de place dans le bloc racine, il y aura un 5ème niveau.

Code :

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

Qu'entends tu par taille des entrées d'indexes?

deux number(10) c'est plus long que un number(6) ... enfin celà dépends de la vrai taille de la valeur.

Code :

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

Est-ce que le nombre de valeur distinctes et le nombre de valeurs NULLS peuvent jouer?

Le nombre de valeurs distinctes, normalement non. Les entrées nulls oui puisqu'ils ne sont pas indexés dans un index b-tree.

[farenheiit]

deux number(10) c'est plus long que un number(6) ... enfin celà dépends de la vrai taille de la valeur.

Et tu crois que c'est ça qui explique qu'avec l'index numero 2 basé sur deux champs number(10) mettent 20 fois plus de temps qu'avec l'index numero 1 basé sur 2 champs également (une date et un varchar2(2)) ???

J'ai du mal à croire ça ....

[pachot]

Ce n'est qu'une hypothèse. Est-ce que tu peux faire le test avec les 3 indexes, puis:

select object_name,statistic_name,value from v$segment_statistics where (owner,object_name) in (select user,index_name from user_indexes where table_name='ITPCRO') and statistic_name in ('logical reads') order by statistic_name,object_name,value;

Celà donnera au moins une idée du nombre de logical reads sur chaque index durant l'insert et permettra d'aller plus loin.

[farenheiit]

Ok ça m'a l'air pas mal comme idée mais le problème c'est que la valeur des logical reads va se cumuler avec sa valeur précédente. comment faire pour réinitialiser ces valeurs sans avoir à redémarrer la base?

[pachot]

Pour avoir le delta, il faut lire les valeurs avant puis lire les valeurs après et faire la différence.
Mais vu que tu crée les indexes juste avant, la valeur avant ne doit pas être très grosse

[farenheiit]

pas bête toi!!!!
bon comme la création des indexes dure énormément de temps je vais lancer ça ce soir et effectuer la requête lundi.
Bon week end