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;

BANNER
-----------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

create or replace procedure test_proc1 
is
begin
	null;
end;
/

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

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

...
(Hang)

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 (
	select
		h.session_id as sid,
		to_char(h.sample_time,'mi:ss') as sample_time,
		h.sql_id,
		(select sql_text from v$sqlarea a where a.sql_id = h.sql_id) as sql_text,
		event,
		blocking_session as blocker
	from
		v$active_session_history h
	where
		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
                               E90E98)

P2(P2raw)                      384372376(0000000016
                               DAB608)

P3(P3raw)                      384372376(00014F8500
                               010003)

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?

About these ads

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!

    Maclean

    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)

    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
    /

    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

Follow

Get every new post delivered to your Inbox.

Join 61 other followers

%d bloggers like this: