Re: Table fragmentation when using AUTOALLOCATE compared to UNIFORM tablespace extent allocation type.
- From: Saibabu Devabhaktuni <saibabu_d@xxxxxxxxx>
- To: free <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 19 Oct 2012 01:09:39 -0700 (PDT)
"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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
