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
where a.VIEW_NAME='GV$LATCH_CHILDREN'

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 :

GV$LATCH
GV$LATCH_CHILDREN
GV$LATCH_PARENT
V$LATCH
V$LATCHHOLDER
V$LATCHNAME
V$LATCH_CHILDREN
V$LATCH_MISSES
V$LATCH_PARENT

 

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

 

 

1.

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

2.

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

3.

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: ,

Filter by APML

Calendar

<<  December 2018  >>
MoTuWeThFrSaSu
262728293012
3456789
10111213141516
17181920212223
24252627282930
31123456

View posts in large calendar

TextBox