Oracle11g下重現librarycachelock等待事件

Oracle11g下重現librarycachelock等待事件

從下面的例子中可以看到，在生產數據庫中對象的重新編譯會導致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

/

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 Network