Re: Locally Managed vs Dictionary managed tablespaces

I don't disagree with it, but read it carefully. He says that querying *two specific views* (and their ALL and USER equivalents, I guess) would require additional I/O. Absolutely true. That does not mean that normal running of the database requires additional I/O, so there should be no phobias associated with using them -except that you need to use them right (ie, merely using locally managed tablespace does not mean you have a licence to leave rational thought and space planning behind).

I have to say that Steve's answer where he says "I would have a preference for dictionary management for any tablespace with a large number of mostly small constant sized segments" rather begs the question -how do you ensure that the segments *are* 'constantly sized'? Well, using locally managed tablespace is the only solution that actually guarantees that!

Anyway, as he says, running two DBA reports is the only real drawback associated with LMTs. Would I stuff up a database just to make my DBA reports run a bit faster? Nah. Not on your nelly.

Regards
HJR

--

Oracle Resources : http://www.geocities.com/howardjr2000
========================================


"Leigh" <lsatchell_at_geelongcity.vic.gov.au> wrote in message
news:2603dc0c.0110312227.8e0a1dc_at_posting.google.com...


> 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?

>

>

> A.  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.

>

>

> Q. 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?

>

> A. 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.