Recent site activity

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)



Comments