| From: | Steve Adams |
| Date: | 11-Apr-2001 09:51 |
| Subject: | Local vs. dictionary managed tablespaces |
|
|
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 for 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. And 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, hence my comment.
|
![]() |
Why do you prefer that? What is the advantage of the DD managed space over LMT in this situation?
|
![]() |
I would have a preference for dictionary management for any tablespace with a large number of mostly small constant sized segments. SYSTEM fits that description, but RBS and TEMP certainly do not - TOOLS and USERS may do, but probably do not. For the rationale, please see Planning Extents.
|
![]() |
I'm relatively new to 8i. My question is, if locally-managed tablespaces are so much more efficient than dictionary-managed tablespaces, why would I want to create any tablespaces as dictionary-managed? I have inherited a database whose architecture is dictionary-managed for SYSTEM, RBS, TEMP, TOOLS and USERS; and locally-managed for DATA and INDEX. I'm trying to understand what the thought process was for this kind of design.
|