Hi All,
We are testing Oracle 11g OLTP compression feature by benchmarking between AUTOALLOCATE and UNIFORM extent management tablespaces.
But not able to justify or find the reason why segment size is doubled in UNIFORM allocation tablespace.
For AUTOALLOCATE tablespace:
SQL> select TABLESPACE_NAME,BLOCK_SIZE,INITIAL_EXTENT,NEXT_EXTENT,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT,DEF_TAB_COMPRESSION,COMPRESS_FOR from dba_tablespaces where tablespace_name='TAB1';
TABLESPACE BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT EXTENT_MAN ALLOCATION SEGMENT_SPACE_MANAGEMENT DEF_TAB_COMPRESSION COMPRESS_F
---------- ---------- -------------- ----------- ---------- ---------- ------------------------ -------------------------------- ----------
TAB1 32768 65536 LOCAL SYSTEM AUTO ENABLED OLTP
SQL> select TABLE_NAME, COMPRESS_FOR, PCT_FREE from DBA_TABLES where TABLE_NAME = 'TEST' ;
TABLE_NAME COMPRESS_FOR PCT_FREE
------------------------------ ------------------------------------------------ ----------
TEST OLTP 10
According to metalink : How to Evaluate Table Compression Space Savings [ID 1374169.1]
WITH blocks AS (
SELECT COUNT(*) usedblocks, AVG(rowcount) avgrowsperusedblock
FROM (
SELECT
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID),
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID),
COUNT(*) rowcount
FROM DM.TEST
GROUP BY
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID),
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
)
)
SELECT extents, blocks allocatedblocks, usedblocks, avgrowsperusedblock
FROM blocks, dba_segments
WHERE segment_name = 'TEST'
AND owner= 'DM'
/
EXTENTS ALLOCATEDBLOCKS USEDBLOCKS AVGROWSPERUSEDBLOCK
---------- --------------- ---------- -------------------
185 29696 29333 325.842191
SQL> select segment_name,bytes/1024/1024/1024 MB from dba_segments where segment_name='TEST';
SEGMENT_NAME MB
------------------------------ ----------
TEST .90625
For UNIFORM tablespace:
SQL> select TABLESPACE_NAME,BLOCK_SIZE,INITIAL_EXTENT,NEXT_EXTENT,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT,DEF_TAB_COMPRESSION,COMPRESS_FOR from dba_tablespaces where tablespace_name='TAB2';
TABLESPACE BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT EXTENT_MAN ALLOCATION SEGMENT_SPACE_MANAGEMENT DEF_TAB_COMPRESSION COMPRESS_F
---------- ---------- -------------- ----------- ---------- ---------- ------------------------ -------------------------------- ----------
TAB2 32768 33554432 33554432 LOCAL UNIFORM AUTO ENABLED OLTP
SQL> select TABLE_NAME, COMPRESS_FOR, PCT_FREE from DBA_TABLES where TABLE_NAME = 'TEST' ;
TABLE_NAME COMPRESS_FOR PCT_FREE
------------------------------ ------------------------------------------------ ----------
TEST OLTP 10
According to metalink : How to Evaluate Table Compression Space Savings [ID 1374169.1]
WITH blocks AS (
SELECT COUNT(*) usedblocks, AVG(rowcount) avgrowsperusedblock
FROM (
SELECT
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID),
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID),
COUNT(*) rowcount
FROM DM.TEST
GROUP BY
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID),
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
)
)
SELECT extents, blocks allocatedblocks, usedblocks, avgrowsperusedblock
FROM blocks, dba_segments
WHERE segment_name = 'TEST'
AND owner= 'DM'
/
EXTENTS ALLOCATEDBLOCKS USEDBLOCKS AVGROWSPERUSEDBLOCK
---------- --------------- ---------- -------------------
52 53248 52530 181.998877
SQL> select segment_name,bytes/1024/1024/1024 MB from dba_segments where segment_name='TEST';
SEGMENT_NAME MB
------------------------------ ----------
TEST 1.625
Some additional information about database:
Version : 11.2.0.2.0
Blocksize : 32
Both tables have been created using CTAS method.
Could anyone help me in finding why segment size differs so much when i use UNIFORM extent alocation along with OLTP compression.