RE: ON DELETE CASCADE causing problems

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Wed, 9 Jul 2008 16:13:55 -0400
Message-ID: <D1DC33E67722D54A93F05F702C99E2A90291D1B1@usahm208.amer.corp.eds.com>


See
 dba_constaints
 dba_cons_columns  

  • Mark D Powell -- Phone (313) 592-5148

        From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of sol beach

	Sent: Wednesday, July 09, 2008 2:12 PM
	To: Oracle-L
	Subject: ON DELETE CASCADE causing problems
	
	
	Oracle V10.2 on RHAS4
	
	I am supporting an custom, inhouse application which has many
FKs;
	which utilize ON DELETE CASCADE capability.
	Typically the database very low (1-3) active sessions.
	The following SQL results in the database tying itself into
knots.
	DELETE FROM USERPROFILE WHERE ORG_ID IN 
	( Select ont.orgnodeId from OrgNode ont where
ont.fullyQualifiedName
	like '/SOME/RANDOM/PREFIX/%');
	The USERPROFILE table has 3 FK with ON DELETE CASCADE.
	Two of these tables have at least 1FK with ON DELETE CASCADE.
	These 2 tables  have at least 1FK with ON DELETE CASCADE each;
	one of which points back to USERPROFILE.
	When the DELETE above is invoked, the number of active sessions
	soars to 40 & the DELETE took 5239 seconds to complete.
	SELECT statements continued to work, but many DML statements
	"hung" while this hairball got digested.
	
	Needless to say, folks were not pleased.
	Even though I had no part of designed this "capability",
	I have been tasked with reducing the impact across the
application.         

        Which VIEW & COLUMN indicates the "ON DELETE CASCADE" exists?         

	What would be the SQL that reports all tables impacted by the
	DELETE statement above?
	
	Any & all suggestions are welcomed.
	
	Thanks In Advance
	


--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jul 09 2008 - 15:13:55 CDT

Original text of this message