Hi ,
I recently read some articles about a bug in ASSM where a insert can cause multiple 'db sequential reads' causing the insert to be slow. The following was the quote from metalink
This is the same situation as in Bug 7411960 "A SIMPLE INSERT CAUSES LOTS OF 'DB FILE SEQUENTIAL READ' WAITS" and it is the expected behavior. As per bug explanation, this is because there are multiple blocks with insufficient space to store the new data and Oracle needs to skip these blocks during the search for a good candidate. Oracle has been trying to balance the performance versus space management on ASSM segments. In this case, it seems Oracle inclined over the space management and this caused to have multiple blocks not marked as full, but not having enough space to accommodate new rows. So, Oracle needs to read multiple blocks in order to find one block with enough space to insert the new row.
This behavior also applies to higher versions like 11g.
The solution was
The workaround is to manually mark these blocks as full and to increase the PCTFREE to a higher value in order to promote blocks to be marked as full in a better rate.
What i want to understand is If a segment has PCTFREE values as 20 , assume the current space used in the block is 75% and a new row is inserted which would cause the free space to be reduced to 15% , will the row be inserted into the block and caquse it to be taken off the L1 bitmap list or will the block be taken off the L1 bitmap list without the row getting inserted ?
And how would the solution of increasing PCTFREE fix this issue?
Thanks