J'ai eu un petit débat avec mes collègues DBAs la semaine dernière sur le fait que le clustering factor d'un index pouvait expliquer de mauvaises performances sur la mise à jour d'une table. Pour eux, le clustering factor n'impactait que l'opération RANGE SCAN de l'index lors d'un SELECT, alors que j’avançais l'idée que les mauvaises performances d'un insert par exemple pouvait être lié au fait d'avoir un index (à mettre à jour suite à la mise à jour de la table), et que cette mise à jour générait plus de logical reads selon la qualité du clustering factor (CF).
Je n'ai donc pas pu résister à l'idée de faire un petit test case pour le leur prouver.
On commence par créér une table T1 avec 2 colonnes. La table sera ordonnée selon la première colonne tandis que la 2ème colonne sera l'inverse de la première colonne.
Bien sûr on va créer un index sur chacune des 2 colonnes et vous vous en doutez surement, la première colonne aura un clustering factor très bon c-a-d proche du nombre de blocs de la table (car l'ordre d'insertion dans la table correspond aux entrées du 1er index) tandis que la seconde colonne aura un CF très mauvais c’est-à-dire proche du nombre de lignes.
SQL> CREATE TABLE T1 2 AS 3 SELECT LPAD (object_id, 10, '0') AS C1, reverse (LPAD (object_id, 10, '0')) AS C2 4 FROM dba_objects where 1=2; Table created. SQL> create index idx1 on T1(C1); Index created. SQL> create index idx2 on T1(C2);Index created.
Pour l'instant la table ne contient pas de lignes.
On va ensuite insérer les lignes (insert en mode conventionnel) et on va regarder le nombre de logical reads générés pour chaque index dans la vue V$SEGMENT_STATISTICS:
SQL> insert into T1
2 SELECT LPAD (object_id, 10, '0') AS C1, reverse (LPAD (object_id, 10, '0')) AS C2
3 FROM dba_objects
4 order by 1;
57633 rows created.
SQL> commit;
Commit complete.
SQL> select statistic_name,object_name,value from v$segment_statistics
2 where owner='APAE01' and object_name in ('T1','IDX1','IDX2') and value>0
3 order by 1,2;
STATISTIC_NAME OBJECT_NAME VALUE
---------------------------------------------------------------- ------------------------------ ----------
db block changes IDX1 2272
db block changes IDX2 59520
db block changes T1 1504
logical reads IDX1 3840
logical reads IDX2 116752
logical reads T1 3328
space allocated IDX1 2097152
space allocated IDX2 3145728
space allocated T1 2097152
space used IDX1 1389672
space used IDX2 1768402
space used T1 1542726
On s'aperçoit que le 2ème index a généré 30 fois plus de logical reads (116 752 vs 3 840)
Mais c'est en fait très logique: comme le premier index est trié selon le même ordre que les données insérées dans la table on a plus de chances d'insérer une entrée d'index dans le même bloc feuille que l'entrée précédente du coup on change beaucoup de moins de bloc à chaque mise à jour de l'index et on a ainsi moins de logical reads puisque le bloc feuille de l'index à modifier est déjà pinné dans le cache par le process server. A l'inverse, pour le 2ème index qui ne suit pas du tout l'ordre des données insérées dans la table, on aura à chaque nouvelle ligne insérée dans la table un nouveau logical read car la ligne a de fortes chances de se trouver dans un bloc feuille différent.
Un petit calcul de stats sur la table et ses index nous indique qu'effectivement l'index 1 a un clustering factor proche du nombre de blocs feuilles alors que l'index 2 a un CF proche du nombre de lignes:
SQL> exec dbms_stats.gather_table_stats('APAE01','T1',cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> select index_name,blevel,leaf_blocks,distinct_keys,clustering_factor,num_rows
2 from dba_indexes where index_name in ('IDX1','IDX2')
3 order by 1;
INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS
------------------------------ ---------- ----------- ------------- ----------------- ----------
IDX1 1 160 57633 215 57633
IDX2 1 256 57633 57633 57633
La solution dans ce cas pour éviter le nombre important de logical reads consiste à effectuer un insert en mode DIRECT PATH car dans ce cas l'index est mis à jour seulement à la fin une fois que les données ont été triées pour chaque index.
SQL> insert /*+ APPEND */ into T1
2 SELECT LPAD (object_id, 10, '0') AS C1, reverse (LPAD (object_id, 10, '0')) AS C2
3 FROM dba_objects
4 order by 1;
57633 rows created.
SQL> commit;
Commit complete.
SQL> select statistic_name,object_name,value from v$segment_statistics
2 where owner='APAE01' and object_name in ('T1','IDX1','IDX2') and value>0
3 order by 1,2;
STATISTIC_NAME OBJECT_NAME VALUE
---------------------------------------------------------------- ------------------------------ ----------
db block changes IDX1 6496
db block changes IDX2 63488
db block changes T1 1536
logical reads IDX1 8944
logical reads IDX2 122400
logical reads T1 3904
physical write requests IDX1 16
physical write requests IDX2 25
physical write requests T1 31
physical writes IDX1 208
physical writes IDX2 336
physical writes T1 472
physical writes direct T1 216
space allocated IDX1 4194304
space allocated IDX2 5242880
space allocated T1 4194304
space used IDX1 3004846
space used IDX2 3326326
space used T1 3312198
CONCLUSION:
Il ne faut pas sous estimer le coût de maintenance d'un index notamment si cet index a un clustering factor qui est proche du nombre de lignes de la table.
Ahmed,
RépondreSupprimerDe toutes les façons on ne peut pas avoir plusieurs indexes, sur une même table, avec un bon clustering factor pour tous les indexes. Les données d’une table ne pouvant être que dans un seul ordre il ne pourrait donc théoriquement exister qu’un seul index avec un bon clustering factor. Comme nos tables contiennent souvent au moins deux indexes alors si cela va plus vite en insertion pour l’un cela va moins vite pour l’autre.
Effectivement en direct path load, les indexes sont maintenus différemment. De min-indexes sont formés au cours de l’insert et sont envoyés en bulk dans les indexes à la fin. Mais ici également il faut faire attention aux nombreuses situations où le direct path load est silencieusement ignoré par Oracle (présence de trigger, présence de foreign key) et à celle où ce genre d’insert n’est pas conseillé (deletes qui viennent par la suite et donc l’espace disponible suite à ces deletes ne sera pas ré-utilisé).
Si dans ton insert tu inverse l’ordre (order by 2) tu verras que c’est l’autre index qui génère moins de logical I/O
Conclusion : oui tu as raison mais cela importe peu dans le cas où la table contient plusieurs indexes. Il n’y aura qu’un seul qui bénéficiera de son bon Clustering Factor
Salut Mohamed,
SupprimerJe suis tout à fait d'accord avec toi mais le but de mon article était juste de mettre en évidence le cout de la mise à jour d'un index et que ce cout était encore plus important lorsque l'index avait un mauvais CF.
Pour éviter tout malentendu j'aurais dû prendre l'exemple avec 2 tables différentes (une table et son index avec bon CF, une autre table et son index avec mauvais CF).
Mais en tout cas t'as raison il est toujours bon de rappeler les choses.