20 April, 2016

Partition Storage -- 2 : New Rows Inserted in 12.1 Partitioned Table

Following up from the previous post,  given the current sizes of the Partitions (The underlying Tablespace is AUTOALLOCATE)  :

SQL> col segment_name format a30
SQL> col partition_name format a12
SQL> l
  1  select segment_name, partition_name, segment_type, bytes/1024
  2  from user_segments
  3  where segment_name like 'MY_PART_%'
  4* order by 1,2
SQL> /

SEGMENT_NAME         PARTITION_NA SEGMENT_TYPE       BYTES/1024
------------------------------ ------------ ------------------ ----------
MY_PART_TBL         P_100     TABLE PARTITION      8192
MY_PART_TBL         P_200     TABLE PARTITION      8192
MY_PART_TBL         P_300     TABLE PARTITION      8192
MY_PART_TBL         P_400     TABLE PARTITION      8192
MY_PART_TBL_NDX         P_100     INDEX PARTITION        64
MY_PART_TBL_NDX         P_200     INDEX PARTITION        64
MY_PART_TBL_NDX         P_300     INDEX PARTITION        64
MY_PART_TBL_NDX         P_400     INDEX PARTITION        64

8 rows selected.

SQL> 


I shall now insert rows so that a Partition has to grow beyond the first extent.

SQL> insert into my_part_tbl                      
  2  select 25, 'New Row'
  3  from dual 
  4  connect by level < 100001;

100000 rows created.

SQL> insert into my_part_tbl
  2  select 45, 'New Row'
  3  from dual
  4  connect by level < 500001;

500000 rows created.

SQL> /

500000 rows created.

SQL> commit;

Commit complete.

SQL> 
SQL> select segment_name, partition_name, segment_type, bytes/1024
  2  from user_segments
  3  where segment_name like 'MY_PART_%'
  4  order by 1,2;

SEGMENT_NAME         PARTITION_NA SEGMENT_TYPE       BYTES/1024
------------------------------ ------------ ------------------ ----------
MY_PART_TBL         P_100     TABLE PARTITION     24576
MY_PART_TBL         P_200     TABLE PARTITION      8192
MY_PART_TBL         P_300     TABLE PARTITION      8192
MY_PART_TBL         P_400     TABLE PARTITION      8192
MY_PART_TBL_NDX         P_100     INDEX PARTITION     28672
MY_PART_TBL_NDX         P_200     INDEX PARTITION        64
MY_PART_TBL_NDX         P_300     INDEX PARTITION        64
MY_PART_TBL_NDX         P_400     INDEX PARTITION        64

8 rows selected.

SQL> 


So, now, the first Partition P_100 has grown to exceed 24MB. Let's check the Extent Sizes.

SQL> select extent_id, blocks, blocks*8192/1024 Size_KB
  2  from user_extents
  3  where segment_name = 'MY_PART_TBL'
  4  and segment_type = 'TABLE PARTITION'
  5  and partition_name = 'P_100'
  6  order by 1;

 EXTENT_ID     BLOCKS  SIZE_KB
---------- ---------- ----------
  0  1024     8192
  1  1024     8192
  2  1024     8192

SQL> 
SQL> l
  1  select extent_id, blocks, blocks*8192/1024 Size_KB
  2  from user_extents
  3  where segment_name = 'MY_PART_TBL_NDX'
  4  and segment_type = 'INDEX PARTITION'
  5  and partition_name = 'P_100'
  6* order by 1
SQL> /

 EXTENT_ID     BLOCKS  SIZE_KB
---------- ---------- ----------
  0     8       64
  1     8       64
  2     8       64
  3     8       64
  4     8       64
  5     8       64
  6     8       64
  7     8       64
  8     8       64
  9     8       64
 10     8       64
 11     8       64
 12     8       64
 13     8       64
 14     8       64
 15     8       64
 16   128     1024
 17   128     1024
 18   128     1024
 19   128     1024
 20   128     1024
 21   128     1024
 22   128     1024
 23   128     1024
 24   128     1024
 25   128     1024
 26   128     1024
 27   128     1024
 28   128     1024
 29   128     1024
 30   128     1024
 31   128     1024
 32   128     1024
 33   128     1024
 34   128     1024
 35   128     1024
 36   128     1024
 37   128     1024
 38   128     1024
 39   128     1024
 40   128     1024
 41   128     1024
 42   128     1024

43 rows selected.

SQL> 


So, the Table Partition grew as 8MB Extents but the Index Partition used the normal AutoAllocate routine of 64KB Extents for the first 1MB (16 Extents) followed by 1MB Extents.

What might have been the expected size of the Table Partition ?   ** see the UPDATE at the end of this post.

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

PL/SQL procedure successfully completed.

SQL> select partition_name, trunc(1.2*avg_row_len*num_rows/1024) Expected_KB
  2  from user_tab_partitions
  3  where table_name = 'MY_PART_TBL'
  4  order by 1 
  5  /

PARTITION_NA EXPECTED_KB
------------ -----------
P_100     14179
P_200         0
P_300         0
P_400         0
P_MAX         0

SQL> 


I use a 20% factor to account for PCTFREE and block/row overheads.  **  see the UPDATE at the end of  this post.

Next, what if I use Parallel Insert ?

SQL> alter table my_part_tbl parallel 4;

Table altered.

SQL> alter session enable parallel dml ;

Session altered.

SQL> insert /*+ PARALLEL (MANUAL) */
  2  into my_part_tbl 
  3  select 125, 'New Row'
  4  from dual
  5  connect by level < 100001;

100000 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ PARALLEL (MANUAL) */
  2  into my_part_tbl
  3  select 125, 'New Row'
  4  from dual         
  5  connect by level < 500001;

500000 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ PARALLEL (MANUAL) */
  2  into my_part_tbl
  3  select 125, 'New Row'
  4  from dual
  5  connect by level < 500001;

500000 rows created.

SQL> commit;

Commit complete.

SQL> 
SQL> l
  1  select segment_name, partition_name, segment_type, extents, bytes/1024
  2  from user_segments
  3  where segment_name like 'MY_PART_%'
  4* order by 1,2
SQL> /

SEGMENT_NAME    PARTITION_NA SEGMENT_TYPE      EXTENTS BYTES/1024
------------------------- ------------ ------------------ ---------- ----------
MY_PART_TBL    P_100        TABLE PARTITION     3   24576
MY_PART_TBL    P_200        TABLE PARTITION     4   32768
MY_PART_TBL    P_300        TABLE PARTITION     1    8192
MY_PART_TBL    P_400        TABLE PARTITION     1    8192
MY_PART_TBL_NDX    P_100        INDEX PARTITION    43   28672
MY_PART_TBL_NDX    P_200        INDEX PARTITION    48   33792
MY_PART_TBL_NDX    P_300        INDEX PARTITION     1      64
MY_PART_TBL_NDX    P_400        INDEX PARTITION     1      64

8 rows selected.

SQL> 


Note the size of P_200.  Parallel Insert actually allocated more extents because each PX server process created at least 1 extent for the rows it inserted (Note : For a smaller row-count it is not necessary that each PX server would have inserted rows, some may not have). So, Parallel Insert can result in more allocated space.

** UPDATE :  What about the "expected size" computation based on AVG_ROW_LEN x NUM_ROWS ?

SQL> select partition_name, avg_row_len, num_rows, blocks, trunc(1.2*avg_row_len*num_rows/8192) Expected_Blocks
  2  from user_tab_partitions
  3  where table_name = 'MY_PART_TBL'
  4  order by 1;

PARTITION_NA AVG_ROW_LEN   NUM_ROWS BLOCKS EXPECTED_BLOCKS
------------ ----------- ---------- ---------- ---------------
P_100        11    1100001   3022    1772
P_200        12    1100001   3668    1933
P_300        26   1   1006       0
P_400        28   1   1006       0
P_MAX         0   0      0       0

SQL> 


This shows how any"formula" based on AVG_ROW_LEN and NUM_ROWS can be inaccurate in forecasting the expected size (blocks used).  Also note that P_100 was populated with a Serial Insert while P_200 was populated with a Parallel (Degree=4) Insert.
.
.
.



No comments: