Library cache pin/lock 在Oracle 10g的增强

Library cache pin/lock 在Oracle 10g的增强:

从Oracle 10g开始,以上测试将不会看到同样的效果,这是因为Oracle 10g对于对象编译与重建做出了增强。注意以下测试(来自Oracle 10gR2环境):

sys@NEI> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
sys@NEI> select object_name,last_ddl_time from user_objects where object_name='PINING';
OBJECT_NAME          LAST_DDL_TIME
-------------------- -------------------
PINING               2009-12-06 15:09:24
sys@NEI> create or replace PROCEDURE pining
  2  IS
  3  BEGIN
  4          NULL;
  5  END;
  6  /
Procedure created.
sys@NEI> select object_name,last_ddl_time from user_objects where object_name='PINING';
OBJECT_NAME          LAST_DDL_TIME
-------------------- -------------------
PINING               2009-12-06 15:09:24

注意当重新replace一个过程时,Oracle会首先执行检查,如果代码前后完全相同,则replace工作并不会真正进行(因为没有变化),对象的LAST_DDL_TIME不会改变,这就意味着Latch的竞争可以减少

再来看一下此前的测试在Oracle 10g中的情况如果。
首先在Session 1中执行:

sys@NEI> create or replace PROCEDURE pining
  2  IS
  3  BEGIN
  4  NULL;
  5  END;
  6  /
Procedure created.
sys@NEI> create or replace procedure calling
  2  is
  3  begin
  4  pining;
  5  dbms_lock.sleep(60);
  6  end;
  7  /
Procedure created.
sys@NEI> select object_name,last_ddl_time from dba_objects where object_name in ('PINING','CALLING');
OBJECT_NAME          LAST_DDL_TIME
-------------------- -------------------
PINING               2009-12-06 15:26:58
CALLING              2009-12-06 15:19:38
sys@NEI> set time on
15:31:49 sys@NEI> exec calling;
PL/SQL procedure successfully completed.
15:32:59 sys@NEI>

在Session 1过程中,切换到Session 2执行授权:

sys@NEI> set time on
15:32:00 sys@NEI> grant execute on pining to tq;
Grant succeeded.

可以看到Session 2的授权顺利通过,再转到Session 1:

15:32:59 sys@NEI> select object_name,last_ddl_time from dba_objects where object_name in ('PINING','CALLING');
OBJECT_NAME          LAST_DDL_TIME
-------------------- -------------------
PINING               2009-12-06 15:32:02
CALLING              2009-12-06 15:19:38

注意到对象pining的LAST_DDL_TIME已经变化grant授权已经能够绕过library cache pin的竞争,这是Oracle 10g的增强

- The End -