Tables temporaires

Jusqu’à la 11g, les tables temporaires en mode commit preserve rows sont à manier avec précaution. Si vous vous contentez de les utiliser pour stocker quelques calculs intermédiaires, vous n’aurez pas de problèmes car un full scan fera bien l’affaire.

En revanche, si des tables temporaires sont utilisées dans un batch suite par exemple à une migration de Sybase vers Oracle, vous risquez alors de faire face à des problèmes de performance une fois une certaine volumétrie atteinte.

En effet, si une même requête au sens sql_id est utilisée plusieurs fois, un dynamic sampling souvent insuffisant sera utilisé pour collecter quelques statistiques afin d’élaborer un plan d’exécution lors de la création du curseur dans le shared pool. De plus, si cette requête est utilisée plusieurs fois par d’autres sessions, ce même plan d’exécution sera appliqué alors que le jeu de données risque d’être fort différent en volumétrie et en nature des données.

Pour éviter des performances dégradées et aléatoires, deux solutions peuvent être mises en oeuvre :
1)  Utilisation d’un hint pour forcer le dynamic sampling à 6, voire plus si nécessaire ;
2) Forcer le hard parsing à chaque exécution de requête (http://ahmedaangour.blogspot.fr/2011/07/forcer-un-hard-parse.html) pour obtenir un plan d’exécution adapté à chaque jeu de données.

A partir de la 12c, la possibilité de collecter des statistiques au niveau de chaque session (https://oracle-base.com/articles/12c/session-private-statistics-for-global-temporary-tables-12cr1) change la donne.