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 -