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

Thread: PCTFREE Vs PCTUSED

Hybrid View

  1. #1
    Join Date
    Oct 2000
    Posts
    250
    Dear all,
    I want to pour some questions on the above topic questions :

    1. I come across a statement saying that PCTUSED Parameter explanation - After a data block becomes full, as determined by PCTFREE, Oracle does not consider the block for the insertion of new rows until the percentage of the block being used falls below the parameter PCTUSED. Before this value is archived, Oracle uses the free space of the data block only for updates to rows already contained in the data block.

    Q : What is the reason for this - PCTUSED ? Why they want to control that parameter ? Can I set it to 0 ?

    Q: Is free list this pre-allocated ? Is this only use for PCTFREE ? Where does it store ?

    Your answer is very much appreciated. Thanks.

  2. #2
    Join Date
    Feb 2001
    Posts
    7

    Arrow re: pctused vs pctfree

    hope this will provide you your answer...

    The PCTFREE parameter specifies the percentage of

    space in each data block that is reserved for growth

    resulting from updates of rows in that data block. This

    parameter has a default value of 10 percent.

    For example, the value of the PCTFREE parameter is

    specified as 20 in a CREATE TABLE statement. This

    indicates that inserts to the block should stop as soon as

    free space drops to 20 percent or less. The free space

    thereafter can only be used for updates.

    The PCTUSED parameter represents the minimum

    percentage of the used space that the Oracle server tries

    to maintain for each data block of the table. This

    parameter has a default value of 40 percent.

    When a data block is filled to the limit determined by the

    value of the PCTFREE parameter, Oracle considers the

    block unavailable for the insertion of new rows. The block

    is unavailable for the insertion of new rows until the

    percentage filled by the data of that block falls below the

    value of the PCTUSED parameter.

    Until the percentage of the block falls below the value of

    the PCTUSED parameter, Oracle uses the free space of

    the data block only for updating the rows contained in the

    data block.

    For example, if PCTUSED is defined as 40 percent, the

    block is reused for inserts as soon as utilization drops to

    less than 40 percent. Inserts continue until utilization

    reaches 80 percent and the cycle repeats.


  3. #3
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530
    Pctused and Pctfree are block storage parameters.
    Pctused is used to find out how much percentage of the block will be used to store rows.Pctfree is used to find out how much percentage of the block will be used to store rows resulting from further updates to the rows in the same datablock.

    Eg. If u keep pctused to 40% and pctfree 20.so u can insert rows till 40 %.if the limit exceeds 40%,still also u can insert rows in the datablock till the limit reaches 80% (100%-20%) as u have kept pctfree to 20%.Now if one goes on deleting the rows,the block is not said to be free unless and until pctused falls below 40%.As soon as pctused falls below 40% from deleting the rows, that block can be used to insert the rows.In this way the cycle continous.So it is recommended that u never sum up pctused+pctfree=100.Always have some gap between them this helps in reducing ur Oracle server for allocation and disallocation of freelists.

    If any further doubts write to rohitsn@hotmail.com

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    This is an interesting question. I would like to share my experience.
    In the last 3 months, I had interviewed 15 Oracle DBAs and asked the same question. Only one person answered correctly.

    Both the parameters are applicable and used for each data block in the Database. I hope an example will give you the right answer.

    Consider 8K block size. The total bytes 8 x 1024 = 8196 bytes
    Each block requires approximately 117 bytes for the header. Please note that the header size varies depending upon the block size.
    The total available bytes for data = ( 8196 – 117) = 8079 bytes.

    A table is created with PCTFREE 20 PCTUSED 50 .

    PCTRFREE in bytes = 1615
    PCTUSED in bytes = 4039

    Now the data available for insert and update = (8079 – (20 * 8079)/100 ) = 6463 Bytes.

    Now user can insert new rows into this block as long as the old rows’ total bytes + new row’s total byte is less than or equal to 6463 bytes. If the new row’s total byte cannot be put into this block, then Oracle will get the next block from the free list, and inserts into it.

    When a row is updated and the row’s data is expanded, then PCTFREE come into play. The updated row’s data is placed into PCTFREE’s area, provided the updated row’s new data can be fit into PCTFREE area. If it is not fit into that area, another new block will be obtained from the Freelist, and the row will be migrated. But the original row info (pointer) is kept in the old block. For subsequent access to this row involves 2 read I/O. That is why row migration should be avoided because of excessive I/Os.

    ROW DELETION:

    The PCTUSED parameter value (in this example 50 %) is the threshold limit for the old block to be added in the FREELIST. To understand better, let us assume that a block is of full data. Now the user starts deleting rows from the block. When a row is deleted, Oracle does not put the block into the FREELIST because it requires many recursive calls to update the FREELIST. The PCTUSED % (50) determines when the block should be added into FREELIST. When the total bytes in the block is less than or equal to 4039 bytes, then the block will be added into FREELIST.

    If a table has high inserts and high deletion, then you should decrease the PCTUSED value in order to minimize the frequent update of FREELIST.

    The best confidence booster is knowledge. Good Luck, guys.

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Correction in earlier post.
    The overhead for each block is 107 bytes, not 117 bytes.

    Tamilselvan

  6. #6
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    That was one damn good explanation. But on reading this thread, I'm getting more questions than that of answers.

    1. How do one get to check the over head for different block_sizes.

    2. What happens when one sets the PCTFREE to 80%


    Sorry for such a lame question, but was curious to know the answer, atleast to the first one.

    Thanx,
    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Oracle says the overhead for each block varies from 84 bytes to 107 bytes.
    I do not know how to cross check over head bytes for various block sizes.

    Does anybody know the answer?

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    if pctfree is high then basically we are wasting hard drive space since only 10% of block is used for inserts, of course if you consider that you will update the rows so often and fill the 80% up then it´s up to you but I think it´s pretty rare
    I say 10% but it can be less since pctfree+pctused cant be more than 100 and if we set pctused 20 and pctfree 80 most probably we will face perfomance issues because the block has to be put on freelist and taken off free list all the time.

    As for block overhead I think there is a formula in Oracle DBA Handbook

  9. #9
    Join Date
    Jan 2001
    Posts
    642
    To add on to the question pool,

    I understand that the pctused is used by oracle to put the block into the free list for future inserts.

    1) Will any new blocks(extends) added will directly put into the freelist ?

    2) I know that I have a table, in which I rarely have any updates and deletes but only insertions, then can I set the parameters for

    PCT FREE = 100
    and PCT USED=0?
    Badrinath

  10. #10
    Join Date
    Nov 2000
    Posts
    212
    badrinathn:
    on site [url]http://www.ixora.com.au/q+a/datablock.htm[/url] is good discussion of that issue. from my understanding, new blocks goes to so called master free list.

    pctused=0 and pctfree=100 would make sense for insert only table.

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