What to set pctfree and pctused to for a work table? - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 23

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

  1. #11
    Join Date
    Feb 2002
    Posts
    14
    Ok , Thanks julian , got your point .
    yeah!!!

  2. #12
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    OK but nothing should happen to the design of the application if you truncate an empty table, right?

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

  3. #13
    Join Date
    Feb 2002
    Posts
    14
    yes ys !! u r very true.
    yeah!!!

  4. #14
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    The general guidelines for setting up PCTFREE and PCTUSED are:

    If the table is of

    1 Mostly inserts, Set a small PCTFREE and PCTUSED as 40

    2 Queue like - rows are inserted and subsequently deleted soon after- Set a small PCTFREE (5) and very small PCTUSED (10).
    Example, an audit table (log table). Rows older than a month get deleted every day.

    3 Randon Insert/Delete, estimate row size. Then apply the formula given below:
    PCTUSED = (100 - PCTFREE - (MAX(10,(MAX_ROW_SIZE/BLOCK_SIZE)*100))
    If the PCTUSED ends up -n negative, set it 1.

    4 Mostly update. Estimate row expansion in %, and set it to PCTFREE and set PCTUSED as 40.

    Tamilselvan

  5. #15
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282


    I read in Oracle 8i DBA HandBook that it's better if the sum of pctfree with pctused results 85, according to pctfree value. For example:

    if pctfree is 10%, pctused should be 75%

    if pctfree is 5%, pctused should be 80%

    if pctfree is 50%, pctused should be 35%

    this grants that a block will use most of its space for data storage.

    How far is this true?


    F.



  6. #16
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Oracle DBA HandBook talked about PCTFREE and PCTUSED are in general.
    It does not know the nature of table that undergoes changes.


  7. #17
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    When you switch to 9i you can forget about PCTUSED (and FREELIST stuff, which PCTUSED is all about). You can simply specify your localy managed tablespace as "SEGMENT SPACE MANAGEMENT AUTO" and let Oracle doo all the decisions about which blocks should be available for additional inserts. And no more freelist-contention problems...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #18
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    however with this new feature there are already performance problems being reported on metalink....

  9. #19
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Originally posted by Mnemonical

    For example:
    if pctfree is 10%, pctused should be 75%
    if pctfree is 5%, pctused should be 80%
    if pctfree is 50%, pctused should be 35%
    How far is this true?
    F.
    Very far!!!

    Example :
    DB_BLOCK_SIZE=4K
    Table TTTT has avg_row_len=400 byte
    Row_per_block ~9 max

    Every day insert ~ 1000 new records.
    Avg delete ~5% rows
    Business procedure has 3 steps:
    1) insert row (with initial information) ~ 100 bytes avg len
    2) 1 week later each row should be upbate and add +50 bytes to avg len
    3) 2 weeks later each row should be upbate and add last +250 bytes to avg len

    How we should calculate PCT_FREE, PCT_USED
    If we set :
    if pctfree is 10%, pctused should be 75% ~= 90% rows will be chained (and near 100% of db_blocks)
    if pctfree is 50%, pctused should be 35% ~= 25% rows will be chained

    base on information about 1step ~ 35% alg len of record (then PCT_FREE ~ 30-35%)
    base on information about 5% deleted rows:
    PCT_USED ~ 35-( 9*0.05*400)/4000 ~= 25(or 30%)

    Conclusion : use LMT



  10. #20
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Set PCTFREE to 75 % and PCTUSED 15 %.


    [Edited by tamilselvan on 03-14-2002 at 05:02 PM]

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