Problème de performance suite à un delete

[regal]

Bonjour,

Lorsque nous supprimons quelques dizaines de milliers de lignes dans une table partitionnée - les delete concernent une seule partition, les ordres insert effectués dans cette partition après la suppression ont une performance très dégradée et finissent généralement par un problème avec le tablespace undo (impossible d'étendre le segment par 4 dans le tablespace d'annulation). Les inserts dans d'autres partitions fonctionnent correctement.
Une partition contient entre 8 et 10 millions d'enregistrements. Les delete - que nous effectuons uniquement en cas d'erreur de chargement soit moins d'une fois par an heureusement - concernent de 50000 à 80000 lignes. Le problème apparait avec un delete global des 50000 ou plus lignes ou avec plusieurs delete de 10000 lignes.
Le problème a été constaté sur la base de production et reproduit sur la base de développement. Nous sommes sous Oracle 9.2
Pour retrouver nos performances nous avions tout d'abord sauvegardé les données de la partition, supprimé celle-ci, recréé cette partition et réinséré les données. Dernièrement nous avons effectué un move de la partition suivi d'un rebuild des index.
Pour quelle raison les performances sont-elles si dégradées après une suppression ?
Peut-il y avoir une solution pour effectuer un delete qui ne dégrade pas les performances ?
Est-ce que la solution d'un move de partition est la meilleure pour retrouver les performances ?

[Mohamed.Houri]

Combien d'indexes existent dans cette tables? Combien d'indexes partitionnés et d'indexes non partitionnés? Y-a-t-il des triggers dans cette table? Y-a-t-il des contraintes d'intégrité dans cette table? Comment se font les inserts? Par direct path ou par un insert conventionnel?

Comment sont insérées les données en 'temps normal'?

Lorsque vous supprimez les données d'une partition, vous intervenez sur une seule partition de la table et sur une seule partition des indexes localement partitionnés; par contre les données contenues dans les indexes globaux peuvent être dans n'importe quelle partitions, c'est pourquoi il se peut que vos inserts souffre du temps d'insertion dans les indexes globaux dont l'efficacité se dégrade à cause de ce delete.


Lorsque vous supprimez la partition et vous la recréer les indexes globaux sont recréés(rebuild) et ne ressemblent donc plus à ce qu'ils étaient étaient après le delete.

La prochaine fois, tracez les inserts avec un l'event 10046 level 12 afin de voir où se situe réellement le problème

[regal]

Il existe 29 index bitmap partitionnés sur cette table.
Un rebuild de ces index sans move de la partition n'améliore pas la rapidité des inserts. J'ai aussi lancé le script d'analyse statistique utilisant dbms_stats.gather_table_stats(granularity => 'PARTITION', degree => 4, estimate_percent => 20, cascade => FALSE) sur la partition considérée sans noter d'amélioration notable dans le cas dégradé.
Nous n'avons pas d'index globaux sur cette table.
Nous avons une vingtaine de contrainte d'intégrité référentielle.
Un trigger BEFORE INSERT OR UPDATE for each row existe sur la table.

Les inserts se font par datastage via un ordre sql insert. Un test avec une boucle pl/sql lancée sous sqlplus ou toad met en évidence le même problème de dégradation de performance.

Nous observons dans les fichiers traces qu'il y a beaucoup de
WAIT #5: nam='db file sequential read'
pour l'écriture dans la partition sur laquelle la suppression a été effectuée
alors qu'il y en a très peu pour les inserts dans une autre partition
par exemple pour une boucle de 99 insertions 4351 WAIT contre 9.

Quelles autres informations utiles peuvent nous donner les traces ?
Comment interpréter ces informations ?

[pachot]

Bonjour

Envoyé par regal

Pour quelle raison les performances sont-elles si dégradées après une suppression ?

Peut être un effet d'ASSM qui met parfois du temps pour trouver les blocs où il y a de la place.

Envoyé par regal

Peut-il y avoir une solution pour effectuer un delete qui ne dégrade pas les performances ?

De toute façon, le delete n'est pas optimal sur du gros volume, et ne l'est pas non plus pour les bitmaps index. Donc ici ASSM serait juste une 3ème raison pour ne pas faire de delete

Envoyé par regal

Est-ce que la solution d'un move de partition est la meilleure pour retrouver les performances ?

En 9.2 oui certainement. En 10g, un SHRINK pourrait suffire pour le pb d'ASSM. mais il reste les bitmaps, et le coût des delete.

Cordialement,
Franck.

[Mohamed.Houri]

Code :

1
2
3
Nous observons dans les fichiers traces qu'il y a beaucoup de 
WAIT #5: nam='db file sequential READ' 
pour l'écriture dans la partition sur laquelle la suppression a été effectuée

db file sequential read représente une lecture via indexe. Ce qui veut dire que votre insert est du type

Code :

1
2
3
4
5
6
 
INSERT INTO t1
SELECT FROM
      t2
WHERE
      t2....

Et peut être même comme suit

Code :

1
2
3
4
5
6
7
8
9
10
11
 
INSERT INTO t1
SELECT FROM
      t2
WHERE
      t2....
AND NOT EXISTS
      (SELECT NULL
        FROM t1
       WHERE t1.pk = t2.pk)
;

Est-ce bien ce genre d'insert?

Il va falloir identifier sur quelle index se font ces db file sequential read.

Il y a deux ans j'ai résolu un problème de performance relatif à insert/select sur une table partitionnée. 60% du temps de réponse étaient sur un dbfile sequential read. La solution adoptée était la localisation de l'index incriminé (c'etait un index locallement partitionné) et de faire

Code :

1
2
3
 
    ALTER INDEX local_ind_t1 parallel 4;
    ALTER TABLE t1 parallel 4;

Puisque vous avez reproduit le cas en DEV, pourquoi alors ne pas essayer.

[pachot]

Bonjour Mohamed,

Envoyé par Mohamed.Houri

db file sequential read représente une lecture via indexe.

Ou peut-être lecture d'undo ? il y a quand même eu plein de delete sur ces blocs. Et un block cleanout à faire...

regal,
ce serait intéressant à partir de file# et block# de trouver quel segment subit tous ces i/o (via dba_extents)

Cordialement,
Franck.

[regal]

Ce sont surtout les bitmap index qui donnent lieu à des I/O.
J'ai créé une table de test contenant les données d'une année sans aucun index ni trigger. Il n'y a aucun problème suite à delete massif. Dès que je crée un index bitmap, je reproduit le problème.
Par contre à l'exception éventuelle d'une donnée ou le bitmap n'est pas forcément le meilleur choix, l'ensemble des champs indexé ne contient que peu de valeurs différentes ce qui conduit naturellement à choisir des index bitmap.
Une simple reconstruction de ces index bitmap ne permet pas de retrouver les performances.
Nous allons donc prescrire à notre client d'effectuer un move de partition lors d'un delete massif (dans notre application ces delete ne sont effectués qu'en cas d'anomalie de chargement donc peu fréquents et peuvent donc donner lieu à des manipulations style move de partition)

[pachot]

Bonne idée. bien sûr il ne faut pas supprimer les bitmap index à cause d'un cas exceptionnel.
L'explication, je pense, est liée au fait que pour insérer dans une table qui a beaucoup de trous (car il y a eu des delete) il y a des ranges de rowid à modifier dans les index bitmaps - et ce même s'ils ont été fraîchement reconstruits.
Alors qu'en insérant en 'bulk' dans des nouveaux blocs, ce sont des nouveaux ranges de bitmaps qui sont rajoutés - les anciens ne sont pas touchés. donc moins de redo, moins d'undo, etc.

Mais le delete devait être long non ?
Plutôt qu'un delete + move il serait probablement plus rapide de faire un Create Table As Select des lignes à garder je pense.

[regal]

Envoyé par pachot

Mais le delete devait être long non ?
Plutôt qu'un delete + move il serait probablement plus rapide de faire un Create Table As Select des lignes à garder je pense.

Pour information, le delete n'est pas très long par rapport au volume de données. Et il y a largement plus de lignes à garder que de lignes à supprimer - la suppression ne représente qu'une partie des lignes insérées dans une journée, alors qu'il faut conserver dans la partition les lignes correctement insérées ... pour tout le mois.

Merci pour les réponses.

[alexisongagna]

Bonjour,

Question toute bête : tu fais des delete en SQL ou en PL SQL ?
Car si le UNDO pète, c'est peut être parce qu'il y a trop d'opérations à rollbacker éventuellement.
Ton delete est t-il fait en utilisant un filtre sur un champ/des champs indexés ?

Quelle est la taille de ton tablespace UNDO ?

Est ce que tu as essayé de faire un delete en PL/SQL avec commit intermediaire tous les 100 delete ? Tu peux aussi utiliser le BULK mode pour faire des delete par paquet.

Merci de répondre, nous allons investiguer.