[Oracle 11g] Index d'origine plus utilisé sur création/suppression nouvel index

Bonjour,

Ma base de données 11gR2 contient une table APPEL, qui comporte 20 colonnes et compte env. 3 millions de lignes.
5 indexes mono-colonne sont positionnés sur cette table, dont 1 sur la Primary Key (Id_Appel) qui est une colonne de type NUMBER.

Cela fonctionnait très bien jusqu'à ce que j'ajoute un 6ième index multicolonne sur cette table (incluant aussi la PK Id_Appel). Les temps de réponse se sont subitement dégradés.
Un simple "SELECT COUNT(*) FROM Appel" prenait plusieurs minutes au lieu d'être quasi-immédiat. J'ai donc supprimé l'index ajouté. Cela a résolu le problème de lenteur... pour un temps !

En effet, le lendemain, les temps de réponse de cette table étaient à nouveau exécrables. Je me suis aperçu qu'Oracle lançait son calcul de statistiques pour l'optimiseur vers 22h chaque soir. J'ai supposé que le problème venait de là et j'ai relancé le calcul complet de statistiques de l'optimiseurs. 2h après, le problème était résolu : les temps de réponse étaient redevenus normaux.

Ce matin (donc 4 jours après), alors que je n'ai fait aucune modification en base de données, les problèmes de lenteur sur cette table sont revenus !!! J'imagine que c'est l'optimiseur qui fait à nouveau des sienne... mais je n'ai rien changé !?

Questions :
- Pourquoi (C'est mon hypothèse) l'optimiseur n'utilise-t-il plus l'index sur la PK ? Comment le forcer à l'utiliser comme avant (sans utiliser des HINT) ?
- Pourquoi le problème revient-il 4 jours après alors que je n'ai rien changé dans la structure de la base de données entre-temps ?
- Quand on créé un index, à partir de quand est-il utilisé par l'optimiseur ? Quand on en supprime un, à partir de quand l'optimiseur voit-il qu'il n'existe plus et se rabat-il sur un autre index ?

Merci de votre aide sur ce problème critique.

- Pourquoi (C'est mon hypothèse) l'optimiseur n'utilise-t-il plus l'index sur la PK ? Comment le forcer à l'utiliser comme avant (sans utiliser des HINT) ?

D'abord il faut vérifier cette hypothèse
Lancez votre requête et si elle est lente, lancez aussitôt après la commande suivante

Code:

---

1 2	Select * from table( dbms_xplan.display_cursor()) ;

---

Cette commandevous renvoie le plan d'exécution de la dernière commande passée

- Pourquoi le problème revient-il 4 jours après alors que je n'ai rien changé dans la structure de la base de données entre-temps ?

Je suis à peu près certain qu'il s'agit d'un problème de cache, par contre savoir s'il s'agit de celui de la base, de celui de l'os ou éventuellement de celui de la baie disque si vous en avez une demande à être vérifié

Une trace 10046 pourra vous donner des indication

Pour l'activer (avant de lancer la requête) :

Code:

---

1 2 3

alter session set tracefile_identifier='un_truc_qui_identifie_la_requete' alter session set events='10046 trace name context forever';

---

Pour la désactiver (après la requête):

Code:

---

1 2	alter session set events='10046 trace name context off';

---

un fichier de trace est généré dans le user_dump_dest ( $ORACLE_BASE/diag/rdbms/<db_unique_name>/<instance_name>/trace si l'arborescence est normalisée ) et il contient dans son titre l'identifiant de fichier de trace que vous avez défini plus haut. et peut être lu soit directement ( mais bon c'est assez rustre ) soit après l'avoir fait passer par l'outil tkprof (livré avec le moteur)

- Quand on créé un index, à partir de quand est-il utilisé par l'optimiseur ? Quand on en supprime un, à partir de quand l'optimiseur voit-il qu'il n'existe plus et se rabat-il sur un autre index ?

Il peut être utilisé dès qu'il est créé (les statistiques sont calculées automatiquement à la création en 11g) et l'optimiseur se "rabat" sur un autre index, s'il "estime" qu'il "doit" le faire dès la disparition de l'index droppé.

Comme déjà précisé par ojo77 vous devez analyser les plans d'exécution d'une même requête dans les deux cas pour détecter si il s'agit vraiment d'un changement de plan ou pas. Est-ce que ces requêtes sont toujours de type Select count(*) From ...?
Alternativement une simple exécution de la requête avec "autotrace trace only" vous révèle ce plan et les statistiques d'exécution associées.
Il y a pas mal des paramètres qui concernent le calcul des statistiques et le fait de lancer manuellement ce calcul aurait pu utilisé des paramètres qui n'ont peut être pas les même valeurs que ce qui se fait chaque soir.
De plus est-ce que le contenu de cette table bouge d'une manière sensible sur les 4 jours incriminés ?
Pour les besoins de tests de type quelle est l'impact d'un nouveau index ou suppression d'un index existant vous pouvez utiliser en 11g les indexes invisibles (Invisible Indexes in Oracle Database 11g Release 1).

Merci beaucoup à tous les 2 pour ces informations.
Je découvre au passage l'existence des indexes invisibles qui vont je pense m'être bien utiles pour des tests.

Le plan d'exécution montre que l'index est bien utilisé, contrairement à ce que je supposais. La vérité est ailleurs... :roll:
Je clos ce topic et je poursuis mes investigations...

Bon week-end.