了解你所不知道的SMON功能(二):合并空闲区间

SMON的作用还包括合并空闲区间(coalesces free extent)

触发场景

早期Oracle采用DMT字典管理表空间,不同于今时今日的LMT本地管理方式,DMT下通过对FET$和UET$2张字典基表的递归操作来管理区间。SMON每5分钟(SMON wakes itself every 5 minutes and checks for tablespaces with default pctincrease != 0)会自发地去检查哪些默认存储参数pctincrease不等于0的字典管理表空间,注意这种清理工作是针对DMT的,而LMT则无需合并。SMON对这些DMT表空间上的连续相邻的空闲Extents实施coalesce操作以合并成一个更大的空闲Extent,这同时也意味着SMON需要维护FET$字典基表。

现象

以下查询可以检查数据库中空闲Extents的总数,如果这个总数在持续减少那么说明SMON正在coalesce free space:

SELECT COUNT(*) FROM DBA_FREE_SPACE;

在合并区间时SMON需要排他地(exclusive)持有ST(Space Transaction)队列锁, 其他会话可能因为得不到ST锁而等待超时出现ORA-01575错误。同时SMON可能在繁琐的coalesce操作中消耗100%的CPU。

如何禁止SMON合并空闲区间

可以通过设置诊断事件event=’10269 trace name context forever, level 10′来禁用SMON合并空闲区间(Don’t do coalesces of free space in SMON)

10269, 00000, "Don't do coalesces of free space in SMON"
// *Cause:    setting this event prevents SMON from doing free space coalesces

alter system set events '10269 trace name context forever, level 10';
  1. Keywords: SMON coalesce
    
    Note 298696.1: Fragmentation Issues Due to PCT_INCREASE Settings 
    
    The PCT_INCREASE value has an effect only when the database has Dictionary
    Managed Tablespaces. The locally managed tablespaces (which was
    introduced in 8i)  will not usually have these problems as Oracle
    automatically manages storage. Instead of using Dictionary tables, it
    uses bitmaps to keep track of free blocks that are available in various
    datafiles. Ideally, under LMT we should be using Uniform/System
    allocation policy, which results in Oracle automatically managing
    extent allocation. This will minimize the fragmentation problem. Under
    this, the PCT_INCREASE and other extent settings will not have much
    effect.However,when the tablespaces are migrated from DMT to LMT, they still contain
    the objects that violate the Uniform allocation policy. Hence, they are
    left with "User" value set as their Allocation_Type. Under "User"
    allocation policy, the PCT_INCREASE value DOES matter. Hence
    this article addresses instances with DMT and LMT that uses "User"
    allocation policy.
    
    The PCT_INCREASE value has an effect only when the database has Dictionary Managed Tablespaces. 
    Please reference Note 298696.1 for the details.
    
    Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents.
    
    Coalesce simply takes adjacent free extents and merge them into one larger free extent. 
    Coalesce applies ONLY to dictionary managed tablespaces as space is always automatically coalesced in locally managed.
    
    For your reference:
    Note 105120.1: Advantages of Using Locally Managed vs Dictionary Managed Tablespaces
    
    Oracle maintains a bitmap in each datafile to track used and free space availability in an LMT. 
    The initial blocks in the datafiles are allocated as File Space Bitmap blocks to maintain the extent allocation 
    information present in the datafile. Each bit stored in the bitmap corresponds to a block or a group of blocks. 
    Whenever the extents are allocated or freed, oracle changes the bitmap values to reflect the new status. 
    Such updates in the bitmap header do not generate any rollback information.
    LMTs avoid recursive space management operations and automatically track adjacent free space, 
    thus eliminating the need to coalesce free extents. This further reduces fragmentation.
    
    There is no question of SMON coalescing in LMTs.
    
  2. Pingback: 了解你所不知道的SMON功能系列文章汇总 | Ask Maclean Oracle Blog