Gunther Pippèrr - Oracle APEX and Oracle Database Expert

If your application has a lot of deeply linked pl/sql package and you recompile all the packages sometimes the recompile process hangs.

The compiling session has long wait times on the wait event “library cache pin”.

One reason may be that a background job using the same pl/sql packages or a pl/sql packages that depend on the recompiled packages.

With these statements you can find the blocking session:

  SELECT s.sid
        ,s.serial#
        ,SQL.SQL_TEXT
        ,holder.event AS holder_wait_event
        ,holder.p1raw AS wait_event_param_one
        ,holder.p2raw AS wait_event_param_two
        ,holder.p3raw AS wait_event_param_three
        -- How waits
        ,COUNT (s.sid) AS users_count_blocked
        ,waiter.p1raw AS waiter_param_one
    FROM v$sql SQL
        ,v$session s
        ,x$kglpn p
        ,v$session_wait waiter
        ,v$session_wait holder
   WHERE     s.sql_hash_value = SQL.hash_value
         AND p.kglpnhdl = waiter.p1raw
         AND s.saddr = p.kglpnuse
         AND waiter.event LIKE 'library cache pin'
         AND holder.sid = s.sid
GROUP BY s.sid
        ,s.serial#
        ,waiter.p1raw
        ,holder.event
        ,holder.p1raw
        ,holder.p2raw
        ,holder.p3raw
        ,SQL.SQL_TEXT;

So simulate the behavior:

Create procedure in Session A:

CREATE OR REPLACE PROCEDURE gpi$b
AS
BEGIN
   DBMS_OUTPUT.put_line ('Procedure B');
 
   EXECUTE immediate 'alter procedure gpi$a compile';
END;
/
 
CREATE OR REPLACE PROCEDURE gpi$a
AS
BEGIN
   DBMS_OUTPUT.put_line ('Procedure A');
   -- Call gpi$b
   gpi$b;
END;
/

In Session B:

BEGIN
 gpi$a;
END;
/

Now you can query with the statement above to find the self-blocking session B and kill this session completely.

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

• http://docs.oracle.com/cd/B16240_01/doc/doc.102/e16282/oracle_database_help/oracle_database_wait_bottlenecks_library_cache_pin_pct.html