initial and next extent
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: initial and next extent

  1. #1
    Join Date
    May 2002
    Posts
    193
    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

  2. #2
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    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
    anandkl

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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

  4. #4
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    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
    anandkl

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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)

  6. #6
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405

    Cool

    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...

    -nagarjuna

  7. #7
    Join Date
    May 2002
    Posts
    193
    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]

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Jul 2002
    Location
    Lincolnshire, IL
    Posts
    203
    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
    "Greatest Rewards come only with Greatest Commitments!"

  10. #10
    Join Date
    May 2002
    Posts
    193
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width