Reproduce the librarycachelock wait event at Oracle11g

Reproduce the librarycachelock wait event at Oracle11g

From the example below, we can see that re-Compilation of objects in the production database will lead to library cache lock, so we should try to avoid compiling objects during business peaks. If a package or complex dependency exists in the process, it can easily lead to library cache lock. Therefore, you should also pay attention to this issue during application development.

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 Network