Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Making self deadlock on the library cache pin

with 4 comments

It’s very easy to make your session deadlocked on the library cache pin. Let me prove it.

Step 1. Make a dummy procedure called TEST_PROC1.

TPACK@ukja1120> select * from v$version where rownum = 1;


Oracle Database 11g Enterprise Edition Release - Production

create or replace procedure test_proc1 

Step 2. Execute simple PL/SQL which executes TEST_PROC1 and compiles it.

TPACK@ukja1120> begin
  2     test_proc1;
  4     execute immediate 'alter procedure test_proc1 compile';
  6  end;
  7  /


Very simple, isn’t it?

ASH cleary shows that the session is waiting for the library cache pin to be released, but it’s being acquried by the session itself.

select * 
from (
		h.session_id as sid,
		to_char(h.sample_time,'mi:ss') as sample_time,
		(select sql_text from v$sqlarea a where a.sql_id = h.sql_id) as sql_text,
		blocking_session as blocker
		v$active_session_history h
		h.session_id = &sid
	order by h.sample_time desc			
) where rownum <= 20	

 SID SAMPL SQL_ID        SQL_TEXT             EVENT         BLOCKER
---- ----- ------------- -------------------- ---------- ----------
 136 49:10                                    library ca        136
                                              che pin

 136 49:09                                    library ca        136
                                              che pin

 136 49:08                                    library ca        136
                                              che pin

 136 49:07                                    library ca        136
                                              che pin

 136 49:06                                    library ca        136
                                              che pin

 136 49:05                                    library ca        136
                                              che pin

 136 49:04                                    library ca        136
                                              che pin

 136 49:03                                    library ca        136
                                              che pin

 136 49:02                                    library ca        136
                                              che pin

 136 49:01                                    library ca        136
                                              che pin

 136 49:00                                    library ca        136
                                              che pin

 136 48:59                                    library ca        136
                                              che pin


TPack‘s Wait Detail Report gives more detailed information on the deadlocked library cache pin contention.

TPACK@ukja1120> select * from table(tpack.session_detail(136,'wait_detail'))

NAME                           VALUE
------------------------------ --------------------
SID                            136
Serial#                        2797
SPID                           5148
Program                        sqlplus.exe
Process                        5404:672
Module                         SQL*Plus
SQL ID                         9pbva4bn2m25b
Child No                       0
SQL Text                       alter procedure test
                               _proc1 compile

Status                         ACTIVE
Blocking Instance              1
Blocking Session               136
SQL Exec Start                 2010/09/15 13:45:34
Event                          library cache pin
Seq#                           130
P1(P1raw)                      384372376(0000000016

P2(P2raw)                      384372376(0000000016

P3(P3raw)                      384372376(00014F8500

Seconds in wait                40
State                          WAITING
Wait Event                     library cache pin
Holder SID                     136
Namespace                      TABLE/PROCEDURE
Object                         TEST_PROC1
Holding Mode                   2(S)

So, this is not a normal situation, isn’t it? What would be the reason of the library cache pin not being released even after the execution?

Written by Dion Cho

September 16, 2010 at 2:06 am

Posted in Troubleshooting

4 Responses

Subscribe to comments with RSS.

  1. Hi Dion,

    So, this is not a normal situation, isn’t it?

    It’s normal behavior. See Doc ID 264476.1 for details.

    Timur Akhmadeev

    September 16, 2010 at 9:02 am

    • Hi, Timur.

      Yes, this is a normal behavior, which I didn’t expected. :(

      Oracle could have implemented more fine-grained locking!

      Thanks for the link.

      Dion Cho

      September 16, 2010 at 10:04 am

  2. This is a normal behavior, but should be distinct!


    October 2, 2010 at 4:09 pm

  3. Cool…
    Some database, developers often recompile stored procedure.
    We could not recompile stored procedure. After I checked I found that stored procedure hung in Library Cach…. I have to kill them.

    I use this sql from Steve Adams (www.ixora.com.au)

    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”
    sys.X$KGLOB o,
    sys.X$KGLPN p,
    sys.X$KSUSE s
    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

    Surachart Opun

    November 29, 2010 at 3:37 am

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


Get every new post delivered to your Inbox.

Join 65 other followers

%d bloggers like this: