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,