原文请参考eygle的《关于shared pool的深入探讨》，当时eygle在9.2.0.3中做的测试，而我在10.2.0.1中做了相同的测试，发现Oracle已经对grant操作在library cache object上加锁模式发生了变化，特此记录。

SQL> create or replace PROCEDURE pining
  2  IS
  3  BEGIN
  4          NULL;
  5  END;
  6  /

Procedure created.

SQL> create or replace procedure calling
  2  is
  3  begin
  4          pining;
  5          dbms_lock.sleep(1000);
  6  end;
  7  /

Procedure created.

开4个session，sid分别为141，142，143，159
1. 在session1执行：
SQL> select distinct sid from v$mystat;

       SID
----------
       141

SQL> exec calling;
2. 在session2执行：
SQL> select distinct sid from v$mystat;

       SID
----------
       142

SQL> grant execute on pining to mytest;

Grant succeeded.

SQL> alter procedure pining compile;
编译操作会挂起

3. 在session4执行：
SQL> select distinct sid from v$mystat;

       SID
----------
       159

SQL> select sid,event from v$session_wait where event like 'library%';

       SID EVENT
---------- ----------------------------------------------------------------
       142 library cache pin
4. 在session3执行：
SQL> select distinct sid from v$mystat;

       SID
----------
       143

SQL> grant execute on pining to mytest;
此时grant操作也挂起

5. 在session4执行：
SQL> select sid,event from v$session_wait where event like 'library%';

       SID EVENT
---------- ----------------------------------------------------------------
       142 library cache pin
       143 library cache lock
6. session2的编译操作超时退出，并报ORA-04021错误

SQL> alter procedure pining compile;
alter procedure pining compile
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object SYS.PINING

7. session2编译操作超时退出后，session3的grant操作成功

SQL> grant execute on pining to mytest;

Grant succeeded.