Database Software

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

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,

>
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.)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

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,

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
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.)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

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

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;

>
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
/

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

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.