Why PCTINCREASE?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Why PCTINCREASE?

  1. #1
    Join Date
    Feb 2001
    Posts
    295
    A little question:

    If PCTINCREASE other than zero potentially causes fragmentation and used space growth, what is the use of that parameter, why isn't it zero by default?

    I mean, what's the real advantage of PCTINCREASE parameter? Could you tell an illustrative scenario?



    Adriano.

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Why PCTINCREASE defaults to 50? IMHO the only reason are lazy/unknowledged DBAs and developers.
    Supose the tablespace default INITIAL parameter is 10K and default PCTINCREASE would be 0. When a new table (or index) is created without specifying storage, when the table grows to 100M (a rather tiny table nowadays) it will be consisted of 10,000 extents! This bares huge consequences regarding the maintaining of database dictionary and (in such an extreme case) also on performance when scaning such a table. If you apply PCTINCREASE=50, you will get 100M table in only about 20 extents. If I would have to choose between a table of 10.000 equaly sized extents and a same table of 20 differently sized extents I would choose the second option!

    However I agree that PCTINCREASE should alway be 0, but also INITIAL=NEXT size should be set correctly at the same time.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Oct 2000
    Posts
    139
    Hi

    The number of extents is very important? We want to use locally managed tablespace but dont know what size we should use since we have relatively big tables and very small tables and we want to use the uniform size extent option in locally managed tablespaces, will this have a huge impact on big tables if we use small extent sizes?

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Well, the localy managed tablespaces are totaly different beast. The number of extents per segment is almost of no importance here, as long as the size of extents conforms with the DB_BLOCK_SIZE and DB_FILE_MULTIBLOCK_READ_COUNT. If your extent size conforms with "SAFE" algorithm you'll be just fine (SAFE method has been mentioned quite frequently in this forum, so if you need more detail you should be able to find a URL for that paper right here in this forum).
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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