首页 > Linux操作系统 > Linux操作系统 > 关于HW-contention等待的处理


原创 Linux操作系统 作者:物理狂人 时间:2012-05-19 09:53:15 0 删除 编辑
在大量并发插入时,发生busy buffer wait 和HW-contention


The HW enqueue is used to serialize the allocation of space beyond the high water mark of a segment.
  • V$SESSION_WAIT.P2 / V$LOCK.ID1 is the tablespace number.

  • V$SESSION_WAIT.P3 / V$LOCK.ID2 is the relative data block address (dba) of segment header of the object for which space is being allocated.

If this is a point of contention for an object, then manual allocation of extents solves the problem.


但为什么手动分配extent就可以解决问题呢?extent和high water mark有什么关系呢?

首先是关于MSSM和ASSM下的high water mark资料

MSSM uses free lists to manage segment space. At table creation, no blocks in the segment are formatted. When a session first inserts rows into the table, the database searches the free list for usable blocks. If the database finds no usable blocks, then it preformats a group of blocks, places them on the free list, and begins inserting data into the blocks. In MSSM, a full table scan reads all blocks below the HWM.

ASSM does not use free lists and so must manage space differently. When a session first inserts data into a table, the database formats a single bitmap block instead of preformatting a group of blocks as in MSSM. The bitmap tracks the state of blocks in the segment, taking the place of the free list. The database uses the bitmap to find free blocks and then formats each block before filling it with data. ASSM spread out inserts among blocks to avoid concurrency issues.


1)首先HWM的位置是存储在段头块,并且HWM的提升会增加Freelist中的free blocks,而freelist中的块数的纪录,也存储在段头(当然如果freelist group=n>1,那么存储在段头后面的n个块中~),所以这个时候要修改的是段头块(以及freelist 所在的块),还有一个地方要修改,就是原来freelist中最后一个block,这个里面记录了freelist单项列表的最后一个地址,要把他修改,指向新加入freelist的块的地址~还要在所有新加入freelist的块上建立freelist单项链表~ 可见提高HWM是需要修改很多块的~

但是如果新加入一个extent呢?对于字典管理表空间,需要修改数据字典等待为ST enqueue(印象中)~ 而本地管理表空间,仅仅需要修改以下数据文件头上的位图~
这个工作量是非常小的~ 我不认为分配extent会造成多大的等待~ 当然就算有等待也不是hw enqueue

2)ASSM high-HWM extensions are not done over extent boundaries IIRC, thus with small extents you'd have lots of extensions to do, each requiring HW-enqueue get. ASSM relieves HW-enqueue contention by increasing HHWM in large sizes (depending on extent and current segment size), so less HW operations have to be done.

The LHWM extensions can be done without having HW-enqueue as they involve only changing few records in a BMB block and the BMB is pinned for that exclusively anyway. The actual batch formatting of datablocks is done while having FB enqueue, allowing finer granularity of locking block ranges in case of multiple parallel inserts.


解决方法:对于经常大并发插入的表,在创建表时将next设置的大点。对于偶尔大并发插入的表,则可以在通过手动allocate extent 的方式解决 

来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录


  • 博文量
  • 访问量