Oracle Database Discussions

Hello,

I read some recent thread about tablespace fragmentation, including some older posts, and was wondering why some Oracle DBA experts claim that fragmentation does not exist with locally managed tablespaces LMT, or was a question of "definition".

During my research I also found http://jonathanlewis.wordpress.com/2010/07/16/fragmentation-2, but I'd say it rather confirms what I think I know so far:

I think it is clear that when extents are allocated as needed, the extents of a segment may not be contiguous on disk. Within a tablespace, a segment can also span across different datafiles. This type of fragmentation can certainly have a negative impact on performance, for example causing disk head movements between inner and outer tracks of a disk and invalidating a disk's read ahead buffer.

Local management of extents means when space to store data needs to be allocated, Oracle will search the data file's bitmap for the required number of adjacent free blocks. If a data file does not have enough contiguous free blocks, Oracle will search in another datafile. So although LMT will help to reuse free space, there is no guarantee there won't be space or data fragmentation within an object, which again can certainly decrease performance due mechanical aspects of a hard drive.

Even with LMT, there will be usable or unusable free space at the block level, which is where Automatic Segment Space Management ASSM comes in. ASMM can be set to AUTO when creating a locally managed tablespace. However, row chaining and row migrating can be unavoidable, resulting in data blocks that can span across datafiles thereby affect performance.

LMT does not void the operation of segment shrinking to compact data in rows to release unused space, which contributes to physical data fragmentation and negative performance as more extents may need to be allocated than necessary.

Kind regards.