CODEWITHSUNDEEP
oracle-databaseperformance
Damien
Damien

Reputation: 4121

Delete suddenly taking a long time

We have a feed process which runs every day of the year. As part of that we delete every row from a table (approx 1 million rows) every day, repopulate it using 5 different stored procedures and then commit the transaction. This is the only commit statement that we call. All of a sudden the delete has started takign about 2 hours to complete. The delete is also very simple (delete from T_PROFILE_WORK) This has worked perfectly well for the past year, but in the past week I have noticed this issue.

Any help on this is greatly appreciated

Upvotes: 2

Views: 4321

Answers (4)

Alain Pannetier
Alain Pannetier

Reputation: 9514

For the record, here is the script from Tom Kyte and mentioned by Vincent Malgrat in the accepted answer (so that you don't have to sift through the gazillion comments and follow more links).

The script yields all the columns for all the tables; you still have to figure out which ones apply to the table where you want to delete rows and which FK tables have the largest cardinality.

Said otherwise, not all hits need an index of course.

    SELECT table_name,
       constraint_name,
          cname1
       || NVL2 (cname2, ',' || cname2, NULL)
       || NVL2 (cname3, ',' || cname3, NULL)
       || NVL2 (cname4, ',' || cname4, NULL)
       || NVL2 (cname5, ',' || cname5, NULL)
       || NVL2 (cname6, ',' || cname6, NULL)
       || NVL2 (cname7, ',' || cname7, NULL)
       || NVL2 (cname8, ',' || cname8, NULL)
          columns
  FROM (  SELECT b.table_name,
                 b.constraint_name,
                 MAX (DECODE (position, 1, column_name, NULL)) cname1,
                 MAX (DECODE (position, 2, column_name, NULL)) cname2,
                 MAX (DECODE (position, 3, column_name, NULL)) cname3,
                 MAX (DECODE (position, 4, column_name, NULL)) cname4,
                 MAX (DECODE (position, 5, column_name, NULL)) cname5,
                 MAX (DECODE (position, 6, column_name, NULL)) cname6,
                 MAX (DECODE (position, 7, column_name, NULL)) cname7,
                 MAX (DECODE (position, 8, column_name, NULL)) cname8,
                 COUNT (*) col_cnt
            FROM (SELECT SUBSTR (table_name, 1, 30) table_name,
                         SUBSTR (constraint_name, 1, 30) constraint_name,
                         SUBSTR (column_name, 1, 30) column_name,
                         position
                    FROM user_cons_columns) a, user_constraints b
           WHERE a.constraint_name = b.constraint_name
                 AND b.constraint_type = 'R'
        GROUP BY b.table_name, b.constraint_name) cons
 WHERE col_cnt >
          ALL (  SELECT COUNT (*)
                   FROM user_ind_columns i
                  WHERE i.table_name = cons.table_name
                        AND i.column_name IN
                               (cname1,
                                cname2,
                                cname3,
                                cname4,
                                cname5,
                                cname6,
                                cname7,
                                cname8)
                        AND i.column_position <= cons.col_cnt
               GROUP BY i.index_name);

Upvotes: 0

Gary Myers
Gary Myers

Reputation: 35401

How about you have a second (perhaps global temporary) table which you populate from the stored procedures and, at the end, do a MERGE into the existing table. Without know the details of the activity, table structures etc, it is hard to be certain. But you may find it quicker.

As an alternative, if you have Enterprise Edition with the partitioning option, look at partition exchange.

Upvotes: 0

Vincent Malgrat
Vincent Malgrat

Reputation: 67742

please review the answers of this SO Question: "oracle delete query taking too much time":

  1. You could be blocked by another session (most likely). Before you delete you should make sure noone else is locking the rows, eg: issue SELECT NULL FROM tablename WHERE colname=:value FOR UPDATE NOWAIT,
  2. There could be a ON DELETE TRIGGER that does additional work,
  3. Check for UNINDEXED REFERENCE CONSTRAINTS pointing to this table (there is a script from AskTom that will help you determine if such unindexed foreign keys exist).

I would check #2 and #3 first, they are the easiest to diagnose.

Upvotes: 5

Rene
Rene

Reputation: 10541

If you just want to remove everything from the table use truncate instead of delete.

Upvotes: 2

Related Questions