eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« Oracle10g中过程(PROCEDURE )重建的增强 | Blog首页 | EMC-我的硬盘可以继续坏 »

library cache pin与PROCEDURE的重建

前面提到,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-


历史上的今天...
    >> 2013-04-02文章:
    >> 2005-04-02文章:
    >> 2004-04-02文章:

无觅

By eygle on 2007-04-02 09:42 | Comments (6) | SQL.PLSQL | 1398 |

6 Comments

Oracle10g内部改进还不少
沙发

到11g再做这个时, 是不是又变了呢? Create procedure是非可以顺利通过呢?

grant is ok , but not for revoke. it still needs library cache pin though , interesting.

revoke的确会触发library cache pin ,有意思

估计对已经有权限的再授权的话, 就不会pin了吧, 应当试试, 一开始已给了public的权限, 后来再给user显式地授权.

确实有意思。


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com