more clarification
amit poddar, March 02, 2005 - 10:58 am UTC
When we update a row, its possible that it is not able to fit on the block it is on because of following:
1. Becuase of existing data on the block and very high pctfree.
2. Because the updated row itself is too big to fit in the block. i.e the row would not fit in the block even if the block was empty.
In the first case we would have a migrated row and in the second case we would end up with a chained row.
Is my reasoning correct ?
March 02, 2005 - 11:17 am UTC
1) just because of existing data, pctfree doesn't come into play. pctfree is used for INSERTS, not updates
2) that would be a truly chained row as opposed to a migrated row.
confusion
amit poddar, March 02, 2005 - 11:26 am UTC
I though pctfree was used for updates not inserts.
when we have pctfree has 90 than means 10% of the block space is reserved for updates
so that would imply that if we have very high pctfree nearing 100 we would not have any space for updates
Please clarify
March 02, 2005 - 11:37 am UTC
pctfree is used for inserts,
pctused for updates/deletes
pctfree says when to take the block OFF of the freelist, make it not available for future inserts.
pctused says when to put it back ON the freelist, make it avaialable for inserts again.
if you have a very high pctfree, you would have an incredible amount of space for updates -- pctfree is there to preserve space for FUTURE UPDATES, that is what it does.
do you have "Expert one on one Oracle"? I write about this in the chapter on tables extensively.
confusion
amit poddar, March 02, 2005 - 11:46 am UTC
Hi,
I think we are talking in two different terms
When I sais High pctfree I meant very close to 100
You are defining high pctfree as around 40-50%
Correct ?
March 02, 2005 - 12:29 pm UTC
no, high pctfree means "high pctfree", big number, high.
pctfree is consulted during inserts.
pctused is consulted during updates and deletes.
excellent
aman, March 03, 2005 - 12:27 pm UTC
sir
this is excellent information and explanation.I read the 2nd thread too(I am still reading it and have somethings which are not yet into my THICK head but will understand soon).
Please clarify this what you mentioned sir
<Quote>
(1)pctfree is consulted during inserts.
(2)pctused is consulted during updates and deletes.
</Quote>
(1)PCTFREE is consultated during inserts.Does this mean that whenever Oracle will insert into any block,first it will veryify the value of PCTFREE,to make it sure that the total used space(100-PCTFREE) is not consumed,in other words,block is out of FREELIST(not available for inserts)?am i understanding it right?please clarify.
(2)Really didnt get this that how PCTUSED is consultated in updates and deletes?Whenever we will update,wont be checking the PCTFREE for the available space reserved for the Updates?and what delete has to do with the PCTUSED?i understood this only that delete will make the block come back on the FREELIST as the used space will come lesser than the PCTUSED?But certainly i am not sure about UPDATE<->PCTUSED?Please clarify this sir.
with best regards
aman
March 03, 2005 - 12:32 pm UTC
<quote src=expert one on one Oracle>
These two settings control when blocks will be put on and taken off the FREELISTS. When used with a table (but not an index organized table as we'll see) - PCTFREE tells Oracle how much space should be reserved on a block for future updates. By default, this is 10%. What that means is that if we use an 8k block size, as soon as the addition of a new row onto a block would cause the free space on the block to drop below about 800 bytes, Oracle will use a new block instead of the existing block. This 10% of the data space on the block is set aside for updates to the rows on that block. If we were to update them - the block would still be able to hold the updated row (hopefull).
Now, whereas PCTFREE tells Oracle when to take a block off of the FREELIST making it no longer a candidate for insertion, PCTUSED tells Oracle when to put a block ON the FREELIST again. If the PCTUSED is set to 40 (the default) and the block hit the PCTFREE level (it is not on the FREELIST currently) then 61% of the block must be free space before Oracle will put the block back on the FREELIST. If we are using the default values for PCTFREE (10) and PCTUSED (40) then a block will remain on the FREELIST until it is 90% full (10% free space). Once it hits 90%, it will be taken off of the FREELIST and remain off of the FREELIST, until the free space on the block exceeds 60% of the block.
</quote>
Very Clear
Henry Liang, April 28, 2005 - 4:21 pm UTC
Thanks for your efforts