DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: What to set pctfree and pctused to for a work table?

  1. #1
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    I have a work table that inserts and deletes thousands of rows per month. This is causing data fragmentation, i.e. 14000+ blocks and only 135 blocks used. What should I set the pctfree and pctused to eliminate this type of fragmentation? Thanks.

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by ssmith
    I have a work table that inserts and deletes thousands of rows per month. This is causing data fragmentation, i.e. 14000+ blocks and only 135 blocks used. What should I set the pctfree and pctused to eliminate this type of fragmentation? Thanks.
    I gather somebody told you that you eliminate fragmenation by setting "right value" for PCTFREE and PCTUSED. However, they are not the real fragmentation factors.

    It is the delayed block cleanout which causes most of the fragmentation. I assume you know what a fast commit is?

    The only way to eliminate fragmentation is drop and recreate the object. Usually export and import will solve the fragmentation problem.

    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  3. #3
    Join Date
    Feb 2002
    Posts
    27
    Hi juliaan

    EXP/IMP will remove the fragmentation ok .
    but if he will not change the storage setting ,In next few months , will he not face fragmantation again ??

    please thru some light .

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Fragmentation usually does not come due to bad storage settings. There are things, we as DBAs cannot influence on, things that a major fragmentation factors.

    Here is how tables get most oftenly fragmented: say that there is a table or index upon which you are doing large deletes and then inserts very frequently. After deleting lots of rows and immediately afterwards insert rows, well the table will grow. The reason is: delayed block cleanout, which will cause fragmentation in the table.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    pctused and pctfree controls block fragmentation, but the fragmentation you are talking about is at segment level!
    you can reconstruct your table with alter table xxx move tablespace vvvv

    if you want to use your blocks efficiently, set a low pctfree (if there are no updates) and high pctused so the blocks cab be relinked to freelists more easily

    [Edited by pando on 03-13-2002 at 05:44 AM]

  6. #6
    Join Date
    Feb 2002
    Posts
    14
    Thanks Julian/pando,

    I got ur point.

    Now I have a slightly different question ,

    I have a table not having records more then 10000.
    But users process are such that they delete ALL the records daily and then next days new records are inserted for processing.

    I am not facing fragmantation problem but sometimes performance problem there.

    As all the data is daily deleted from the table . It it makes any sense to Rebuild indexs of such a table.
    yeah!!!

  7. #7
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Do they TRUNCATE the table or just DELETE?
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  8. #8
    Join Date
    Feb 2002
    Posts
    14
    They just delete.

    In fact rows are processed one by one and then moved to some other table ( i.e. fst insert in to some other table and then delete from the main table)
    yeah!!!

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by sri_sumit123
    They just delete.

    In fact rows are processed one by one and then moved to some other table ( i.e. fst insert in to some other table and then delete from the main table)
    Why don't you ask them to truncate the table after it's empty?

    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  10. #10
    Join Date
    Feb 2002
    Posts
    14
    Hi julian,

    This is application design, based on some values of columns the rows are deleted after pocessing. Design can not be changed.





    yeah!!!

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