Discussion :

[farenheiit]

Sur ma table j'ai actuellement 2 indexes sur 3 (l'indexe numero 2 et l'index numéro3).

J'ai essayé de recréer l'index numero1 mais j'obtiens une erreur ORA-00603 ORACLE server session terminated by fatal error => je ne sais pas pourquoi

les indexes présents sont le numéro 2 basé sur les champs NUBCR(number(10)) et NUFCR(number(6)) et l'indexe 3 qui est basé sur NTUBX (number(6)) seulement.

Je rappelle que l'index qui posait problème était l'index numéro2.

L'insert s'effectue en 40 minutes et voici le nb de logical reads effectués pour chacun des indexes:

Code :

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

1
2
3
 
Index 2 = 2 321 296 logical reads
index 3 = 165 440 ogical reads

L'index 2 induit 14 fois plus de logical reads...

[pachot]

Bonjour.
Pour fatal error, il faudrait voir le fichier dump.
pour les 2 millions de blocs, il faudrait voir:
- les stats de l'index avant et après
- les stats de session lors de l'insert
Cordialement,
Franck.

[farenheiit]

Bonjour.

- les stats de l'index avant et après
- les stats de session lors de l'insert

Quand tu parles des stats de l'index ce sont les stats dans v$segment_statistics ?
Si oui les nombres que je t'ai donné correspondent au differentiel entre avant et après l'insert.

qu'entends tu parles des stats de session lors de l'insert ? ce sont les stats dans v$session_wait et v$system_events?

[pachot]

Dans user_indexes après un analyze, pour voir le nb de blocs, la hauteur, etc.

[farenheiit]

Finalement on m'a fourni un nouvelle base de test.
J'ai bien maintenant mes 3 indexes.
L'insert met plus de 30 minutes et j'ai toujours 14 fois plus logical reads sur l'index 2 que sur l'index 1 et 3.

j'ai collecté les stats avec un dbms_stat (echantillon de 5%).

Voici les stats sur la table:

Code :

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

1
2
3
4
5
6
 
  NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO
 
---------- ---------- ------------ ---------- ----------- ------------------------- ------------------- ----------------- -------------------
 
  73954700    4677112            0          0         139                         0                   0

et voici les stats sur les indexes:

Code :

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

1
2
3
4
5
6
7
8
9
10
 
INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR   NUM_ROWS AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO
 
------------------------------ ---------- ----------- ------------- ----------------------- ----------------------- ----------------- ---------- ----------------- -------------------
 
INDEX1                                  3      380980           334                    1140                 6025              2012520   75403180
 
INDEX2                                  3      203600       1614204                       1                  666             72771560   73242680
 
INDEX3                                  3      269080           158                     1703                12006              1897060   73224720

Mon problème est toujours de savoir pourquoi j'ai autant de LR sur l'index 2.
Par ailleurs lorsque je recrée l'index 2 sur la 1ère colonne seulement j'obtiens un gain de temps (moitié moins)

[farenheiit]

J'ai exécuté l'insertion en mode parallel 4 et l'insert passe à 2 minutes => extraordinaire

mais je veux comprendre pourquoi mon index 2 génère autant de logical reads...

[pachot]

Bonjour,

En fait chaque index a une hauteur de 3 branche, donc on peut s'attendre à ce qu'un insert aille voir 5 blocks (3 branches, une feuille, un block de table )
Donc 2321296 de logical reads pour 500000 inserts, celà me parait normal.
Maintenant, pourquoi l'index 3 ne fait que 165440 ? La seule chose que je vois, c'est que l'ordre des enregistrements dans la table est proche de l'ordre des index 1 et 3 (clustering factor plutôt proche du nombre de feuilles) et pas du tout proche de l'ordre de l'index 3 (clustering factor proche du nombre d'enregistrements)
Donc, comme on peut supposer que l'ordre de la table reflète l'ordre dans lequel les enregistrements sont insérés, il semble que lors de l'insert, il y a de grande chances que 2 inserts adjacents touchent aux mêmes blocs des index 1 et 2 (donc ils sont toujours en cache, et peut-être même toujours pinnés) mais que pour l'index 3 c'est à chaque fois un bloc différent.

C'est juste une hypothèse...

Cordialement,
Franck.

[farenheiit]

comment vois tu l'ordre des enregistrements dans la table ?
Peux tu expliquer le principe du clustering_factor?

[pachot]

En gros, il indique si des entrées d'index adjacentes on des chances de correspondre au même block de table. Si il est proche du nombre d'enregistrement, alors cela signifie que les enregistrements correspondant à des entrées d'index proches se retrouvent disperdées partout dans la table. S'il est proche du nombre de feuilles de l'index, c'est que les entrées d'une feuille (donc proches en valeur) correspondent à des enregistrements proches dan la table (même bloc)

[farenheiit]

t'as pas un exemple ? j'ai du mal...

[pachot]

Bonjour,

Je te confirme mon hypothèse:
Un insert va toucher 5 blocs (3 branches, 1 feuille et 1 table)
Donc 500000 insert vont toucher 2500000 blocs

C'est le cas de ton index 2.

Cependant, il se peut que un insert concerne des valeurs pour les colonnes d'index identiques au précédant insert. Donc, l'entrée d'index va dans les même bloc que le précédent. Alors ce n'est pas un nouvau logical read car la session est déjà dessus.

Ce qui signifie que si l'ordre dans lequel tu fait tes inserts suit l'ordre de l'index, tu auras beaucoup moins de logical reads. C'est le cas de ton index 3.

Je te fais un exemple vite fait:

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
SQL> create table TEST ( a varchar2(10) , b varchar2 (10) );
Table created.

SQL> create index TESTA on TEST ( a );
Index created.

SQL> create index TESTB on test ( b );
Index created.

SQL> /* add highst value to avoid leaf node 90-10 splits :*/
SQL> insert into TEST values ('Z','Z');
1 row created.

SQL> /* insert 100000 rows with same values for a and b but b being in opposite order, so a is inserted in order but not b */
SQL> insert into TEST ( a , b ) select to_char(rownum,'FM0999999999') , reverse(to_char(rownum,'FM0999999999')) from dual connect by level <= 100000;
100000 rows created.

SQL> select name,value from v$mystat join v$statname using(statistic#) where (name like '%sort%' or name like 'db block%' or name like 'consistent%' or name like '%pinned%' or name like '%index%' or name like '%branch%' or name like '%leaf%') and value>0;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
db block gets                                                        210533
db block gets from cache                                             210533
consistent gets                                                      104845
consistent gets from cache                                           104845
consistent gets - examination                                         27733
db block changes                                                     210773
consistent changes                                                       61
pinned buffers inspected                                                 20
index crx upgrade (positioned)                                            6
leaf node splits                                                        524
index fetch by key                                                    51757
index scans kdiixs1                                                       8
buffer is pinned count                                                51634
buffer is not pinned count                                            99723
sorts (memory)                                                           13
sorts (rows)                                                             35

SQL> select statistic_name,object_name,value from v$segment_statistics
  2  where owner=user and object_name in ('TEST','TESTA','TESTB') and value>0 order by 1,2;

STATISTIC_NAME                                                   OBJECT_NAME                         VALUE
---------------------------------------------------------------- ------------------------------ ----------
db block changes                                                 TEST                                 1872
                                                                 TESTA                                4112
                                                                 TESTB                              100624

logical reads                                                    TEST                                 3264
                                                                 TESTA                                5184
                                                                 TESTB                              200224

SQL> analyze index TESTA compute statistics;
Index analyzed.

SQL> analyze index TESTB compute statistics;
Index analyzed.

SQL> select index_name,blevel,leaf_blocks,distinct_keys,clustering_factor,num_rows,avg_leaf_blocks_per_key,avg_data_blocks_per_key
  2  from user_indexes where index_name in ('TESTA','TESTB') order by 1;

INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR   NUM_ROWS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
------------------------------ ---------- ----------- ------------- ----------------- ---------- ----------------------- -----------------------
TESTA                                   1         270        100001               186     100001                       1                       1
TESTB                                   1         256        100001            100001     100001                       1                       1

tu vois à la fin que mes 2 indexes font la même taille. La seule différence est le clustering factor qui reflète le fait que la table TEST est physiquement triée de manière proche de TEST1 (car TEST1 indexe la colonne 1 que j'ai incrémenté un à un durant l'insert ) mais très éloignée de TEST2 (car j'ai pris l'inverse du nombre pour distribuer les valeurs un peu partout)


C'est un peu ton cas.

Et tu vois plus haut comme le nombre de 'db block changes' est différent sur chaque index.

Comment fais-tu ton insert ? Tu devrais faire un insert en direct-path (avec /*+ APPEND */) et dans ce cas tu n'aura plus ce problème car:
-> oracle va inserer toutes els données dans la table sans toucher aux indexes
-> pour chaque index, lire ces données, les trier et mettre à jour l'index

Donc, les données étant triées pour chaque index, tu n'aura plus ce pb.

D'ailleurs, c'est pour celà que c'était rapide en PARALLEL(4) , parce que APPEND est là par défaut en parallèle


Cordialement,
Franck.

[farenheiit]

Effectivement avec le append je suis à 2 minutes aussi.

Voici mon insert:

Code :

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

1
2
3
4
5
6
7
8
9
 
INSERT INTO ITPCRO (NTUBX,CTCRO,NUTRA,DAHIS,COMAR,ID_COINC,ID_NUCPT,ID_COVAV,ID_NUVAV,ID_CNACT,CNACH,RGCOD,RGCID,CODEV,MONT1,MONT2,MONT3,MONT4,MONT5,
CCARV,CTCPT,CTSTR,DAVAL,DATRA,CAECH,CMECH,MTSNA,CSOPT,NUVER,NUINS,CNACN,NUBCR,NUFCR,CTOPE, IDMAN) 
SELECT 126611,'M1',M.NUTRA,TO_DATE('25062009','DDMMYYYY'),C.COMAR,  '316',  '397',I.IDINT,A.ID_NUCPT,N.ID_CNACT,C.CNACH,C.RGCOD,C.RGCID,C.CODEV,
C.MMARG,C.MMACJ,C.MMADJ,C.MMACV,C.MMADV,C.CCARV,C.CTCPT,C.CTSTR,C.DAVAL,C.DATRA,C.CAECH,C.CMECH,C.MTSNA,C.CSOPT,C.NUVER,C.NUINS,N.CNACN,C.NUBCR,
C.NUFCR,C.CTOPE,C.IDMAN 
FROM TMPMAR C,MULDIF M,NATACF N,INTERV I,COMPTE A 
WHERE A.COINT=C.COINF AND A.NUCPT=C.NUCPF AND C.COMAR IN ('ICE') AND C.CTMAR IN ('N','F')           
AND C.CTPMA IN ('O','P') AND M.COMAR=C.COMAR AND N.CNACT(+)=C.CNACT AND I.COINT=C.COINF AND I.CTINT='F'

On se rend compte que les champs sur lesquels sont basés les indexes 1 (DAHIS et CTCRO) et 3 (NTUBX) se situent au début du select alors que les champs sur lesquels sont basés l'index 2 (NUBCR et NUFCR) sont vers la fin du select.

Si ton analyse est juste il suffirait de mettre les champs NUBCR et NUFCR au début du select pour faire baisser le nombre de LR sur l'index 2 ?
j'ai essayé pourtant et ça ne marche pas.

[pachot]

Non, c'est l'ordre des enregistrements.

Ce que je veux dire c'est que par exemple tu insère dans cet ordre là:
DAHIS=1 NUBCR=12 NTUBX=20
DAHIS=2 NUBCR=52 NTUBX=21
DAHIS=3 NUBCR=97 NTUBX=22
DAHIS=4 NUBCR=20 NTUBX=25
DAHIS=5 NUBCR=82 NTUBX=30

Donc, la mise à jour dans l'index 2 se fait à chaque fois à un endroit très différent car les valeurs de NUBCR sont très dispersées

Par contre les autres indexes on des valeurs consécutives.

Imagines que tu doive classer une pile de document par année de publication. Tu as une pile 2005, une pile 2006, etc.

Ce sera beaucoup plus facile pour toi si je te les donne dans l'ordre, ou presque.

[farenheiit]

Effectivement j'ai lancé l'insert en rajoutant un order by sur NUBCR et NUFCR dans le select et la requête s'execute en 4 minutes.
C'est toi le plus fort Francky

merci bcp de t'être investi sur mon problème j'ai appris bcp de choses grâce à toi.

[breizh76]

Et si on ré-organise ta table en suivant l'index 2. Du coup, on a plus a mettre l'order by. non ???

[farenheiit]

Et si on ré-organise ta table en suivant l'index 2. Du coup, on a plus a mettre l'order by. non ???

comment ça?

[Waldar]

Je pense qu'il fait allusion à une IOT, mais de mémoire ça se fait sur la PK uniquement non ?

[pachot]

Et si on ré-organise ta table en suivant l'index 2. Du coup, on a plus a mettre l'order by. non ???

Bonjour,
Réorganiser la table en suivant l'index 2, pour que les enregistrements soient physiquement triés suivant l'index 2, celà peut être bon pour un index range scan.
Mais ici, il s'agit de l'ordre dans lequel c'est inséré.

Si je regardais le clusturing factor, c'est pour le raisonnement suivant:
clustering factor élevé => enregistrements physiques dans 'mauvais ordre' => probablement insert dans 'mauvais ordre'.

Mais le pb de départ, c'est l'ordre de l'insert, d'où le order by.
L'ordre physique dans la table n'est que secondaire ici, juste une conséquence de la même cause.

Mais la vrai solution au problème c'est /*+ APPEND */ car:
- très peu de undo pour la table
- on l'a vu, mimimum de logical reads sur index
- donc à nouveau moins de undo pour les index
- tout celà fait moins de redo aussi
- en en plus so noarchivelog ou si nologging, pas de redo pour la table non plus

Cordialement,
Franck.

[farenheiit]

Bonjour,


Mais la vrai solution au problème c'est /*+ APPEND */ car:
- très peu de undo pour la table
- on l'a vu, mimimum de logical reads sur index
- donc à nouveau moins de undo pour les index
- tout celà fait moins de redo aussi
- en en plus so noarchivelog ou si nologging, pas de redo pour la table non plus

Cordialement,
Franck.

oui mais les incovenients sont les suivants:
- insertion après HWm donc les blocs vides sous le HWM ne seront jamais remplis sauf si on réorganise la table
- la table est lockée pendant l'insert donc pas de mise à jour possible pour une autre session

[pachot]

Dans ce cas, insertion de 500 000 lignes, je suppose que ce n'est pas une opération courante online, et que perdre quelques blocs n'est pas trop gênant.
Mais oui, tu fais bien de le préciser.