Experiment regarding extent allocation in direct-path inserts.

pkg_space_debug.sql

create or replace package pkg_space_debug is
  
   procedure log_iteration(p_testcase in space_debug_tab.testcase%type, p_iteration in space_debug_tab.iteration%type);
   
   /**
      Expected results:
         at p_connect_by_level => 3285 => ~18 MB segment size
         at p_connect_by_level => 3286 => ~8GB segment size
     
      Prerequisites:
         drop table if exists t1;
         create table t1 (col_a number) tablespace CHEMISTRY_IDX;
    
      Testcase
         alter session set current_schema=chemistry;
         truncate table space_debug_tab;
         set timing on;
         exec pkg_space_debug.run_testcase('A', 3285);
         exec pkg_space_debug.run_testcase('B', 3286);
    */
   procedure run_testcase(p_testcase in space_debug_tab.testcase%type, p_connect_by_level in number);
   
   procedure print_usage;
   
end pkg_space_debug;
/

create or replace package body pkg_space_debug is
  
   c_owner dba_segments.owner%type := 'OWNER';
   c_segment dba_segments.segment_name%type := 'T1';
   
   /*
   create table space_debug_tab (
      testcase varchar2(1 char),
      iteration integer not null,
      -- dba_extents:
      extent_count number,
      max_extent_id number,
      sum_blocks number,
      max_blocks number,
      -- dbms_space.space_usage:
      unformatted_blocks number,
      unformatted_bytes number,
      fs1_blocks number,
      fs1_bytes number,
      fs2_blocks number,
      fs2_bytes number,
      fs3_blocks number,
      fs3_bytes number,
      fs4_blocks number,
      fs4_bytes number,
      full_blocks number,
      full_bytes number,
      -- dbms_space.unused_space:
      total_blocks number,
      total_bytes number,
      unused_blocks number,
      unused_bytes number,
      last_used_extent_file_id number,
      last_used_extent_block_id number,
      last_used_block number
   );
   */
   procedure log_iteration(p_testcase in space_debug_tab.testcase%type, p_iteration in space_debug_tab.iteration%type) as
      pragma autonomous_transaction;
      l_spc_rec space_debug_tab%rowtype;
   begin
      l_spc_rec.testcase := p_testcase;
      l_spc_rec.iteration := p_iteration;
      
      select count(*) as extent_count,
             max (extent_id) as max_extent_id,
             sum(blocks) as sum_blocks,
             max(blocks) as max_blocks
             into l_spc_rec.extent_count,
                  l_spc_rec.max_extent_id,
                  l_spc_rec.sum_blocks,
                  l_spc_rec.max_blocks
         from dba_extents
         where owner = c_owner
            and segment_name = c_segment;
            
      dbms_space.space_usage(
         segment_owner => c_owner,
         segment_name => c_segment,
         segment_type => 'TABLE',
         unformatted_blocks => l_spc_rec.unformatted_blocks,
         unformatted_bytes => l_spc_rec.unformatted_bytes,
         fs1_blocks => l_spc_rec.fs1_blocks,
         fs1_bytes => l_spc_rec.fs1_bytes,
         fs2_blocks => l_spc_rec.fs2_blocks,
         fs2_bytes => l_spc_rec.fs2_bytes,
         fs3_blocks => l_spc_rec.fs3_blocks,
         fs3_bytes => l_spc_rec.fs3_bytes,
         fs4_blocks => l_spc_rec.fs4_blocks,
         fs4_bytes => l_spc_rec.fs4_bytes,
         full_blocks => l_spc_rec.full_blocks,
         full_bytes => l_spc_rec.full_bytes);
         
      dbms_space.unused_space(
         segment_owner => c_owner,
         segment_name => c_segment,
         segment_type => 'TABLE',
         total_blocks => l_spc_rec.total_blocks,
         total_bytes => l_spc_rec.total_bytes,
         unused_blocks => l_spc_rec.unused_blocks,
         unused_bytes => l_spc_rec.unused_bytes,
         last_used_extent_file_id => l_spc_rec.last_used_extent_file_id,
         last_used_extent_block_id => l_spc_rec.last_used_extent_block_id,
         last_used_block => l_spc_rec.last_used_block);
      
      insert into space_debug_tab values l_spc_rec;
      
      commit;
   end;
   
   procedure run_testcase(p_testcase in space_debug_tab.testcase%type, p_connect_by_level in number) is
   begin
      execute immediate 'truncate table "' || c_owner || '"."' || c_segment || '"';
      log_iteration(p_testcase, 0);
      for i in 1..300 loop
         insert /*+ append */ into t1
            select 1 from dual connect by level <= p_connect_by_level;      
         -- commit;
         log_iteration(p_testcase, i);
      end loop;
   end;
   
   procedure print_usage is
      l_spc_rec space_debug_tab%rowtype;
   begin
       dbms_space.space_usage(
         segment_owner => c_owner,
         segment_name => c_segment,
         segment_type => 'TABLE',
         unformatted_blocks => l_spc_rec.unformatted_blocks,
         unformatted_bytes => l_spc_rec.unformatted_bytes,
         fs1_blocks => l_spc_rec.fs1_blocks,
         fs1_bytes => l_spc_rec.fs1_bytes,
         fs2_blocks => l_spc_rec.fs2_blocks,
         fs2_bytes => l_spc_rec.fs2_bytes,
         fs3_blocks => l_spc_rec.fs3_blocks,
         fs3_bytes => l_spc_rec.fs3_bytes,
         fs4_blocks => l_spc_rec.fs4_blocks,
         fs4_bytes => l_spc_rec.fs4_bytes,
         full_blocks => l_spc_rec.full_blocks,
         full_bytes => l_spc_rec.full_bytes);
       dbms_space.unused_space(
         segment_owner => c_owner,
         segment_name => c_segment,
         segment_type => 'TABLE',
         total_blocks => l_spc_rec.total_blocks,
         total_bytes => l_spc_rec.total_bytes,
         unused_blocks => l_spc_rec.unused_blocks,
         unused_bytes => l_spc_rec.unused_bytes,
         last_used_extent_file_id => l_spc_rec.last_used_extent_file_id,
         last_used_extent_block_id => l_spc_rec.last_used_extent_block_id,
         last_used_block => l_spc_rec.last_used_block);
         
      dbms_output.put_line(rpad('_', 30)                  || '|' || lpad('Blocks', 15)                     || '|' || lpad('Bytes', 15));
      dbms_output.put_line(rpad('Unformatted', 30)        || '|' || lpad(l_spc_rec.unformatted_blocks ,15) || '|' || lpad(l_spc_rec.unformatted_bytes, 15));
      dbms_output.put_line(rpad('0 to 25% used', 30)      || '|' || lpad(l_spc_rec.fs4_blocks ,15)         || '|' || lpad(l_spc_rec.fs4_bytes, 15));
      dbms_output.put_line(rpad('25 to 50% used', 30)     || '|' || lpad(l_spc_rec.fs3_blocks ,15)         || '|' || lpad(l_spc_rec.fs3_bytes, 15));
      dbms_output.put_line(rpad('50 to 75% used', 30)     || '|' || lpad(l_spc_rec.fs2_blocks ,15)         || '|' || lpad(l_spc_rec.fs2_bytes, 15));
      dbms_output.put_line(rpad('75 to <100% used', 30)   || '|' || lpad(l_spc_rec.fs1_blocks ,15)         || '|' || lpad(l_spc_rec.fs1_bytes, 15));
      dbms_output.put_line(rpad('Full', 30)               || '|' || lpad(l_spc_rec.full_blocks ,15)        || '|' || lpad(l_spc_rec.full_bytes, 15));
      dbms_output.put_line(rpad('Unused', 30)             || '|' || lpad(l_spc_rec.unused_blocks ,15)      || '|' || lpad(l_spc_rec.unused_bytes, 15));
      dbms_output.put_line(rpad('Total', 30)              || '|' || lpad(l_spc_rec.total_blocks ,15)       || '|' || lpad(l_spc_rec.total_bytes, 15));
   end;

end pkg_space_debug;
/