Can't recompile stored procedure & library cache pin wait

On OLTP database or (on database has many sessions), we can not recompile stored procedure. when we check we'll see high "library cache pin" event on "Concurrency" wait class.

Sure... before we recompile stored procedure, we should close application whatover call that stored procedure. (that can help no that stored procedure pin on library cache)

However, Stored procedure might pin on library cache anyway. that make we can not recompile... successful

we should check.. and may kill some sessions...

refer http://surachartopun.com/2008/12/recompiled-stored-procedure-library.html

Example:

- Step 1: check session id, create procedure, run procedure and check

Session 1:

SQL> SELECT sys_context('USERENV', 'SID') FROM dual;

SYS_CONTEXT('USERENV','SID')

----------------------------

139

SQL> CREATE OR REPLACE PROCEDURE PRC_SLEEP (i number)

is

BEGIN

dbms_lock.sleep(i);

DBMS_OUTPUT.PUT_LINE('Done!');

END;

/

Procedure created.

SQL> exec PRC_SLEEP(300);

Session 2:

SQL> SELECT sys_context('USERENV', 'SID') FROM dual;

SYS_CONTEXT('USERENV','SID')

----------------------------

162

Session 3:

SQL> SELECT

substr(DECODE(o.kglobtyp,

7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 12, 'TRIGGER', 13,

'CLASS'),1,15) "TYPE",

substr(o.kglnaown,1,30) "OWNER",

substr(o.kglnaobj,1,30) "NAME",

s.indx "SID",

s.ksuseser "SERIAL"

FROM

sys.X$KGLOB o,

sys.X$KGLPN p,

sys.X$KSUSE s

WHERE

o.inst_id = USERENV('Instance') AND

p.inst_id = USERENV('Instance') AND

s.inst_id = USERENV('Instance') AND

o.kglhdpmd = 2 AND

o.kglobtyp IN (7, 8, 9, 12, 13) AND

p.kglpnhdl = o.kglhdadr AND

s.addr = p.kglpnses and substr(o.kglnaobj,1,30)='&NAME'

ORDER BY 1, 2, 3

/

Enter value for name: PRC_SLEEP

old 20: s.addr = p.kglpnses and substr(o.kglnaobj,1,30)='&NAME'

new 20: s.addr = p.kglpnses and substr(o.kglnaobj,1,30)='PRC_SLEEP'

TYPE OWNER NAME SID SERIAL

--------- ------------------------------ ------------------------------ ---------- ----------

PROCEDURE SURACHART PRC_SLEEP 139 13

SQL> select event from v$session where sid=139;

EVENT

----------------------------------------------------------------

PL/SQL lock timer

SQL> c/139/162

1* select event from v$session where sid=162

SQL> /

EVENT

----------------------------------------------------------------

SQL*Net message from client

- Step 2: recompile while procedure running and check

Session 2:

SQL> CREATE OR REPLACE PROCEDURE PRC_SLEEP (i number)

is

BEGIN

dbms_lock.sleep(i);

DBMS_OUTPUT.PUT_LINE('Done!');

END;

/

Waiting...

Session 3:

SQL> select event from v$session where sid=162;

EVENT

----------------------------------------------------------------

library cache pin

SQL> SELECT

substr(DECODE(o.kglobtyp,

7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 12, 'TRIGGER', 13,

'CLASS'),1,15) "TYPE",

substr(o.kglnaown,1,30) "OWNER",

substr(o.kglnaobj,1,30) "NAME",

s.indx "SID",

s.ksuseser "SERIAL"

FROM

sys.X$KGLOB o,

sys.X$KGLPN p,

sys.X$KSUSE s

WHERE

o.inst_id = USERENV('Instance') AND

p.inst_id = USERENV('Instance') AND

s.inst_id = USERENV('Instance') AND

o.kglhdpmd = 2 AND

o.kglobtyp IN (7, 8, 9, 12, 13) AND

p.kglpnhdl = o.kglhdadr AND

s.addr = p.kglpnses and substr(o.kglnaobj,1,30)='&NAME'

ORDER BY 1, 2, 3

/

Enter value for name: PRC_SLEEP

old 20: s.addr = p.kglpnses and substr(o.kglnaobj,1,30)='&NAME'

new 20: s.addr = p.kglpnses and substr(o.kglnaobj,1,30)='PRC_SLEEP'

TYPE OWNER NAME SID SERIAL

--------- ------------------------------ ------------------------------ ---------- ----------

PROCEDURE SURACHART PRC_SLEEP 139 13

PROCEDURE SURACHART PRC_SLEEP 162 10

- Step 3: cancel, check (no this procedure pin library cache) and recompile.

Session 3:

SQL> SELECT

substr(DECODE(o.kglobtyp,

7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 12, 'TRIGGER', 13,

'CLASS'),1,15) "TYPE",

substr(o.kglnaown,1,30) "OWNER",

substr(o.kglnaobj,1,30) "NAME",

s.indx "SID",

s.ksuseser "SERIAL"

FROM

sys.X$KGLOB o,

sys.X$KGLPN p,

sys.X$KSUSE s

WHERE

o.inst_id = USERENV('Instance') AND

p.inst_id = USERENV('Instance') AND

s.inst_id = USERENV('Instance') AND

o.kglhdpmd = 2 AND

o.kglobtyp IN (7, 8, 9, 12, 13) AND

p.kglpnhdl = o.kglhdadr AND

s.addr = p.kglpnses and substr(o.kglnaobj,1,30)='&NAME'

ORDER BY 1, 2, 3

/

Enter value for name: PRC_SLEEP

old 20: s.addr = p.kglpnses and substr(o.kglnaobj,1,30)='&NAME'

new 20: s.addr = p.kglpnses and substr(o.kglnaobj,1,30)='PRC_SLEEP

no rows selected

Session 2:

SQL> CREATE OR REPLACE PROCEDURE PRC_SLEEP (i number)

is

BEGIN

dbms_lock.sleep(i);

DBMS_OUTPUT.PUT_LINE('Done!');

END;

/

Procedure created.

No Wait...

So, we should check... when that stored procedure doesn't pin in library cache, we can recompile that stored procedure.

By Surachart