Locally managed table spaces

[ddrillich]

We are in the midst of an Oracle upgrade from 8i to 9i.
One of the issues that came up is the new feature of 9i - locally managed table spaces.
What are benefits of locally managed table spaces?

Thanks,
Dan

 

[ngibbs]

Mainly that extents are managed locally within the tablespace using bitmaps rather than on the dictionary. This improves perfomance since there is no overhead looking up extents for dmls.

 

[SantaMufasa]

Actually, Dan, Locally Managed Tablespace have been around and fully function for all of the 8i versions. I prefer the autoallocating LMTs over standard tablespaces since they cause a much more orderly growth of extents for segements (i.e., tables, indexes) and it removes the need/concern with INITIAL, NEXT, and PCTINCREASE specifications. In LMTs, INITIAL becomes an implied 64K, then LMTs allocation proceeds with 16 extents that are 64K each, then 63 extents that are 1MB each, then "exty-twelve" extents that are 8MB each. (I say "exty-twelve" because I've not checked to find out how many 8MB extents Oracle allocates.)

But, LMTs seem to be the best of all the Oracle world's space-allocation algorithms.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:39 (10Dec03) GMT, 13:39 (10Dec03) Mountain Time)

 

[karluk]

Locally managed tablespaces are supposed to offer performance benefits over dictionary management. The idea is that extent information isn't stored in the Oracle catalog, so inserts and updates no longer have the overhead of potentially updating the catalog tables.

I was fortunate enough to attend a seminar by Tom Kyte of "Ask Tom" fame at about the same time as we started planning for our Oracle 9i upgrade. He was absolutely rapturous on the subject of locally managed tablespaces. In fact, he suggested that we perform our upgrade by exporting the old 8i database and importing to a newly created 9i database. As I recall, the issue was that the system tablespace could not be converted from dictionary to local management, so it was better to start fresh. He said export/import was the way they had done their upgrade, and the effort was worth it, as long as the database is not so large as to make to process excessively slow.

 

[SantaMufasa]

Gents,

I don't know why we are focusing on 9i for the Locally Managed Tablespace issue. LMTs have been around for YEARS; I don't have an 8i db older that 8.1.6, but LMTs work just fine on it.

Also, Amens to what Karl and NGibbs said earlier:
"LMTs -- Use 'em !!!"

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:31 (10Dec03) GMT, 14:31 (10Dec03) Mountain Time)

 

[carp]

Concur. Around our work site, you'd have to present a good justification NOT to use them - and nobody's done it yet!

 

[karluk]

SantaMufasa,

Use of locally managed tablespaces is very much an Oracle 9i upgrade issue. Anyone who hasn't been using them in 8i should take the opportunity to convert during the migration. Even if the 8i database is already using locally managed tablespaces, the export/import upgrade advocated by Tom Kyte should be seriously considered. Oracle 8i did not allow the system tablespace to be locally managed, so converting it to local management as part of the upgrade is a highly desirable goal.

 

[SantaMufasa]

Karl,

I understand now. My concern was that people were avoiding LMTs because they were under the incorrect assumption that LMTs were available only beginning with 9i.

Holiday greetings,

Mufasa
(aka Dave of Sandy, Utah, USA @ 22:25 (10Dec03) GMT, 15:25 (10Dec03) Mountain Time)