Rob, - Can you share the delete statement text? - Is the index structure on all involved foreign keys the same (on this and the other database)? - Can perform a sql-trace of both cases (fast and slow)? Toon -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of LegeDoos Sent: vrijdag 19 oktober 2007 14:29 To: oracle-l@xxxxxxxxxxxxx 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 -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l