Setting up Local Managed Tablespace !!

[Chris Fung]

Hi there,

platform: HP-UX 11.x + Oracle 8i

I am now planning to create 2 new locally managed tablespaces in a production data warehouse database (all its tablespaces are dictionary managed currently).

The reason for setting up this configuration is to accomodate a new set of application data.

My preference is to use "Autoallocate" option since the new tables will have difference size requirements.

Just wondering.....:
1. Do I need to provide the initial extent information for the tablespaces or the tables ? (As I got some confuse information in Oracle8i concept manual chapter 4-13)

2. No tablespace coalescing is required..... but is it mean there will be no "fragmentation"...?? especially for those tables undergo frequent insert, update and delete operations. From my understanding, database coalescing only combine the adjacent free extents to form a large one.... as such it is inevitably to run the DB export and import with compress set to "Y" to reorganize the tables (please correct me if I am wrong)

3. If I setting up another LMT index tablespace, does the LMT take care of those index management operations e.g. rebuilding indexes etc.

Much appreciated for your comments,

Cheers,

Chris,

[Jean-Luc Oudart]

Chris,

We have separated our tables in different size group (SMALL,MEDIUM,LARGE,XLARGE) for each of them we have a different locally managed tablespace with the size of extent that we want.
e.g
create tablespace MEDIUM_DATA datafile '/ora34/oradata0/cnv4/medium_data_1.dbf'
SIZE 1000M
extent management local uniform size 2048K;

There is no fragementation as all extents have the same size.

As far as I knoe you still have to rebuild your indexes.
LMT is just to reduce impact on SYSTEM tablespace and speed up extent allocation for inserts.

Rgds,
JL

[Jean-Luc Oudart]

You can get further documentation from Oracle metalink (Note:105120.1) or read the following doc from OTN

Rgds,
JL

[Brian Crabtree]

1. Do you have to, no. Should you, yes. If you give it an initial extent, it will not have to allocate a number of smaller extents to reach the largest size, but will begin using the size requested (by using the extent sizes required). So, if you know that a table is going to be 500m, it is better to create the table with an initial extent size of 500m, and it will create the table with 64m extents.

2. There will be no extent fragmentation. There is still the possiblity of row-fragmentation (from deletes and insert statements occuring). Extent fragmentation is the most common performance problem, however you should make sure you keep your tables analyzed, and keep an eye on chained rows to make sure that it is not out of hand. Reorganizing tables after a large delete will normally be a good idea (true without LMT too).

3. No. The LMT just handles the extent allocation for the index. You will need to rebuild indexes as needed.

For very large tables (over 1-2g) you should also consider using UNIFORM LMT's rather than autoallocate. Oracle has said that performance begins to be impacted after 1024 extents, which for a table created with autoallocate and no initial extent is around 1g.

Hope this helps,

Thanks,

Brian

[Indira Aramandla]

Hi Chris,

Locally Managed Tablespaces feature specifies that tablespace is locally managed.

There are two options available for defining locally managed tablespaces, which you already know.
1. AUTOALLOCATE specifies that the tablespace is system managed. Users cannot specify an extent size. For system-managed extents, Oracle determines the optimal size of extents, with a minimum extent size of 64K. This is the default for permanent tablespaces.

2. UNIFORM specifies that the tablespace is managed with uniform extents of SIZE bytes. The default SIZE is 1 megabyte. For uniform extents, you can specify an extent size or use the default size, which is 1 MB. Temporary tablespaces that manage their extents locally can only use this type of allocation.

***** So if you plan to choose autoallocate, then the answer to your first question is ???the user cannot specify an extent size for autoallocate???.

As you have read in the manual, a tablespace that is locally managed i.e (manages its own extents) maintains a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents. The sizes of extents that are managed locally can be determined automatically by the system.

*****So the answer to your second question is ???LMT eliminates the need to coalesce free extents???

Locally Managed Tablespaces feature is for space management. It does not rebuild the indexes. And I agree with Yogeeraj that indexes do not need to be rebuilt unless there is a need for like the index is corrupted, or there are lots of records deleted or you moved the index to a different tablespace???etc.

I hope your questions have been answred.


Regards
Indira

[Indira Aramandla]

Hi Chris,

Locally Managed Tablespaces feature specifies that tablespace is locally managed.

There are two options available for defining locally managed tablespaces, which you already know.
1. AUTOALLOCATE specifies that the tablespace is system managed. Users cannot specify an extent size. For system-managed extents, Oracle determines the optimal size of extents, with a minimum extent size of 64K. This is the default for permanent tablespaces.

2. UNIFORM specifies that the tablespace is managed with uniform extents of SIZE bytes. The default SIZE is 1 megabyte. For uniform extents, you can specify an extent size or use the default size, which is 1 MB. Temporary tablespaces that manage their extents locally can only use this type of allocation.

***** So if you plan to choose autoallocate, then the answer to your first question is ???the user cannot specify an extent size for autoallocate???.

As you have read in the manual, a tablespace that is locally managed i.e (manages its own extents) maintains a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents. The sizes of extents that are managed locally can be determined automatically by the system.

*****So the answer to your second question is ???LMT eliminates the need to coalesce free extents???

Locally Managed Tablespaces feature is for space management. It does not rebuild the indexes. And I agree with Yogeeraj that indexes do not need to be rebuilt unless there is a need for (eg:- if the index is corrupted, or there are lots of records deleted or you moved the index to a different tablespace???etc.
I hope your doubts are cleared.


Regards
Indira

[Chris Fung]

Hi all,

Thanks very much for the comments and I found the document quite useful !!

Cheers,

Chris,