Tuesday, December 14, 2010

PCTFREE Vs PCTUSED

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


Q: Is free list this pre-allocated ? Is this only use for PCTFREE ? Where does it store ?
Can I set it to 0 ? or 100 ?


This is an interesting question. I would like to share my experience.

In the last 3 months, I had interviewed 10 expert Oracle DBAs and asked the same question. Only one person answered correctly.


First I would like to bring the very simple definition and than further i would explain in more details :

********************

PCTFREE: 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.

Note : For Update only.



PCTUSED :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 for insert.
This parameter has a default value of 40 percent.

Note : For Insert only.



*******************


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.



************

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 107 bytes for the header. Please note that the header size varies depending upon the block size.

The total available bytes for data = ( 8196 – 107) = 8089 bytes.

A table is created with PCTFREE 20 PCTUSED 50 .

PCTRFREE in bytes = 1615
PCTUSED in bytes = 4039

Now the data available for insert only = (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.



Note :

1 >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.



2> 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.


3>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.


4> We can not set PCTFREE =100 or PCTUSED=100 ,The value 100 is invalid value.
Also sum of PCTUSED and PCTFREE can not exceed 100.

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

No comments:

Post a Comment