Optimal Extent size when setting up LMT's

Hats off to the senior guys here who clarified a lot of things.

Well - One last question before I go ahead and implement LMT's in my production database.

1. Since my schema contains tables of diferent sizes ranging from 0.5M to 100M (Database size is roughly 10G), shall I use the autoallocate option or the uniform size.

2 - When I tested autoallocate, the extent sizes were so different for each segment (first 8M, second 1M etc).
Will this not cause fragmentation again?

3. I am planning an export, creation of LMt's and import of data into that LMT? Is that the right way to go about it?

Thanks,
Nizar

Quote:

---

Originally posted by nabaig
Hats off to the senior guys here who clarified a lot of things.

Well - One last question before I go ahead and implement LMT's in my production database.

1. Since my schema contains tables of diferent sizes ranging from 0.5M to 100M (Database size is roughly 10G), shall I use the autoallocate option or the uniform size.

2 - When I tested autoallocate, the extent sizes were so different for each segment (first 8M, second 1M etc).
Will this not cause fragmentation again?

3. I am planning an export, creation of LMt's and import of data into that LMT? Is that the right way to go about it?

Thanks,
Nizar

---

1. I would suggest the UNIFORM size. If you choose system-managed extents, you can specify the size of the initial extent and Oracle determines the optimal size of additional extents, with a minimum extent size of 64 KB. This is the default for permanent tablespaces.

2. If you choose to use AUTOALLOCATE, you have specified to allow the system (Oracle) to manage the extent size so it really should not matter what extent sizes are used or how they are chosen. The important thing is to ensure that you have enough free space to accomodate the extent request.

If you want predictable extent sizes then you should use UNIFORM extent sizing instead of AUTOALLOCATE.

And, do not worry about fragmentation.

3. Yes.

I like to setup different sized tablespaces to handle different tables. In your case, I might setup a LARGE tablespace with uniform size of 32M, a MEDIUM tablespace with 1M uniform extents, and a SMALL tablespace with 32K extents.

You can create your LMT and then MOVE/REBUILD your tables/indexes to the new TS.

Jeff's advise is very good, I suggest you follow it.

I downloaded a white paper by Bhaskar Himatingka, Oracle Corp and Juan Loaiza, Oracle Corp that recommends

Segments smaller than 128m should be placed in 128k extent tablespace.
Segments between 128M and 4G should be place in 4M extent tablespaces.
Segments Larger thean 4G should be placed in 128M extent tablespaces.

When a segment reaches 1024 extents it is a good candidate to be moved to the next larger extent size tablespace....


The article was called
How to Stop Defragmenting and Start Living: The Definitive Word on Fragmentation.

Quote:

---

Originally posted by molonede
I downloaded a white paper by Bhaskar Himatingka, Oracle Corp and Juan Loaiza, Oracle Corp that recommends

Segments smaller than 128m should be placed in 128k extent tablespace.
Segments between 128M and 4G should be place in 4M extent tablespaces.
Segments Larger thean 4G should be placed in 128M extent tablespaces.

When a segment reaches 1024 extents it is a good candidate to be moved to the next larger extent size tablespace....


The article was called
How to Stop Defragmenting and Start Living: The Definitive Word on Fragmentation.

---

Thanks a lot for the tip. was there any explanation why. Meaning why those 3 groups and why those limits?

I personally find the jump from 128K to 4M rather big. I use 64K (mostly for look-ups), 1M and 10M.

I read that article also and it had more relevance to dictionary managed TS in V 7.0 and 8.0 . That article did not address LMT's.

Quote:

---

Originally posted by nabaig
I read that article also and it had more relevance to dictionary managed TS in V 7.0 and 8.0 . That article did not address LMT's.

---

That makes sense. I was wondering why we should care about the amount of extents in LMTs...