Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Library Cache Lock

878536Aug 14 2011 — edited Sep 13 2011
Hi,
my db version is 11.0.1.7
i have high library cahce lock waits, and problematic sql is SELECT statement.
what may be the cause?
shared_pool_size 1500 MB
session_cached_cursors 20.
Thanks,


{
Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
library cache lock 6,193,375 203,610 33 79.9 Concurrenc
DB CPU 12,668 5.0
library cache: mutex X 59,365,758 11,970 0 4.7 Concurrenc
cursor: mutex S 35,166,712 8,079 0 3.2 Concurrenc
control file sequential read 2,208,754 2,646 1 1.0 System I/O
}

Comments

sgalaxy
Hi,
you can consult the following:
http://asktom.oracle.com/pls/asktom/f?p=100:11:3135704510460626::::P11_QUESTION_ID:1163635055580

http://www.dba-oracle.com/m_library_cache_load_lock.htm

Greetings,
Sim
878536
Hi,
there is no DDL activity in terms of causing library cache lock.
cursor_sharing is SIMILAR.
But sql causing library cache lock is SELECT statement.
And version count for that SELECT statement is high.
select count(id) from XXXX where a1 is not null and a2 is not null and a3=:"SYS_B_0"
what may be the reason for that.
Thanks,
Jonathan Lewis
>
cursor_sharing is SIMILAR.
But sql causing library cache lock is SELECT statement.
And version count for that SELECT statement is high.
*select count(id) from XXXX where a1 is not null and a2 is not null and a3=:"SYS_B_0"
what may be the reason for that.
How high is high ?
Some people might say that a version count of 10 is high, some might not think it high until it reaches 100 ?

High version counts lead to increased activity on library cache latches (and mutexes).
cursor_sharing = SIMILAR leads to an increase in version counts because Oracle will re-optimize the query if it thinks the input values "might make a difference" to the execution plan.
Range-based predicates, partition keys in predicates, histograms on columns in predicates are cases where you might see re-optimisation taking place.

I believe Oracle's suggestion for 11g is to set cursor_sharing to force (rather than similar) and let adaptive cursor sharing take care of variation in input values. This MIGHT (in your case) result in fewer versions and less contention.
The ideal solution is to rewrite the code so that you can set cursor_sharing back to exact.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk

To post code, statspack/AWR report, execution plans or trace files, START and END the text with the tag {noformat}
{noformat} (the word "code" in lowercase, curly brackets, no spaces) so that the text appears in
fixed format
.

There is a +"Preview"+ tab at the top of the text entry panel. Use this to check what your message will look like before you post the message. If it looks a complete mess you're unlikely to get a response. (Click on the +"Plain text"+ tab if you want to edit the text to tidy it up.)
878536
Hi Jonothan,
thanks for reply.
version_count in my case is about 8500 in 1 hour period.
If i remove histograms for the columns in the sql, would that solve my problem?
In that case, no optimization will take for the execution plan ?
Thanks a lot,
Jonathan Lewis
user8907689 wrote:
Hi Jonothan,
thanks for reply.
version_count in my case is about 8500 in 1 hour period.
If i remove histograms for the columns in the sql, would that solve my problem?
It might do. I gave you two other reasons why you get re-optimisation, though, and I know there is one more that I can't remember at the moment.
You may still see the problem persist, at least for a while, because with that many child cursors you could have a bit of a mess in your library cache that stops the existing child cursors from being discarded properly - even after a flush shared pool - until you restart the instance. (There are a couple of bugs in this area.) But if you're lucky a flush shared pool after dropping the histograms might clear the pool and restore sanity
In that case, no optimization will take for the execution plan ?
Typically you tend to end up with a single plan that will get re-used - but there are various reasons (length of string variables, nls settings varying across clients, different optimizer environments) why you might see more than one. For a query as simple as yours, though I've rearely seen number beyond a couple of dozen. Once the small number of plans has been created, re-optimization is no longer needed.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk


To post code, statspack/AWR report, execution plans or trace files, START and END the text with the tag {noformat}
{noformat} (the word "code" in lowercase, curly brackets, no spaces) so that the text appears in
fixed format
.

There is a +"Preview"+ tab at the top of the text entry panel. Use this to check what your message will look like before you post the message. If it looks a complete mess you're unlikely to get a response. (Click on the +"Plain text"+ tab if you want to edit the text to tidy it up.)
user8985629
I am having this issue where my select statemant caused library cache lock and oteher queries where hung ...what could be the reason for select statement to cause lock.

Thanks
sb92075
user8985629 wrote:
I am having this issue where my select statemant caused library cache lock and oteher queries where hung ...what could be the reason for select statement to cause lock.
post SQL & results that prove statement above

With Oracle readers do not block writers & writers do not block readers.

do as below so we can know complete Oracle version & OS name.

Post via COPY & PASTE complete results of
SELECT * from v$version;
VenkatB
>
I am having this issue where my select statemant caused library cache lock and oteher queries where hung ...what could be the reason for select statement to cause lock.
>

Run this SQL when you encounter the library cache lock to find out who and which SQL is really causing the lib lock. Think I found this SQL from one of the OTN sites long back when I had this library cache lock problem in 9i databases.
SELECT /*+ ordered */ 
	w1.sid  waiting_session,  
	w1.username who,
 	w1.sql_id SQL_ID,
	h1.sid  holding_session,
	w.kgllktype lock_or_pin,
	w.kgllkhdl address,     
	DECODE(h.kgllkmod,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 'Unknown') mode_held,
        DECODE(w.kgllkreq,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 'Unknown') mode_requested,
	h1.sql_hash_value  ,
	lock_id1 object_waited_on
FROM 
	dba_kgllock w, 
	dba_kgllock h, 
	v$session w1, 
	v$session h1 , 
	dba_lock_internal dl
WHERE   
	(((h.kgllkmod != 0) AND (h.kgllkmod != 1)      
   AND 	((h.kgllkreq = 0) OR (h.kgllkreq = 1)))
   AND  (((w.kgllkmod = 0) OR (w.kgllkmod= 1))      AND ((w.kgllkreq != 0)
   AND (w.kgllkreq != 1))))
    AND  w.kgllktype    =  h.kgllktype   AND  w.kgllkhdl        =  h.kgllkhdl
AND  w.kgllkuse=w1.saddr   AND  h.kgllkuse=h1.saddr
and dl.mode_requested<>'None'
and dl.mode_requested<>dl.mode_held
and w1.sid=dl.SESSION_ID
/
Karan
This metric library cache lock happens when one client can prevent other clients from accessing the same object or the client can maintain a dependency for persistent time and this lock is also obtained to locate an object in the library cache. In your case i think library cache lock corruption can also be the cause may be, and a process might be waiting for a library cache lock without a holder or incorrect lock mode and so on .. first try to restart the instance and then try with your query.. and definitely if you are in 11G you better keep it to FORCE for cursor_sharing in order to reduce versions and contention.

Regards
Karan
user8985629
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production


5 rows selected.
1 - 10
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 11 2011
Added on Aug 14 2011
#general-database-discussions
10 comments
4,180 views