In this Document
  Symptoms
  Cause
  Solution
  References

Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.7.4 and later [Release: 8.1.7 and later ]
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 8.1.7.4

Symptoms

Performance is bad due to high version count. V$SQL_SHARED_CURSOR shows new child cursors for the parent one due to BIND_MISMATCH.

Cause

This is due to the bind buffer mismatch of the current child cursor. If oracle is unable to bind the current value to the existing child cursors bind buffer, oracle upgrades the existing child cursor with a high bind buffer. This will force the query to
do a hard parse and a new child cursor will be created.

The previous one will be marked 'dont use'.

These types of cursors are never re-used. As a result, they permanently occupy a slot in the child table which will result in increasing the version count.

This issue has been described in the Bug: 2450264 base bug to Bug: 3012881

Solution

The fix of the bug 2450264 has introduced a new event (10503) which enables users to specify a character bind buffer length. Depending on the length used, the character binds in the child cursor can all be created using the same bind length;

ALTER SESSION SET EVENTS '10503 trace name context forever, level ';

Eg:
ALTER SESSION SET EVENTS '10503 trace name context forever, level 4096';

References

BUG:3012881 - HIGH VERSION COUNT/INVALIDATION CAUSE BAD PERFORMANCE

ITPub博客

High Version Count Due To BIND_MISMATCH [ID 336268.1]

Applies to:

Symptoms

Cause

Solution

References

相关内容

产品

最新文章