Delete statement on Huge table is very slow [message #332518] |
Tue, 08 July 2008 15:52  |
prax_14
Messages: 64 Registered: July 2008
|
Member |
|
|
I have a table of 100,000 records and I am trying to delete few rows based on where condition. I have also created Index on columns used on where condition.
Delete statement which is very slow :
DELETE FROM dp_archived_object
WHERE nbinstancesondisk <= 0
AND nbinstancesontape <= 0;
Index:
CREATE INDEX DP_IDX_INS_DISK_TAPE ON DP_ARCHIVED_OBJECT(NBINSTANCESONDISK,NBINSTANCESONTAPE)
Thanks In advance
|
|
|
|
|
|
|
|
|
|
Re: Delete statement on Huge table is very slow [message #332541 is a reply to message #332518] |
Tue, 08 July 2008 22:51   |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
I am not sure what may or may not be happening on your system;
but something is sorely amiss with your reported reality.
SQL> set term on echo on time on
20:27:00 SQL> select count(*) from TBLREPORTDELTA;
COUNT(*)
----------
38831902
20:29:15 SQL> SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS
20:29:21 SQL> delete from TBLREPORTDELTA;
commit;
38831902 rows deleted.
Execution Plan
----------------------------------------------------------
Plan hash value: 3127137464
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 53871 (1)| 00:10:47 |
| 1 | DELETE | TBLREPORTDELTA | | | |
| 2 | TABLE ACCESS FULL| TBLREPORTDELTA | 1 | 53871 (1)| 00:10:47 |
-----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
2310 recursive calls
41388971 db block gets
245981 consistent gets
244808 physical reads
839 bytes sent via SQL*Net to client
722 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
38831902 rows processed
20:48:43 SQL>
Commit complete.
20:48:43 SQL>
[Updated on: Tue, 08 July 2008 22:52] by Moderator Report message to a moderator
|
|
|
Re: Delete statement on Huge table is very slow [message #332623 is a reply to message #332541] |
Wed, 09 July 2008 03:53   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I'd guess that either
1) There is an on delete trigger on the table that takes a long time to run.
2) The table you are deleting from is the parent to a child table with an un-indexed foreign key
3) Another session has a lock on the table, and your session is waiting for the lock to be released.
What do you get from v$session_wait for the session running the delete?
|
|
|
Re: Delete statement on Huge table is very slow [message #332787 is a reply to message #332518] |
Wed, 09 July 2008 13:11   |
prax_14
Messages: 64 Registered: July 2008
|
Member |
|
|
I managed to get the Execution plan and statistics while deleting 113 rows from 4,000,000 records. It takes nearly 3 minutes. But when I try to delete 100,000 records from the same table the delete statements does not finish even after 5 hours.
The table from which records are deleted "dp_archived_object" is the parent table to 2 child tables (foreign key) having 4,000,000 records each. I have also indexed the foreign keys in child table.
SQL> DELETE FROM dp_archived_object
2 WHERE nbinstancesondisk = 0
3 AND nbinstancesontape = 0;
113 rows deleted.
Elapsed: 00:02:52.02
Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=CHOOSE
1 0 DELETE OF 'DP_ARCHIVED_OBJECT'
2 1 INDEX (RANGE SCAN) OF 'DP_IDX_INS_DISK_TAPE' (NON-UNIQUE
)
Statistics
----------------------------------------------------------
10803 recursive calls
5152 db block gets
545080 consistent gets
540404 physical reads
591780 redo size
794 bytes sent via SQL*Net to client
878 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
67 sorts (memory)
0 sorts (disk)
113 rows processed
Please suggest a way to make this delete statement work faster.
Thanks in advance.
|
|
|
|
|
Re: Delete statement on Huge table is very slow [message #332819 is a reply to message #332518] |
Wed, 09 July 2008 16:19   |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
> unindexed foreign key from child table(s)
I am fighting this very battle this afternoon.
I make no guarentee, but the following SQL may prove helpful.
select distinct table_name, constraint_name
from user_constraints
where constraint_name in (select constraint_name
from user_cons_columns
where exists (select column_name
from user_cons_columns
minus
select column_name
from user_ind_columns
)
)
and constraint_type = 'R'
alternatively...
select table_name, constraint_name, column_name
from user_cons_columns
where (table_name, column_name) not in (select table_name, column_name from user_ind_columns)
and constraint_name in (select constraint_name from user_constraints where constraint_type = 'R')
order by 1,2,3
Needless to say, you need to be logged into the schema which owns the application.
The query above returned "only" 79 rows for 1 simple DELETE that exposed the missing indexes.
[Updated on: Wed, 09 July 2008 18:55] by Moderator Report message to a moderator
|
|
|
Re: Delete statement on Huge table is very slow [message #333160 is a reply to message #332818] |
Thu, 10 July 2008 11:42   |
prax_14
Messages: 64 Registered: July 2008
|
Member |
|
|
I found the following things
1)The table has a delete trigger which deletes from another table.
2) I have Index all the foreign key constraints point back to the parent table.
How did I check these :
1) I used toad to find out that the table has trigger.
2) I Used toad to find the foreign key constraints point back to the parent table and check if they are index properly
|
|
|
|
|
|
Re: Delete statement on Huge table is very slow [message #333167 is a reply to message #332518] |
Thu, 10 July 2008 12:07   |
prax_14
Messages: 64 Registered: July 2008
|
Member |
|
|
TABLE_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
DN_ACTIONS DN_ACTIONS_SLOT_FK
DA_SLOT_ID
DN_ACTIONS DN_ACTIONS_TASK_FK
DA_TASK_ID
DN_COMMON_FILTER DN_COMMON_FILTER_PLAN_FK
PLAN
TABLE_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
DN_FILTER DN_FILTER_PLAN_FK
PLAN
DN_FILTER DN_FILTER_SLOT_FK
SLOT_ID
DN_SLOT DN_SLOT_STPLAN_FK
STORAGEPLAN_NAME
TABLE_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
DN_TOCREATE SYS_C00403173
PARENT_ID
DN_TRANSFORM_RULE DN_TRANSFORM_RULE_FK
FILTER_ID
DP_TRANSCODERS SYS_C00403166
TR_ACTOR
9 rows selected.
In the Nine rows seleted the table "DN_TOCREATE" is the child to the table on which the trigger deletes.
But this table "DN_TOCREATE" has zero records in it.
The remaing 8 rows selected are not used.
|
|
|
|
|
|
|