Partition Storage -- 6 : Revisiting Partition HWM

After the curious finding in my previous blog post, where a Partition's HighWaterMark was noticeably higher than that for a non-Partitioned Table but then shrunk on a MOVE operation, retrying the same rows with a different pattern of INSERT statements.

However, I am still sticking to a single session doing the INSERT (as I don't want ASSM spreading the incoming rows to different non-contiguous blocks)

This in 12.1.0.2

SQL> connect hemant/hemant
Connected.
SQL> create table part_table_3(id_column number(6), data_column varchar2(100))
  2  partition by range (id_column)
  3  (partition p_100 values less than (101),
  4   partition p_200 values less than (201),
  5   partition p_300 values less than (301),
  6   partition p_400 values less than (401),
  7   partition p_max values less than (maxvalue))
  8  /

Table created.

SQL> insert into part_table_3 values (51,'Fifty One');

1 row created.

SQL> 
SQL> commit;

Commit complete.

SQL> declare
  cntr number;
  begin
   cntr := 0;
   while cntr < 100000
    loop
     insert into part_table_3 values (25, 'New Row') ; 
     commit;
     cntr := cntr + 1;
    end loop;
end;
  2    3    4    5    6    7    8    9   10   11   12  
 13  /

PL/SQL procedure successfully completed.

SQL> 
SQL> declare
  cntr number;
  begin
   cntr := 0;
   while cntr < 500001
    loop
     insert into part_table_3 values (55, 'New Row') ; 
     commit;
     cntr := cntr + 1;
    end loop;
end;  2    3    4    5    6    7    8    9   10   11  
 12  /

PL/SQL procedure successfully completed.

SQL> 
SQL> declare
  cntr number;
  begin
   cntr := 0;
   while cntr < 500001
    loop
     insert into part_table_3 values (45, 'New Row') ; 
     commit;
     cntr := cntr + 1;
    end loop;
end;  2    3    4    5    6    7    8    9   10   11  
 12  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> 
SQL> exec dbms_stats.gather_table_stats('','PART_TABLE_3',granularity=>'ALL');

PL/SQL procedure successfully completed.

SQL> 
SQL> select avg_row_len, num_rows, blocks
from user_tab_partitions
where table_name = 'PART_TABLE_3'
and partition_name = 'P_100'  2    3    4  
  5  /

AVG_ROW_LEN   NUM_ROWS    BLOCKS
----------- ---------- ----------
  11    1100003      3022

SQL> 
SQL> alter table part_table_3 move partition p_100 ;

Table altered.

SQL>  exec dbms_stats.gather_table_stats('','PART_TABLE_3',granularity=>'ALL');

PL/SQL procedure successfully completed.

SQL> select avg_row_len, num_rows, blocks
from user_tab_partitions
where table_name = 'PART_TABLE_3'
and partition_name = 'P_100'
/  2    3    4    5  

AVG_ROW_LEN   NUM_ROWS    BLOCKS
----------- ---------- ----------
  11    1100003      2484

SQL> 
SQL> select extent_id, blocks
from dba_extents
where segment_name = 'PART_TABLE_3'
and segment_type = 'TABLE PARTITION'
and partition_name = 'P_100'
and owner = 'HEMANT'
order by 1
/  2    3    4    5    6    7    8  

 EXTENT_ID     BLOCKS
---------- ----------
  0  1024
  1  1024
  2  1024

SQL> 

So, a Row-By-Row Insert still resulted in the the HWM being 3,022 and shrinking to 2,484 after a MOVE.



Let's try the same data-set in 11.2.0.4

SQL> connect hemant/hemant
Connected.
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> 
SQL> create table part_table_3(id_column number(6), data_column varchar2(100))
  partition by range (id_column)
  (partition p_100 values less than (101),
   partition p_200 values less than (201),
   partition p_300 values less than (301),
   partition p_400 values less than (401),
   partition p_max values less than (maxvalue))
/

  2    3    4    5    6    7    8  
Table created.

SQL> SQL> show parameter deferr

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation      boolean  TRUE
SQL> 
SQL> insert into part_table_3 values (51,'Fifty One');

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL> declare
  cntr number;
  begin
   cntr := 0;
   while cntr < 100000
    loop
     insert into part_table_3 values (25, 'New Row') ; 
     commit;
     cntr := cntr + 1;
    end loop;
end;  2    3    4    5    6    7    8    9   10   11  
 12  /

PL/SQL procedure successfully completed.

SQL> 
SQL> declare
  cntr number;
  begin
   cntr := 0;
   while cntr < 500001
    loop
     insert into part_table_3 values (55, 'New Row') ; 
     commit;
     cntr := cntr + 1;
    end loop;
end;  2    3    4    5    6    7    8    9   10   11  
 12  /

PL/SQL procedure successfully completed.

SQL> 
SQL> declare
  cntr number;
  begin
   cntr := 0;
   while cntr < 500001
    loop
     insert into part_table_3 values (45, 'New Row') ; 
     commit;
     cntr := cntr + 1;
    end loop;
end;
  2    3    4    5    6    7    8    9   10   11   12  
 13  /

PL/SQL procedure successfully completed.

SQL> 
SQL> exec dbms_stats.gather_table_stats('','PART_TABLE_3',granularity=>'ALL');

PL/SQL procedure successfully completed.

SQL> select avg_row_len, num_rows, blocks
from user_tab_partitions
where table_name = 'PART_TABLE_3'
and partition_name = 'P_100'
/  2    3    4    5  

AVG_ROW_LEN   NUM_ROWS    BLOCKS
----------- ---------- ----------
  11    1100003      3022

SQL> 
SQL> alter table part_table_3 move partition p_100 ;

Table altered.

SQL> exec dbms_stats.gather_table_stats('','PART_TABLE_3',granularity=>'ALL');

PL/SQL procedure successfully completed.

SQL> select avg_row_len, num_rows, blocks
from user_tab_partitions
where table_name = 'PART_TABLE_3'
and partition_name = 'P_100'
/  2    3    4    5  

AVG_ROW_LEN   NUM_ROWS    BLOCKS
----------- ---------- ----------
  11    1100003      2484

SQL> 
SQL> select extent_id, blocks
from dba_extents
where segment_name = 'PART_TABLE_3'
and segment_type = 'TABLE PARTITION'
and partition_name = 'P_100'
and owner = 'HEMANT'
order by 1
/  2    3    4    5    6    7    8  

 EXTENT_ID     BLOCKS
---------- ----------
  0  1024
  1  1024
  2  1024

SQL> 

So, 11.2.0.4 and 12.1.0.2 display the same behaviour for the Partition HWM.  A HWM of 3,022 blocks shrinking to 2,484 blocks.

The next test would be with a larger AVG_ROW_LEN.
.
.
.