当前位置: 首页 > EVENT, 调优 > 正文

11G INSERT语句出现latch:cache buffer chain等待事件

一个朋友在INSERT的时候,每次INSERT就会出现latch:cache buffer chain等待事件,并且最多只有几个进程同时INSERT操作,最后通过搜索MOS发现是由于BUG导致的,将表更改为非压缩表就可解决问题。

Bug 13063120 – Inserts of chained rows to COMPRESSed table do unnecessary single block reads (文档 ID 13063120.8)

 

clip_image001[4]

Bug 13063120  Inserts of chained rows to COMPRESSed table do unnecessary single block reads

This note gives a brief overview of bug 13063120.

The content was last updated on: 23-JUL-2013

 Click here for details of each of the sections below.

Affects:

 

Product (Component)

Oracle Server (Rdbms)

Range of versions believed to be affected

Versions >= 11.2 but BELOW 12.1

Versions confirmed as being affected

Platforms affected

Generic (all / most platforms affected)

It is believed to be a regression in default behaviour thus:

   Regression introduced in 11.2.0.1

Fixed:

 

Interim patches may be available for earlier versions – click here to check.

Description

Excessive buffer gets and/or single block reads may be seen when inserting a rowinto a compressed table where the row needs to be chained across multiple blocks in an ASSM tablespace.
 
Rediscovery Notes

  Two factors are needed to reproduce this problem :
 

 1) The target table is OLTP compressed
  2) The inserted row is chained.
      Chaining can happen for any of the following reasons :
      – Very long row pieces, such as can occur with lengthy LONG or LONG RAW columns, or even just many long VARCHAR2 or similar columns
      – Chaining also occurs implicitly(肯定的) for rows > 255 columns, regardless of their length.
  The symptoms can show as hiigh “buffer gets” values (eg: in AWR reports) along with high
  buffer cache related waits such as:
    ‘latch: cache buffers chains’     ‘buffer busy waits’     ‘cell single block physical read’     ‘db file sequential read’     etc…

 
Workaround

   Typically chained rows do not gain much from using a compressed table
so if this is common it may be worth considering using an uncompressed table.For LONG / LONG RAW consider migrating to CLOB / BLOB.
 

 

Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support.

References

Bug:13063120 (This link will only work for PUBLISHED bugs)

Note:245840.1 Information on the sections in this article

本文固定链接: http://www.htz.pw/2014/10/22/11g-insert%e8%af%ad%e5%8f%a5%e5%87%ba%e7%8e%b0latchcache-buffer-chain%e7%ad%89%e5%be%85%e4%ba%8b%e4%bb%b6.html | 认真就输

该日志由 huangtingzhong 于2014年10月22日发表在 EVENT, 调优 分类下, 通告目前不可用,你可以至底部留下评论。
原创文章转载请注明: 11G INSERT语句出现latch:cache buffer chain等待事件 | 认真就输
关键字: , , , ,