Re: Locally Managed Tablespaces - any cons???

Another cute feature of LMTs is that when you rebuild an object in an LMT, it tends to migrate to the start of the file - Oracle scans the bitmap to find space, so finds early empty extents first. In comparison, when you rebuild an object in a DMT, there is a non-trivial algorithm that Oracle uses to find space, which means that space near the end of file is just as likely to be used as space near the start of file.

As a consequence, if you want to make a file readonly, it is quite easy with an LMT to move a few objects and pack the data down to the front of the file, and resize the file to a minimum before making it read-only.

--
Jonathan Lewis

Host to The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html

For latest news of public appearances
See http://www.jlcomp.demon.co.uk

Screensaver or Lifesaver: http://www.ud.com
Use spare CPU to assist in cancer research.




no-spam hotmail.com (Kenneth Koenraadt) wrote in message
<3b7e264f.2229800_at_news.mobilixnet.dk>...


>On Sat, 18 Aug 2001 08:55:38 +1000, "Howard J. Rogers"

><howardjr_at_www.com> wrote:

>

>>

>>"Frank Hubeny" <fhubeny_at_ntsource.com> wrote in message

>>news:3B7CADF3.432D3FF6_at_ntsource.com...

>>> I tend to avoid locally managed tablespaces for the following reasons:

>>>

>>> (1) A bug using the locally managed tablespaces and read only

tablespaces


>>in

>>> 8i almost prohibits using these two features together at least in 8.1.6

>>(see

>>> Note 131886.1 on MetaLink for more info).

>>>

>>> (2) Once the storage parameters of the locally managed tablespace are

set,


>>> they cannot be changed without rebuilding the tablespace.  Setting the

>>> default storage parameters of a dictionary managed tablespace is far

more


>>> flexible.

>>

>>Why on earth would you ever *want* to change a tablespace's default

storage


>>parameters?  Doing so affects nothing already created within the

tablespace


>>(they've already picked up the old default as their own, operative,

storage


>>clause).

>>

>>LMT's inability to do something that has no point in any event is hardly a

>>drawback.

>>

>>But, assuming you had a desperate need to re-jig the thing, 8i's 'move

>>tablespace' makes creating a new tablespace with the "correct" size and

then


>>porting all segments into it a doddle.

>>

>>The business about DBA_SEGMENTS running slowly is true.

>>

>>Regards

>>HJR

>>

>>

>>>

>>> (3) Performance of queries against dba_segments appear, at least to me,

to


>>> be far slower than similar queries against dictionary managed

tablespaces.


>>>

>>> Locally managed tablespaces may have a performance benefit, as others

have


>>> mentioned, if there are many concurrent changes to the extents in the

>>> tablespace.  I would be curious to know how volatile a tablespace has to

>>be

>>> to justify making it locally managed rather than dictionary managed.

>>>

>>> However, since there are costs, in at least flexibility, with using

>>locally

>>> managed tablespaces, using them indiscriminately does not appear to be

>>> prudent.

>>>

>>> Frank Hubeny

>>>

>>>

>The real benefit of LMT is not performance-related, IMHO. It is

>administrative.

>

>LMT's DON't use default storage parameters, as you do not specify

>efault values for initial, next and pctincrease for LMT. Segments

>created in aLMT do not need to have initial, next and pctincrease

>specified. The 2 latter will be ignored, AFAIK.

>

>And that is the great thing about LMT : They effectively eliminates

>99% of the considerations with INITIAL and NEXT when creating segments

>in them => saving time, money and trouble. And, even better, they bury

>the controversial PCTINCREASE parameter once and for all.

>

>>>

>>>

>>> Ron Gardiner wrote:

>>>

>>> > New for 8i (I believe) is locally managed tablespaces...

>>> >

>>> > From what I've read, sounds like there is no reason NOT to use them.

Can


>>> > they be used for all types of TS?... rollback segs, temporary, how

about


>>> > system itself?

>>> >

>>> > Any feedback would be appreciated!

>>>

>>

>>

>

>Regards,

>Kenneth Koenraadt

>Systems Consultant

>Oracle DBA

>plovmand@<no-spam>hotmail.com