initial and next extent

Dear Sir,


Is it advisable for the size of the initial and next extent size of the table to coincide with the values given for the initial and next extents of the corresponding tablespace?? If so may I know the reason.

Thanks and Regards,

K.Diwakar

Its not necessary for the initial and next extent of table to concide with the tablespace.It would be good if you set ur initial and next extent storage parameter at the table level rather than the tablespace level to avoid fragmentation and the storage parameter set at the table level will over ride the tablespace level parameters...


regards
anandkl

Quote:

---

Originally posted by anandkl
set ur initial and next extent storage parameter at the table level rather than the tablespace level to avoid

---

in fact by doing that you will cause fragmentation, the ideal is dont set anything at segment level so all extents share same size (assumming tablespace is created with same initial and next size) which is what LMT can achieve with uniform size clause

I was talking for tablespace which are not LMT and u need to set ur initial and next of equal size at the table level...which will avoid fragmentation...

regards
anandkl

in DMT what you do is set minimum extent clause when you create your tablespace so even you set different extent size they will be multiple of each other and can be resued more easily (reducing fragmentation)

The strategy is like this.
1. Know the extent sizes of typical segments that are going to be created on a particular tablespace.
2. Use these values to create the tablespace.
3. These default values can be used for typical segments (Tables, Indexes etc).
4. User defined extent sizes can be used if the new segment is going to behave differently ( very small or large size)

I guess, this slves the debate of whether to use tablespace defaults or at segment level values.

To come to the base question...

It is advisable to have size of initial and next extents for tablespaces. The same applies for the newly created tables too.
If the table is created using a direct inserts and expected to be of bigger size, then we should go for a bigger intial extent that is multiple of next extent. This gives minimal ammount of fragmentation.

To remind you guys... The thumb rule is to have the extent sizes as multiples of "db_file_multiblock_read_count"

Offourse, LMT (locally managed tablespace) solves the problem of fragmentation. But, the bulk inserts are slower in LMT compared with DMT (dictionary managed tablespace).

So, it is upto we DBAs to chose the righ extent size and tablespace type to suit our requirements and needs.. This is called human touch... :p

-nagarjuna

Dear all,

Thanks for your answers. But my question is like this : say you want to set the size of initial and next extent of a table to a value say 'x' (value is chosen depending on the growth you expect), Is there any advantage of this value being a multiple of the size of initial and next extent of the corresponding tablespace.

Regards,

K.Diwakar

[Edited by diwakar on 08-30-2002 at 05:08 AM]

Quote:

---

Originally posted by nagarjuna
But, the bulk inserts are slower in LMT compared with DMT (dictionary managed tablespace).

---

How did you come to that conclusion? Can you elaborate this a bit further?

quote:
--------------------------------------------------------------------------------
Originally posted by diwakar
Is there any advantage of this value being a multiple of the size of initial and next extent of the corresponding tablespace.

--------------------------------------------------------------------------------

It can be advantageous as you'll face lesser disk fragmentation. But it is advisable that if you need to specify intial and next extent at table level, don't give a option for the same while creating tablespace.
Plz correct me if I'm wrong.
Sandy

Quote:

---

Originally posted by sandycrab
quote:
--------------------------------------------------------------------------------
Originally posted by diwakar
Is there any advantage of this value being a multiple of the size of initial and next extent of the corresponding tablespace.

--------------------------------------------------------------------------------

It can be advantageous as you'll face lesser disk fragmentation. But it is advisable that if you need to specify intial and next extent at table level, don't give a option for the same while creating tablespace.
Plz correct me if I'm wrong.
Sandy

---

Thank you for your answer but could you tell me how this could cause lesser fragmentation.

Regards,

K.Diwakar

Quote:

---

Originally posted by jmodic

Quote:

---

Originally posted by nagarjuna
But, the bulk inserts are slower in LMT compared with DMT (dictionary managed tablespace).

---

How did you come to that conclusion? Can you elaborate this a bit further?

---

This is not documented. This can be learn by experiance. Try creating two tablespaces of sizes 5gbs. One as LMT and other one as DMT. The try inserting huge amount of data. Other things being equal, the insertions in LMT takes more time. Try it out. Usually 30% more time than that of DMT.

-nagarjuna

I can not afford to play around with 10GB space in any of my testing instances, however I'm sure there are bits and peaces missing in your scenario, if you realy got 30% diference in bulk load performance between LMT and DMT.

For example:

What was the extent size in your LMT set to? Sure, if the bulk load created thousands of extents in LMT, then performance might be worse because of the extended bitmap management (we had discussion about this few days ago in this forum). But if you set your uniform extent size to let's say 1GB, so that the whole 5GB load will fit into 5 extents, then I simply can't belive that LMT will perform anything worse than DMT.

All that bulk load has to do with tablespace organisation is when it has to allocate new extent, that's all.

I said "OTHERS BEING EQUAL"
It means, extents sizes are of same size. I used 16mb extents. All are uniform extents in both LMT and DMT (pctincrease 0)

if you have tried in 9.2.0.1 then most probably slow insert is caused by automatic segment management