OCP Blog
Super DBA
Another Solution for latch library cache, Troubleshooting this event
by Vazha Mantua Tuesday, May 3, 2011 4:36 PM

Early we discuss about latch library lock and we have got some solutions for avoid this problem for Database.


Now discuss troubleshoot scenario for latch library cache event:


First of all we should find latch address of problem statement:

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

where value of p1text must be “address” and p1raw latch address for problem latch.

Now we have got latch address , we should find latch number.


select a.CHILD# from v$latch_children a
where a.ADDR=p1raw



We know child number of possible bottleneck.


Let’s prove that we can’t find exact SQL statement if we know only a latch address.

We should get structure of v$latch_children view with this statement:


select * from v$fixed_view_definition a

Result of this statement tell us that view v$latch_children is based on x$ksllt object.


Now find which of data dictionary object created by x$ksllt with this statement


select * from v$fixed_view_definition a
where upper(a.VIEW_DEFINITION) like '%X$KSLLT%'
or upper(a.VIEW_DEFINITION) like '%GV$LATCH%'

Result of this Select are :



in these views the are no sql statements.



What we can do for find possible problematic SQL statement , if we only know number of child latch.


All SQL statements are served by number of latches given by parameter _kgl_latch_count, which means total number of latches in system , maximum value of this parameter is 66.


Return back to our case.


For find possible problematic statement we should run this statements:


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


Problematic sql statements must be statements with maximum number of exection.


Also there is very interesting view named v$db_object_cache.

with this view and these SQL statements we can find cause of latch library cache




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


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


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

if you can’t find cause of latch library cache you can restart database and SQL statements will give new order of latch child number, and you can compare statements with new problematic latch child number.

Tags: ,

Comments (3) -

9/12/2011 4:06:00 PM #

culvert pipe

In case you could e-mail me with a couple of recommendations on just how you made your blog look this excellent, I might be grateful.

culvert pipe United States

11/18/2011 4:54:34 AM #

repairing credit score

Its really a cool and useful piece of info. I?m happy that you just shared this helpful info with us. Please stay us up to date like this. Thank you for sharing.

repairing credit score United States

1/25/2012 9:16:59 AM #

carpet cleaning forest lake

Thanks so much for writing all of the excellent information! Looking forward to checking out more posts!

carpet cleaning forest lake United States

Pingbacks and trackbacks (1)+

Add comment

  Country flag

  • Comment
  • Preview

Filter by APML


<<  March 2019  >>

View posts in large calendar