Manipuler avec soins car contient des paramètres standards, cachés et Hints

L'une des premières vérifications que je vous suggère fortement de faire lorsque vous effectuez de l'optimisation de requêtes est de vérifier la valeur des paramètres de bases de données qui ont été personnalisées (modifiées).

Dans plusieurs cas que j'ai rencontrés, surtout dans le cadre de projet de migration, certains paramètres avaient été modifiés pour contourner des problèmes (bugs) et, suite à la migration, ceux-ci sont la plupart du temps plus nécessaires.

Utiliser la commande "ALTER SESSION" pour ceux qui sont modifiable dynamiquement. Vous pourrez vérifier le comportement de vos requêtes avant d'appliquer le changement de façon permanente (ALTER SYSTEM) sur la base de données.

Autres conseils, évitez de modifier les paramètres de base de données, surtout ceux cachés. Vous aurez des problèmes lors des futures migrations. Si vous n'avez pas le choix, je vous conseille de bien documenter leur utilisation.

C'est la même situation pour les HINTS de requêtes, ce n'est que des diachylons et… souvenez-vous qu'un diachylon fini toujours par se décoller :)

Objets sans statistiques ou désuètes

Voici un script qui permet d'afficher les objets dont les statistiques sont absentes ou désuètes (stale) :

SET SERVEROUTPUT ON

DECLARE
vListeObjt dbms_stats.ObjectTab;
BEGIN
-- Stats désuetes
dbms_output.put_line('-- **********************');
dbms_output.put_line('-- Statistiques désuètes');
dbms_output.put_line('-- **********************');
dbms_stats.gather_database_stats(objlist=>vListeObjt, options=>'LIST STALE');
FOR i in vListeObjt.FIRST..vListeObjt.LAST
LOOP
dbms_output.put_line(vListeObjt(i).ownname || '.' || vListeObjt(i).ObjName || ' (' || vListeObjt(i).ObjType || ' ' || vListeObjt(i).partname||' )');
END LOOP;
-- Sans Stats
dbms_output.put_line('-- **********************');
dbms_output.put_line('-- Sans statistique');
dbms_output.put_line('-- **********************');
dbms_stats.gather_database_stats(objlist=>vListeObjt, options=>'LIST EMPTY');
FOR i in vListeObjt.FIRST..vListeObjt.LAST
LOOP
dbms_output.put_line(vListeObjt(i).ownname || '.' || vListeObjt(i).ObjName || ' (' || vListeObjt(i).ObjType || ' ' || vListeObjt(i).partname||' )');
END LOOP;
END;
/

Comportement des statistiques systèmes en mode RAC

Lors de la collecte des statistiques systèmes d'un environnement RAC, on peut déclencher la collecte à partir de n'importe quelle instance. Tout s'effectue correctement... À vrai dire, pas exactement car les autres instances attachées au cluster ne prennent pas en compte les nouvelles valeurs.

Je me suis aperçu de ce problème quand une requête est devenue gourmande. Le plan d'exécution de la requête était complètement différent sur 2 instances. En me connectant sur l'une des instances du cluster, j'ai vérifié les valeurs des statistiques (select * from sys.aux_stats$). C'est à ce moment que j'ai remarqué qu'elles avaient été régénérées récemment et l'un de mes collègues me l'a confirmé. J'ai aussi eu la confirmation que la lenteur avait été observée depuis la date de la régénération des statistiques. Donc, je pouvais déduire que la collecte de statistiques systèmes était l'élément déclencheur.

Que se soit sur l'une ou l'autre des instances du cluster, les mêmes statistiques sont supposées être utilisées dont je suspecte que le problème soit au niveau de la mémoire ou d'une certaine cache. J'ai tenté une simple purge du "shared pool" et celle des données mais sans succès. Comme à l'habitude, j'ai fait une petite recherche sur Metalink et effectivement, il y a un problème de répertorier sur la base de données 10.2.0.3. (ID 7645777.8) concernant un plan d'exécution différent sur des instances RAC. La solution : Recycle the instances... Qu'est-ce que cela veut dire exactement ?!? J'imagine que c'est l'équivalent d'un redémarrage d'instance et, si c'est le cas, et bien, je ne peux pas me le permettre. Vu que ce bug concerne les statistiques systèmes alors, j'ai décidé de pousser la vérification en influençant l'optimisateur pour qu'il n'utilise pas les statistiques liées au CPU. Pour ce faire, j'ai utilisé le hint "NO_CPU_COSTING". Les résultats furent concluants, j'ai obtenu le même plan d'exécution que sur l'instance où la requête est optimale.

Tant qu'à pousser, j'ai décidé d'effectuer une trace de l'optimisateur sur l'exécution de la requête sur chacune des instances avec la commande :

ALTER SESSION SET EVENTS '10053 trace name context forever, level 1'

Les 2 fichiers de trace ont démontrés que les statistiques systèmes sont différentes donc ça confirme que l'optimisateur n'utilise pas les mêmes valeurs d'une instance à l'autre... Inquiétant ?

Après réflexion, je me suis dit, pourquoi ne pas répéter ou presque les mêmes étapes de collecte de statistiques systèmes mais cette fois-ci, en étant connecté sur l'instance n'ayant pas le même comportement ou si vous préférez, le même plan d'exécution. Alors, j'ai effectué ceci :

-- Établir une connexion sur l'instance qui n'utilise pas les nouvelles valeurs
connect sys@inst1 as sysdba

-- Prendre une copie des statistiques actuelles
Drop table stats_bkp;
EXECUTE DBMS_STATS.CREATE_STAT_TABLE ('SYS','stats_bkp','SYSAUX');
EXECUTE DBMS_STATS.EXPORT_SYSTEM_STATS (stattab => 'stats_bkp', statid => 'stats_bkp', statown => 'SYS');
select * from sys.stats_bkp;

-- Charger les valeurs
execute DBMS_STATS.DELETE_SYSTEM_STATS;
EXECUTE DBMS_STATS.IMPORT_SYSTEM_STATS (stattab => 'stats_bkp', statid => 'stats_bkp', statown => 'SYS');
select * from sys.aux_stats$;

-- Initialiser les espaces mémoires
Alter system flush shared_pool;
Alter system flush buffer_cache;

Et bien oui, ça fonctionne! La requête a maintenant le même plan d'exécution que sur l'autre instance. Stop! Le problème s'est déplacé sur l'autre instance... oups! Ok, alors, je vais refaire la même chose, mais cette fois-ci sur l'instance où le problème s'est déplacé. Suite à l'exécution des commandes précédentes, le plan d'exécution est redevenu celui d'auparavant et, sans impacter le plan d'exécution sur l'autre instance.

Je crois qu'un redémarrage de l'instance aurait pu régler le problème sauf que lorsque nous sommes sur un environnement de production, cela n'est pas toujours une solution et ce, même si c'est un environnement RAC à moins que vous preniez le temps de déplacer tous les services vers une autre instance puis que vous patientez jusqu'à ce que toutes les sessions se soient reconnectés vers la nouvelle instance.

Les caprices d'Oracle EM

S'il vous arrive de rencontrer l'erreur "numeric or value error" lorsque vous naviguez à travers l'outil d'Oracle Enterprise Manger (Grid Control)... et possiblement la version Stand Alone (database Control), pensez à tout simplement modifier la langue utilisée par votre fureteur.

J'ai rencontré à maintes reprises ce problème et le simple fait de mettre la langue à "Anglais" vous permet de continuer ou plutôt contourner ce problème.