Re: Table fragmentation when using AUTOALLOCATE compared to UNIFORM tablespace extent allocation type.
From: Saibabu Devabhaktuni <saibabu_d_at_yahoo.com>
Date: Fri, 19 Oct 2012 01:09:39 -0700 (PDT)
Message-ID: <1350634179.84134.YahooMailNeo_at_web161305.mail.bf1.yahoo.com>
"Is it true that AUTOALLOCATE will cause fragmentation if application performs inserts and deletes heavily on the table?" Absolutely not, Autoallocate for locally managed tablespaces will only use few extent sizes based on how big the object is during extent allocation. Tablespace fragmentation is possible in dictionary managed tablespace. Any holes left in the datafiles by autoallocate will be used by the objects during extent allocation if there is any space pressure.
Date: Fri, 19 Oct 2012 01:09:39 -0700 (PDT)
Message-ID: <1350634179.84134.YahooMailNeo_at_web161305.mail.bf1.yahoo.com>
"Is it true that AUTOALLOCATE will cause fragmentation if application performs inserts and deletes heavily on the table?" Absolutely not, Autoallocate for locally managed tablespaces will only use few extent sizes based on how big the object is during extent allocation. Tablespace fragmentation is possible in dictionary managed tablespace. Any holes left in the datafiles by autoallocate will be used by the objects during extent allocation if there is any space pressure.
In regards to what can influence table size across two databases for similar tables is, tablespace type (ASSM or MSSM), if MSSSM then the number of freelists and freelist groups, how many processes used for concurrent DML's, direct part versus conventional load for inserts, and pctfree/pctused settings for MSSM. You may want to figure out the true number of free blocks (by running rowid range query based on metadata from dba_extents) and dump some of those blocks to get more insight into why autoallocate table grew faster.
Thanks,
Sai
http://sai-oracle.blogspot.com
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Oct 19 2012 - 10:09:39 CEST