Pctfree/pctused
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Pctfree/pctused

  1. #1
    Join Date
    Oct 2002
    Posts
    21

    Pctfree/pctused

    Please advise.
    Daily data is refreshed in 100 tables.Some of the rows have Avg row length between 1025 –1150 bytes I have to recreate the tables/indexes
    No updates against the tables, only:
    1.truncate
    2.inserts
    What experienced DBA’s would set for pctfree /pctused

    Please help.
    Thanks

  2. #2
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    PCTFREE is the space reserved for updates.
    PCTUSED is threshhold for putting a data block onto the freelist. That is, when the free space/total space percentage is less than PCTUSED, the data block goes back on the freelist.

    If you perform no updates, then you should set your PCTFREE to be very low, i.e. 5 or 10.

    If you are deleting all of the data, you can set the PCTUSED to be low also, say 25 or 30. It seems irrelevant though if all the data is deleted.
    David Knight
    OCP DBA 8i, 9i, 10g

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I don't see the need to stop a PCTFREE=5 or 10 -- set PCTFREE=0 and use the whole block.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282
    slimdave,

    dknight is correct because pctfree does not refer just to updates. It also refers to transactions ocurring in a data block. So, each transaction ocurring with a row means the block needs free space for this overhead allocation, then pctfree space will be used, so pctfree should be more than 0.

    F

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    What kind of transactions do youmean, and what is it that they need the free space for?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Oh that kind of transaction entry, sorry, it's a bit late for brain work.

    That's only for insert, delete, update and "select for update", none of which are going to be going on in this case (insert and truncate). And you only need about 23 bytes for those, which is much less than half-a-percent of an 8k block, so even if you were looking a having to guarantee space for the transaction entries, you could do so with a pctfree of 1.

    In this case I'd still go for pctfree=0
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I thought that space is reserved in the block overhead and not pctfree?

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by pando
    I thought that space is reserved in the block overhead and not pctfree?
    Check http://www.dbasupport.com/forums/sho...threadid=22877 for the explanation.
    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