Wait event latch free

Avant d'exposer le problème rencontré il y a quelques jours, je vous encourage à lire ce lien qui explique ce qu'est un latch et permet de trouver l'origine de ce type de contention: http://tech.e2sn.com/oracle/troubleshooting/latch-contention-troubleshooting.

En testant le produit DB Change Manager d'Embarcadero, ce dernier permettant principalement de gérer les DDL, je me suis heurté à un fort ralentissement d'une instance Oracle 9.2. Après avoir identifié la session à l'origine du problème ( vue v$session ), j'ai commencé par regarder les wait events: select * from v$session_wait  where sid = 20. On pouvait alors observer des wait events de type latch free. En exécutant la requête suivante: select * from v$latchname  where latch# = 98, j'ai identifié le nom du latch, cache buffers chains.

Puis, j'ai utilisé les deux requêtes suivantes:
- select count(*) from v$latch_children where name = 'cache buffers chains' => 1024 ( seule la session posant problème était active ).
- select name, gets, misses, sleeps  from v$latch_children where name = 'cache buffers chains' => 0 <= misses <= quelques centaines.

Ensuite, j'ai utilisé l'outil latchprof de T.Poder  pour identifier les requêtes à l'origine de ces latchs ( exemple: @latchprof sid,name,sqlhash % 2E6982FC 100000 , addr = 2E6982FC ). Après quelques lancements, on pouvait remarquer qu'une requête ( vue v$sql ) revenait systématiquement:
SELECTB.OWNER,B.CONSTRAINT_NAME,B.CONSTRAINT_TYPE,B.OWNER,B.TABLE_NAME,A.OWNER,A.CONSTRAINT_NAME,A.OWNER,A.TABLE_NAME FROM SYS.DBA_CONSTRAINTS A,SYS.DBA_CONSTRAINTS B, SYS.DBA_OBJECTS O
WHERE A.CONSTRAINT_TYPE='R' AND A.R_CONSTRAINT_NAME=B.CONSTRAINT_NAME AND A.R_OWNER=B.OWNER  AND O.OWNER=A.OWNER AND O.OBJECT_NAME=A.TABLE_NAME AND O.OBJECT_TYPE='VIEW'  AND B.OWNER='XXXXX' AND
A.OWNER NOT IN ('SYS','SYSTEM','DBSNMP','CTXSYS','ORDSYS','OUTLN','MDSYS','OLAPSYS','ORDPLUGINS','ORDSYS','WKSYS','WMSYS','XDB') AND NOT (B.TABLE_NAME LIKE 'BIN$%')
ORDER BY B.OWNER, B.TABLE_NAME

Un rapide coup d'oeil sur la vue v$sql_plan a permis de voir une multitude de nested loop et le non calcul des statistiques sur le dictionnaire.

Après avoir consulté la note 245051.1 sur le support d'Oracle ( anciennement Metalink ), j'ai lancé les statistiques sur le dictionnaire: execute dbms_stats.gather_schema_stats('SYS', cascade=>TRUE); pour résoudre le problème initial.

Pour aller plus loin: Latch, mutex and beyond