Does anyone agree/disagree with Steve's synopsis of locally managed
tablespaces particularly that they can cause much more physical IO
than dict managed for tablespaces with many small segments? Has
anyone had any significant improvment after using them on moderately
sized databases - eg. 10 - 20GB or any phobia of using them?
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.
Q. 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.
Ta, Leigh.
Received on Thu Nov 01 2001 - 00:27:25 CST
