关于ASSM和pctfree,pctused,freelists,freelist groups参数

Oracle9i在本地表空管理(LMT)的基础上，对段空间管理也引入了位图管理（Segment Space ManagementAuto）来取代原来的freelist管理方式（Segment Space Management Manual）。

 

  在手动段空间管理(MSSM)中，HWM之下的空闲block都在freelist上，当insert导致block的可用空间少于pctfree指定的值后，block将从freelist上摘下。Insert时只会从freelist上来查找可用块，所以摘下的block将不能再被用于insert。那么当block中的记录被delete，出现大量可用空间的时候，就需要将block再次放到freelist上，(---这是老的段管理方式，采用的是freelist--空闲队列，采用这种方式，pctused和pctfree都需要设置值.)

pctused参数就是指：The PCTUSED parameter sets theminimum percentage of a block that can be used for row data plusoverhead before new rows are added to the block. (下有)

 

 

In this case, a data block used for this table's datasegment is considered unavailable for the insertion of any new rowsuntil the amount of used space in the block falls to 39% or less(assuming that the block's used space has previously reachedPCTFREE).

当数据块的使用空间小于PCTUSER的时候，就可以将block重新放回freelist上的。

 

  而在自动段空间管理(ASSM)中，使用了位图来追踪block的空间使用情况，insert通过扫描位图来查找可用的block，即使block的可用空间低于pctfree，也不会从位图中摘除，也就不需要一个pctused参数来指示什么时候将block放回位图中了，所以pctused参数在ASSM中不再需要。而对于pctfree参数，依旧需要它来指示需要保留多少空间给后续的update导致的行数据增长使用。如果没有pctfree，block在insert时将可以使用所有的可用空间，当update导致行数据增长时，就会发生行迁移。freelists和freelistgroups参数是用来控制freelist的个数，在ASSM中自然无用武之地了。(---在新的段管理方式下，即位图管理。那么pctused不需要这参量了，但需要参量pctfree来限定行更新后产生的空间增加，避免不发生行迁移)

 

官方地址：http://docs.oracle.com/cd/B19306_01/server.102/b14220/logical.htm#sthref344

 

The PCTFREE Parameter

The PCTFREE parameter sets the minimumpercentage of a data block to bereserved as freespace for possible updates to rows that already exist in thatblock. For example, assume that you specify the following parameterwithin aCREATE TABLE statement:

PCTFREE 20 

This states that 20% of each data block in this table's datasegment be kept free and available for possible updates to theexisting rows already within each block. New rows can be added tothe row data area, and corresponding information can be added tothe variable portions of the overhead area, until the row data andoverhead total 80% of the total block size.Figure 2-3illustrates PCTFREE.

Figure 2-3 PCTFREE

Description of "Figure 2-3PCTFREE"

The PCTUSED Parameter

The PCTUSED parameter sets theminimum percentage of a block that can be used for row data plusoverhead before new rows are added to the block. After adata block is filled to the limit determined byPCTFREE, Oracle considers the block unavailable forthe insertion of new rows until the percentage of that block fallsbeneath the parameterPCTUSED. Until this value isachieved, Oracle uses the free space of the data block only forupdates to rows already contained in the data block. For example,assume that you specify the following parameter in aCREATETABLE statement:

PCTUSED 40 

In this case, a data block used for this table's datasegment is considered unavailable for the insertion of any new rowsuntil the amount of used space in the block falls to 39% or less(assuming that the block's used space has previously reachedPCTFREE).

 

 

 Figure2-4 illustrates this.

Figure 2-4 PCTUSED

 

 

How PCTFREE and PCTUSED Work Together

PCTFREE and PCTUSED worktogether to optimize the use of space in the data blocks of theextents within a data segment.Figure 2-5 illustrates the interaction of these twoparameters.

Figure 2-5 Maintaining the Free Space of DataBlocks with PCTFREE and PCTUSED

Description of "Figure 2-5Maintaining the Free Space of Data Blocks with PCTFREE andPCTUSED"

In a newly allocated datablock, the space available for inserts is the block size minus thesum of the block overhead and free space (PCTFREE).Updates to existing data can use any available space in the block.Therefore, updates can reduce the available space of a block toless than PCTFREE, the space reserved for updates butnot accessible to inserts.

For each data and index segment,Oracle maintains one or morefreelists—lists of data blocks that have beenallocated for that segment's extents and have free space greaterthanPCTFREE. These blocks are available for inserts.When you issue an INSERT statement, Oracle checks afree list of the table for the first available data block and usesit if possible. If the free space in that block is not large enoughto accommodate theINSERT statement, and the block isat least PCTUSED, then Oracle takes the block off thefree list. Multiple free lists for each segment can reducecontention for free lists when concurrent inserts take place.

After you issue a DELETE or UPDATEstatement, Oracle processes the statement and checks to see if thespace being used in the block is now less thanPCTUSED. If it is, then the block goes to thebeginning of the transaction free list, and it is the first of theavailable blocks to be used in that transaction. When thetransaction commits, free space in the block becomes available forother transactions.