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 ?
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
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.
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
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.
Reply With Quote
re: pctused vs pctfree
Bookmarks