Following up from the previous post, given the current sizes of the Partitions (The underlying Tablespace is AUTOALLOCATE) :
I shall now insert rows so that a Partition has to grow beyond the first extent.
So, now, the first Partition P_100 has grown to exceed 24MB. Let's check the Extent Sizes.
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.
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 ?
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 ?
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.
.
.
.
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:
Post a Comment