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 APPS.XXXX9999_PKG

I ran into this issue and used the following:

How to: Determine if a Package that is About to be Compiled is Being Used Currently [ID 1054939.6]

SQL> @/tmp/who_is_using.plb

Procedure created.

SQL> set serveroutput on

SQL> exec who_is_using('XXXX9999_PKG');

(1-866) - APPS

PL/SQL procedure successfully completed.