Balvant Chhasiya's Oracle Blog: September 2020

Friday, September 25, 2020

Oracle Extent Allocation Mystery and wasted/fragmented space in partitioned table PART-2

In Part-1-Oracle Extent Allocation for non-partitioned table, we learned how Oracle allocates extents for non-partitioned tables and which is most appropriate in most of the cases, however when you have large tables with partitions , sub partitions , composite partitions used for your application, then I would suggest you to take closer look at the way Oracle allocates extents for such tables, most of the cases when all the extents and not densely populated with rows then it will tend to have more storage space wasted and few millions of rows would required 100s of GBs of storage which could have been accommodate in lesser storage.

Recently while investigating data fragmentation with one of our client's database, I was amazed to see that there was huge wasted space for the large partitioned tables having multiple sub-partitions. The database I was investigating with fragmentation was an OLTP database having couple of large partitions/sub-partitions tables that occupies majority of database storage and I was surprised to see more than 70% storage was not properly utilized due extent allocation mystery for partitioned table which I will be revealing in this article.

Table Partition with subpartition creation:


SQL>  CREATE TABLE "T_OBJ_SUB1"
  2     (	"OWNER"       VARCHAR2(128 BYTE) ,
  3  	"OBJECT_NAME"     VARCHAR2(128 BYTE) ,
  4  	"SUBOBJECT_NAME" VARCHAR2(128 BYTE) ,
  5  	"OBJECT_ID" NUMBER,
  6  	"DATA_OBJECT_ID" NUMBER,
  7  	"OBJECT_TYPE" VARCHAR2(23 BYTE) ,
  8  	"CREATED" DATE,
  9  	"LAST_DDL_TIME" DATE,
 10  	"TIMESTAMP" VARCHAR2(19 BYTE) ,
 11  	"STATUS" VARCHAR2(7 BYTE) ,
 12  	"TEMPORARY" VARCHAR2(1 BYTE) ,
 13  	"GENERATED" VARCHAR2(1 BYTE) ,
 14  	"SECONDARY" VARCHAR2(1 BYTE) ,
 15  	"NAMESPACE" NUMBER,
 16  	"EDITION_NAME" VARCHAR2(128 BYTE) ,
 17  	"SHARING" VARCHAR2(18 BYTE) ,
 18  	"EDITIONABLE" VARCHAR2(1 BYTE) ,
 19  	"ORACLE_MAINTAINED" VARCHAR2(1 BYTE) ,
 20  	"APPLICATION" VARCHAR2(1 BYTE) ,
 21  	"DEFAULT_COLLATION" VARCHAR2(100 BYTE) ,
 22  	"DUPLICATED" VARCHAR2(1 BYTE) ,
 23  	"SHARDED" VARCHAR2(1 BYTE) ,
 24  	"CREATED_APPID" NUMBER,
 25  	"CREATED_VSNID" NUMBER,
 26  	"MODIFIED_APPID" NUMBER,
 27  	"MODIFIED_VSNID" NUMBER,
 28  	ins_dt date
 29     )  SEGMENT CREATION IMMEDIATE
 30    PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255
 31   NOCOMPRESS LOGGING
 32   tablespace IPYPRTBPS1
 33    --STORAGE  --commented
 34    PARTITION BY RANGE ("INS_DT") INTERVAL (NUMTODSINTERVAL (1, 'DAY'))
 35    SUBPARTITION BY HASH (  "OWNER",  "OBJECT_NAME",  "SUBOBJECT_NAME"  )
 36    SUBPARTITION TEMPLATE (
 37      SUBPARTITION "T1"  ,
 38      SUBPARTITION "T2" ,
 39      SUBPARTITION "T3" ,
 40      SUBPARTITION "T4" ,
 41      SUBPARTITION "T5" ,
 42      SUBPARTITION "T6" ,
 43      SUBPARTITION "T7" ,
 44      SUBPARTITION "T8"  )
 45  (PARTITION "P_FIRST"  VALUES LESS THAN (TIMESTAMP' 2018-02-12 00:00:00')
 46  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 47    STORAGE(
 48    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 49     ( SUBPARTITION "P_FIRST_T1" SEGMENT CREATION DEFERRED
 50     NOCOMPRESS ,
 51    SUBPARTITION "P_FIRST_T2" SEGMENT CREATION DEFERRED
 52     NOCOMPRESS ,
 53    SUBPARTITION "P_FIRST_T3" SEGMENT CREATION DEFERRED
 54     NOCOMPRESS ,
 55    SUBPARTITION "P_FIRST_T4" SEGMENT CREATION DEFERRED
 56     NOCOMPRESS ,
 57    SUBPARTITION "P_FIRST_T5" SEGMENT CREATION DEFERRED
 58     NOCOMPRESS ,
 59    SUBPARTITION "P_FIRST_T6" SEGMENT CREATION DEFERRED
 60     NOCOMPRESS ,
 61    SUBPARTITION "P_FIRST_T7" SEGMENT CREATION DEFERRED
 62     NOCOMPRESS ,
 63    SUBPARTITION "P_FIRST_T8" SEGMENT CREATION DEFERRED
 64    NOCOMPRESS ) )  ENABLE ROW MOVEMENT ;

Table created.

SQL> 
SQL> insert into t_obj_sub1 (select o.*,o.created from dba_objects o);

194556 rows created.

SQL> commit;

Commit complete.

Now let's see how the extents have been allocated for this table when we created table without specifying storage clause. Please note that we have truncated some content in below output to make it short and slim. If you observe the output, the first extent allocated for the extent is 8MB in size, which is 1024 blocks with each block is 8kb block_size.




SQL> set line 500 pagesize 50 echo on
SQL> col owner for a10
SQL> col segment_name for a20
SQL> col partition_name for  a25
SQL> col segment_type for a25
SQL> col tablespace_name for a15
SQL> col extent_id for 99
SQL> col file_id for 99
SQL> col block_id for 9999999999
SQL> col bytes for 9999999999
SQL> col blocks for 99999
SQL> 
SQL> 
SQL> select owner,segment_name,partition_name,segment_type,tablespace_name,extent_id,file_id,block_id,bytes,blocks
  2   from  dba_extents where owner='TEST' and segment_name='T_OBJ_SUB1';

OWNER      SEGMENT_NAME         PARTITION_NAME            SEGMENT_TYPE              TABLESPACE_NAME EXTENT_ID FILE_ID    BLOCK_ID       BYTES BLOCKS
---------- -------------------- ------------------------- ------------------------- --------------- --------- ------- ----------- ----------- ------
TEST       T_OBJ_SUB1           SYS_SUBP137446            TABLE SUBPARTITION        IPYPRTBPS1              0       8      266496     8388608   1024
TEST       T_OBJ_SUB1           SYS_SUBP137448            TABLE SUBPARTITION        IPYPRTBPS1              0       8      270592     8388608   1024
TEST       T_OBJ_SUB1           SYS_SUBP137454            TABLE SUBPARTITION        IPYPRTBPS1              0       8      275712     8388608   1024
TEST       T_OBJ_SUB1           SYS_SUBP137455            TABLE SUBPARTITION        IPYPRTBPS1              0       8      272640     8388608   1024
TEST       T_OBJ_SUB1           SYS_SUBP137459            TABLE SUBPARTITION        IPYPRTBPS1              0       8      277760     8388608   1024
TEST       T_OBJ_SUB1           SYS_SUBP137467            TABLE SUBPARTITION        IPYPRTBPS1              0       8      286976     8388608   1024
TEST       T_OBJ_SUB1           SYS_SUBP137469            TABLE SUBPARTITION        IPYPRTBPS1              0       8      280704     8388608   1024
TEST       T_OBJ_SUB1           SYS_SUBP137470            TABLE SUBPARTITION        IPYPRTBPS1              0       8      284928     8388608   1024
TEST       T_OBJ_SUB1           SYS_SUBP137472            TABLE SUBPARTITION        IPYPRTBPS1              0       8      283904     8388608   1024
TEST       T_OBJ_SUB1           SYS_SUBP137473            TABLE SUBPARTITION        IPYPRTBPS1              0       8      282880     8388608   1024
TEST       T_OBJ_SUB1           SYS_SUBP137475            TABLE SUBPARTITION        IPYPRTBPS1              0       8      290944     8388608   1024
TEST       T_OBJ_SUB1           SYS_SUBP137476            TABLE SUBPARTITION        IPYPRTBPS1              0       8      291968     8388608   1024
TEST       T_OBJ_SUB1           SYS_SUBP137487            TABLE SUBPARTITION        IPYPRTBPS1              0       8      300288     8388608   1024
TEST       T_OBJ_SUB1           SYS_SUBP137488            TABLE SUBPARTITION        IPYPRTBPS1              0       8      296192     8388608   1024
TEST       T_OBJ_SUB1           SYS_SUBP137489            TABLE SUBPARTITION        IPYPRTBPS1              0       8      295168     8388608   1024
TEST       T_OBJ_SUB1           SYS_SUBP137493            TABLE SUBPARTITION        IPYPRTBPS1              0       8      305408     8388608   1024
TEST       T_OBJ_SUB1           SYS_SUBP137495            TABLE SUBPARTITION        IPYPRTBPS1              0       8      309504     8388608   1024
TEST       T_OBJ_SUB1           SYS_SUBP137496            TABLE SUBPARTITION        IPYPRTBPS1              0       8      311552     8388608   1024
TEST       T_OBJ_SUB1           SYS_SUBP137497            TABLE SUBPARTITION        IPYPRTBPS1              0       8      302336     8388608   1024
TEST       T_OBJ_SUB1           SYS_SUBP137499            TABLE SUBPARTITION        IPYPRTBPS1              0       8      306432     8388608   1024
TEST       T_OBJ_SUB1           SYS_SUBP137507            TABLE SUBPARTITION        IPYPRTBPS1              0       8      314624     8388608   1024
TEST       T_OBJ_SUB1           SYS_SUBP137509            TABLE SUBPARTITION        IPYPRTBPS1              0       8      313600     8388608   1024
TEST       T_OBJ_SUB1           SYS_SUBP137512            TABLE SUBPARTITION        IPYPRTBPS1              0       8      315648     8388608   1024
TEST       T_OBJ_SUB1           SYS_SUBP137516            TABLE SUBPARTITION        IPYPRTBPS1              0       8      317696     8388608   1024
:
:
:
TEST       T_OBJ_SUB1           SYS_SUBP137372            TABLE SUBPARTITION        IPYPRTBPS1              0       9     2998400     8388608   1024
TEST       T_OBJ_SUB1           SYS_SUBP137373            TABLE SUBPARTITION        IPYPRTBPS1              0       9     2996352     8388608   1024
TEST       T_OBJ_SUB1           SYS_SUBP137374            TABLE SUBPARTITION        IPYPRTBPS1              0       9     3000448     8388608   1024
TEST       T_OBJ_SUB1           SYS_SUBP137378            TABLE SUBPARTITION        IPYPRTBPS1              0       9     3005568     8388608   1024
TEST       T_OBJ_SUB1           SYS_SUBP137381            TABLE SUBPARTITION        IPYPRTBPS1              0       9     3004544     8388608   1024
TEST       T_OBJ_SUB1           SYS_SUBP137382            TABLE SUBPARTITION        IPYPRTBPS1              0       9     3006592     8388608   1024
TEST       T_OBJ_SUB1           SYS_SUBP137385            TABLE SUBPARTITION        IPYPRTBPS1              0       9     3009664     8388608   1024

342 rows selected.

Now let us observe the storage clause picked up by default when this partitioned table is created without specifying storage clause(which will pick default storage clause). First we need to collect statistics to have the statistics related columns populated in dba_tab_subpartitions and related dictionary views. Please note that we have truncated some content in below output to make it short and slim.



SQL> 
SQL> exec dbms_stats.gather_table_stats(ownname => 'TEST', tabname => 'T_OBJ_SUB1',GRANULARITY=>'ALL', estimate_percent => dbms_stats.auto_sample_size, degree => '4',cascade => TRUE, method_opt=>'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

SQL> 
SQL> col table_owner for a10
SQL> col table_name for a15
SQL> col partition_name for a15
SQL> col subpartition_name for a25
SQL> col partition_position for 999
SQL> col subpartition_position for 9999
SQL> col tablespace_name for a15
SQL> col initial_extent for 999999999999
SQL> col next_extent  for 999999999999
SQL> col num_rows for 999999999999
SQL> col blocks for 99999999
SQL> col last_analyzed for a25
SQL> 
SQL> select table_owner,table_name ,partition_name,subpartition_name,partition_position,subpartition_position,tablespace_name,initial_extent, next_extent ,
  2  num_rows, blocks,last_analyzed
  3  from dba_tab_subpartitions where  table_name='T_OBJ_SUB1';

TABLE_OWNE TABLE_NAME      PARTITION_NAME  SUBPARTITION_NAME         PARTITION_POSITION SUBPARTITION_POSITION TABLESPACE_NAME INITIAL_EXTENT   NEXT_EXTENT      NUM_ROWS    BLOCKS LAST_ANALYZED 
---------- --------------- --------------- ------------------------- ------------------ --------------------- --------------- -------------- ------------- ------------- --------- --------------
TEST       T_OBJ_SUB1      P_FIRST         P_FIRST_T1                                 1                     1 IPYPRTBPS1             8388608       1048576          8971      1006 23-SEP-20     
TEST       T_OBJ_SUB1      P_FIRST         P_FIRST_T2                                 1                     2 IPYPRTBPS1             8388608       1048576          9069      1006 23-SEP-20     
TEST       T_OBJ_SUB1      P_FIRST         P_FIRST_T3                                 1                     3 IPYPRTBPS1             8388608       1048576          8850      1006 23-SEP-20     
TEST       T_OBJ_SUB1      P_FIRST         P_FIRST_T8                                 1                     8 IPYPRTBPS1             8388608       1048576          9067      1006 23-SEP-20     
TEST       T_OBJ_SUB1      P_FIRST         P_FIRST_T5                                 1                     5 IPYPRTBPS1             8388608       1048576          9088      1006 23-SEP-20     
TEST       T_OBJ_SUB1      P_FIRST         P_FIRST_T6                                 1                     6 IPYPRTBPS1             8388608       1048576          9134      1006 23-SEP-20     
TEST       T_OBJ_SUB1      P_FIRST         P_FIRST_T7                                 1                     7 IPYPRTBPS1             8388608       1048576          9144      1006 23-SEP-20     
TEST       T_OBJ_SUB1      P_FIRST         P_FIRST_T4                                 1                     4 IPYPRTBPS1             8388608       1048576          9033      1006 23-SEP-20     
TEST       T_OBJ_SUB1      SYS_P137258     SYS_SUBP137250                             2                     1 IPYPRTBPS1             8388608       1048576             3      1006 23-SEP-20     
TEST       T_OBJ_SUB1      SYS_P137258     SYS_SUBP137251                             2                     2 IPYPRTBPS1             8388608       1048576             2      1006 23-SEP-20     
TEST       T_OBJ_SUB1      SYS_P137258     SYS_SUBP137252                             2                     3 IPYPRTBPS1             8388608       1048576             4      1006 23-SEP-20     
TEST       T_OBJ_SUB1      SYS_P137258     SYS_SUBP137257                             2                     8 IPYPRTBPS1             8388608       1048576             1      1006 23-SEP-20     
TEST       T_OBJ_SUB1      SYS_P137258     SYS_SUBP137254                             2                     5 IPYPRTBPS1             8388608       1048576             4      1006 23-SEP-20     
TEST       T_OBJ_SUB1      SYS_P137258     SYS_SUBP137255                             2                     6 IPYPRTBPS1             8388608       1048576             2      1006 23-SEP-20     
TEST       T_OBJ_SUB1      SYS_P137258     SYS_SUBP137256                             2                     7 IPYPRTBPS1             8388608       1048576             2      1006 23-SEP-20     
TEST       T_OBJ_SUB1      SYS_P137258     SYS_SUBP137253                             2                     4 IPYPRTBPS1             8388608       1048576             4      1006 23-SEP-20     
TEST       T_OBJ_SUB1      SYS_P137339     SYS_SUBP137331                             3                     1 IPYPRTBPS1             8388608       1048576             4      1006 23-SEP-20     
TEST       T_OBJ_SUB1      SYS_P137339     SYS_SUBP137332                             3                     2 IPYPRTBPS1             8388608       1048576             1      1006 23-SEP-20     
TEST       T_OBJ_SUB1      SYS_P137339     SYS_SUBP137333                             3                     3 IPYPRTBPS1             8388608       1048576             4      1006 23-SEP-20     
TEST       T_OBJ_SUB1      SYS_P137339     SYS_SUBP137334                             3                     4 IPYPRTBPS1             8388608       1048576             2      1006 23-SEP-20     
TEST       T_OBJ_SUB1      SYS_P137339     SYS_SUBP137335                             3                     5 IPYPRTBPS1             8388608       1048576             1      1006 23-SEP-20     
TEST       T_OBJ_SUB1      SYS_P137339     SYS_SUBP137336                             3                     6 IPYPRTBPS1             8388608       1048576             3      1006 23-SEP-20     
TEST       T_OBJ_SUB1      SYS_P137339     SYS_SUBP137337                             3                     7 IPYPRTBPS1             8388608       1048576             4      1006 23-SEP-20     
TEST       T_OBJ_SUB1      SYS_P137339     SYS_SUBP137338                             3                     8 IPYPRTBPS1             8388608       1048576             1      1006 23-SEP-20     
TEST       T_OBJ_SUB1      SYS_P137357     SYS_SUBP137349                             4                     1 IPYPRTBPS1             8388608       1048576             1      1006 23-SEP-20     
TEST       T_OBJ_SUB1      SYS_P137357     SYS_SUBP137350                             4                     2 IPYPRTBPS1             8388608       1048576             3      1006 23-SEP-20     
TEST       T_OBJ_SUB1      SYS_P137357     SYS_SUBP137351                             4                     3 IPYPRTBPS1             8388608       1048576             3      1006 23-SEP-20     
TEST       T_OBJ_SUB1      SYS_P137357     SYS_SUBP137352                             4                     4 IPYPRTBPS1             8388608       
:
:
TEST       T_OBJ_SUB1      SYS_P137429     SYS_SUBP137423                            57                     3 IPYPRTBPS1             8388608       1048576             3       376 23-SEP-20     
TEST       T_OBJ_SUB1      SYS_P137429     SYS_SUBP137421                            57                     1 IPYPRTBPS1             8388608       1048576             2      1006 23-SEP-20     
TEST       T_OBJ_SUB1      SYS_P137429     SYS_SUBP137422                            57                     2 IPYPRTBPS1             8388608       1048576             1      1006 23-SEP-20     

456 rows selected.

If you observe in the above output, the INITIAL_EXTENT is 8MB(8388608) and NEXT_EXTENT size is 1MB(1048576) for the default storage clause. In below output you can observe that almost all the exten space in each subparitions are wasted and total 2.50GB (2527086KB) storage is being wasted where as actual data is approximately 25MB(26097KB). Please note that we have truncated some content in below output to make it short and slim.



SQL>
SQL> --Wasted space below HWM
SQL> col owner for a20
SQL> col table_name for a30
SQL> col LAST_ANALYZED for a20
SQL> col EXTENT_MANAGEMENT for a20
SQL> col SEGMENT_SPACE_MANAGEMENT for a20
SQL> col tablespace_name for a20
SQL> col sgment_space_mgmt for a15
SQL> col partition_name for a25
SQL> col subparition_name for a30
SQL>  
SQL> 
SQL> select * from
  2  (
  3  select
  4     table_owner,table_name,PARTITION_NAME,subpartition_name,last_analyzed,s.tablespace_name,EXTENT_MANAGEMENT EXTENT_MGMT,SEGMENT_SPACE_MANAGEMENT SEGMENT_SPACE_MGMT,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,round((blocks*8),2) "size (kb)" ,
  5     round((num_rows*avg_row_len/1024),2) "actual_data (kb)",
  6     (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)"
  7  from
  8     dba_tab_subpartitions T, dba_tablespaces s
  9  where
 10     (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
 11  and
 12  t.tablespace_name=s.tablespace_name
 13  order by (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) desc
 14  )
 15  where table_owner='TEST' AND TABLE_NAME='T_OBJ_SUB1' ;

TABLE_OWNE TABLE_NAME                     PARTITION_NAME            SUBPARTITION_NAME         LAST_ANALYZED        TABLESPACE_NAME      EXTENT_MGM SEGMEN      NUM_ROWS    BLOCKS EMPTY_BLOCKS  size (kb) actual_data (kb) wasted_space (kb)                                                                                                                                                                                                                                                                       
---------- ------------------------------ ------------------------- ------------------------- -------------------- -------------------- ---------- ------ ------------- --------- ------------ ---------- ---------------- -----------------                                                                                                                                                                                                                                                                       
TEST       T_OBJ_SUB1                     SYS_P137690               SYS_SUBP137682            23-SEP-20            IPYPRTBPS1           LOCAL      AUTO               1      1006            0       8048              .11           8047.89                                                                                                                                                                                                                                                                       
TEST       T_OBJ_SUB1                     SYS_P137708               SYS_SUBP137705            23-SEP-20            IPYPRTBPS1           LOCAL      AUTO               1      1006            0       8048              .11           8047.89                                                                                                                                                                                                                                                                       
TEST       T_OBJ_SUB1                     SYS_P137708               SYS_SUBP137706            23-SEP-20            IPYPRTBPS1           LOCAL      AUTO               1      1006            0       8048              .11           8047.89                                                                                                                                                                                                                                                                       
TEST       T_OBJ_SUB1                     SYS_P137717               SYS_SUBP137714            23-SEP-20            IPYPRTBPS1           LOCAL      AUTO               1      1006            0       8048              .11           8047.89                                                                                                                                                                                                                                                                       
TEST       T_OBJ_SUB1                     SYS_P137753               SYS_SUBP137748            23-SEP-20            IPYPRTBPS1           LOCAL      AUTO               1      1006            0       8048              .11           8047.89                                                                                                                                                                                                                                                                       
TEST       T_OBJ_SUB1                     SYS_P137357               SYS_SUBP137355            23-SEP-20            IPYPRTBPS1           LOCAL      AUTO               1      1006            0       8048              .11           8047.89                                                                                                                                                                                                                                                                       
TEST       T_OBJ_SUB1                     SYS_P137483               SYS_SUBP137476            23-SEP-20            IPYPRTBPS1           LOCAL      AUTO               1      1006            0       8048              .11           8047.89                                                                                                                                                                                                                                                                       
TEST       T_OBJ_SUB1                     SYS_P137510               SYS_SUBP137507            23-SEP-20            IPYPRTBPS1           LOCAL      AUTO               1      1006            0       8048              .11           8047.89                                                                                                                                                                                                                                                                       
TEST       T_OBJ_SUB1                     SYS_P137339               SYS_SUBP137332            23-SEP-20            IPYPRTBPS1           LOCAL      AUTO               1      1006            0       8048              .11           8047.89                                                                                                                                                                                                                                                                       
TEST       T_OBJ_SUB1                     SYS_P137339               SYS_SUBP137335            23-SEP-20            IPYPRTBPS1           LOCAL      AUTO               1      1006            0       8048              .11           8047.89                                                         :                                               
:
:
TEST       T_OBJ_SUB1                     SYS_P137690               SYS_SUBP137683            23-SEP-20            IPYPRTBPS1           LOCAL      AUTO               1       754            0       6032              .11           6031.89                                                                                                                                                                                                                                                                       
TEST       T_OBJ_SUB1                     SYS_P137528               SYS_SUBP137520            23-SEP-20            IPYPRTBPS1           LOCAL      AUTO               1       376            0       3008              .14           3007.86                                                                                                                                                                                                                                                                       
TEST       T_OBJ_SUB1                     SYS_P137429               SYS_SUBP137423            23-SEP-20            IPYPRTBPS1           LOCAL      AUTO               3       376            0       3008              .39           3007.61                                                                                                                                                                                                                                                                       
TEST       T_OBJ_SUB1                     SYS_P137294               SYS_SUBP137289            23-SEP-20            IPYPRTBPS1           LOCAL      AUTO             803       124            0        992           105.08            886.92                                                                                                                                                                                                                                                                       

320 rows selected.
SQL> 
SQL> 
SQL> 
SQL> select table_owner,table_name,sum(size_kb),sum(actual_data_kb),sum(wasted_space_kb) from
  2  (
  3  select
  4     table_owner,table_name,PARTITION_NAME,subpartition_name,   last_analyzed,s.tablespace_name,EXTENT_MANAGEMENT EXTENT_MGMT,SEGMENT_SPACE_MANAGEMENT SEGMENT_SPACE_MGMT,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,
  5     round((blocks*8),2) size_kb ,
  6     round((num_rows*avg_row_len/1024),2) actual_data_kb,
  7     (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) wasted_space_kb
  8  from
  9     dba_tab_subpartitions T, dba_tablespaces s
 10  where
 11     (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
 12  and
 13  t.tablespace_name=s.tablespace_name
 14  order by (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) desc
 15  )
 16  where table_owner='TEST' AND TABLE_NAME='T_OBJ_SUB1'
 17  group by table_owner,table_name;

TABLE_OWNE TABLE_NAME                     SUM(SIZE_KB) SUM(ACTUAL_DATA_KB) SUM(WASTED_SPACE_KB)
---------- ------------------------------ ------------ ------------------- --------------------                                                   
TEST       T_OBJ_SUB1                          2553184            26097.06           2527086.94                                                   SQL> 
SQL> 
SQL>

So the important questions is how can we control the wasted spacefor partitioned/sub-partitioned tables. There are TWO ways you can control the storage clause as mentioned below:

#1: specify 65k or smaller extent_size when you define the paritioned tables.

#2: set hiddent parameter _partition_large_extents to false. (We will discuss this in another article.)

(Initial Extent Size of a Partition Changed to 8MB from 64KB After Upgrade to 11.2.0.2 or Later (Doc ID 1295484.1)

Now let's examine method #1, when we create paritioned/sub-paritioned table T_OBJ_S2 with smaller initial_extent size i.e STORAGE(INITIAL 10K NEXT 20K). Oracle will allocate 64k initial extent_size. Please note that if you create 64k or lesser initial extent size Oracle will allocate at least 64k initial extent and the same algorithm for storage allocation will be applied similar to normal tables that mean first 16 extent size will be 64k and then 17th extent onward size would be 1MB and so on, which is evident from the below example. With smaller extent size in this case all the data was able to fit in 54MB instead of 2.50GB in previous case with default storage. Please note that it again depends on how densely you populate each extents, if you populate enough rows in each extents to optimally then it should be fine but if you use default extent allocation scheme for paritioned/sub-partitioned tables and you do not populate enough rows in each extents allocatged for the partitiones/sub-partitions then you may have huge wasted space in your database.



SQL> 
SQL>   CREATE TABLE "T_OBJ_S2"
  2     (	"OWNER"       VARCHAR2(128 BYTE) ,
  3  	"OBJECT_NAME"     VARCHAR2(128 BYTE) ,
  4  	"SUBOBJECT_NAME" VARCHAR2(128 BYTE) ,
  5  	"OBJECT_ID" NUMBER,
  6  	"DATA_OBJECT_ID" NUMBER,
  7  	"OBJECT_TYPE" VARCHAR2(23 BYTE) ,
  8  	"CREATED" DATE,
  9  	"LAST_DDL_TIME" DATE,
 10  	"TIMESTAMP" VARCHAR2(19 BYTE) ,
 11  	"STATUS" VARCHAR2(7 BYTE) ,
 12  	"TEMPORARY" VARCHAR2(1 BYTE) ,
 13  	"GENERATED" VARCHAR2(1 BYTE) ,
 14  	"SECONDARY" VARCHAR2(1 BYTE) ,
 15  	"NAMESPACE" NUMBER,
 16  	"EDITION_NAME" VARCHAR2(128 BYTE) ,
 17  	"SHARING" VARCHAR2(18 BYTE) ,
 18  	"EDITIONABLE" VARCHAR2(1 BYTE) ,
 19  	"ORACLE_MAINTAINED" VARCHAR2(1 BYTE) ,
 20  	"APPLICATION" VARCHAR2(1 BYTE) ,
 21  	"DEFAULT_COLLATION" VARCHAR2(100 BYTE) ,
 22  	"DUPLICATED" VARCHAR2(1 BYTE) ,
 23  	"SHARDED" VARCHAR2(1 BYTE) ,
 24  	"CREATED_APPID" NUMBER,
 25  	"CREATED_VSNID" NUMBER,
 26  	"MODIFIED_APPID" NUMBER,
 27  	"MODIFIED_VSNID" NUMBER,
 28  	ins_dt date
 29     )  SEGMENT CREATION IMMEDIATE
 30    tablespace IPYPRTBPS1
 31    STORAGE(INITIAL 10K NEXT 20K)
 32    PARTITION BY RANGE ("INS_DT") INTERVAL (NUMTODSINTERVAL (1, 'DAY'))
 33    SUBPARTITION BY HASH (  "OWNER",  "OBJECT_NAME",  "SUBOBJECT_NAME"  )
 34    SUBPARTITION TEMPLATE (
 35      SUBPARTITION "T1"  ,
 36      SUBPARTITION "T2" ,
 37      SUBPARTITION "T3" ,
 38      SUBPARTITION "T4" ,
 39      SUBPARTITION "T5" ,
 40      SUBPARTITION "T6" ,
 41      SUBPARTITION "T7" ,
 42      SUBPARTITION "T8"  )
 43  (PARTITION "P_FIRST"  VALUES LESS THAN (TIMESTAMP' 2018-02-12 00:00:00')
 44      ( SUBPARTITION "P_FIRST_T1" SEGMENT CREATION DEFERRED
 45     NOCOMPRESS ,
 46    SUBPARTITION "P_FIRST_T2" SEGMENT CREATION DEFERRED
 47     NOCOMPRESS ,
 48    SUBPARTITION "P_FIRST_T3" SEGMENT CREATION DEFERRED
 49     NOCOMPRESS ,
 50    SUBPARTITION "P_FIRST_T4" SEGMENT CREATION DEFERRED
 51     NOCOMPRESS ,
 52    SUBPARTITION "P_FIRST_T5" SEGMENT CREATION DEFERRED
 53     NOCOMPRESS ,
 54    SUBPARTITION "P_FIRST_T6" SEGMENT CREATION DEFERRED
 55     NOCOMPRESS ,
 56    SUBPARTITION "P_FIRST_T7" SEGMENT CREATION DEFERRED
 57     NOCOMPRESS ,
 58    SUBPARTITION "P_FIRST_T8" SEGMENT CREATION DEFERRED
 59    NOCOMPRESS ) )  ENABLE ROW MOVEMENT ;

Table created.

SQL> 
SQL> 
SQL> insert into t_obj_s2 (select o.*,o.created from dba_objects o);

195070 rows created.

SQL> 
SQL> 
SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> 

SQL> BEGIN
  2  dbms_stats.gather_table_stats(ownname => 'TEST', tabname => 'T_OBJ_S2',GRANULARITY=>'ALL', estimate_percent => dbms_stats.auto_sample_size, degree => '4',cascade => TRUE, method_opt=>'FOR ALL COLUMNS SIZE AUTO');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> 
SQL> 
SQL> 
SQL> col owner for a10
SQL> col segment_name for a20
SQL> col partition_name for  a25
SQL> col segment_type for a25
SQL> col tablespace_name for a15
SQL> col extent_id for 99
SQL> col file_id for 99
SQL> col block_id for 9999999999
SQL> col bytes for 9999999999
SQL> col blocks for 99999
SQL> 
SQL> select owner,segment_name,partition_name,segment_type,tablespace_name,extent_id,file_id,block_id,bytes,blocks
  2   from  dba_extents where owner='TEST' and segment_name='T_OBJ_S2';

OWNER      SEGMENT_NAME         PARTITION_NAME            SEGMENT_TYPE              TABLESPACE_NAME EXTENT_ID FILE_ID    BLOCK_ID       BYTES BLOCKS
---------- -------------------- ------------------------- ------------------------- --------------- --------- ------- ----------- ----------- ------
TEST       T_OBJ_S2             SYS_SUBP137941            TABLE SUBPARTITION        IPYPRTBPS1              0       9      209144       65536      8
TEST       T_OBJ_S2             SYS_SUBP137783            TABLE SUBPARTITION        IPYPRTBPS1              0       9      160936       65536      8
TEST       T_OBJ_S2             SYS_SUBP137783            TABLE SUBPARTITION        IPYPRTBPS1              1       8       68768       65536      8
TEST       T_OBJ_S2             SYS_SUBP137783            TABLE SUBPARTITION        IPYPRTBPS1              2      11       56488       65536      8
TEST       T_OBJ_S2             SYS_SUBP137783            TABLE SUBPARTITION        IPYPRTBPS1              3      10       56504       65536      8
TEST       T_OBJ_S2             SYS_SUBP137783            TABLE SUBPARTITION        IPYPRTBPS1              4       9      282784       65536      8
TEST       T_OBJ_S2             SYS_SUBP137788            TABLE SUBPARTITION        IPYPRTBPS1              0       9      147672       65536      8
TEST       T_OBJ_S2             SYS_SUBP137788            TABLE SUBPARTITION        IPYPRTBPS1              1       8       64760       65536      8
TEST       T_OBJ_S2             SYS_SUBP137788            TABLE SUBPARTITION        IPYPRTBPS1              2      11       56512       65536      8
TEST       T_OBJ_S2             SYS_SUBP137788            TABLE SUBPARTITION        IPYPRTBPS1              3      10       56528       65536      8
TEST       T_OBJ_S2             SYS_SUBP137788            TABLE SUBPARTITION        IPYPRTBPS1              4       9      282808       65536      8
TEST       T_OBJ_S2             SYS_SUBP137794            TABLE SUBPARTITION        IPYPRTBPS1              0       9      160984       65536      8
TEST       T_OBJ_S2             SYS_SUBP137794            TABLE SUBPARTITION        IPYPRTBPS1              1       8       71920       65536      8
TEST       T_OBJ_S2             SYS_SUBP137794            TABLE SUBPARTITION        IPYPRTBPS1              2      11       56552       65536      8
TEST       T_OBJ_S2             SYS_SUBP137871            TABLE SUBPARTITION        IPYPRTBPS1              0       9      198816       65536      8
TEST       T_OBJ_S2             SYS_SUBP137871            TABLE SUBPARTITION        IPYPRTBPS1              1       8       60640       65536      8
TEST       T_OBJ_S2             SYS_SUBP137877            TABLE SUBPARTITION        IPYPRTBPS1              0       9      197840       65536      8
TEST       T_OBJ_S2             SYS_SUBP137877            TABLE SUBPARTITION        IPYPRTBPS1              1       8       60656       65536      8
TEST       T_OBJ_S2             SYS_SUBP138053            TABLE SUBPARTITION        IPYPRTBPS1              0       9      228568       65536      8
TEST       T_OBJ_S2             SYS_SUBP137891            TABLE SUBPARTITION        IPYPRTBPS1              0       9      198848       65536      8
:
:
:
TEST       T_OBJ_S2             SYS_SUBP138163            TABLE SUBPARTITION        IPYPRTBPS1              0       9      281800       65536      8
TEST       T_OBJ_S2             SYS_SUBP138164            TABLE SUBPARTITION        IPYPRTBPS1              0       9      281784       65536      8
TEST       T_OBJ_S2             SYS_SUBP138166            TABLE SUBPARTITION        IPYPRTBPS1              0       9      281792       65536      8
TEST       T_OBJ_S2             SYS_SUBP138169            TABLE SUBPARTITION        IPYPRTBPS1              0       9      281824       65536      8
TEST       T_OBJ_S2             SYS_SUBP138170            TABLE SUBPARTITION        IPYPRTBPS1              0       9      281816       65536      8
TEST       T_OBJ_S2             SYS_SUBP138171            TABLE SUBPARTITION        IPYPRTBPS1              0       9      281832       65536      8
TEST       T_OBJ_S2             SYS_SUBP138178            TABLE SUBPARTITION        IPYPRTBPS1              0       9      282840       65536      8
TEST       T_OBJ_S2             SYS_SUBP138179            TABLE SUBPARTITION        IPYPRTBPS1              0       9      282856       65536      8
TEST       T_OBJ_S2             SYS_SUBP138180            TABLE SUBPARTITION        IPYPRTBPS1              0       9      282848       65536      8
TEST       T_OBJ_S2             SYS_SUBP138183            TABLE SUBPARTITION        IPYPRTBPS1              0       9      282864       65536      8
TEST       T_OBJ_S2             SYS_SUBP138186            TABLE SUBPARTITION        IPYPRTBPS1              0       9      283800       65536      8
TEST       T_OBJ_S2             SYS_SUBP138187            TABLE SUBPARTITION        IPYPRTBPS1              0       9      283832       65536      8
TEST       T_OBJ_S2             SYS_SUBP138189            TABLE SUBPARTITION        IPYPRTBPS1              0       9      283816       65536      8
TEST       T_OBJ_S2             SYS_SUBP138190            TABLE SUBPARTITION        IPYPRTBPS1              0       9      282872       65536      8
TEST       T_OBJ_S2             SYS_SUBP138191            TABLE SUBPARTITION        IPYPRTBPS1              0       9      283824       65536      8
TEST       T_OBJ_S2             SYS_SUBP138193            TABLE SUBPARTITION        IPYPRTBPS1              0       9      283808       65536      8


753 rows selected.


SQL> 
SQL> --Wasted space below HWM
SQL> col owner for a20
SQL> col table_name for a30
SQL> col LAST_ANALYZED for a20
SQL> col EXTENT_MANAGEMENT for a20
SQL> col SEGMENT_SPACE_MANAGEMENT for a20
SQL> col tablespace_name for a20
SQL> col sgment_space_mgmt for a15
SQL> col partition_name for a25
SQL> col subparition_name for a30
SQL> 
SQL> 
SQL> select * from
  2  (
  3  select
  4     table_owner,table_name,PARTITION_NAME,subpartition_name,last_analyzed,s.tablespace_name,EXTENT_MANAGEMENT EXTENT_MGMT,SEGMENT_SPACE_MANAGEMENT SEGMENT_SPACE_MGMT,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,round((blocks*8),2) "size (kb)" ,
  5     round((num_rows*avg_row_len/1024),2) "actual_data (kb)",
  6     (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)"
  7  from
  8     dba_tab_subpartitions T, dba_tablespaces s
  9  where
 10    -- (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
 11  --and
 12  t.tablespace_name=s.tablespace_name
 13  order by (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) desc
 14  )
 15  where table_owner='TEST' AND TABLE_NAME='T_OBJ_S2' ;

TABLE_OWNE TABLE_NAME                     PARTITION_NAME            SUBPARTITION_NAME         LAST_ANALYZED        TABLESPACE_NAME      EXTENT_MGM SEGMEN      NUM_ROWS    BLOCKS EMPTY_BLOCKS  size (kb) actual_data (kb) wasted_space (kb) 
---------- ------------------------------ ------------------------- ------------------------- -------------------- -------------------- ---------- ------ ------------- --------- ------------ ---------- ---------------- ----------------- 
TEST       T_OBJ_S2                       SYS_P137816               SYS_SUBP137813            23-SEP-20            IPYPRTBPS1           LOCAL      AUTO            5997       244            0       1952           802.33           1149.67 
TEST       T_OBJ_S2                       SYS_P137816               SYS_SUBP137814            23-SEP-20            IPYPRTBPS1           LOCAL      AUTO            5983       244            0       1952            806.3            1145.7 
TEST       T_OBJ_S2                       SYS_P137816               SYS_SUBP137812            23-SEP-20            IPYPRTBPS1           LOCAL      AUTO            5998       244            0       1952           808.32           1143.68 
TEST       T_OBJ_S2                       SYS_P137816               SYS_SUBP137809            23-SEP-20            IPYPRTBPS1           LOCAL      AUTO            6048       244            0       1952           809.16           1142.84 
TEST       T_OBJ_S2                       SYS_P137816               SYS_SUBP137808            23-SEP-20            IPYPRTBPS1           LOCAL      AUTO            6012       244            0       1952           810.21           1141.79 
TEST       T_OBJ_S2                       SYS_P137816               SYS_SUBP137810            23-SEP-20            IPYPRTBPS1           LOCAL      AUTO            6043       244            0       1952           814.39           1137.61 
TEST       T_OBJ_S2                       SYS_P137816               SYS_SUBP137811            23-SEP-20            IPYPRTBPS1           LOCAL      AUTO            6105       244            0       1952           816.78           1135.22 
TEST       T_OBJ_S2                       SYS_P137816               SYS_SUBP137815            23-SEP-20            IPYPRTBPS1           LOCAL      AUTO            6092       244            0       1952           820.99           1131.01 
TEST       T_OBJ_S2                       P_FIRST                   P_FIRST_T3                23-SEP-20            IPYPRTBPS1           LOCAL      AUTO            8850       244            0       1952           1218.6             733.4 
TEST       T_OBJ_S2                       P_FIRST                   P_FIRST_T1                23-SEP-20            IPYPRTBPS1           LOCAL      AUTO            8971       244            0       1952          1235.26            716.74 
TEST       T_OBJ_S2                       P_FIRST                   P_FIRST_T8                23-SEP-20            IPYPRTBPS1           LOCAL      AUTO            9067       244            0       1952          1239.63            712.37 
TEST       T_OBJ_S2                       P_FIRST                   P_FIRST_T4                23-SEP-20            IPYPRTBPS1           LOCAL      AUTO            9033       244            0       1952           1243.8             708.2 
:
:
:
TEST       T_OBJ_S2                       SYS_P137969               SYS_SUBP137965            23-SEP-20            IPYPRTBPS1           LOCAL      AUTO               0         0            0          0                0                 0 
TEST       T_OBJ_S2                       SYS_P137969               SYS_SUBP137966            23-SEP-20            IPYPRTBPS1           LOCAL      AUTO               0         0            0          0                0                 0 
TEST       T_OBJ_S2                       SYS_P137969               SYS_SUBP137967            23-SEP-20            IPYPRTBPS1           LOCAL      AUTO               0         0            0          0                0                 0 
TEST       T_OBJ_S2                       SYS_P137969               SYS_SUBP137968            23-SEP-20            IPYPRTBPS1           LOCAL      AUTO               0         0            0          0                0                 0 
TEST       T_OBJ_S2                       SYS_P137978               SYS_SUBP137970            23-SEP-20            IPYPRTBPS1           LOCAL      AUTO               0         0            0          0                0                 0 
TEST       T_OBJ_S2                       SYS_P137978               SYS_SUBP137972            23-SEP-20            IPYPRTBPS1           LOCAL      AUTO               0         0            0          0                0                 0 

456 rows selected.

SQL> 
SQL> 
SQL> select table_owner,table_name,sum(size_kb),sum(actual_data_kb),sum(wasted_space_kb) from
  2  (
  3  select
  4     table_owner,table_name,PARTITION_NAME,subpartition_name,   last_analyzed,s.tablespace_name,EXTENT_MANAGEMENT EXTENT_MGMT,SEGMENT_SPACE_MANAGEMENT SEGMENT_SPACE_MGMT,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,
  5     round((blocks*8),2) size_kb ,
  6     round((num_rows*avg_row_len/1024),2) actual_data_kb,
  7     (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) wasted_space_kb
  8  from
  9     dba_tab_subpartitions T, dba_tablespaces s
 10  where
 11     (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
 12  and
 13  t.tablespace_name=s.tablespace_name
 14  order by (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) desc
 15  )
 16  where table_owner='TEST' AND TABLE_NAME='T_OBJ_S2'
 17  group by table_owner,table_name;

TABLE_OWNE TABLE_NAME                     SUM(SIZE_KB) SUM(ACTUAL_DATA_KB) SUM(WASTED_SPACE_KB)
---------- ------------------------------ ------------ ------------------- --------------------
TEST       T_OBJ_S2                              54072            26163.87             27908.13

SQL>

Thursday, September 24, 2020

Oracle Extent Allocation for non-partitioned table - Part-1

As we all know, Oracle allocates storage space logically using blocks, extents(contiguous block space), segments for tables, indexes, partitions, subpartitions in tablespace consisting of one of more physical datafiles.

Oracle 9i introduced LOCALLY MANAGED TABLESPACE, prior to that Oracle was using DICTIONARY MANAGED TABLESPACE. With introduction of LOCALLY MANAGED TABLESPACE , AUTOMATIC SEGMENT SPACE MANAGEMENT(ASSM) was also introduced, which removes the need for managing freelists and freelist groups by using bitmaps to describe the space usage of each block is within a segment.

Let us first of all understand how Oracle performs extent allocation for normal/non-partitioned tables.

Below is the sample script we generally use to create locally managed ASSM tablespace.

CREATE TABLESPACE DATA
DATAFILE '+DATA001/PGTEST/DATAFILE/ipydata01.dbf' SIZE 1400M
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING ONLINE

EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;

Now, we will create non-partitioned table without storage clause and will insert data from dba_objects dictionary table to populate data.



SQL> set line 500 echo on                                     
SQL>
SQL>   drop table t_obj;

Table dropped.

SQL>
SQL>
SQL> CREATE TABLE "T_OBJ"
  2     ("OWNER" VARCHAR2(128 BYTE),				
  3  "OBJECT_NAME" VARCHAR2(128 BYTE),
  4  "SUBOBJECT_NAME" VARCHAR2(128 BYTE),
  5  "OBJECT_ID" NUMBER,
  6  "DATA_OBJECT_ID" NUMBER,
  7  "OBJECT_TYPE" VARCHAR2(23 BYTE),
  8  "CREATED" DATE,
  9  "LAST_DDL_TIME" DATE,
 10  "TIMESTAMP" VARCHAR2(19 BYTE),
 11  "STATUS" VARCHAR2(7 BYTE),
 12  "TEMPORARY" VARCHAR2(1 BYTE),
 13  "GENERATED" VARCHAR2(1 BYTE),
 14  "SECONDARY" VARCHAR2(1 BYTE),
 15  "NAMESPACE" NUMBER,
 16  "EDITION_NAME" VARCHAR2(128 BYTE),
 17  "SHARING" VARCHAR2(18 BYTE),
 18  "EDITIONABLE" VARCHAR2(1 BYTE),
 19  "ORACLE_MAINTAINED" VARCHAR2(1 BYTE),
 20  "APPLICATION" VARCHAR2(1 BYTE),
 21  "DEFAULT_COLLATION" VARCHAR2(100 BYTE),
 22  "DUPLICATED" VARCHAR2(1 BYTE),
 23  "SHARDED" VARCHAR2(1 BYTE),
 24  "CREATED_APPID" NUMBER,
 25  "CREATED_VSNID" NUMBER,
 26  "MODIFIED_APPID" NUMBER,
 27  "MODIFIED_VSNID" NUMBER
 28     )   SEGMENT CREATION IMMEDIATE
 29    PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255
 30   NOCOMPRESS LOGGING
 31    TABLESPACE "DATA" ;

Table created.

SQL>

SQL>
SQL> insert into t_obj (select * from dba_objects);

51684 rows created.

SQL> commit;

Commit complete.

SQL> 

SQL>

Let us check the extent allocation detail for the table segment created above.


  
SQL> set line 200 pagesize 50

SQL> col owner for a10
SQL> col segment_name for a20
SQL> col partition_name for  a25
SQL> col segment_type for a25
SQL> col tablespace_name for a15
SQL> col extent_id for 99
SQL> col file_id for 9999
SQL> col block_id for 9999999999
SQL> col bytes for 9999999999
SQL> col blocks for 99999
SQL>
SQL> select owner,segment_name,partition_name,segment_type,tablespace_name,extent_id,file_id,block_id,bytes,blocks
  2   from  dba_extents where owner='ADMIN' and segment_name='T_OBJ';

OWNER      SEGMENT_NAME  PARTITION_NAME    SEGMENT_TYPE    TABLESPACE_NAME EXTENT_ID FILE_ID    BLOCK_ID       BYTES BLOCKS
---------- ------------- ----------------- --------------- --------------- --------- ------- ----------- ----------- ------
ADMIN      T_OBJ                           TABLE           DATA                    0    4619      828416       65536      8
ADMIN      T_OBJ                           TABLE           DATA                    1    4619      828424       65536      8
ADMIN      T_OBJ                           TABLE           DATA                    2    4619      828432       65536      8
ADMIN      T_OBJ                           TABLE           DATA                    3    4619      828440       65536      8
ADMIN      T_OBJ                           TABLE           DATA                    4    4619      828448       65536      8
ADMIN      T_OBJ                           TABLE           DATA                    5    4619      828456       65536      8
ADMIN      T_OBJ                           TABLE           DATA                    6    4619      828464       65536      8
ADMIN      T_OBJ                           TABLE           DATA                    7    4619      828472       65536      8
ADMIN      T_OBJ                           TABLE           DATA                    8    4619      828480       65536      8
ADMIN      T_OBJ                           TABLE           DATA                    9    4619      828488       65536      8
ADMIN      T_OBJ                           TABLE           DATA                   10    4619      828496       65536      8
ADMIN      T_OBJ                           TABLE           DATA                   11    4619      828504       65536      8
ADMIN      T_OBJ                           TABLE           DATA                   12    4619      828512       65536      8
ADMIN      T_OBJ                           TABLE           DATA                   13    4619      828520       65536      8
ADMIN      T_OBJ                           TABLE           DATA                   14    4619      828528       65536      8
ADMIN      T_OBJ                           TABLE           DATA                   15    4619      828536       65536      8
ADMIN      T_OBJ                           TABLE           DATA                   16    4619      828544     1048576    128
ADMIN      T_OBJ                           TABLE           DATA                   17    4619      828672     1048576    128
ADMIN      T_OBJ                           TABLE           DATA                   18    4619      828800     1048576    128
ADMIN      T_OBJ                           TABLE           DATA                   19    4619      828928     1048576    128
ADMIN      T_OBJ                           TABLE           DATA                   20    4619      829056     1048576    128
ADMIN      T_OBJ                           TABLE           DATA                   21    4619      829184     1048576    128
ADMIN      T_OBJ                           TABLE           DATA                   22    4619      829312     1048576    128
ADMIN      T_OBJ                           TABLE           DATA                   23    4619      829440     1048576    128

24 rows selected.

SQL>

In the above output, If you observe carefully under extent_id, bytes and blocks columna, you will see that there are first 16 extents allocated for this segment with size 64kb (65536 bytes) (8 blocksx8kb) from the 17th extent extent size is increased to 1MB (128 blocks* 8kb), please remember this database created with default block size of 8kb.

Remember when we created tablespace DATA in the beginning with EXTENT MANAGEMENT LOCAL and AUTOALLOCATE, below extent allocation behavior is because of the AUTOALLOCATE and please note that this behavior is not disclosed in oracle document and can change at any time, however since 9i to 19c this behavior is the evident:

First 16 extents: 64 KB (1 MB in total).
Next 63 extents: 1 MB (total space is 16 MB).
Next 126 extents: 8 MB (until total space 1 GB).
Then extent size 64 MB is chosen.

and so on.

Extent allocation with AUTOALLOCATE in LMT may work best for most of the non-partitioned table however it may become little more complicated when used with partitioned tables, especially with multiple subpartitions. We will discuss about this in PART-2-Oracle Extent Allocation Mystery and wasted/fragmented space in partitioned table

Pages

Friday, September 25, 2020

Oracle Extent Allocation Mystery and wasted/fragmented space in partitioned table PART-2

Thursday, September 24, 2020

Oracle Extent Allocation for non-partitioned table - Part-1