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

  7. nice post. thanks for your sharing.
    Could u pls clear some points
    - what happens after killing session 122 ? session 118 recompile package and then session 125 run package ?
    - what about this scenario ?: many sessions calling to package, lock was placed on that package. and one developer change an object that package refer to, package became invalid. what can i handle this scenario ? i faced this scenario with over 1000 sessions waiting for that blocked, invalid package. i must stop app and kill all session calling to package. i don't want to stop app.

    tks so much,

    1. 1) yes, queue is continued where it was 1st possible statement to execute.
      >what can i handle this scenario ?
      Do not understand ... if object caused package to become invalid then your sessions are blocked (as you say) but your compile session can only started after all previous session are killed because you cannot promote your session to the top of the queue. only when all previous sessions are killed your compiled session can start.
      So do not touch such an objects on heavy utilized production environment or prepare to kill the session.

    2. yes, i stopped app, kill all session and recompile my package.

      Let me clear my scenario
      1. some sessions call package and lock it
      2. some changes result in invalid package
      3. many subsequence sessions will be "hang"

      Oracle documents said that an invalid object will be recompiled the 1st time it invoked, right ? so, what i need to do in this scenario is find all session in (1) and kill them. Then, the 1st session in (3) will recompile the package, use it (may be lock it), release it, the 2nd one will continue ..., is this right ?

      If my db is RAC, how can i find exactly which sessions (sid, serial#,inst_id) on which instances is holding lock on package to kill them ?, i plan to join gv$session which gv$sqlarea where sql_text like '%mypackage%'

      tks so much for quick reply

    3. >Oracle documents said that an invalid object will be recompiled the 1st time it invoked, right
      Yes if this session can compile! And I think that according locking, that session CANNOT COMPILE IT!


Zagreb u srcu!

Copyright © 2009-2014 Damir Vadas

All rights reserved.

Sign by Danasoft - Get Your Sign