首页 » ORACLE 9i-23c » Troubleshooting Oracle 11.2.0.4 高并发insert等待 ‘enq: HW – contention’后分配大量空extent

Troubleshooting Oracle 11.2.0.4 高并发insert等待 ‘enq: HW – contention’后分配大量空extent

  • 2023/03/10
  • ORACLE 9i-23c
  • 132 views
  • Troubleshooting Oracle 11.2.0.4 高并发insert等待 ‘enq: HW – contention’后分配大量空extent已关闭评论

环境11.2.0.4 RAC，表空间耗尽大量insert并发等待enq: HW, 增加30数据文件后，但很快空间再次接近耗尽，后分析增长最大的segment时发现了新增加的数据文件被insert的segment分配大量extent，但都是unused block，存在重复提升High water Mark现象，通常extent是用完后再allocate new extent，这种大量创建空extent的现象属于软件bug.

查找变量最大的segment为以1 day为粒度的interval partition，再分析记录数行数同前一天几乎持平，但segment size确实增加了近30GB，也就是enq HW 过程增加的new datafile空间，从dba_extent  map可以确认分配extent 位置很连贯。

通过DBMS_SPACE.SPACE_USAGE分析SEGMENT的空间使用情况

XXX.XXXXXX SYS_P26465
0% -- 25% free spaceblocks..............177
0% -- 25% free spacebytes...............2899968
25% -- 50% free spaceblocks.............346
25% -- 50% free spacebytes..............5668864
50% -- 75% free spaceblocks.............527
50% -- 75% free spacebytes..............8634368
75% -- 100% free spaceblocks............63672
75% -- 100% free spacebytes.............1043202048
Unused Blocks...........................1679983
Unused Bytes............................27524841472
Total Blocks............................693236
Total bytes.............................11357978624

Unused Bytes=>unformatted_bytes

分析MOS中发现 匹配 Bug 18221857 : BMB CONTENTION ON LARGE SEGMENTS WITH LOTS OF EXTENTS AND HIGH INSERT RATE

The hwm movement is primarily done in ktspbmphwm().
ktspbmphwm() overall does the following:
Phase 1: Add extent
Phase 2: Bump HWM in seghdr

 

Phase 1:
– Acquire HW enqueue – this serializes “extent allocation” and “HWM movement in seghdr”.
– Check if extent requires to be allocated. If the HWM is not at the end the segment, then there is room to move HWM, so we do not add extent, we go to the next step of moving HWM in seghdr.
– If hwm is already at seg end, then we compute “up”(-by how much should the hwm be adjusted). For larger segments, up can be aggressive – upto 64MB or 10% of segment size whichever is larger.
Once the “up” is computed, we check to see if some other process advanced the HWM by more than 64MB or 10% segment size by comparing “oldblocks under HWM” and “new blocks under HWM”. If some other process did advance by more than the threshold, then we do not add extent. We quietly return…
– Extent is added
– HW enqueue is released
– Since the HW enqueue is released we repeat the process above and then check to see if “HWM in seghdr” should be moved.

Phase 2:
– Acquire HW enqueue
– Bump up the HWM by “up” blocks.
– Release HW enqueue

Consider the following case:
p1,p2,p3 all wait on “hw enqueue” to “bump up the HWM” (to say, all are in their Phase 2)

1. P1 acquires hw enqueue in phase 1
2. p1 adds an extent
3. p1 releases hw enqueue
4. p2.. pn bumps up the hwm to the segment end
5. p1 re-acquires the hwm enqueue in phase 1 (notice the goto retry at the end of phase 1)
6. p1 notices that the hwm is already moved to the segment end
7. p1 adds an extent again.
修复这个bug 就是记住P1 已经add extent的位置。

打赏

,

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：