rebuild (ou pas) des indexes d'une table "géante"

[taha1]

Bonjour,

Mon souci c'est que j'ai une table qui compte une dizaine de milliers d'enregistrements et les opérations de suppression sont relativement longues voir impossible si il y a (des like dans les requêtes, je peux comprendre puisque la taille de la table est énorme mais bon), je ne suis pas dba donc si je soupçonne les index (et que je veux les rebuilder), il faut d'abord vérifier l'état des index :

alors j'ai vu qu'il ya
Analyse nom_index validate structure
ou bien
dbms_stats.gather_index_stats

le grand problème c'est que je ne connais pas le coût de ces deux opérations en terme de blocage des transactions DML sur la table concernée.
quelcun pourra m'éclaircir sur le sujet ou peut être me donner une méthode plus soft car la table est tj utilisée et il n ya pas des moments où le traffic baisse

merci

[Waldar]

Vous devriez envisager le COALESCE.

[taha1]

ça me fait de la doc oki je vais regarder merci

[Mohamed.Houri]

Bonjour,

En oracle il ne faut pas soupçonner ; il faut plutôt d’abord savoir pourquoi les deletes sont très lents. Pour cela vous devez soit (a) avoir l’explain plan du delete soit (a) activer le 10046 trace event afin de voir la cause réelle de la lenteur du delete.
Quant au coalesce des indexes proposé ici, si tant est que votre problème vienne des indexes, il faut savoir qu’il existe une fonction interne Oracle (sys_op_lbid) qui permet d’analyser un index afin de connaitre la répartition de ses leaf_blocks par clés. C’est grâce à cette répartition que vous pourriez décider de faire un coalesce ou pas d’un index. Généralement, se sont les indexes basés sur une séquence (right hand index) qui ne font qu’augmenter en allant vers la droite et qui subissent des deletes sur leur gauche qui sont généralement candidats à un coalesce.

Mohamed Houri

[doc malkovich]

Hello,

En BI on manipule des tables de volumétrie importante, tu fais beaucoup de delete ?
Si c'est le cas une astuce est de passer par une table temporaire, tu ne fais plus de delete mais que des truncate/insert en mode direct.
Sinon le mieux est de partitionner les tables et de tronquer les partitions, mais ça se pense avant la création des tables.

[ojo77]

Le temps de réponse sur un effacement dépend de pas mal de paramètres mais le principal est l'indexation :

admettons que la requête de suppression soit

Code :

DELETE FROM TABLE WHERE col1 LIKE '%t%' ;

Si il y a des index sur la table, TOUS les index doivent être mis à jour.

Si cela vous est possible, la méthode préconisée par doc malkovich est plus rapide dès lors que vous touchez plus de 15% des blocs de tables avec votre delete (en mode logging) ou 5% des blocs (en mode direct), j'ai fait des tests pour un client les chiffres ne sortent donc pas d'un chapeau magique .

L'avantage est que vos données sont retassées (vous évitez le gruyère) et que les index sont équilibrés et leurs statistiques à jour et que le recalcul des stats sur la table sera d'autant plus rapide qu'elle couvre moins de blocs.

[McM]

Au fait, on parle d'une taille "géante" d'une "dizaine de milliers d'enregistrements"..
Pour moi ça c'est une petite table. La reconstruction d'index devrait prendre moins d'une minute.

[taha1]

Pardon pour le retard,
Alors, la table concernée est une table qui subit un purge (total) tous les X jours, les insertions sont journalières, toute fois il y a un purge des données (jugées trop vieilles en se basant sur la date d'entée de ces données en base) ce purge est aussi journalier.
Je n'ai pas accés au explain plan (faut que je demande l'autorisation mais ça me prendra plus de temps ...)
je ne peux pas vraiment modifier la structure du schéma : c'est l'appli qui construit la base et les tables lors de son installation.
le rebuild des index n'est pas possible puisque les instructions DML ne doivent pas être bloquées). j'ai lu ceci http://www.orafaq.com/usenet/comp.da...06/23/1956.htm
ah oui la base est une base 9i

- pour l'analyse de l'index, j'ai vu ce script qui utilise la fameuse fonction (qui n'est pas documentée : sys_op_lbid ici http://jonathanlewis.wordpress.com/index-efficiency-3/

Je n'ai pas utilisé cette fonction avant (enfin sur notre base le résultat ne prend même pas 30 seconde mais la table en question est une table de production et non de test), je ne connait pas le coût de cette fonction ni si elle est bloquante, si quelqu'un de vous l'a utilisé, ceci pourrait m'aider

et Merci de me lire

[Mohamed.Houri]

Envoyé par taha1

Pardon pour le retard,
Alors, la table concernée est une table qui subit un purge (total) tous les X jours, les insertions sont journalières, toute fois il y a un purge des données (jugées trop vieilles en se basant sur la date d'entée de ces données en base) ce purge est aussi journalier.
Je n'ai pas accés au explain plan (faut que je demande l'autorisation mais ça me prendra plus de temps ...)
je ne peux pas vraiment modifier la structure du schéma : c'est l'appli qui construit la base et les tables lors de son installation.
le rebuild des index n'est pas possible puisque les instructions DML ne doivent pas être bloquées). j'ai lu ceci http://www.orafaq.com/usenet/comp.da...06/23/1956.htm
ah oui la base est une base 9i

- pour l'analyse de l'index, j'ai vu ce script qui utilise la fameuse fonction (qui n'est pas documentée : sys_op_lbid ici http://jonathanlewis.wordpress.com/index-efficiency-3/

Je n'ai pas utilisé cette fonction avant (enfin sur notre base le résultat ne prend même pas 30 seconde mais la table en question est une table de production et non de test), je ne connait pas le coût de cette fonction ni si elle est bloquante, si quelqu'un de vous l'a utilisé, ceci pourrait m'aider

et Merci de me lire

Bonjour,

Afin de vous aider un peu dans la compréhension de votre problème, je peux vous dire que la fonction sys_op_lbid donne la répartition des clés par leaf block (number of index key per leaf block). Lorsque vous l'executez sur un de vos index et si vous trouvez, par exemple, que pour accéder à une clé d'index vous devez visiter des centaines de leaf blocks alors votre index est un index candidat à "Coalesce". Le coalesce est une opération qui ressemble très fort à une défragmentation du disque dur d'un PC. Oracle va commencer par visiter les blocks d'index en partant de la gauche vers la droite et essayera de "coalescer" ou unir deux leaf block adjacents en un seul block (si c'est possible) et ainsi de suite. Le résultat c'est que la taille de l'index ne change pas mais la répartition des clés par leaf block devient impecable.

Pour être plus directe si dans votre situation vous avez un algorithme de purge régulier qui supprime les anciennes données sur la base d'une certaine date(par exemple supprimer tous ce qui est > sysdate +7), alors tous vos index basés sur une séquences doivent subir un coalesce. Non seulement ils doivent subir un coalesce mais cette opération doit être faite régulierement aussi. Tous vos indexes basés sur une date sont aussi candidat à un coalesce. C'est ce qu'on appèlle communément "right hand indexes" des indexes remplit uniquement sur leur droite. Tom kyte les appelle "sweeper indexes"

L'operation coalesce ne lock pas l'index. Mais attention, dépendant du nombre de block à coalescer cela peut prendre beaucoup de temps.

La fonction sys_op_lbid pour l'avoir utilisée peut également prendre du temps.

Maintenant ayant dit cela, je vous conseille d'abord d'être sûr que votre problème de performance vient des indexes.

Bien à vous

Mohamed Houri

[taha1]

d'accord merci je vais essayer d'analyser ces index pour voir d'où viennent les problème de lenteur .

[skuatamad]

Envoyé par taha1

Alors, la table concernée est une table qui subit un purge (total) tous les X jours

Comment est faite la purge totale ?
Il faut utiliser TRUNCATE (et pas DELETE) pour réinitialiser le High Water Mark.
Il faut pour ça désactiver les clés étrangères.

[taha1]

je ne peux pas le savoir c'est l'appli qui le fait ce purge et donc je ne sais pas si le code utilise un delete ou mieux un truncate ...