Sélectionner une page

Avec la récente mise à disposition de la version 19c d’Oracle dans le Cloud, on a vu arriver parmi d’autres bonnes nouvelles le summum de l’automatisation dans la gestion des index : l’« Automatic Indexing », capable de créer, reconstruire et supprimer des index de manière autonome. L’idée, c’est que la base sql gère toute seule ses index : un bon point pour les administrateurs Oracle férus de tuning qui se demandent régulièrement s’ils n’en ont pas oublié un. Sur le papier, cette fonctionnalité paraît idéale… si on est prêt à déléguer cette tâche en automatique à Oracle.

Il est intéressant de noter que les index générés par ce système automatique (les « auto index ») disposent d’une gestion qui leur est propre : ils peuvent donc co-exister avec vos propres manières de travailler.

Un assistant de tuning Oracle classique

L’indexation automatique fonctionne de manière très ordinaire pour un assistant de tuning Oracle : une tâche de fond se lance sur une base périodique, analyse la charge, génère et supprime les index en fonction des résultats obtenus. Si un index est marqué UNUSABLE suite à une partition ou un MOVE, la fonctionnalité est capable de reconstruire celui-ci spontanément.

Une API permet de gérer et de configurer le job, et de lui demander des rapports d’activité (DBMS_AUTO_INDEX).

Tâche d’analyse

La tâche d’analyse scrute l’activité pendant la période de maintenance déterminée. Elle identifie les index candidats, crée des index invisibles (sans incidence) et les teste avec des requêtes SQL. S’ils sont jugés performants, ils seront alors rendus visibles, sinon, ils seront marqués UNUSABLE (et seront supprimés plus tard). La tâche supprime également les auto index inutilisés depuis un temps donné, ainsi que les index non auto (les vôtres), si vous le souhaitez. Par défaut, la tâche patientera 373 jours avant de supprimer définitivement un index, mais cette valeur peut être ajustée.

Configuration

L’automatic indexing est paramétrable, certains paramètres peuvent être configurés (DBA_AUTO_INDEX_CONFIG). Par exemple, on peut demander à l’assistant de ne pas rendre visible ses auto-index, afin de conserver la main. De cette manière, c’est vous qui choisirez si vous les activez ou non. Vous pouvez aussi lui demander de créer un tablespace spécifique pour stocker les auto-index qu’il génère.

Cependant, le système a aussi quelques limites. Les index ainsi générés seront uniquement de type B-tree, et ne pourront pas être créés sur des tables temporaires, par exemple. De plus, la fonctionnalité d’auto-indexing n’est disponible que sur la version Oracle Entreprise… comme la plupart des fonctions de tuning automatique d’Oracle, d’ailleurs.

Quelques exemples

La documentation officielle de Oracle 19c propose quelques commandes pour l’automatic indexing, en attendant de pouvoir se lancer sur la version « on premise ». Si vous avez un compte trial sur Oracle Cloud Infrastructure, vous pouvez dès à présent tester cette fonction !

Configurer l’automatic indexing

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

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

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

Même chose avec les index « non auto »

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

Fixer un tablespace cible pour les créations d’index

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

Obtenir un rapport d’activité

Sur la dernière analyse (il existe différentes options bien sûr) :

declarereport clob := null;beginreport := DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY();end;