Il existe un blog Oracle dédié à l’optimizer et plus généralement au tuning. Ses articles sont beaucoup plus synthétiques que les « what’s new » de la documentation Oracle. Il renvoie de plus sur les éléments de cette même documentation quand on cherche des détails. Je vous le recommande donc pour vous tenir facilement au courant des nouveautés. Vous aurez ainsi du répondant dans les conversations de tuning à la machine à café ;).

Un récent article présente des nouveautés de la version 19c et notamment la gestion des indexes qui va atteindre son summum d’automatisation avec « Automatic Indexing » (appelons cette feature AI). Il faut dire que, pour être « autonomous », la database doit maintenant faire elle-même ce type de choix. Cela aura au moins l’avantage d’éviter que chaque personne qui veut améliorer les performances ne demande si on n’a pas oublié un index :).

Les fonctionnalités ont l’air alléchantes si on est prêt à confier une tâche à Oracle en automatique : AI peut automatiquement créer, reconstruire et supprimer des indexes.

Un point important est que les indexes créés par ce système, nommés « auto indexes », vont avoir un cycle de vie qui leur est propre. La fonctionnalité peut donc cohabiter avec vos habitudes de travail.

Principe

AI fonctionne comme la plupart des assistants de tuning, vous ne trouverez donc rien de bien surprenant dans son principe.Une tâche de fond est lancée périodiquement. Celle-ci analyse la charge, crée ou supprime des indexes en fonction des résultats. Elle reconstruit également les indexes qui auraient été marqués UNUSABLE suite à des opérations de partition ou des MOVE.

L’ensemble est gérable via une API permettant de configurer AI et d’obtenir des rapports d’activité (DBMS_AUTO_INDEX).

Tâche d’analyse

Cette tâche, qui fonctionne durant la fenêtre de maintenance, scrute l’activité pour identifier les indexes candidats et créer des indexes invisibles, donc sans impact. Elle teste ensuite ces indexes en utilisant les requêtes SQL avant de les rendre visibles s’ils sont jugés assez efficaces. En cas d’inefficacité, ces indexes deviennent UNUSABLE pour être supprimés plus tard. Enfin, elle vient supprimer les auto indexes non utilisés depuis une certaine durée. Pour information, il est possible de demander au job de supprimer également les indexes « non auto », c’est à dire ceux que vous auriez créés vous-mêmes.

NB : La durée avant suppression des indexes est par défaut de 373 jours mais la valeur est paramétrable.

Configuration

Il est évidemment possible de configurer AI : Plusieurs paramètres sont modifiables mais notons qu’on peut faire en sorte que AI :

• ne rende jamais visible les indexes qu’il crée afin de garder la main sur leur activation ou non
• utilise un tablespace cible spécifique pour stocker ses auto indexes.

Quelques exemples de commandes

En attendant de pouvoir tester sur du On-Premise quand la 19c sera disponible hors Cloud, voici quelques commandes issues de la documentation officielle.

Configurer AI

 -- en mode complet :
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
-- sans rendre les indexes visibles
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');
-- pour le désactiver et désactiver tous les auto indexes qui vont avec lui !!
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');

Changer la rétention des auto indexes non inutilisés

 EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', '90');

Même commande pour les indexes « non auto »

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_MANUAL', '90');

Fixer le tablespace cible des créations d’indexes

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE', 'TBS_AUTO');

Les différents paramètres sont visibles dans « DBA_AUTO_INDEX_CONFIG »

Obtenir un rapport d’activité

Ici sur la dernière analyse (il existe plusieurs options de rapport)

declare
report clob := null;
begin
report := DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY();
end;

Le système a évidemment des limitations :

• les indexes sont uniquement de type B-tree,
• les indexes ne peuvent pas être créés sur des tables temporaires,
• AI n’est disponible qu’en Enterprise Edition, comme beaucoup de fonction de tuning automatique.

Si vous le souhaitez, vous pouvez trouver plus d’informations dans le tuning guide de la 19c ainsi que dans la documentation d’administration à propos des indexes.

La fonction est d’ores et déjà testable si vous avez un compte trial valide sur Oracle Cloud Infrastructure puisque la version 19c y est disponible.  Le mien est malheureusement expiré, alors n’hésitez pas si vous faites des tests à partager vos résultats :).