Dear Team,
We have a delete statement that, when executed, runs for infinite time (we even tried to keep it running for more than 3 days and it was still running) until the we killed the session. We are struggling with support since more than a month and they don't have any idea so far, about what could be the possible cause. We need your help to find why/where it is hanging.
The delete command is as follow:
delete from T where rec_date <=to_date('15-JAN-2013')
/
Below are the details about table:
SQL> select count(*) FROM T;
COUNT(*)
------------
100175652
SQL> ed
Wrote file c:\app\afiedt.buf
1 select count(*) FROM T
2* where rec_date <=to_date('15-JAN-2013')
SQL> /
COUNT(*)
------------
328786
As per the number of records, it shouldn't run for this much time.
The table involved has a child table, too (we have already deleted the records from child table before executing above query). If we re-import (in a different schema) only the parent table (T) and try deleting from that table, it completes in 10 minutes. However, if we re-import parent and child table, the hang behavior can be seen.
10046 trace
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.02 0 0 0 0
Execute 1 257.64 1332.39 3575286 1360867 2214317 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 257.67 1332.42 3575286 1360867 2214317 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 64
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 DELETE T (cr=0 pr=0 pw=0 time=0 us starts=8)
21 21 21 TABLE ACCESS FULL T (cr=1360867 pr=1361006 pw=0 time=1990133 us starts=33923681 cost=1990133 size=6508050 card=216935)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
library cache lock 1 0.00 0.00
library cache pin 1 0.00 0.00
row cache lock 5 0.00 0.00
Disk file operations I/O 36 0.00 0.00
db file sequential read 2214297 1.03 1155.11
db file scattered read 10646 0.03 19.67
resmgr:cpu quantum 99 0.10 1.63
gc current grant 2-way 77 0.00 0.13
latch: gc element 16 0.00 0.00
resmgr:internal state change 1 0.09 0.09
latch: gcs resource hash 1 0.00 0.00
latch: object queue header operation 1 0.00 0.00
DDL of parent/child tables (table/constraint name renamed)
Create Table T
( Serv_Prov_Code Varchar2(15 Char) Not Null Enable,
B1_Con_Nbr Number Not Null Enable,
B1_Con_History_Nbr Number Not Null Enable,
B1_Con_Genus Varchar2(15 Char) Not Null Enable,
B1_Con_Comment Varchar2(4000),
B1_Con_Des Varchar2(255 Char),
B1_Con_Eff_Dd1 Date,
B1_Con_Expir_Dd Date,
B1_Con_Impact_Code Varchar2(8 Char),
B1_Con_Iss_Agency_Code Varchar2(8 Char),
B1_Con_Iss_Bureau_Code Varchar2(8 Char),
B1_Con_Iss_Division_Code Varchar2(8 Char),
B1_Con_Iss_Section_Code Varchar2(8 Char),
B1_Con_Iss_Group_Code Varchar2(8 Char),
B1_Con_Iss_Office_Code Varchar2(8 Char),
B1_Con_Iss_Dd Date,
B1_Con_Iss_Fname Varchar2(70),
B1_Con_Iss_Mname Varchar2(70),
B1_Con_Iss_Lname Varchar2(70),
B1_Con_Ref_Num1 Varchar2(20 Char),
B1_Con_Ref_Num2 Varchar2(20 Char),
B1_Con_Stat_Agency_Code Varchar2(8 Char),
B1_Con_Stat_Bureau_Code Varchar2(8 Char),
B1_Con_Stat_Division_Code Varchar2(8 Char),
B1_Con_Stat_Section_Code Varchar2(8 Char),
B1_Con_Stat_Group_Code Varchar2(8 Char),
B1_Con_Stat_Office_Code Varchar2(8 Char),
B1_Con_Stat_Dd Date,
B1_Con_Stat_Fname Varchar2(70),
B1_Con_Stat_Mname Varchar2(70),
B1_Con_Stat_Lname Varchar2(70),
B1_Con_Status Varchar2(30 Char),
B1_Con_Typ Varchar2(255),
R3_Agency_Addr_Seq_Nbr Number(22,0),
B1_Manipulat_Typ Varchar2(10 Char),
B1_Manipulat_Date Date,
B1_Manipulat_Ful_Nam Varchar2(70 Char),
Rec_Date Date Not Null Enable,
Rec_Ful_Nam Varchar2(70 Char) Not Null Enable,
Rec_Status Varchar2(1 Char),
B1_Con_Long_Comment Varchar2(4000 Char),
R3_Con_Nbr Number(22,0),
G6_Act_Num Number(22,0),
B1_Con_Dis_Con_Notice Varchar2(1 Char),
B1_Con_Inc_Con_Name Varchar2(1 Char),
B1_Con_Inc_Short_Desc Varchar2(1 Char),
B1_Con_Inheritable Varchar2(1 Char),
B1_Con_Status_Typ Varchar2(20 Char),
Cond_Source Varchar2(20 Char),
R6_Apo_Num Varchar2(30 Char),
R6_Cond_Num Number(25,0),
B1_Con_Group Varchar2(255 Char),
B1_Con_Dis_Notice_Aca Varchar2(1 Char),
B1_Con_Dis_Notice_Aca_Fee Varchar2(1 Char),
Lang_Id Varchar2(10 Char),
Constraint T_Pk Primary Key (Serv_Prov_Code, B1_Con_History_Nbr)
Using Index Enable
) Enable Row Movement ;
Create Table T_Child
( Serv_Prov_Code Varchar2(15 Char) Not Null Enable,
B1_Con_History_Nbr Number(22,0) Not Null Enable,
B1_Con_Resolution_Action Varchar2(4000 Char),
B1_Con_Public_Dis_Message Varchar2(2000 Char),
Rec_Date Date Not Null Enable,
Rec_Ful_Nam Varchar2(70 Char) Not Null Enable,
Rec_Status Varchar2(1 Char),
Lang_Id Varchar2(10 Char),
Additional_Information Clob,
Priority Number(5,0),
Addit_Info_Plain_Text Clob,
Constraint T_Child_Pk Primary Key (Serv_Prov_Code, B1_Con_History_Nbr)
Using Index Enable,
Constraint T_Child$T_Fk Foreign Key (Serv_Prov_Code, B1_Con_History_Nbr)
References T (Serv_Prov_Code, B1_Con_History_Nbr) Enable
) Enable Row Movement ;
SQL> select count(*) FROM dba_indexes where table_name='T_CHILD'
COUNT(*)
------------
3
SQL> ed
Wrote file c:\app\afiedt.buf
1* select count(*) FROM dba_indexes where table_name='T'
SQL> /
COUNT(*)
------------
3
Note: Sorry for the confusion; I've renamed the table in the execution plan. The delete command is executed parent table (and 10046 trace refers to the same table).
Regards,
In this case, you are deleting 328786 rows (not that that trace shows that, which seems like you've given some mixed information here).
The delete cost is:
- finding the rows in the parent
- with every row deleted from the parent, we need to ensure that they are no rows in the child, which means a lookup
Since you are not doing "on delete cascade", this would suggest you do not have a good means of locating child records from the parent, so each check is inefficient.
Check your index definitions, or alternatively, look at
a) disabling the constraint
b) delete from child where (keys) in ( select keys from parent)
c) delete from parent
d) re-enabling the constraint