What is the difference between locally managed and dictionary managed tablespaces, and what are the benefits of using a locally managed tablespace?
When Oracle allocates space to a segment (like a table or index), a group of contiguous free blocks called an extent is added to the segment. Metadata regarding extent allocation and unallocated extents are either stored in the data dictionary or in the tablespace itself. Tablespaces that record extent allocation in the dictionary are called dictionary managed tablespaces, and tablespaces that record extent allocation in the tablespace header are called locally managed tablespaces.
Locally managed tablespaces have the following advantages over dictionary-managed tablespaces:
- Local management of extents avoids recursive space management operations, which can occur in dictionary-managed tablespaces if consuming or releasing space in an extent results in another operation that consumes or releases space in a rollback segment or data dictionary table.
- Local management of extents tracks adjacent free space, eliminating coalescing free extents.
- Reliance on data dictionary is reduced. This minimizes access to the data dictionary, potentially, improving performance and availability.
Due to these improvements, Oracle recommends using locally managed tablespaces for all new tablespaces if fragmentation is expected to be an issue The only drawback with locally managed tablespaces is that 'used-extent' information is not kept in the data dictionary. It must be read from the segment header blocks (and additional extent map blocks if any) whenever it is required, including queries against DBA_SEGMENTS and DBA_EXTENTS. If a tablespaces with a large number of mostly small segments is locally managed rather than dictionary managed, then access to these views can cause a lot more physical I/O and thus impact the cache retention of user data. Furthermore, if the segments are mostly constant in size then the risk of the tablespace contributing to ST enqueue contention if dictionary managed is low. Thus, there is little motivation to make such a tablespace locally managed and a minor performance risk in doing so.
Dig Deeper on SAP Basis
Related Q&A from Farooq Ali
SAP BI/BW expert Farooq Ali discusses the differences in the use of SAP Software Deployment Manager (SDM) and SAP Java Support Package Manager (JSPM)... Continue Reading
Find out how resetting a transport buffer can help fix an error that occured during the installation of an SAPGUI patch. Learn how to avoid an ... Continue Reading
Find out how to set a default SAP BI client within an SAP Business Intelligence (BI) system. Learn how to run an SAP Basis TCode in a default SAP BI ... Continue Reading