在另外一篇文章中,大师指出:
oracle对于assm中块的使用率标志有四种:
0---25,25---50,50----75,75--100
所以联合pctfree,在做判断的时候是否会出现问题,就是使得实质上的 pctused 显得过高,假设pctfree = 10 ,则pctused 实质上成为了 75,pctfree = 26则 pctused实质上成为了 50 ?
Automatic Segment Space Management
This is the concept of locally managed segments - must be set at tablespace level and in LMTs only. Each extent in the segment has a bitmap block containing a set of bits to represent the 'fullness' of each block in the extent. For large extents, a hierarchy of bitmap blocks is required.
create tablespace ...
segment space management auto;
Each block in the extent can be either 0-25% full, 25-50% full, 50-75% full or 75-100% full and the bits will be set accordingly. An additional bit indicates whether or not pctfree has been exceeded. Eg. For a pctfree of 10%, a block would be considered to have free space unless the bit representing 75-100% had been set. If the block utilisation dropped below 75% full, the bit for the block would be cleared and the block could then be considered for row insertion.
This introduces a complication in that each extent now has it's own high water mark. Forunately, free blocks above this extent high water mark (now known as unformatted) are not read when the table is full table scanned.
Some observations about ASSM:
Heavy, concurrent inserts will spray rows across extents and could even distribute rows randomly within an extent. This will have a significant impact on the clustering factor for any indexes created on the table.
Full table scans read the extent bitmap to determine which blocks in the extent are unformatted (ie. above the extent high water mark).
What would happen if adjusting pctfree would cause a block to cross a 'fullness' boundary? The bitmaps would not represent the blocks accurately. Fortunately, DBMS_REPAIR now includes a SEGMENT_FIX_STATUS procedure to account for this. You would need to remember to run this procedure if you adjust pctfree for any segment in an ASSM tablespace.
On a personal note, I fail to see any advantage of ASSM. It cannot benefit concurrency (and therefore scalability) and depending on the number of extents and the pctfree setting, you could end up wasting space at best and seriously damaging performance at worst (as indexes become less viable). To me, it seems that Oracle is attempting to 'dumb down' what is an extremely important and fundamental aspect of data storage. What was wrong with freelists anyway? The 'less performance in exchange for low maintenance' trade-off doesn't work because of the issue with changing pctfree.
|