PCTFREE Vs PCTUSED

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.

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.

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 [email protected]

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.

Correction in earlier post.
The overhead for each block is 107 bytes, not 117 bytes.

Tamilselvan

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

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?

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

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

badrinathn:
on site http://www.ixora.com.au/q+a/datablock.htm 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.

shouldn't pctfree be more like 'Zero' for insert-only table ?

pctfree = 100 means nothing can be inserted !

Hey, Thats correct,

In that case , for a insert only tables it means
pctfree = 0
and
pctused = 0

Advisors, what do you say?

When PCTFREE is set to 0, then every update on a row requires a row migration.

When PCTUSED is set to 0 , then after deleting all rows in the block, it will be added into FREELIST.

All,I just read this thread and i see very useful information. I just want to share my thoughts for PCTUSED Vs PCTFREE for insert only tables.

PCTFREE = 0 AND PCTUSED value does matter for insert only tables. PCTUSED will not play the role as long as we are not deleting any data in the block. Another point here is, we can not set PCTUSED=100 or PCTFREE = 100. The value 100 is invalid value. Also sum of PCTUSED and PCTFREE can not exceed 100. Pease reply if any one is not agreeing. Thanks

SQL> create table test
2 (no number)
3 pctused 0
4 pctfree 100;
pctfree 100
*
ERROR at line 4:
ORA-02211: invalid value for PCTFREE or PCTUSED


SQL> create table test(no number)
2 pctused 60
3 pctfree 41;
create table test(no number)
*
ERROR at line 1:
ORA-04000: the sum of PCTUSED and PCTFREE cannot exceed 100

its 6 years old for gods sake