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

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

biuquote
  • Comment
  • Preview
Loading

Filter by APML

Calendar

<<  October 2018  >>
MoTuWeThFrSaSu
24252627282930
1234567
891011121314
15161718192021
22232425262728
2930311234

View posts in large calendar

TextBox