前面提到，Oracle10g重建Procedure的處理有所增強，最初看到這個增強的時候，我想這個增強是否可以減少困擾已久的Library Cache的競爭呢？
我們看一下以下測試，首先在第一個session執行操作：
SQL> create or replace PROCEDURE pining
2 IS
3 BEGIN
4 NULL;
5 END;
6 /
Procedure created.
SQL>
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> create or replace procedure calling
2 is
3 begin
4 pining;
5 dbms_lock.sleep(60);
6 end;
7 /
Procedure created.
SQL>
SQL> col object_name for a30
SQL> select object_name,last_ddl_time from dba_objects where object_name in ('PINING','CALLING');
OBJECT_NAME LAST_DDL_TIME
------------------------------ -------------------
CALLING 2007-04-02 09:12:57
PINING 2007-04-02 09:12:57
SQL>
SQL> exec calling;
此時Calling對於Pining的引用將會在Pining的Body上獲得共享Pin，此時在另外一個Session執行重建Procedure的操作：
SQL> create or replace PROCEDURE pining
2 IS
3 BEGIN
4 NULL;
5 END;
6 /
這個操作將一直掛起，直到第一個session的操作完成，此時在第三個session可以觀察到Library Cache Pin的競爭：
SQL> select sid,event from v$session where username='EYGLE'
2 /
SID EVENT
---------- ----------------------------------------------------------------
137 library cache pin
139 PL/SQL lock timer
157 SQL*Net message to clIEnt
當第一個session執行完成之後，第二個session的操作隨之完成，我們可以看到LAST_DDL_TIME並未改變：
SQL> exec calling;
PL/SQL procedure successfully completed.
SQL>
SQL> select object_name,last_ddl_time from dba_objects where object_name in ('PINING','CALLING');
OBJECT_NAME LAST_DDL_TIME
------------------------------ -------------------
CALLING 2007-04-02 09:12:57
PINING 2007-04-02 09:12:57
實際上session 2執行了一次無謂的Library Cache Pin，理想的方式應該是，Oracle能夠判斷之前的Library Cache Pin的模式，如果是共享模式，則可以跳過Pin請求，如果是排他模式，則必須等待，目前的處理並不能從實質上改變競爭。 不過並非全無益處，我們發現，對於另一類DDL操作，Oracle完全可以跳過Library Cache Pin的請求，這類操作是Grant，在以前版本中的行為可以參考：
http://www.eygle.com/archives/2004/10/shared_pool-5.Html
在Oracle10g中，Grant授權操作無需再獲得Library Cache Pin的排他鎖，我們看以下測試：
在Session 1中執行：
09:40:18 SQL> drop procedure calling;
Procedure dropped.
09:40:18 SQL>
09:40:18 SQL> drop procedure pining;
Procedure dropped.
09:40:18 SQL>
09:40:18 SQL> create or replace PROCEDURE pining
09:40:18 2 IS
09:40:18 3 BEGIN
09:40:18 4 NULL;
09:40:18 5 END;
09:40:18 6 /
Procedure created.
09:40:18 SQL>
09:40:18 SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
09:40:18 SQL> create or replace procedure calling
09:40:18 2 is
09:40:18 3 begin
09:40:18 4 pining;
09:40:18 5 dbms_lock.sleep(60);
09:40:18 6 end;
09:40:18 7 /
Procedure created.
09:40:18 SQL>
09:40:18 SQL> col object_name for a30
09:40:18 SQL> select object_name,last_ddl_time from dba_objects where object_name in ('PINING','CALLING');
OBJECT_NAME LAST_DDL_TIME
------------------------------ -------------------
CALLING 2007-04-02 09:40:18
PINING 2007-04-02 09:40:18
09:40:18 SQL>
09:40:18 SQL> exec calling;
在Session 2執行授權：
SQL> set time on
09:40:22 SQL> grant execute on pining to sys;
Grant succeeded.
09:40:22 SQL>
我們看到Session 2的授權順利通過，再轉到Session 1：
09:40:18 SQL> exec calling;
PL/SQL procedure successfully completed.
09:41:18 SQL>
09:41:18 SQL> select object_name,last_ddl_time from dba_objects where object_name in ('PINING','CALLING');
OBJECT_NAME LAST_DDL_TIME
------------------------------ -------------------
CALLING 2007-04-02 09:40:18
PINING 2007-04-02 09:40:22
我們看到對象PINING的LAST_DDL_TIME已經變化。
看來Grant已經能夠繞過了Library Cache Pin的競爭，這是Oracle10g的增強。
這個問題最早由網友dqpylf在閱讀我的新書《深入淺出Oracle》，在不同版本中測試案例時發現，今天才有時間做一點探究，感謝dqpylf。
-The End-
