Recompiling Busy Package Bodies in a RAC Environment

In short, probably not a good idea.

A package body needed to be recompiled on production. The large package body took about ten seconds to compile in QA. Obviously, nothing else went invalid or needed to be recompiled.

On production, the compile seemed to hang, as it was still going on after 30s. OEM showed a spike in sessions working or waiting, all of which were concurrency waits. Investigating further, these were all “library cache pin” waits. We killed the rebuild session from its terminal, and about two-thirds of the sessions waiting disappeared fairly quickly.

The remaining hundred or so sessions were all on the same node. Each was trying to execute one of two stored procedures from the package we had tried to recompile. I looked in GV$SQL to see if there was anything significantly different about the statements in each instances memory, but this was slightly out of my depth and nothing was immediately obvious to me.

The waiting sessions were made to go away by being manually killed. The available literature on how to “fix” library cache pins generally refers to x$ views to which I didn’t have access. Nor was DBA_BLOCKERS available.

What I would like to know is, what was the correct way of dealing with this? Was a lock or latch held on the package body by one of the sessions? If so, was it for a good reason, or was there a process failure of some sort? Where could I have looked for this information?