does anyone have an opinion on LMTablespaces 2

[bookouri]

Does anyone have any opinions, or any recommended reading about locally managed tablespaces/tablespace default storage parameters, etc... in 9i? When to use, when not to use, determining the right storage parameters etc..

Id appreciate any input.

 

[sybaseguru]

bookouri,

This is an important and involved subject. However, I will give you my opinion which should be a starter for you.

Locally Managed Tablespace (LMT) is one of the key features in Oracle database. These have been made available since Oracle 8i (though not all options). It is worth using LMTs considering the benefits in doing so.

Advantages of LMTs

1) Dictionary contention is reduced.

Extent management in Dictionary Managed Tablespace(s) (DMT)s is maintained and carried out at the data dictionary level. This requires exclusive locks on dictionary tables. Heavy data processing that results in extent allocation/deallocation may sometimes result in contentions in the dictionary.

Extents are managed at the datafile level in LMTs (good news). Dictionary tables are no longer used for storing extent allocation/deallocation information. The only information still maintained in the dictionary for LMTs is the tablespace quota for users.

2) Space wastage removed.

In DMTs, there is no implied mechanism to enforce uniform extent sizes. The extent sizes may vary depending on the storage clause provided at the object level or the tablespace level, resulting in space wastage and fragmentation. 

Oracle enforces the uniform extents allocation in the LMTs (when created with UNIFORM SIZE clause). Space wastage is removed, as this would result in all the same sized extents in the tablespace. 

3) No Rollback generated.

In DMTs, all extent allocations and deallocations are recorded in the data dictionary. This generates undo information thus using vital resources and may compete with other processes.

In LMTs, no rollback is generated for space allocation and deallocation activities. 

4) Recursive space management operations removed.

In DMTs, SMON (the Oracle background process) wakes up every 5 minutes for coalescing free space in DMTs. Optionally, the ALTER TABLESPACE <tablespace name> COALESCE command is also used to coalesce DMTs and reduce fragmentation.

On the other hand, LMTs avoid recursive space management operations and automatically track adjacent free space, thus eliminating the need to coalesce free extents. This further reduces fragmentation.

5) Fragmentation reduced.

Fragmentation is reduced in LMTs but not completely eliminated. Since adjacent free spaces are automatically tracked, there is no need to do coalescing, as is required in the case of DMTs.

Should you begin to use locally managed tablespaces, I believe the answer is yes. In summary LMT is higly beneficial and a powerful feature. LMT makes management of object extents much easier.

For more information read the following document:

http://www.oracle.com/oramag/oracle/00-nov/index.html?o60o8i.html

Hope this helps

 

[bookouri]

Thanks, that was exactly what I needed to get started.

 

[walkerci]

bookouri,

I agree with sybaseguru's points. LMT is definitely the way to go.

You do need to know your table and index sizes to arrive at uniform extent sizes that are appropriate.

You don't want your tables and extents to have too many extents (> 100 or so). On the other hand, you don't want a 5K table sitting in a tablespace with a 128M uniform allocation.

So in many cases, you will need at least 3 tablespaces of with different uniform extent sizes (small, medium, and large). It also improve performance to separate your indexes and tables into separate tablespaces on separate physical disks.

For example: Small = 4K, Medium = 8M, Large = 128M.

What is right for your database is will depend on the size of your tables and indexes. And this can change over time as your database grows.

Hope this helps,

Charles

 

[SantaMufasa]

I agree with all the fine points appearing earlier in favor of locally managed extents. I believe that the downsides that Charles mentions may go away with the inclusion of the "AUTOALLOCATE" feature; thus using the syntax:
CREATE TABLESPACE <ts-name> DATAFILE '<filename>'...
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

With this feature, your segments grow in this fashion:
16 extents of 64K
~64 extents of 1MB
~256 extents of 8MB
et cetera

This way, smaller tables have a manageable number of smaller extents, and larger tables have a manageable number of large extents, all in the same tablespace.

Cheers,

Dave

 

[bookouri]

Yes, the autoallocate option is the one area Im still wondering about. The autoallocate vs the uniform size extents. My first impulse it kind of like pctincrease, I hesitate to let it manage itself. Hasnt it ALWAYS got to be better to do it manually?

 

[SantaMufasa]

Bookouri,

I find the autoallocate algorithm VERY beneficial/efficient. I manage about 55 Oracle databases/instances worldwide, and I have every one that I can on autoallocate. I have not found a manual algorithm that surpasses the performance of Oracle's autoallocate algorithm. If you find a better manual algorithm, please share.

Dave

 

[bookouri]

If I remember though, once a tablespace is created you can not change it to autoallocate without recreating the entire tablespace?

 

[SantaMufasa]

Bookouri,

That is correct...you must specify &quot;EXTENT MANAGEMENT LOCAL AUTOALLOCATE&quot; when you create the tablespace. Therefore, if you want all that behavior for a tablespace that does not current exhibit that behavior (which I have had happen on some legacy databases), then I recommend the following (if/when it is reasonable to do so):

1) Export schemas with objects in the non-EML (&quot;Extent-Management Local&quot tablespace.
2) &quot;DROP TABLESPACE <non-EML-ts-name> INCLUDING CONTENTS;&quot;
3) &quot;CREATE TABLESPACE <ts-name>...EXTENT MANAGEMENT LOCAL AUTOALLOCATE;&quot;

Not only does this cause the tablespace to become EML, but it also squeezes out all of the &quot;swiss-cheese&quot; fragmentation from the tablespace.

Cheers,

Dave

 

[bookouri]

thanks again, as usual a few good posts on tek-tips answers more questions than a few hundred pages of Oracle documentation..<G>

 

[walkerci]

Most of my work is in Large to Very Large Data Warehouses.
If I use AUTOALLOCATE, won't I end up with very large numbers of extents?
Based on my experience, large numbers of extents result in poor performance with table scans, index builds, mass inserts, etc.

I suppose if I were managing > 55 databases, some degree of performance penalty might be acceptable.

I can see good uses for AUTOALLOCATE in a DW environment though, particulary for tablespaces holding dimension tables and indexes.

Thanks for the insights.

Charles

 

[SantaMufasa]

Charles, AUTOALLOCATE does not necessarily imply a large number of extents. AUTOALLOCATE behavior does reflect larger extent size as the size of a segment increases. (See AUTOALLOCATE growth pattern seven posts above.)

Even if AUTOALLOCATE produced a large number of extents, it should not be a serious worry: Cary Milsap, Oracle's chief performance guru has written treatises concerning the relative lack of impact upon performance from a proliferation of extents. The only time heavy extent proliferation causes serious performance degradation is if you DROP or TRUNCATE a segment.

Among the downsides of the alternative to &quot;EXTENT MANAGEMENT LOCAL AUTOALLOCATE&quot; (manual, dictionary management) particularly for large segments in data warehouses applications, is the gigantic size to which NEXT EXTENT grows (due to PCTINCREASE), causing application run-time failures since Oracle cannot find 250MB (for example) of contiguous free space in the tablespace.