Latch free

Oracle: latch free

latch free is a generic name for a latch problem. To find out the kind of problem we have to find out what latch the problem is on.

We can get information about "latch free" or any wait for that matter from V$EVENT_NAME. V$EVENT_NAME gives brief descriptions of the fields P1, P2 and P3 for a wait event:

   select * from v$event_name
   where name = 'latch free'

   PARAMETER1 PARAMETER2 PARAMETER3 
   ---------- ---------- ----------
      address     number      tries 

In other words the latch# is P2 aka PARAMETER2. We can go int ASH and find out the id, the latch#, of all latch free waits we had:

   select p2, count(*)
   from v$active_session_history
   where event='latch free'
   group by p2

           P2   COUNT(*)
   ---------- ----------
          127       3556

In this case I was only getting waits on latch# 127. What is latch# 127? We can look it up in v$latchname:

   select * from v$latchname where latch#=127

   LATCH#     NAME        
   ---------- --------------
   127        simulator lru latch 

Embarcadero's DB Optimizer does this for you (and OEM from Oracle doesn't)