Ask Maclean

了解你所不知道的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';

Posted

in

by

mac

Tags:

Comments

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

  1. maclean Avatar
    maclean
    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.
    Reply
  2. 了解你所不知道的SMON功能系列文章汇总 | Ask Maclean Oracle Blog

    […] 了解你所不知道的SMON功能(二):合并空闲区间 了解你所不知道的SMON功能(三):清理obj$基表 […]

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *