After yesterday’s post one of the obvious follow-up questions was whether the problem I demonstrated was a side effect of my use of PL/SQL arrays and loops to load data. What would happen with a pure “insert select” statement. It’s easy enough to check:
rem rem Script: assm_argh2.sql rem Author: Jonathan Lewis rem Dated: Jan 2018 rem Purpose: rem rem Last tested rem 12.2.0.1 rem 12.1.0.2 rem 11.2.0.4 rem drop table t2 purge; drop table t1 purge; create table t2 segment creation immediate tablespace test_8k_assm as select * from all_objects where rownum <= 50000 -- >comment to avoid WordPress anomaly ; create table t1 segment creation immediate tablespace test_8k_assm as select * from all_objects where rownum = 0 ; begin dbms_stats.gather_table_stats( ownname => user, tabname =>'T2', method_opt => 'for all columns size 1' ); end; / insert /*+ append */ into t1 select t2.* from ( select /*+ cardinality(40) */ rownum id from dual connect by level <= 40 -- > comment to avoid WordPress anomaly ) d, t2 ; commit; declare m_unformatted_blocks number; m_unformatted_bytes number; m_fs1_blocks number; m_fs1_bytes number; m_fs2_blocks number; m_fs2_bytes number; m_fs3_blocks number; m_fs3_bytes number; m_fs4_blocks number; m_fs4_bytes number; m_full_blocks number; m_full_bytes number; begin dbms_space.SPACE_USAGE( segment_owner => 'TEST_USER', segment_name => 'T1', segment_type => 'TABLE', unformatted_blocks => m_unformatted_blocks, unformatted_bytes => m_unformatted_bytes, fs1_blocks => m_fs1_blocks , fs1_bytes => m_fs1_bytes, fs2_blocks => m_fs2_blocks, fs2_bytes => m_fs2_bytes, fs3_blocks => m_fs3_blocks, fs3_bytes => m_fs3_bytes, fs4_blocks => m_fs4_blocks, fs4_bytes => m_fs4_bytes, full_blocks => m_full_blocks, full_bytes => m_full_bytes ); dbms_output.put_line('Unformatted : ' || to_char(m_unformatted_blocks,'999,999,990') || ' / ' || to_char(m_unformatted_bytes,'999,999,999,990')); dbms_output.put_line('Freespace 1 ( 0 - 25% free) : ' || to_char(m_fs1_blocks,'999,999,990') || ' / ' || to_char(m_fs1_bytes,'999,999,999,990')); dbms_output.put_line('Freespace 2 ( 25 - 50% free) : ' || to_char(m_fs2_blocks,'999,999,990') || ' / ' || to_char(m_fs2_bytes,'999,999,999,990')); dbms_output.put_line('Freespace 3 ( 50 - 75% free) : ' || to_char(m_fs3_blocks,'999,999,990') || ' / ' || to_char(m_fs3_bytes,'999,999,999,990')); dbms_output.put_line('Freespace 4 ( 75 - 100% free) : ' || to_char(m_fs4_blocks,'999,999,990') || ' / ' || to_char(m_fs4_bytes,'999,999,999,990')); dbms_output.put_line('Full : ' || to_char(m_full_blocks,'999,999,990') || ' / ' || to_char(m_full_bytes,'999,999,999,990')); end; /
I’ve copied the first 50,000 rows from all_objects as a way of generating date, then cloned it 40 times into the main table to give me a total of 2,000,000 rows.
A comment on yesterday’s blog reported that the behaviour I described has been fixed in the October bundle patch for 12.1.0.2, but I haven’t patched my copy yet. So here are the results (with a little cosmetic editing) from running the insert and reporting on space usage from 11.2.0.4, 12.1.0.2, and 12.2.0.1 in order:
11.2.0.4 ======== 2000000 rows created. Unformatted : 764 / 6,258,688 Freespace 1 ( 0 - 25% free) : 0 / 0 Freespace 2 ( 25 - 50% free) : 1 / 8,192 Freespace 3 ( 50 - 75% free) : 0 / 0 Freespace 4 ( 75 - 100% free) : 133 / 1,089,536 Full : 28,579 / 234,119,168 12.1.0.2 ======== 2000000 rows created. Unformatted : 256 / 2,097,152 Freespace 1 ( 0 - 25% free) : 32,810 / 268,779,520 Freespace 2 ( 25 - 50% free) : 0 / 0 Freespace 3 ( 50 - 75% free) : 1 / 8,192 Freespace 4 ( 75 - 100% free) : 47 / 385,024 Full : 443 / 3,629,056 12.2.0.1 ======== 2000000 rows created. Unformatted : 764 / 6,258,688 Freespace 1 ( 0 - 25% free) : 0 / 0 Freespace 2 ( 25 - 50% free) : 1 / 8,192 Freespace 3 ( 50 - 75% free) : 0 / 0 Freespace 4 ( 75 - 100% free) : 226 / 1,851,392 Full : 39,706 / 325,271,552
The total number of blocks involved changes from version to version, of course, thanks to the huge differences in the contents of all_objects, but the headline message is clear – 12.1.0.2 is broken for this basic requirement. On the plus side, though, this is what you get from 12.1.0.2 if you change that insert to include the /*+ append */ hint:
2000000 rows created. Unformatted : 0 / 0 Freespace 1 ( 0 - 25% free) : 0 / 0 Freespace 2 ( 25 - 50% free) : 0 / 0 Freespace 3 ( 50 - 75% free) : 0 / 0 Freespace 4 ( 75 - 100% free) : 0 / 0 Full : 33,380 / 273,448,960
Unsurprisingly, 11.2.0.4 and 12.2.0.1 also behave and report 100% Full. This is a slightly special case, of course since there was no previous data in the table, but even when I started the big insert after inserting and committing a few rows all three versions behaved.
Comments and related questions are welcome.