I'm not convinced its the clob (without the trace data its all a bit of guess work).
I did this sample on my exadata instance (its 19 not 12). I tried both easily compressible data and non-compressible. Both results in zippy deletion.
SQL>
SQL> CREATE TABLE tmp_claims
2 (
3 ID NUMBER,
4 LINECOUNT NUMBER NOT NULL,
5 RAWDATA CLOB,
6 DATEOFSERVICEBEGIN DATE NOT NULL,
7 DATEOFSERVICEEND DATE NOT NULL
8 )
9 LOB (RAWDATA) STORE AS SECUREFILE (
10 ENABLE STORAGE IN ROW
11 CHUNK 8192
12 NOCACHE
13 LOGGING
14 )
15 LOGGING
16 ROW STORE COMPRESS ADVANCED
17 NOCACHE
18 MONITORING
19 ENABLE ROW MOVEMENT;
Table created.
SQL>
SQL> truncate table tmp_claims;
Table truncated.
SQL> declare
2 pk int := 0;
3 begin
4 for i in 1 .. 1000
5 loop
6 insert into tmp_claims
7 values ( pk, 0, dbms_random.string('A',6000), sysdate, sysdate );
8 pk := pk + 1;
9 end loop;
10 commit;
11 for i in 1 .. 1000
12 loop
13 insert into tmp_claims
14 values ( pk, 0, dbms_random.string('A',32000) || dbms_random.string('A',32000) , sysdate, sysdate );
15 pk := pk + 1;
16 end loop;
17 commit;
18
19 for i in 1 .. 1000
20 loop
21 insert into tmp_claims
22 values ( pk, 0,
23 dbms_random.string('A',32000) ||
24 dbms_random.string('A',32000) ||
25 dbms_random.string('A',32000) ,
26 sysdate, sysdate );
27 pk := pk + 1;
28 end loop;
29 commit;
30
31 end;
32 /
PL/SQL procedure successfully completed.
SQL>
SQL> set timing on
SQL> delete tmp_claims;
3000 rows deleted.
Elapsed: 00:00:00.48
SQL>
SQL> truncate table tmp_claims;
Table truncated.
Elapsed: 00:00:00.69
SQL> declare
2 c1 clob;
3 pk int := 0;
4 begin
5 for i in 1 .. 1000
6 loop
7 insert into tmp_claims
8 values ( pk, 0, rpad('A',6000,'A'), sysdate, sysdate );
9 pk := pk + 1;
10 end loop;
11 commit;
12 c1 := rpad('A',32000,'A');
13 c1 := c1 || c1;
14 for i in 1 .. 1000
15 loop
16 insert into tmp_claims
17 values ( pk, 0,c1 , sysdate, sysdate );
18 pk := pk + 1;
19 end loop;
20 commit;
21
22 c1 := rpad('A',32000,'A');
23 c1 := c1 || c1 || c1 || c1;
24 for i in 1 .. 1000
25 loop
26
27 insert into tmp_claims
28 values ( pk, 0,c1 , sysdate, sysdate );
29 pk := pk + 1;
30 end loop;
31 commit;
32
33 end;
34 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:13.61
SQL>
SQL> set timing on
SQL> delete tmp_claims;
3000 rows deleted.
Elapsed: 00:00:00.37
SQL>
SQL> truncate table tmp_claims;
Table truncated.
Elapsed: 00:00:00.33
SQL> declare
2 c1 clob;
3 pk int := 0;
4 begin
5 for i in 1 .. 1000
6 loop
7 insert into tmp_claims
8 values ( pk, 0, rpad('A',6000,'A'), sysdate, sysdate );
9 pk := pk + 1;
10 end loop;
11 commit;
12 c1 := rpad('A',32000,'A');
13 c1 := c1 || c1;
14 for i in 1 .. 1000
15 loop
16 insert into tmp_claims
17 values ( pk, 0,c1 , sysdate, sysdate );
18 pk := pk + 1;
19 end loop;
20 commit;
21
22 c1 := rpad('A',32000,'A');
23 c1 := c1 || c1 || c1 || c1;
24 for i in 1 .. 1000
25 loop
26
27 insert into tmp_claims
28 values ( pk, 0,c1 , sysdate, sysdate );
29 pk := pk + 1;
30 end loop;
31 commit;
32
33 end;
34 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:14.33
SQL>
SQL> alter table tmp_claims move;
Table altered.
Elapsed: 00:00:00.32
SQL> alter table tmp_claims move lob (rawdata) STORE AS SECUREFILE;
Table altered.
Elapsed: 00:00:10.88
SQL>
SQL> set timing on
SQL> delete tmp_claims;
3000 rows deleted.
Elapsed: 00:00:00.45
SQL>
This leads me to think - is this table a parent of lots of child tables? Looking at the stats/events you can see you only lost 12.76 seconds in I/O but also:
56247801 session logical reads
129393 CPU used by this session
1293.93 is almost all of your 20 mins in CPU time, and logical reads are the chief burner of CPU.
Was this a fresh session? The 'execute count' looks like a red flag to me.