Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Releasing library cache pin

with 12 comments

Interesting question from one of my customers.

One of the sessions was killed in the process of debugging a procedure with Toad. But the library cache pin continued to be hold and couldn’t recompile or modify the procedure.

Even “shutdow immediate” got blocked, so the only one I could do was “shutdown abort”.

Why is this happening and how do I force the release of the library cache pin.

The answer is simple and clear.


When the session got termininated unsuccessfully hodling library cache pin, there is a chance that library cache pin is still hold. Killing the corresponding perfectly would release the library cache pin.

What does this mean? I’ve built up very simple test case to prove my viewpoint.

-- Session #1
-- Create procedure PROC1 which sleeps 1000 sec
UKJA@ukja102> create or replace procedure proc1
  2  is
  3  begin
  4     dbms_lock.sleep(1000);
  5
  6  end;
  7  /

Procedure created.


-- Execute procedure proc1. It would be blocked due to the TX lock contention.
-- And kill the session simply with Ctrl+C
-- The problem is that killing session with Ctrl+C(especially on Windows)
-- does not actually kill the corresponding process.
UKJA@ukja102> exec proc1;
^C
C:\Documents and Settings\exem>sqlplus ukja/ukja@ukja102

-- Session #2
-- Okay, now the session #1 got killed. 
-- But how about the procedure itself? Is it terminated or alive?
SYS@ukja10> @lock_internal 144 PROC1

       SID TY handle            mod        req NAME
---------- -- ---------- ---------- ---------- ------------------------------
       142 LK 2A3557A8            1          0 PROC1
       142 LN 2A3557A8            2          0 PROC1  <-- Here!

SYS@ukja10> @lco 2A3557A8 %

KGLHDADR                      : 2A3557A8
KGLNAOBJ                      : PROC1
TYPE                          : Parent
LOCK_HOLDER                   : 142
KGLLKMOD                      : 1
KGLLKREQ                      : 0
PIN_HOLDER                    : 142
KGLPNMOD                      : 2    <-- Here
KGLPNREQ                      : 0
-----------------

PL/SQL procedure successfully completed.

-- The session is alive holding the shared library cache pin on the PROC1.
-- No change on the procedure is allowed. Ouch!
-- It would be alive forever without proper cleanout for the corresponding resources.

-- Session #2
-- Kill the corresponding process(thread) using orakill
-- On unix, kill command
SYS@ukja10> ho orakill UKJA10 5288

Kill of thread id 5288 in instance UKJA10 successfully signalled.

-- After a couple of seconds, PMON would cleanout the resource of the killed process
-- And the library cache pin got released. 
UKJA@ukja102> @lco 2A3557A8 %
KGLHDADR                      : 2A3557A8
KGLNAOBJ                      : PROC1
TYPE                          : Parent
LOCK_HOLDER                   :
KGLLKMOD                      :
KGLLKREQ                      :
PIN_HOLDER                    :
KGLPNMOD                      :
KGLPNREQ                      :
-----------------

PL/SQL procedure successfully completed.



I hope that this simple and stupid test case clearly explains what I’m trying to say.
(lock_internal.sql is here and lco.sql is here)

I’m not sure why Toad did not release the libary cache pin in the above question. Debugging functionality given by Toad(and Oracle!) might have some critical sections that hold library cache pin on the procedure(which would be quite natural with debugging function) and fail to release it without proper termination process.




About these ads

Written by Dion Cho

May 15, 2009 at 6:54 am

12 Responses

Subscribe to comments with RSS.

  1. It’s because the CTRL+C (especially when connecting from windows sqlplus) doesn’t necessarily kill the session!

    If you actually kill the session, the lock will cleaned up by pmon.

    Tanel Poder

    May 15, 2009 at 3:05 pm

  2. @Tanel

    I’ve updated the post with the correction. What a shame I misunderstood the situation(especially on Windows!). :(

    Dion Cho

    May 15, 2009 at 11:34 pm

  3. No problems, that’s how we all learn the most, from our mistakes! :) Did you also try alter system kill session, that should have fixed the issue as well assuming that the target session isn’t hopelessly stuck..

    Tanel Poder

    May 16, 2009 at 8:26 am

  4. Tanel.

    It’s not related the original post, but do you have any information on “_cursor_features_enabled” hidden parameter which seems to be introduced(10.2.0.3?) to control cursor features including mutex?

    Dion Cho

    May 16, 2009 at 1:37 pm

  5. Well, Ctrl-c on windows sqlplus is for me a mistery, because it doesn’t stop the elaboration so i’ve always to kill the session by oracle. In the past i’ve had problems with debugging mode of toad and resources locked that needed instance bounce.

    Cristian

    May 17, 2009 at 12:52 pm

  6. Hi Dion,

    I’ve noticed the _cursor_features_enabled parameter but I’ve never checked what exactly it controls. It isn’t a “mainstream” undocumented parameter with associated metalink notes (at least yet) so it’s probably not too practical for solving any real problems.

    Tanel Poder

    May 20, 2009 at 9:35 pm

  7. @Tanel

    Metalink bug# 6795880 has some explanation and I posted same question on OTN.

    http://forums.oracle.com/forums/message.jspa?messageID=3438125#3438125

    My suspicion is that it has a capability to control mutex(in any way) of which we lost controlability as 11g has decided to code mutex in permanent way, but this could be totally wrong imagination.

    Dion Cho

    May 21, 2009 at 2:04 am

  8. Is it OK for you to share the two scripts ?
    @lco

    @lock_internal

    Thx
    Sean

    Sean

    August 14, 2009 at 11:38 am

  9. Here’s a script that I put together for finding who is waitng and who is blocking on library cache pins and locks.

    http://sites.google.com/site/embtdbo/wait-event-documentation/oracle-library-cache#TOC-library-cache-pin

    script outputs blocking SID and SERIAL# so it’s easy to follow up with a
    alter system kill session …

    select
           waiter.sid   waiter,
           waiter.event wevent,
           to_char(blocker_event.sid)||','||to_char(blocker_session.serial#) blocker,
           substr(decode(blocker_event.wait_time,
                         0, blocker_event.event,
                        'ON CPU'),1,30) bevent
    from
           x$kglpn p,
           gv$session      blocker_session,
           gv$session_wait waiter,
           gv$session_wait blocker_event
    where
              p.kglpnuse=blocker_session.saddr
       and p.kglpnhdl=waiter.p1raw
       and waiter.event in ( 'library cache pin' , 
                                          'library cache lock' ,
                                          'library cache load lock')
       and blocker_event.sid=blocker_session.sid
       and waiter.sid != blocker_event.sid
    order by
          waiter.p1raw,waiter.sid;
    

    Kyle Hailey

    May 30, 2010 at 2:38 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 59 other followers

%d bloggers like this: