【转】Oracle Freelist和HWM原理及性能优化

 文章转自：http://www.wzsky.net/html/Program/DataBase/74799.html

nfl = 3, nfb = 1 typ = 1 nxf = 0 
    SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000 
    SEG LST:: flg: USED lhd: 0x03c00233 ltl: 0x03c00233 
    SEG LST:: flg: USED lhd: 0x03c00234 ltl: 0x03c00234 
    SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000 

    Segment Header: 
    ==> nfl: number of free lists/block 
    ==> nfb: number of free list blocks + segment header 
    ==> typ: block type 
    ==> nxf: number of transaction free lists 
    Segment List: 
    ==> flg: flag USED or UNUSED the free list 
    ==> lhd: head of free list 
    ==> ltl: tail of free list

Block header dump: 0x03c00235 
    Object id on Block? Y 
    seg/obj: 0xe2d8 csc: 0x00.6264c61 itc: 1 flg: O typ: 1 - DATA 
    fsl: 1 fnx: 0x3c00234 ver: 0x01 

    ==> Seg/obj Object ID in dictionary 
    ==> csc SCN of last block cleanout 
    ==> itc Number of ITL slots 
    ==> flg O = On freelist , - = Not on freelist 
    ==> typ 1 = DATA 2 = INDEX 
    ==> fsl ITL TX freelist slot 
    ==> fnx DBA of NEXT block on freelist

　　select list entry = (PID % NFL) + 1

　　segment header block中与HWM相关信息说明如下:     EXTENT CONTROL: 
    Extent Header:: spare1: 0 space2: 0 #extents: 13 #blocks: 1429 
    last map 0x00000000 #maps: 0 offset: 4128 
    Highwater:: 0x020004d0 ext#: 12 blk#: 275 ext size: 475 
    #blocks in seg. hdr’s freelists: 5 
    #blocks below: 1229 
    mapblk 0x00000000 offset: 12 
    Unlocked 
    ==> spare1: this field is no longer used (old inc#, now always 0) 
    ==> space2: this field is no longer used (old ts#, now always 0) 
    ==> #extents: number of extents allocated to segment 
    ==> #blocks: number of blocks allocated to segment 

    ==> last map: address of last extent map block 
    0 if extent map is entirely in the segment header 
    ==> #maps: number of extent map block 
    ==> offset: offset to end of extent map 

    ==> HWM dba: address of block at highwater mark 
    ==> ext#: HWM extent number relative to segment 
    ==> blk#: HWM block number within extent 
    ==> ext size: HWM extent size (in blocks) 
    ==> #blocks in seg. hdr’s freelists: number of blocks in seg. hdr’s free list 
    ==> #blocks below: number of blocks below HWM 
    ==> mapblk dba: dba of extent map block containing HWM extent 
    is 0 if HWM is in the segment header 
    ==> offset: offset within extent map block 
    is the ext# if HWM is in segment header 
    ==> Locked by: if locked by a transaction, the xid is displayed

show_space   SQL>
    create or replace procedure show_space
    ( p_segname in varchar2,
    p_owner in varchar2 default user,
    p_type in varchar2 default ‘TABLE‘,
    p_partition in varchar2 default NULL )
    as
    l_free_blks number;
    l_total_blocks number;
    l_total_bytes number;
    l_unused_blocks number;
    l_unused_bytes number;
    l_LastUsedExtFileId number;
    l_LastUsedExtBlockId number;
    l_last_used_block number;
    procedure p( p_label in varchar2, p_num in number )
    is
    begin
    dbms_output.put_line( rpad(p_label,40,‘.‘) || p_num );
    end;
    begin
    dbms_space.free_blocks
    ( segment_owner => p_owner,
    segment_name => p_segname,
    segment_type => p_type,
    partition_name => p_partition,
    freelist_group_id => 0,
    free_blks => l_free_blks );
    dbms_space.unused_space
    ( segment_owner => p_owner,
    segment_name => p_segname,
    segment_type => p_type,
    partition_name => p_partition,
    total_blocks => l_total_blocks,
    total_bytes => l_total_bytes,
    unused_blocks => l_unused_blocks,
    unused_bytes => l_unused_bytes,
    last_used_extent_file_id => l_LastUsedExtFileId,
    last_used_extent_block_id => l_LastUsedExtBlockId,
    last_used_block => l_last_used_block );
    p( ‘Free Blocks‘, l_free_blks );
    p( ‘Total Blocks‘, l_total_blocks );
    p( ‘Total Bytes‘, l_total_bytes );
    p( ‘Unused Blocks‘, l_unused_blocks );
    p( ‘Unused Bytes‘, l_unused_bytes );
    p( ‘Last Used Ext FileId‘, l_LastUsedExtFileId );
    p( ‘Last Used Ext BlockId‘, l_LastUsedExtBlockId );
    p( ‘Last Used Block‘, l_last_used_block );
    end;

Oracle7:
SELECT count(distinct substr(rowid, 15,4) || substr(rowid, 1,8) ) FROM schema.table;
Oracle8 and Oracle9:
SELECT count(distinct substr(rowid, 7,3) || substr(rowid, 10,6) ) FROM schema.table;

alter table t1 deallocate unused;

alter table t1 deallocate unused keep 10K;

4.2删减表

根据3.2.1可以得到HWM以下块的使用情况。如何p大于时，对全表扫描性能会产生影响，同时也会耗用空间。

如果能够确认应用有良好的索引几乎不会用到全表扫描，那么HIGH WATER MARK以下的空块，尽管耗费了空间，不会对访问产生影响。如果不能确定，那么就需要考虑删减表。

删减表的操作将删除表中所有的记录，并且重置HWM标记。表在删减之后将成为一个空表。

在Oracle中删减表只有如下的两种办法:

1.使用drop语句

先使用drop语句删除整个表，然后再重建这个表。在删除-重建的过程中，与表相关的所有索引、完整性约束以及触发器都会丢失，并且所有依赖于该表的对象都会变为INVALID状态，同时原来争对表的授权也会失效。因此采用这种方式删除表中的记录代价太大。

2.使用TRUNCATE语句

TRUNCATE语句属于DDL语句，不会产生任何回退信息，并且被立即自动提交。在执行TRUNCATE语句时不会影响到与被删减表相关的任何数据库对象与授权，也不会触发表中所定义的触发器。此外，在对标进行删减时，HWM将重置，已经为表分配的存储空间将被回收。

在执行TRUNCATE语句时，可以通过drop storage子句和reuse storage子句来控制被释放的区是否回收到表空间中。如何作在线系统的TRUNCATE，不希望表长时间锁住，那么可以使用reuse storage子句，仅将HWM重置。

4.3 free list优化

free list 竞争出现在多个进程使用同一个free list并试图同时修改free list头部数据块时。可以通过查询视图v$waitsate的class类型为data block 的记录来检查竞争情况。

产生data block类型竞争的主要原因是多个进程试图同时修改free list头部数据块。 然而，它也会出现在当进程准备将块读入buffer cathe时，另一个进程需要访问同一个块。如果能在V$SESSION_WAIT中正好捕获buffer busy waits，就可以通过查询V$SESSION_WAIT中的P3来判定是那一类。A 0 或 1014代表读类型，其他的值为修改竞争的类型。

下一步需要确定竞争涉及那些段。 如果能够在V$SESSION_WAIT捕获waits，就可以用P1和P2的值 (对应file 和 block) 在DBA_EXTENTS中找到段名。 如何是一个表，就很可能需要重建表来创建更多的process freelists。 一种计算需要创建多少个freelist的方法是dump一些段中接近HWM的块，检查interested transaction list的个数，具体方法可参见3.1。interested transactions个数的峰值加1 就是需要的最小process freelists的值。

从2.3和2.4可以看出，使用多个free list可能导致更多的空块未被使用， 也可能导致段更快地扩展。如果性能是当前所关心的重点，那么多free lists 可以用来提高并发访问能力，当然会增加一些额外空间的耗用。然而，如果空间使用大小是首先考虑的因素，那么推荐使用single freelist，使参数FREELISTS=1, 当然就不能提升并发事务的性能了。

V$WAITSTAT 也可显示其他类型class的竞争，包括segment header 和free list。 出现在同一个free list group中多个事务需要同时更新它们的free list header记录时。 有多种方法来解决这个问题如重建表采用更多的free list groups,或者增加 _bump_highwater_mark_count大小，或者调整应用本身。

【转】Oracle Freelist和HWM原理及性能优化,布布扣,bubuko.com