librarycachepin与PROCEDURE的重建,library cache pin与PROCEDURE的重建
【 tulaoshi.com - Oracle教程 】
前面提到,Oracle10g重建Procedure的处理有所增强,最初看到这个增强的时候,我想这个增强是否可以减少困扰已久的Library Cache的竞争呢?
我们看一下以下测试,首先在第一个session执行操作:
SQL create or replace PROCEDURE pining[next]
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:57SQL
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
(本文来源于图老师网站,更多请访问http://www.tulaoshi.com)
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:1809: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
来源:http://www.tulaoshi.com/n/20160129/1494094.html
看过《librarycachepin与PROCEDURE的重建》的人还看了以下文章 更多>>