12c: Statistiques privées pour les tables temporaires (GTT)

On travaille souvent avec des volumes différents sur les Global Temporary Tables. Assez souvent, le même code est appelé en online pour quelques enregistrements ou en batch pour des millions.
Alors quelles statistiques mettre pour les Global Temporary Table concernées ?

  • Pas de statistiques. les requêtes feront du dynamic sampling.
    Oui mais c’est seulement la première qui va optimiser le plan d’exécution qui va faire ce dynamic sampling. Les suivantes réutiliseront le même plan. Et un jour on va se retrouver avec un batch qui va travailler sur des millions de lignes en faisant un NESTED LOOP qui était prévu pour quelques lignes.
  • L’autre solution jusqu’en 11g est de fixer des stats qui amènent à choisir un plan d’exécution correct pour les 2 cas.
    Très souvent, le plan prévu pour un grand nombre de lignes est valable aussi sur un faible volume. Il vaut faire un HASH JOIN même pour quelques lignes, plutôt que de risquer de faire un NESTED LOOP à partir d’une grosse table.

En 12c, une autre solution: les statistiques privées, propres à la session. Chaque session calcule les stats sur la table temporaire chargée (et en 12c c’est automatique pendant le chargement – cf. post précédent). Et si les stats sont différentes, le curseur ne sera pas partagé. On aura donc toujours un plan d’exécution optimisé pour les données de la session.

Le choix de la visibilité des stats se fait avec dbms_stats.set_table_prefs: GLOBAL_TEMP_TABLE_STATS peut être ‘SHARED’ (comme c’était avant en 11g) ou ‘SESSION’.
La réutilisation d’un curseur dépendra aussi de ces stats.

Comme d’habitude, la demo

12c: possibilité de ne plus générer de redo sur les GTT

Sur une Global Temporary Table, il n’est pas nécessaire de générer du redo car les données ne sont pas persistantes. Les données (table et index) sont dans un tablespace temporaire sur lequel il n’y a pas de recovery.
Par contre le undo est nécessaire, pas pour des raisons de consistent reads (puisque les données ne sont pas partagées avec d’autres sessions), mais simplement parce qu’on peut faire un rollback dans notre session.

Et malheureusement cet undo est généré dans le tablespace UNDO qui est permanent, donc protégé par du redo.

En 12c, c’est toujours le comportement par défaut, mais on a la possibilité de faire en sorte que le UNDO généré par des opérations sur des GTT soit écrit dans le tablespace temporaire Et donc de ne plus générer de redo du tout (Sauf un minimum, pour les modifications du dictionnaire par exemple).

Il suffit de positionner le paramètre suivant:

alter session set temp_undo_enabled =true;

Cette fonctionnalité a été introduite pour pouvoir utiliser les GTT dans une standby Active DataGuard (où les datafiles sont ouverts en read-only seulement). Le paramètre est par défaut à FALSE sur une base primaire.

J’ai repris une demo complète qui mesure le redo généré par chaque opération sur une table permanentes et sur une table temporaire. Si on laisse temp_undo_enabled à sa valeur par défaut, il n’y a pas de différence entre le redo généré en 11g et en 12c sur toutes les opérations de cette demo.