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.
Problem
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):
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!!):
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 WHERE 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 000007FF10E0A298Our 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 WHERE lock_id2='000007FF10E0A298' 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!
Solution
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; SQL> SELECT 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, 192This 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 SQL>
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 SQL>
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 way...so prevent that is sometimes more then fairy tale!
Cheers!
P.S.
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)
P.S. II
Fully automated procedure (kills sessions automatically) can be found Compile blocked package/procedure/function II
