Search My Oracle Blog

Custom Search

29 April, 2016

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.
.
.
.


25 April, 2016

Partition Storage -- 5 : Partitioned Table versus Non-Partitioned Table ? (in 12.1)

Reviewing my second blog post in this series, I found it strange that Partition P_100 (populated by Serial Inserts of 1 row, 100,000 rows, 500,000 rows and 500,000 rows) had such a High Water Mark.

For 1.1million rows of an Average Row Length of 11, the High Water Mark was 3,022 blocks.

In the fourth blog post, a simple ALTER TABLE MOVE PARTITION had brought the High Water Mark to 2,482 blocks !

This needs further investigation.

Let's compare a single Partition of a Partitioned Table with a Non-Partitioned Table for exactly the same data and same pattern of INSERT statements.

Starting with a new Partitioned Table.

SQL> l
  1  create table new_part_tbl (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))
SQL> /

Table created.

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

1 row created.

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

100000 rows created.

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

500000 rows created.

SQL> /

500000 rows created.

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

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

AVG_ROW_LEN   NUM_ROWS     BLOCKS
----------- ---------- ----------
  11           1100001       3022

SQL> 
SQL> REM  Let's MOVE the Partition
SQL> alter table new_part_tbl move partition P_100;

Table altered.

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

PL/SQL procedure successfully completed.

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

AVG_ROW_LEN   NUM_ROWS     BLOCKS
----------- ---------- ----------
  11           1100001       2484

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

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

SQL> 


As expected (see the first blog post), the Extents are still 8MB each.  But the High Water Mark has "magicallly" shrunk from 3,022 blocks to 2,484 blocks.

Let's create a Non-Partitioned Table with the same columns and rows.

SQL> create table non_part_tbl (id_column number(6), data_column varchar2(100));

Table created.

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

1 row created.

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

100000 rows created.

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

500000 rows created.

SQL> /

500000 rows created.

SQL> commit;

Commit complete.

SQL> 
SQL> exec dbms_stats.gather_table_stats('','NON_PART_TBL');

PL/SQL procedure successfully completed.

SQL> select avg_row_len, num_rows, blocks
  2  from user_tables
  3  where table_name = 'NON_PART_TBL'
  4  /

AVG_ROW_LEN   NUM_ROWS     BLOCKS
----------- ---------- ----------
  11           1100001       2512

SQL> 
SQL> REM Let's MOVE the Table
SQL> alter table non_part_tbl move;

Table altered.

SQL> select avg_row_len, num_rows, blocks
  2  from user_tables
  3  where table_name = 'NON_PART_TBL'
  4  /

AVG_ROW_LEN   NUM_ROWS     BLOCKS
----------- ---------- ----------
  11           1100001       2512

SQL> 
SQL> l
  1  select extent_id, blocks
  2  from dba_extents
  3  where segment_name = 'NON_PART_TBL'
  4  and segment_type = 'TABLE'
  5  and owner = 'HEMANT'
  6* order by 1
SQL> /

 EXTENT_ID     BLOCKS
---------- ----------
         0          8
         1          8
         2          8
         3          8
         4          8
         5          8
         6          8
         7          8
         8          8
         9          8
        10          8
        11          8
        12          8
        13          8
        14          8
        15          8
        16        128
        17        128
        18        128
        19        128
        20        128
        21        128
        22        128
        23        128
        24        128
        25        128
        26        128
        27        128
        28        128
        29        128
        30        128
        31        128
        32        128
        33        128
        34        128

35 rows selected.

SQL> 


The Non-Partitioned Table had a High Water Mark of 2,512 blocks.  This did not change with a MOVE.  The allocation of Extents is also expected in AutoAllocate.

Why, then, does the Partition behave differently ?  It started with a High Water Mark of 3,022 blocks which shrunk to 2,484 blocks after a MOVE ?

Is the Average Row Length or the actual data a factor ?  (Note : I am *not* using Table Compression).

To be explored further with a larger row size ...........

Possibly, to be explored with a different pattern of INSERT statements  ......

Possibly to be compared in 11.2 as well. ......
.
.
.

24 April, 2016

Partition Storage -- 4 : Resizing Partitions

Building on Posts 2 (Adding Rows) and 3 (Adding Partitions) where we saw Table Partitions using 8MB Extents ..... is there a way to "resize" Partitions to smaller Extents (and, maybe, lesser space consumed) without using Compression ?

Let's explore.

Beginning with Partitions P_100 and P_200 ....

SQL> select segment_name, partition_name, bytes/1024, extents
  2  from user_segments
  3  where segment_name like 'MY_PART_%'
  4  and partition_name in ('P_100','P_200')
  5  order by 1,2
  6  /

SEGMENT_NAME                   PARTITION_NA BYTES/1024    EXTENTS
------------------------------ ------------ ---------- ----------
MY_PART_TBL                    P_100             24576          3
MY_PART_TBL                    P_200             32768          4
MY_PART_TBL_NDX                P_100             28672         43
MY_PART_TBL_NDX                P_200             33792         48

SQL> 
SQL> alter table my_part_tbl move partition p_100 storage (initial 64K next 64K);

Table altered.

SQL> alter index my_part_tbl_ndx rebuild partition p_100 storage (initial 64K next 64K)
  2  /

Index altered.

SQL> alter table my_part_tbl move partition p_200 storage (initial 64K next 64K);

Table altered.

SQL> alter index my_part_tbl_ndx rebuild partition p_200 storage (initial 64K next 64K)
  2  /

Index altered.

SQL> 
SQL> select segment_name, partition_name, bytes/1024, extents
  2  from user_segments
  3  where segment_name like 'MY_PART_%'
  4  and partition_name in ('P_100','P_200')
  5  order by 1,2
  6  /

SEGMENT_NAME                   PARTITION_NA BYTES/1024    EXTENTS
------------------------------ ------------ ---------- ----------
MY_PART_TBL                    P_100             20480         35
MY_PART_TBL                    P_200             21504         36
MY_PART_TBL_NDX                P_100             18432         33
MY_PART_TBL_NDX                P_200             19456         34

SQL> 
SQL> select partition_name, blocks, num_rows
  2  from user_tab_partitions
  3  where table_name = 'MY_PART_TBL'
  4  and partition_name in ('P_100','P_200')
  5  order by 1
  6  /

PARTITION_NA     BLOCKS   NUM_ROWS
------------ ---------- ----------
P_100              3022    1100001
P_200              3668    1100001

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

PL/SQL procedure successfully completed.

SQL> select partition_name, blocks, num_rows
  2  from user_tab_partitions
  3  where table_name = 'MY_PART_TBL'
  4  and partition_name in ('P_100','P_200')
  5  order by 1
  6  /

PARTITION_NA      BLOCKS  NUM_ROWS
------------ ---------- ----------
P_100              2482    1100001
P_200              2639    1100001

SQL> 
SQL> 
SQL> l
  1  select partition_name, blocks, count(*)
  2  from dba_extents
  3  where owner = 'HEMANT'
  4  and segment_name = 'MY_PART_TBL'
  5  and segment_type = 'TABLE PARTITION'
  6  and partition_name in ('P_100','P_200')
  7  group by partition_name, blocks
  8* order by 1,2
SQL> /

PARTITION_NA     BLOCKS   COUNT(*)
------------ ---------- ----------
P_100                 8         16
P_100               128         19
P_200                 8         16
P_200               128         20

SQL> 


Partition P_100 has shrunk from 3 extents of 8MB adding up to 24,576KB to 35 extents adding up to 20,480KB. The High Water Mark has shrink from 3,022 blocks to 2,482 blocks (Remember : P_100 was populated with a Serial Insert.  Partition P_200 that had been populated with Parallel (DoP=4) insert has also shrunk from 32,768KB to 21,504KB and the High Water Mark from 3,668 blocks to 2,639 blocks.  The Extents are a combinaion of 64KB (the first 16, adding up to 1MB) and 1MB sizes.
Even the Index Partitions seem to have shrunk.

So, a MOVE/REBUILD (the REBUILD of the Index Partitons was required because I did a Partition MOVE without UPDATE INDEXES), could be used to shrink the Partitions with newer, smaller, Extents allocated.

But what about the case of SPLIT Partition, where Partitions SPLIT from an 8MB Partition resulted in 2 8MB Partitions, even for empty Partitions.

Here's a workaround.  Before SPLITting the P_MAX Partition, I resize it.

SQL> alter table my_part_tbl move partition p_max storage (initial 64K next 64K);

Table altered.

SQL> alter index my_part_tbl_ndx rebuild partition p_max storage (initial 64K next 64K);

Index altered.

SQL> alter table my_part_tbl
  2  split partition p_max
  3  at (1001)
  4  into (partition p_1000, partition p_max)
  5  /

Table altered.

SQL> alter table my_part_tbl
  2  split partition p_1000
  3  at (901)
  4  into (partition p_900, partition p_1000)
  5  /

Table altered.

SQL> alter table my_part_tbl
  2  split partition p_900
  3  at (801)
  4  into (partition p_800, partition p_900)
  5  /

Table altered.

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

SEGMENT_NAME                   PARTITION_NA BYTES/1024    EXTENTS
------------------------------ ------------ ---------- ----------
MY_PART_TBL                    P_100             20480         35
MY_PART_TBL                    P_200             21504         36
MY_PART_TBL                    P_300              8192          1
MY_PART_TBL                    P_400              8192          1
MY_PART_TBL                    P_600              8192          1
MY_PART_TBL                    P_680              8192          1
MY_PART_TBL                    P_700              8192          1
MY_PART_TBL                    P_800                64          1
MY_PART_TBL                    P_900                64          1
MY_PART_TBL                    P_1000               64          1
MY_PART_TBL                    P_MAX                64          1
MY_PART_TBL_NDX                P_100             18432         33
MY_PART_TBL_NDX                P_200             19456         34
MY_PART_TBL_NDX                P_300                64          1
MY_PART_TBL_NDX                P_400                64          1
MY_PART_TBL_NDX                P_600                64          1
MY_PART_TBL_NDX                P_680                64          1
MY_PART_TBL_NDX                P_700                64          1
MY_PART_TBL_NDX                P_800                64          1
MY_PART_TBL_NDX                P_900                64          1
MY_PART_TBL_NDX                P_1000               64          1
MY_PART_TBL_NDX                P_MAX                64          1

22 rows selected.

SQL> 


(Note : I have manually relocated Partition P_1000 in the listing).
Partitions P_600, P_680 and P_700 had been created by SPLIT PARTITION commands in the previous post, beginning with segment-created P_MAX partition.  However, after rebuilding P_MAX to 64KB Extents, subsequently SPLITted Partitions (P_800 to P_1000) are also 64KB.

Note : I am not advising that all have to Partitions be 64K.  (Observe how AutoAllocate did allocate 1MB Extents to P_100 and P_200 after the first 1MB of space usage (using 16 64KB Extents).
.
.
.


Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com