Library Cache Pin Waits during PL/SQL compilation

Did you ever tried to compile a PL/SQL procedure in your production database and it took long time?

Did you face slowdown or other production issues because of this?

Did you hit an error similar to the following when you or a developer tried to compile PL/SQL procedure which is referenced by another constantly executing high load PL/SQL procedure?

ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object AZIMUTH.UPDATEME

If yes, then carry on reading.

This post is about Conncurrency Wait issues you might get when trying to compile highly used constantly executing PL/SQL code in your production database.

I have come accross this situation on an Oracle 10g R2 production platform. What hapenned was someone tried to quickly compile PL/SQL procedures which were executing 2 times in a second by many sessions. More this procedure was called by a dozen of other PL/SQL procedures that is there were lots of dependencies between packages, views. The PL/SQL procedure in question was basically the busiest piece of code in the database. Logging sessions and their events.

You have to be carefull when you want to compile such highly used constantly executing piece of PL/SQL code in your system, at run-time. The rule is, that objects cannot be changed when they are used. As the session you want to compile the code in tends to keep on waiting to obtain a Library Cache Pin latch from the shared pool. If you do go ahead and compile such code, without scheduling downtime, you will probably halt the system. Try it with re-compiling packages which have dependencies with multiple other packages which are constantly in use, if you dare on production, and see what happens. You will face concurrency waits. Oracle says in metalink that such behaviour is expected and that you have to schedule downtime and maintenance window for re-compiling heavily used PL/SQL. The solution seem to be not to write complex dependencies in PL/SQL or to arrange maintenance window for deployments of PL/SQL. That is what the support told me, they also asked me to use some undocumented "_???" parameters to help with the latches. But the maintenance window seems to be the easiest or maybe standby database configuration. Feel free to comment on this post, workarounds and better solutions you can suggest.

See some Metalink notes with good reading on the subject:

• Doc ID: 115656.1 WAIT SCENARIOS REGARDING LIBRARY CACHE PIN AND LIBRARY CACHE LOAD LOCK



• Doc ID: 1054939.6 COMPILATION OF PACKAGE IS HANGING ON LIBRARY CACHE LOCK AND LIBRARY CACHE



• Doc ID:264476.1 ORA-4021 'LIBRARY CACHE PIN' and PL/SQL blocks



• Doc ID:169139.1 How to analyze ORA-04021 or ORA-4020 errors?

I am trying to simulate the above Concurrency waits with the following procedures where I run lock_test by many sessions and then try to re-compile updateme. I do see the Concurrency Waits in V$SESSION_WAITS.



CREATE OR REPLACE
PROCEDURE updateme
AS
BEGIN
FOR i IN 1 .. 100000000
LOOP
UPDATE test SET names = 'x' WHERE names = 'b';
END LOOP;
ROLLBACK;
END;





CREATE OR REPLACE
PROCEDURE lock_test
AS
BEGIN
updateme;
END;