Procédure stockée : comportement instable

**dgi77** · 17/10/2007, 15h52

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.

**Loyd1974** · 17/10/2007, 16h36

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

**dgi77** · 17/10/2007, 17h31

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 ?

**Loyd1974** · 17/10/2007, 17h53

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