I cannot believe we are the only customers experiencing this issue- is anyone else running into this? I have an SR opened on this and Oracle is saying this is by design. Note we are running on Exadata but I believe this happens on non-exadata as well.
Master Note for OLTP Compression ( Doc ID 1223705.1 )
"Oracle Database - Enterprise Edition - Version 11.1.0.6 and later"
"Deletes from a compressed block are identical to deletes from a non-compressed block, there is no change to consider.
If the deletes cause a block that was full to have sufficient free space then the block will be considered again for new inserts. "
In 11.2 that is true. But in 12.2 it is not true and I cannot find any documentation saying this should have changed in 12.2
Test case:
create table temp_tbl1 (a varchar2(50), b varchar2(50), c varchar2(50), d varchar2(50)) tablespace blah;
create table t_compress (a varchar2(50), b varchar2(50), c varchar2(50), d varchar2(50)) tablespace blah
compress for oltp;
declare
begin
for x in 1..1000000 loop
insert into temp_tbl1 values (lpad(x,20,'Z'),lpad(x,20,'Z'),lpad(x,20,'Z'),lpad(x,20,'Z'));
end loop;
commit;
end;
/
SQL> insert into t_compress select * from temp_tbl1;
1000000 rows created.
SQL> commit;
Commit complete.
SQL> select bytes/1024/1024 from dba_segments where segment_name = 'T_COMPRESS';
BYTES/1024/1024
---------------
40
SQL> delete from t_compress;
1000000 rows deleted.
SQL> commit;
Commit complete.
SQL> insert into t_compress select * from temp_tbl1;
1000000 rows created.
SQL> commit;
Commit complete.
SQL> select bytes/1024/1024 from dba_segments where segment_name = 'T_COMPRESS';
BYTES/1024/1024
---------------
80 <-- did not reuse space
SQL> delete from t_compress;
1000000 rows deleted.
SQL> commit;
Commit complete.
SQL> insert into t_compress select * from temp_tbl1;
1000000 rows created.
SQL> commit;
Commit complete.
SQL> select bytes/1024/1024 from dba_segments where segment_name = 'T_COMPRESS';
BYTES/1024/1024
---------------
120 <-- did not reuse space
Oracle is not reusing deleted space. If you look at the amount of work the insert does it also will increase over time. In 11.2 Oracle would reuse space for this test.