PCT INCREASE
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: PCT INCREASE

  1. #1
    Join Date
    Nov 2000
    Posts
    416
    Our Data and index Tablespace has PCT-INCREASE = 25 is that good or not? What's the rule of thumbs ?

    Thanks

    An ounce of prevention is worth a pound of cure

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    A simple answer IMHO would be "not good". And my rule of the thumb is to use LMTs, not DMTs:

    Code:
    CREATE TABLESPACE ALPHA_DATA DATAFILE '/datadg/disk21/ads_data_01.dbf' 
    SIZE 512M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

  3. #3
    Join Date
    Nov 2000
    Posts
    416
    Can you explain more for DMT, we can't move to LMT

    Regards
    An ounce of prevention is worth a pound of cure

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by farrokhp
    Can you explain more for DMT, we can't move to LMT

    Regards
    Whay not? What version of Oracle do you use?


  5. #5
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    PCTINCREASE = >0 wil result in fragmentation. It's best left at zero.
    OCP 8i, 9i DBA
    Brisbane Australia

  6. #6
    Join Date
    Feb 2001
    Posts
    290
    This question is to Julian

    My database is running on 8.1.7 version, And i did see all i\of the tablespaces are DMT and not LMT,, so what all teh adavanrages i am going to get by converting the DMT to LMT..

    would take some time for me,

    Thanks,
    Madhu

  7. #7
    Join Date
    Nov 2000
    Posts
    416
    We run Oracle 8.0.5 . Is thar true that PCTINCREASE > 0 always create Fragmentation???? Can you explain a little why?

    What I ahve to do now? ALTER TABLESPACE PCTINCREASE TO 0 , OR ALTER TABLESPACE FROM DMT TO LMT ?? Can you send me the script , Thanks man
    An ounce of prevention is worth a pound of cure

  8. #8
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    farrokhp, Saying that it'll cause fragmentation ALWAYS, was probably a little quick fire on my behalf. If you NEVER have to recreate objects then you may never have fragmentation. (Very unlikely though) Additionally, having PCTINCREASE > 0 makes administration more difficult, ie. segment sizing because, really you don't know what the object NEXT extent size will be without some serious cacluations on your behalf. If you have large tables, it won't be long before your table is looking for a 2GB NEXT extents when the table stated out at 1 MG and had a 50% increase.

    I would make at segments PCTINCREASE = 0 . I haven't come across a situation where I've need to have PCTINCREASE > 0 yet. Although, your situation might be different.

    To change this, you'd have to change all the segments that have PCTINCREASE > 0. Just because your TABLESPACE has PCTINCREASE > 0 doesn't mean any segments have been created with this. (Tablespace params only get used when a segment is created in the tablespace WITHOUT the specific storage parms set).

    If you said

    CREATE TABLE TEST (a number);

    Then it would use the TABLESPACE defaults to create the segment.

    as opposed to

    CREATE TABLE TEST (a number) STORAGE (INITIAL 1m NEXT 1m PCTINCREASE 0);

    Which WON'T use tablespace defaults cause you've specifed them yourself.

    mrvajrala - The advantages of using LMT far outweigh any adantage of using DMT's.

    Cheers
    OCP 8i, 9i DBA
    Brisbane Australia

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by mrvajrala
    This question is to Julian

    My database is running on 8.1.7 version, And i did see all i\of the tablespaces are DMT and not LMT,, so what all teh adavanrages i am going to get by converting the DMT to LMT..

    would take some time for me,

    Thanks,
    Madhu
    I suggest that after using 8.1.7, you convert to LMTs. There are several advantages of LMTs to DMTs, make a search in this forum with keywords like LMT, advantage, etc. You will find a lot of information!


  10. #10
    Join Date
    Sep 2001
    Location
    chennai
    Posts
    69

    coalescing

    when pctincrease is equal to zero oracle will not do a coalescing when fragmentation occurs. we have to coalesce explicitly by ourselves. but if the pctincrease is greater than zero oracle(SMON) will take care of coalescing automatically.
    but managing the extent size becomes a real problem.so it is better to have the pctincreae equal to zero or a value closer to zero(say 1% or 2%).

    regards

    swaminathan

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