1. Tow modes of Segment Space Management
a. Automatic — ASSM
b. Manual — MSSM
We only care about ASSM here.
2.High-water Mark
2.1 What is it for ?
Question: I’ve deleted all the data from the table by using "delete from …". Why does "select count(*) from …" take a long time?
Answer: There is a High-water Mark (HWM) in each table.
HWM rises as rows are added. But it doesn’t fall as rows are deleted, and the blocks are still there though empty.
So, even if the delete all the data, a full-table access like "select count(*)" will still acess all the blocks.
2.2 How does ASSM deal with it?
Some blocks under HWM represent some data, some don’t under, such as deleted rows. If we can decide which blocks are empty-blocks and which are not, then performance will be better.
There will be a Low HWM in in ASSM under which all rows are "real blocks" which should be accessed.
3. Row Migration & PCTFREE
3.1 Why migrat a row?
a. If a row’s size is going to increased so much that current block doesn’t have enough space for it, then the row has to be migrated to another row, in a whole or partically.
b. However, in the original block, there will be a pointer to the new block.
3.2 Anything bad about Row Migration?
A Row spans blocks => Caching more than one block => Performance Issues
3.3 How to deal with problem?
Let the block always have a faily enough free space by setting PCTFREE, such as 40%
If the size of row may be increased significantly => Set large PCTFREE
Otherwise => Set small PCTFREE
4. INITTRANS
INITTRANS = INITTRANS of a block
The number of transactions allowed on the same block. There will be waiting if this value is too low.