Procédure stockée : comportement instable

Bonjour,

Oracle version 9.2.0.4.0.

J'ai une procédure stockée à priori toute bête qui :
- DELETE d'une table Z les enreg. d'une période donnée.
- recharge cette même période dans la table Z en faisant un INSERT...SELECT à partir de 3 tables A, B et C.
- en fin de procédure, met à jour les stats sur la table Z avec le package DBMS_STATS.GATHER_TABLE_STATS.

Ma table Z est vide au départ.
Je lance la procédure une première fois, le temps de réponse est correct (8 secondes) : 488 lignes sont insérées dans la table Z.
Si je lance la procédure une deuxième fois, le temps de réponse est de 10 minutes ! Le pas à pas montre que c'est le INSERT...SELECT qui est long.
A ma connaissance, les statistiques peuvent détériorer l'accès aux tables en SELECT dans certains cas, mais peuvent-elles également le détériorer en INSERT ?

Si je supprime les stats avec DBMS_STATS.DELETE_TABLE_STATS, le temps d'exécution de la procédure reste mauvais.
Si je droppe et recrée la table, idem.
Si je quitte ma session et je me reconnecte, idem.
Par contre, l'exécution manuelle des 2 requêtes de la procédure stockée (DELETE puis INSERT) est très rapide.

La seule solution que j'ai trouvé pour que la procédure stockée s'exécute à nouveau dans un temps normal, c'est de couper puis redémarrer la base.

Après avoir redémarrer la base, je recompile ma procédure en commentant l'appel au package DBMS_STATS.
Je peux lancer ma procédure 2 fois de suite avec des performances correctes (8 s).
Si je fais un TRUNCATE de la table, je retombe dans le cas de figure décrit précédemment avec un temps d'exécution de la procédure stockée de 10 minutes.
Là aussi, ce n'est qu'en redémarrant la base que ça redevient normal...

Je pensais dans un premier temps que ça venait des statistiques, mais je m'aperçois que même en les inhibant, un TRUNCATE fout le bazar. Je me demande si ce n'est pas lié à l'exécution d'un ordre DDL, ou sinon à un éventuel verrou laissé sur la table (ce qui ne colle pas non plus avec le cas où le DROP de la table ne récupère pas la situation), ou à un paramètre de la base, ou encore à autre chose...

Avez-vous des idées ?

Merci beaucoup.

Une première piste serait de comparer les plans d'exécutions avant et après les statisques, cela te permettra déjà de comprendre pourquoi ta requête est si longue après le calcul des stats

Effectivement, il y a une différence : il me fait un HASH JOIN entre 2 instances de ma table C lors de la première exécution, puis un NESTED LOOPS à cet endroit lors de la deuxième exécution.

Mais en quoi la mise à jour des statistiques ou un TRUNCATE sur la table destination Z peut modifier le plan d'accès à l'une des tables sources ?

Citation:

---

Envoyé par dgi77

Mais en quoi la mise à jour des statistiques ou un TRUNCATE sur la table destination Z peut modifier le plan d'accès à l'une des tables sources ?

---

Le truncate met le num_rows à zéro dans les stats. Et les stats, ca sert justement à modifier le plan d'accès pour avoir celui le plus optimum possible.

Tu dois avoir un souci dans ta gestion des stats alors

Oui, mais je mets à jour les stats pour la table destination, et c'est l'accès à l'une des tables source qui est modifié...

Citation:

---

Envoyé par dgi77

Oui, mais je mets à jour les stats pour la table destination, et c'est l'accès à l'une des tables source qui est modifié...

---

Ca c'est effectivement très étrange... Tu es bien sur que tu te limites à ta table de destination pour la collecte des stats? juste au cas où hein ;)

Tu peux nous mettre les plans exacts s'ils ne sont pas trop longs?

Oui, oui, je te confirme, je l'ai vérifié 10 fois, et puis ça n'aurait aucun sens fonctionnellement : je ne mets à jour que les stats de la table destination après l'insertion. Et du coup, à l'exécution suivante, ça mouline. Comme je l'ai dit, même problème si je fais un TRUNCATE sur la table destination...

Je ne comprends toujours pas ce qui se passe. Constatant que le plan d'exécution performant utilise un HASH JOIN au lieu d'un NESTED LOOPS pour le plan foireux, j'ai essayé de forcer le HASH avec un hint /* +HASH (nom_table_source) */. Ca semble résoudre le problème suite au TRUNCATE, mais pas suite au calcul des stats sur la table destination. Je suis donc de plus en plus perplexe...

Je te laisse donc les 2 plans d'exécution en pièces jointes en espérant qu'ils sont assez lisibles. Ce sont les lignes 6 et 19 qui diffèrent, tantôt en HASH, tantôt en NESTED.

Merci de te pencher dessus.

Est ce qu'il est possible qu'un autre process touche à ta table source? (n'importe quoi, un trigger, une autre proc, un utilisateur,..)

Il n'y a pas de trigger, pas d'autre utlisateur car je suis en DEV et tout seul sur une nouvelle base.

La table source incriminée est utilisée pour alimenter une autre table, mais c'est un traitement indépendant, et que je n'ai pas exécuté entre les multiples exécutions de l'autre traitement.

un détail qui compte :
un truncate supprime également les EXTENTS de la table, alors qu'un delete va uniquement vider les données mais garder l'espace de stockage déjà alloué à la table.

Donc à vérifier si la table de destination n'a pas des allocations d'extents très (trop) grands et doit alors alouer d'énormes espaces disques pour quelques lignes (notament avec une mauvaise utilisation de PCT_INCREASE). Ou au contraire des extents extrêmement petits qui multiplient à outrance les opérations d'allocation de ces extents.

Non, je fais un DELETE car je travaille sur 10 jours glissants, et il faut bien que je conserve l'historique.

Ce que tu dis sur le TRUNCATE est vrai si tu laisses l'option par défaut qui est DROP STORAGE, mais si tu vides en sachant que tu recharges derrière à volumétrie comparable il faut faire un REUSE STORAGE de manière à conserver les extents. Quoi qu'il en soit, les lenteurs dûes à l'allocation d'espace ne se font ressentir que sur des gros volumes, ce qui n'est pas mon cas, et paraît-il même que depuis la 9i ça n'est plus vrai.

Je te remercie quand même pour ton aide.

Pour l'instant on ne sait pas pourqui l'exécution de la procédure prend assez des temps. Pour en savoir plus, exécutez le traitement avec une trace sql, format le fichier de trace ainsi résultat avec tkprof et analyse le résultat.