Re: Storage parameters

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Fri, 06 May 2005 20:50:10 +0200
Message-ID: <d5ge8r$6nq$03$1@news.t-online.com>

Hi,
basically, the advantage of LMT is not only elimination of fragmentation , but much more efficient resource management - i.e. information of free /used extents is no more keeped in the datadictionary ( basically tables

   in sys schema ), but in the bitmap, so all changes can be processed more quicker... From the extent size point of view it does not matter - you set it manually ( but always, by every segment ) to 64K extent size (DMT) or let it do the database (LMT with uniform size ) - i would prefer the job to be done by the database of course ;-)

Exp/imp inherit the extent size and recalculate it to the appropriate amount of uniform extents ( if compress=y as per default, then you get initial equal to current size of your segment), and it takes effect by creation of tables through imp (if you not use precreated tables) , the further allocation will be influenced by tablespace extent size (whatever , system or uniform ).

But all that staff is excellent given in the Tom Kyte's Effective Oracle by Design pp 221 - 226 , exactly the things you are asking about, so , i would highly recommend.

Best regards
Maxim

Randy Harris schrieb:
> "Mark D Powell" <Mark.Powell_at_eds.com> wrote in message
> news:1115321479.225708.38930_at_f14g2000cwb.googlegroups.com...
>

>>For Dictionary managed tablespace using initial = next with pctincrease
>>=0 is a good plan providing you can move the very large and very small
>>objects into tablespaces designed to hold object for the extent size
>>chosen.
>>
>>Being that you are going to migrate the data to a new tablespace in two
>>months I would not go to alot of work now resizing everything but would
>>instead just verify each object has a realistic next extent size and
>>proper setting of pctincrease, that is 0.  The exception is if by
>>reloacting and resizing objects now you would be getting the object
>>ready for how you intend to store it on the new database.
>>
>>Locally managed tablespaces are very nice.  I like uniform extents
>>especially for large objects.  We divided all our tables/indexes into
>>small and large and built the correct number of tablespaces to hold
>>eveything with room to grow.  Space management consists of counting the
>>number of free exents left in the tablespaces every week.
>>
>>We use autoallocate for a couple of tablespace that house vendor
>>products that lump everything into one tablespace.
>>
>>HTH -- Mark D Powell --
>>

>
>
> The tables will definitely be migrated to a different database with LMTs in
> the future (not too distant). What I need to understand now, however, is
> what the initial, next and pctincrease settings that I make now will matter
> then. Do they become totally irrelevant in the LMT, or just less
> sifnificant?
>
> This is what I read in the docs:
> "When you allocate a locally managed tablespace, you cannot specify default
> storage parameters or minimum extent size. If AUTOALLOCATE is specified, the
> tablespace is system managed with the smallest extent size being 64K. If
> UNIFORM SIZE is specified, then the tablespace is managed with uniform size
> extents of the specified SIZE. The default SIZE is 1M.
> When you allocate segments in a locally managed tablespace, the storage
> clause is interpreted differently than for dictionary-managed tablespaces.
> When an object is created in a locally managed tablespace, Oracle uses its
> INITIAL, NEXT, and MINEXTENTS parameters to calculate the initial size of
> the object's segment."
>
> I'm having trouble grasping the difference between autoallocate and uniform
> size, other than the default sizes. It seems to be saying that with
> autoallocate it sets the initial and next both to 64K. How is that
> different from setting both to 64K with a dictionary managed tablespace?
> (pctincrease to 0)
>
> Further, how does it manage extents on tables that are migrated into it
> with imp? Those tables already have initial and next settings, do those get
> used or are they discarded?
>
> I'm sure these questions are answered in the docs but I'll be darned if I
> can find them.
>
>
Received on Fri May 06 2005 - 13:50:10 CDT