Oracle Latch Library Cache Troubleshooting

What are latches?

Latches are serialization mechanisms that protect areas of Oracle’s shared memory(the SGA). In simple terms latches prevent two processes from simultaneously  updating-and possible corrupting-the same area of the SGA.

The total number of latches in the system is defined by _kgl_latch_count parameter.(The maximum value of this parameter is 66). It cannot be seen in v$parameter. To change its value you should add it in init.ora file.

The major cause of library cache latch contention is the failure to use bind variables.

Note: The only changes that you can do at Oracle database level is to increase total number of latches(_kgl_latch_count parameter). Your aim is to find problematic SQLs that may have failure in using bind variables, or are badly written(junk) SQLs…

For to find possible problematic SQL statements we can do the following:

select p1text,p1raw
from v$session_wait
where EVENT='latch: library cache'

where value of p1text must be “address”.

Now let’s get the latch address:

select CHILD#
from v$latch_children
where ADDR=p1raw

select *
from v$sqlarea
where CHILD_LATCH=child#
order by EXECUTIONS desc

Top of the result will be all SQL statements that have high execution number, accordingly they tend to be problematic statements, which should be optimized.

The following statements will help you to distinguish problematic and non-problematic SQLs.

These are several ways for to find them:

1. SQLs with high executions

select *
from v$db_object_cache
where CHILD_LATCH=child#
order by EXECUTIONS


2. SQLs with high lock counts

select *
from v$db_object_cache
where CHILD_LATCH=child#
order by lock


3. SQLs with high pin counts

select *
from v$db_object_cache
where CHILD_LATCH=child#
order by pins