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

huangtingzhong 发布于 2014-10-22 15:12EVENT, 调优抢沙发

一个朋友在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)

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**	11.2.0.3 11.2.0.2
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:

The fix for 13063120 is first included in

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

Symptoms:	Related To:
Performance Of Certain Operations Affected Waits for “db file sequential read” Waits for “cell single block physical read” Waits for “latch: cache buffers chains” Waits for “buffer busy waits”	ASSM Space Management (Bitmap Managed Segments) Compressed Data Storage Chained or Migrated Rows INSERT

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 tableso 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

cbc compress insert latch:cache buffer chain latch:cbc

赏