RE: wierd wait event - library cache load lock

John,
Thanks. I forgot all about LAST_DDL_TIME. Of course this hasn't happened again, but at least I've got a strategy if/when it does.

Henry

-----Original Message-----
John Kanagaraj
Sent: Monday, July 07, 2003 3:10 PM
To: Multiple recipients of list ORACLE-L

Henry,

These locks should normally occur when dependent objects needs to be compiled (internally by the kernel or externally by scripts) on account of invalidations. This problem is magnified in large ERP packages which exhibits lots of dependencies and objects. Since you mention that this is just moving into Production, you are probably experiencing a lot of last-minute changes that are causing this. The only way of controlling this is by scheduling changes to off-hours (even in Dev/UA), and most certainly in Production. Sort DBA_OBJECTS by LAST_DDL_TIME to check which objects changed. You could also write an ON DDL trigger to log details of who modified which object from where and at what time.

You could look at ML Note 62143.1 'Understanding and Tuning the Shared Pool' to understand the intricacies of the Shared pool.

If this is not due to invalidations, then it could be on account of a number of shared pool related bugs (don't know what version you are on).

John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

Great, uplifting music - http://www.klove.com

• The opinions and statements above are entirely my own and not those of my employer or clients **

> -----Original Message-----
> From: Henry Poras [mailto:[EMAIL PROTECTED]
> Sent: Thursday, July 03, 2003 9:06 AM
> To: Multiple recipients of list ORACLE-L
> Subject: wierd wait event - library cache load lock
>
>
> We have a PeopleSoft system (Finanacials) that is just moving into
> production. Some of the conversion and data entry stuff was
> running slowly
> so I peaked at our system and session wait events (as well as
> looking at
> some recent history with statspack). There was a huge amount
> of time_waited
> for 'library cache load lock'. Never came across this one
> before. According
> to the Docs, it is a lock used to load an object into the
> library cache (you
> don't want the same object loaded more than once). I am
> trying to find more
> information so I can debug this. Of course the problem isn't
> there today
> (other modules are being worked on. Maybe that is why).
>
> Just trying to be ready when/if this happens again. I'm
> thinking a 10046
> trace might give me some object information about what is being
> loaded/locked. Metalink suggests taking a systemstate dump
> (though they seem
> to mix up 'library cache lock' with 'library cache load
> lock'. Don't know if
> the same tracking techniques work on both.).
>
> Anybody else see this before? Suggestions on what to do if it shows up
> again?
>
> Thanks.
>
> Henry
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Henry Poras
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: John Kanagaraj
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Henry Poras
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).