Oracle 11g下重现library cache lock等待事件

从下面的例子中可以看到，在生产数据库中对象的重新编译会导致library cache lock，所以应该尽量避免在业务高峰期编译对象。如果是package或过程中存在复杂的依赖关系就极易导致library cache lock的出现，所以在应用开发的过程中，也应该注意这方面的问题。session1:SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> create or replace procedure pining
is
begin
null;
end;
/
SQL> create or replace procedure calling
is
begin
pining;
dbms_lock.sleep（200）;
end;

/

session2:

alter procedure pining compile;
session3:
drop procedure pining;
session4:
SQL> select sid, event,wait_class, seconds_in_wait
2 from v$session_wait w
3 where w.WAIT_CLASS <> "Idle";
SID EVENT WAIT_CLASS
---------- ---------------------------------------------------------------- ----------------
6 library cache lock Concurrency
132 library cache pin Concurrency
191 SQL*Net message to client NetworkOracle 11g等待事件：db file async I/O submit http://www.linuxidc.com/Linux/2013-12/94527.htm[Oracle] 常见的等待事件 http://www.linuxidc.com/Linux/2013-06/85340.htmOracle Log Buffer内部机制以及常见等待事件 http://www.linuxidc.com/Linux/2013-05/85124.htmRAC数据库中的"log file sync"等待事件 http://www.linuxidc.com/Linux/2013-05/84932.htmOracle Tuning Log File Sync 等待事件的几种策略 http://www.linuxidc.com/Linux/2013-05/84078.htm更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址