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.