Saturday, January 9, 2010

Compile blocked package/procedure/function

Recent blog topic (Unlocking the locked table) remind me to write another (and think more mysterious) blocking problem-how to compile blocked package/procedure/function?

Problem arise when you have to compile package/procedure/function on database where many users in the same time use the same package/procedure/function.


Let us suppose that some user has started his session and execute test procedure from package tools. Because procedure test is some bigger transaction it takes longer time to finish.

A little bit later developer has tried to compile the same package (in both cases picture shows session_id and serial# for more easy reference later):

Unfortunately developer compile is blocked … and might be very dangerous!

To make situation more real, let us suppose that soon after developer tried to compile the package, another user has started from package tools any procedure/function (unimportant which one!!):
He is blocked also! Why?

Cause of the problem

First let us find that problem is real. Look for waiter sessions, sessions who are "handicapped":
select to_char(SESSION_ID,'99999') sid ,     
   substr(LOCK_TYPE,1,30) Type,     
   substr(lock_id1,1,20) Object_Name,     
   substr(mode_held,1,4) HELD,     
   substr(mode_requested,1,4) REQ,     
   lock_id2 lock_addr     
FROM dba_lock_internal     
   mode_requested <> 'None'     
   and mode_requested <> mode_held     

SID    TYPE                           OBJECT_NAME  HELD REQ  LOCK_ADDR     
------ ------------------------------ ------------ ---- ---- ----------------
   125 Table/Procedure/Type Definitio DAMIRV.TOOLS None Shar 000007FF10E0A298     
   118 Table/Procedure/Type Definitio DAMIRV.TOOLS None Excl 000007FF10E0A298  
Our session (sid) 118 is blocked because it holds less lock rights then requested (held=None and requested=Exclusive). Exclusive lock is always needed for compiling package/procedure/function. And this is explanation why developer is waiting for compile-his session cannot obtain exclusive lock on package tools.

Third session (125) is another execution of same package (same LOCK_ADDR) but as mentioned before, this execution came after our recompile request has been placed, and this session (HELD=None) so it is not blocking us!

To find blocker, use lock_id2 (presented through alias LOCK_ADDR for easy reference) from same view dba_lock_internal as before, with changed where condition:
select to_char(SESSION_ID,'99999') sid ,     
   substr(LOCK_TYPE,1,30) Type,     
   substr(lock_id1,1,20) Object_Name,     
   substr(mode_held,1,4) HELD,     
   substr(mode_requested,1,4) REQ,     
   lock_id2 lock_addr     
FROM dba_lock_internal     
  and session_id != 118     
order by sid     

SID    TYPE                           OBJECT_NAME  HELD REQ  LOCK_ADDR     
------ ------------------------------ ------------ ---- ---- ----------------
   122 Table/Procedure/Type Definitio DAMIRV.TOOLS Null None 000007FF10E0A298     
   122 Table/Procedure/Type Definitio DAMIRV.TOOLS Shar None 000007FF10E0A298     
   125 Table/Procedure/Type Definitio DAMIRV.TOOLS None Shar 000007FF10E0A298     

Now it is more then clear that session 122 holds "Share" lock type, what is blocking session 118 (us). Even more, if here exist any other session that hold any type of lock (except "null" or "None"), this session will be blocking our session as well!

Other session (125, the one that has been executed after our compile request has been placed) is waiting to place the "Share" lock. Because session 125 cannot perform lock as requested, this session is blocked also. And it has been blocked by our session which waits also for other session! Ant this will happened to all sessions that started to use tools package after our compile request!

Now you see why this blocked package compile is dangerous action-it may blocked very large part of database if we want to compile very popular object! Consequence is that users would soon bag that all in database is hanged! And it is!

So DBA has to react very quick to prevent that!


According mentioned, let us find more information about session 122 (to be sure we will not kill "good guy"):
SQL> col iid for 990
SQL> col sid_serial for a11;
SQL> col sql_text for a80;
  2         t.sql_text,
  3         s.inst_id iid,
  4         lpad(s.sid,5)||','|| Lpad(s.serial#,5) sid_serial       
  5    FROM gv$sqlarea t,
  6         gv$session s
  7   WHERE t.address = s.sql_address
  8     AND t.hash_value = s.sql_hash_value
  9     AND s.inst_id=&1
10     AND s.SID = &2
11     AND s.sql_hash_value != 0
12  order by 2,3
13  ;
Enter value for 1: 1
old   9:    AND s.inst_id=&1
new   9:    AND s.inst_id=1
Enter value for 2: 122
old  10:    AND s.SID = &2
new  10:    AND s.SID = 122

SQL_TEXT                  IID  SID_SERIAL
------------------------- ---- -----------
BEGIN tools.test; END;       1   122,  192 
This is really our blocker session so we have to kill it. Here is an example of my script which is working on "RAC" and "non RAC" (iid=1) environment and this is the reason why iid is also parameter:
SQL> alter system disconnect session '122,192' immediate;     
alter system disconnect session '122,192' immediate     
ERROR at line 1:     
ORA-00031: session marked for kill     


When you kill session 122 and perform the same query as before, no rows should be shown:
SQL> select to_char(SESSION_ID,'99999') sid ,     
  2     substr(LOCK_TYPE,1,30) Type,     
  3     substr(lock_id1,1,20) Object_Name,     
  4     substr(mode_held,1,4) HELD,     
  5     substr(mode_requested,1,4) REQ,     
  6     lock_id2 lock_addr     
  7  FROM dba_lock_internal     
  8  WHERE     
  9     mode_requested <> 'None'     
10     and mode_requested <> mode_held     
11  ;     

no rows selected     


If there exists more records (more users has started to use package tools before our compile request) then you have to kill them all!

For the end

I know that many of you might say that developers should not have access on production instance-true! But sometimes is that just a nice wish.

Other thing is that some quick bug fixing (why they happened...another discussion!) always happened exactly in shown prevent that is sometimes more then fairy tale!


Prove that "print_record" procedure (in third session-waiting) is one easy and quick finishing action, is explained in my topic Columns to rows (print_record)

Fully automated procedure (kills sessions automatically) can be found Compile blocked package/procedure/function II


  1. Very nice! I follow your way of thinking and it looks to me very cool. Keep on!


  2. Hi Damir,

    nice explanation how to troubleshoot annoying locking problem which can cause many troubles.

    It is not so rare case that developer receives requests for quick application tunning. Then most tunning is done directly on production.

    When problem happens it is essential to act quickly and than this article comes as nice soruce where to start with troubleshooting.

    Thanks for sharing...

    Kind regards,

  3. Thank you Marko for these nice words.

    Hope that this will help you once as has helped me in many cases before...

    Damir Vadas

  4. Andrea,

    Thx for your nice words...Hope that you'll find more interesting topics for you in the future on my blog...


    Damir Vadas

  5. Dear Damir,

    Mentioned command "alter system disconnect session" is working on Oracle 11g. Does it working in lower version of Oracle also? Same for DBA_lock_internal also.


  6. Rahul,
    From Oracle 8i and onward I'm pretty sure this command works. For lower versions I do not have information (no testing environment).
    For "DBA_lock_internal" I can tell that it is available from 10g and onwards but for more info search Oracle documentation.
    Problem is that all my work is now 10g/11g I do not focus on lower versions.
    Damir Vadas


Zagreb u srcu!

Copyright © 2009-2014 Damir Vadas

All rights reserved.

Sign by Danasoft - Get Your Sign