FAQ
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

Search Discussions

  • Mercadante, Thomas F (LABOR) at Oct 19, 2007 at 1:02 pm
    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
    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
  • Koppelaars, Toon at Oct 19, 2007 at 1:06 pm
    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_at_freelists.org
    On Behalf Of LegeDoos
    Sent: vrijdag 19 oktober 2007 14:29
    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
  • Elliott, Patrick at Oct 19, 2007 at 2:17 pm
    If that explain plan is for deleting one row, then it is not a good explain plan. It is doing a full table scan in the child table for every row in the parent. Did you analyze the tables with histograms? (i.e. for all indexed columns)

    Pat

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Koppelaars, Toon
    Sent: Friday, October 19, 2007 8:06 AM
    To: legedoos_at_gmail.com; oracle-l_at_freelists.org
    Subject: RE: query and delete records really slow in a table

    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_at_freelists.org
    On Behalf Of LegeDoos
    Sent: vrijdag 19 oktober 2007 14:29
    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

    CONFIDENTIALITY AND PRIVACY NOTICE

    Information transmitted by this email is proprietary to Medtronic and is intended for use only by the individual or entity to which it is addressed, and may contain information that is private, privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please delete this mail from your records.

    To view this notice in other languages you can either select the following link or manually copy and paste the link into the address bar of a web browser: http://emaildisclaimer.medtronic.com
    --
    http://www.freelists.org/webpage/oracle-l
  • Igor Neyman at Oct 19, 2007 at 1:24 pm
    Do you have indexes supporting your foreign keys?

    Igor

    -----Original Message-----
    From: 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
  • Dennis Williams at Oct 19, 2007 at 1:53 pm
    Rob,

    Explain plan isn't always accurate. You need to look at what the session is
    waiting on. Are you familiar with those tables?

    Dennis Williams

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 19, '07 at 12:28p
activeOct 19, '07 at 2:17p
posts6
users6
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase