Difference in LMT and DMT

Difference in LMT and DMT

LMT (locally managed tablespaces) can use ASSM (automatic segment space management) - free space is managed in bitmap blocks in the extent itself, the bitmap blocks are intermingled with your data in the extent. LMT can use MSSM (manual segment space management) where free space is managed in freelists in the extent header - outside of the extent itself - not intermingled with your data.

With ASSM you set pctfree and not pctused, not freelists, not freelist groups.

With MSSM you set pctfree AND pctused AND freelists AND freelist groups (and if you do not, you suffer the concurrency penalty from not having enough freelist/freelist groups and inefficient space utilization by not setting pctused appropriately.

DMT (dictionary managed tablespaces) only do MSSM.

That is all - when we insert (assuming manual segment space management first) - we need to find a block with sufficient freespace on it. That block is found by walking the freelists (extent header information). The freelists may or may not be in the buffer cache, if they are not, we have to read them in. When we do - we may find that the block we want to insert into (block 55 in file 42 - the freelist told us that is our block) may or may not be in the buffer cache - if it is not, we read it in. Then we have to maintain the indexes - we may or may not have to read them into the buffer cache and so on.

assuming automatic segment space management - we have to read the bitmap blocks in the extent themselves - these may or may not be in the buffer cache - if they are not, we NEED to read them in. We read them and then we find the block we'd like to insert into - and if it is not in the cache - we have to read that in. And then the indexes.

We don't just find a block willy nilly by accident in the buffer cache that is associated with the table and has free space on it - we walk freelists or bitmaps to find a block and then insert into that block.