Re: why would enable constraint cause a delete to wait on library cache lock?

From: Tanel Poder <tanel.poder.003_at_mail.ee>
Date: Mon, 12 Jan 2004 15:39:25 -0800
Message-ID: <F001.005DC73B.20040112153925@fatcity.com>

You should first enable novalidate the constraint and then run enable validate.
This way Oracle knows that any *new* DML can't "invalidate" data and can calmly scan through the table without locking it, to see whether the rest of the data is valid as well.

Another reason for blocking locks that I can think of, is when enabling primary/unique key constraints and you don't have an index to support it. Then it even doesn't matter whether you're enabling validate or novalidate. One should build an index with online option first in order to reduce lock holding times.

Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, January 13, 2004 12:29 AM lock?

> I am only an egg, so I hope someone else can explain this to me.
>
> Oracle Enterprise Edition 8.1.7.4.1 on Windows 2000
>
> I was experimenting to see if an "alter table enable validate constraint"
would cause DML statements to wait. I thought it wouldn't. But in real life I see something different.
>
> I have a table X (object_id 429995) with about 100 million rows. In one
session (sid 15) I enable a check constraint, and in another session (sid 14) I simultaneously delete a few rows from the table. The delete "waits" on the enable constraint to complete, and it's waiting on a library cache lock. Why would that be? At the end of this e-mail you can see the contents of DBA_LOCKS, V$LOCKED_OBJECT, V$SESSION_WAIT. I read the system state dump and I still don't understand why there would be a library cache lock.
>
> Session ID 15
> --- sid-15-SQL1
> alter table hes_a_keeper.many_rows add
> (constraint ck1 check (dummy_column > 'B') disable) ;
> --- sid-15-SQL2 (simultaneous with sid-14-SQL1)
> alter table hes_a_keeper.many_rows enable validate constraint ck1 ;
>
> Session ID 14
> --- sid-14-SQL1 (simultaneous with sid-15-SQL2)
> delete from hes_a_keeper.many_rows where rownum < 10 ;
>
> Session ID 10
> --- statements issued while sid-14-SQL1 and sid-15-SQL2 are running
> alter session set events 'immediate trace name systemstate level 10' ;
> select * from dba_locks where session_id in (14,15) ;
> select * from v$locked_object where object_id = 429995 ;
> select * from v$session_wait where sid in (14,15) ;
>
>
> SQL> select * from dba_locks where session_id in (14,15) ;
> SESSION_ID LOCK_TYPE MODE_HELD
> ---------- -------------------------- ------------------------------------

---

> MODE_REQUESTED LOCK_ID1
> ---------------------------------------- ---------------------------------

---

> LOCK_ID2 LAST_CONVERT BLOCKING_OTHERS
> ---------------------------------------- ------------ --------------------

---

> 15 DML Share
> None 429995
> 0 10 Not Blocking
>
>
> SQL> select * from v$locked_object where object_id = 429995 ;
> XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME
> --------- --------- --------- --------- ---------- -----------------------

---

> OS_USER_NAME PROCESS LOCKED_MODE
> ------------------------------ --------- -----------
> 0 0 0 429995 15 JRK
> jkilchoe 1540:260 4
>
>
> SQL> select * from v$session_wait where sid in (14,15) ;
> SID SEQ# EVENT
> --------- --------- ------------------------------------------------------

---

> P1TEXT P1
P1RAW
> ---------------------------------------------------------------- ---------

---

> P2TEXT P2
P2RAW
> ---------------------------------------------------------------- ---------

---

> P3TEXT P3
P3RAW WAIT_TIME
> ---------------------------------------------------------------- ---------

• ---------
  > SECONDS_IN_WAIT STATE
  > --------------- -------------------
  > 15 5005 db file scattered read
  > file# 13
  0000000D
  > block# 19516
  00004C3C
  > blocks 8
  00000008 0
  > 0 WAITING
  >
  > 14 142 library cache lock
  > handle address 47483828
  02D48BB4
  > lock address 40114204
  0264181C
  > 10*mode+namespace 21
  00000015 0
  > 9 WAITING
  >
  > SQL>
  > --
  > Please see the official ORACLE-L FAQ: http://www.orafaq.net
  > --
  > Author: Jacques Kilchoer
  > INET: Jacques.Kilchoer_at_quest.com
  >
  > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
  > San Diego, California -- Mailing list and web hosting services
  > ---------------------------------------------------------------------
  > To REMOVE yourself from this mailing list, send an E-Mail message
  > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
  > the message BODY, include a line containing: UNSUB ORACLE-L
  > (or the name of mailing list you want to be removed from). You may
  > also send the HELP command for other information (like subscribing).
  >
  

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Mon Jan 12 2004 - 17:39:25 CST