Problème de performance atypique

[tomca]

Bonjour a tous...je bosse sous Oracle et j'ai des problèmes de perf assez bizarre que je n'arrive pas a expliquer...

La version d'Oracle utilisée est 9i

J'expose mon problème:

J'ai une grosse table sous Oracle donc, qui contient 8,6 millions de lignes.
Je souhaite virer des lignes de données, environ 100 000

Quant j'execute cette requête :
select count(*) from rsa_fixe where annee=2006;
elle s'execute en 2sec (Oracle utilise l'index que j'ai placé sur le champ annee)

Lorsque j'execute
delete from rsa_fixe where annee=2006;
Ca dure plus de 4 heures, et quand je regarde mon plan d'éxecution, Oracle n'utilise plus mon index...

J'ai tenté ma suppression en utilisant un autre champ, lui aussi indexé :
delete from rsa_fixe where type_fichier='priv2006';
Cette requête concerne le même nombre de lignes et passe bien par son index...mais même topo, plus de 4h d'execution....

J'ai lancé un analyze sur cette table, même topo, des perfs superbe en select, mais ca rame sévère lors des delete...

La commande utilisée pour analyser ma table :

analyze table rsa_fixe compute statistics for table for all indexes for all indexed columns;

Donc voila, je suis pas dba oracle, donc quelque chose doit m'échapper...si quelqu'un a une idée, je suis preneur car je ne comprends plus...

Merci d'avance a tous!

[dgi77]

La seule chose qui me vient à l'idée, c'est que contrairement au SELECT, le DELETE doit écrire dans les ROLLBACK SEGMENT, mais ça ne justifie pas à ma connaissance un plan d'exécution différent, ni 4 heures d'écritures pour 100 000 lignes (à moins que les lignes comportent des centaines de colonnes...).

A moins qu'il y ait un trigger ligne de la mort sur DELETE de ta table ?

[Loyd1974]

Est-ce que tu n'aurais pas une table qui aurais une foreign key sur une des colonnes de la table que tu veux effacer ?

[tomca]

Pour répondre aux questions, y a pas 100 colonnes, une trentaine tout au plus...en gros, d'habitude c'est quasi instantané...pas de trigger, ni de foreign key...c'est d'ailleurs bien ce qui m'inquiète...
Je me demande si j'ai pas eu une requête précédente qui a été interrompue, et que du coup Oracle a du mal a faire son rollback, ce qui expliquerai pourquoi j'ai des bonnes perf en select, mais lamentables en delete...
Maintenant, comment savoir si je suis sur la bonne piste...pas simple l'administration sous Oracle...
En tout cas merci pour vos réponses...

[LeoAnderson]

4h ça mouline

la vue v$session_longops indiquera forcément quelque chose.. et vous y verrez quoi !

[tomca]

Bon, mon delete tourne toujours, ca fais 6 heures maintenant...

En regardant sous Tora l'état de mes sessions, j'ai découvert ceci :

J'ai deux transactions active sur ma base...et deux fois la même requête...
(delete from rsa_fixe where annee=2006)
Y en a une, je le sais, elle tourne sous mes yeux, mais l'autre?... je me demande si c'est pas un traitement précédent interrompu qui continu a vivre sa vie seul (en train de faire un rollback?)

En regardant la partie longops...pour l'une des requetes, j'ai bien des étapes, qui semblent toutes être 'completed'....pour la seconde je n'ai rien...

Par contre, j'ai découvert une petite chose anormale je pense
Pour une des deux requete, j'ai l'impression d'avoir un PENDING_LOCK en Mode exclusive.
Pour ces deux requêtes, d'ailleurs quand je regarde la partie 'Locked Objects'...ben je retrouve ma table....RSA_FIXE qui semble être lockée avec un locked mode = RowX...

Je sais pas trop ce que ca signifie mais ca ne me semble pas bon signe...
Comment puis-je délocker ma table?

Merci d'avance, et merci de m'avoir mis sur la piste du longops....

[pifor]

Un DELETE va poser un verrou exclusif sur chaque ligne à supprimer. Si les vues DBA_BLOCKERS et DBA_WAITERS ne retournent aucune ligne, cela signifie qu'il n'y a pas d'attente sur des verrous: pas de problème de ce côté là.

Essayez de repérer dans la vue V$SQL la requête en question. Quelles sont les valeurs de:

• DISK_READS
• BUFFER_GETS
• ROWS_PROCESSED
• CPU_TIME
• ELAPSED_TIME

Essayer aussi de repérer dans la vue V$SESSION_WAIT la session qui fait le DELETE: quel est l'évenement (EVENT) qui revient le plus souvent.

Pour analyser complètement ce problème, il faudrait mettre la trace SQL sur la session et analyser le résultat (après exécution de la requête) avec TKPROF.