Delete suddenly, taking a long time
We have an application process that runs every day of the year. As part of this, every day we delete each row from the table (approximately 1 million rows), rewrite it with 5 different stored procedures, and then commit the transaction. This is the only commit statement we call. Suddenly, the uninstallation completed in about 2 hours. Removal is also very easy (remove from T_PROFILE_WORK) This worked great last year, but I noticed this issue last week.
Any help on this is greatly appreciated
a source to share
look at the answers of this SO query: oracle delete query is taking too long :
- You may be blocked by another session (most likely). Before you delete, you must make sure no one else is locking the rows, for example: issue SELECT NULL FROM tablename WHERE colname =: value FOR UPDATE NOWAIT,
- DELETE TRIGGER can be enabled, which does additional work,
- Check out the UNINDEXED REFERENCE CONSTRAINTS pointing to this table (there is a script from AskTom to help you determine if non-indexed foreign keys exist).
I would check # 2 and # 3 first, they are the easiest to diagnose.
a source to share
How about you have a second (possibly global temporary) table that you populate from stored procedures and eventually MERGE on the existing table. Without knowing the details of activity, table structures, etc., it's hard to be sure. But you can find it faster.
Alternatively, if you have an Enterprise Edition with a partitioning option, take a look at the exchange section.
a source to share
For reference, here is a script from Tom Kyte and mentioned by Vincent Malgrate in the accepted answer (so you don't have to sift through gazillion's comments and follow other links).
The script gives all columns for all tables; you still need to figure out which ones refer to the table where you want to delete rows and which FK tables have the most cardinality.
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);
a source to share