If you have several users waiting on a library cache lock, they are normally blocked by one user that is waiting on a
library cache pin. You have to find out who is blocking the user waiting on the library cache pin and find out what they are doing.
Our test case performs the following steps (admittedly contrived):
- Create procedure that sleeps for one second
- Using python, run ten simultaneous connections that execute this procedure, each in a loop of 300. This means the sessions should finish in 300 seconds if there is no other blocking
- In another SQL*PLUS session, run a loop of 300 executions that compiles the procedure above
What we found when we did this is that one of the given sessions would be waiting on the “PL/SQL lock timer” event, which blocked the session attempting to do the compile, waiting on “library cache pin”. The other nine sessions were blocked by the session doing the compile, waiting on “library cache lock”.
According to Steve Adams’ book, “Oracle 8i Internal Services” (dated, but still an incredibly good book for conceptual understanding), a library cache lock can only be broken when the library cache object is not also pinned. Since the compiling session is trying to get the pin, the other nine sessions wait on “library cache lock”.
The fix for this is to either:
- not require library cache pins, i.e, don’t cause things that require recompilation
- Tune the object which is blocking the pin so that it doesn’t block the pin (and the other sessions, in return) for an extended period of time.