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


Other related posts: