RE: query and delete records really slow in a table

From: Mercadante, Thomas F (LABOR) <Thomas.Mercadante_at_labor.state.ny.us>
Date: Fri, 19 Oct 2007 09:02:53 -0400
Message-ID: <ABB9D76E187C5146AB5683F5A07336FFE0907A@EXCNYSM0A1AJ.nysemail.nyenet>

Rob,

Do indexes exist on the child tables that will support the foreign keys? Look at your explain plan. Try and eliminate the full table scans on the OST_STUDENT_STUDIEVOORTGANG & OST_STUDENT_EXAMEN tables by adding indexes.

Tom

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of LegeDoos Sent: Friday, October 19, 2007 8:29 AM
To: oracle-l_at_freelists.org
Subject: query and delete records really slow in a table

Hi there!

We have a couple (18) of tables "linked" with foreign keys and cascading delete. The largest table contains < 1.000.000 records.

When deleting a record from the top level table, this is extremely slow (like waiting a couple of minutes). On another database (on the same server and on another server) deleting is fast (<1sec). Tables contain defferent data.

-There are no blocking locks
-rebuilding the indexes didn't help
-the explain plan looks ok and is the same for the different databases

0 DELETE STATEMENT optimizer=ALL_ROWS (cost=460 card=1 bytes=73)

1	0	  DELETE of 'OST_STUDENT_STUDIEVOORTGANG'	
2	1	    HASH JOIN (SEMI) (cost=460 card=1 bytes=73)	
3	2	      TABLE ACCESS (FULL) of

'OST_STUDENT_STUDIEVOORTGANG' TABLE
(cost=57 card=31859 bytes=1146924)
4 2 TABLE ACCESS (FULL) of 'OST_STUDENT_EXAMEN' TABLE (cost=199
card=55431 bytes=2050947)

-I moved all tables en indexes to another tablespace, didn't help -There
are no errors in the alert.log.

Can anybody help?

Thanks!

Rob
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Fri Oct 19 2007 - 08:02:53 CDT