Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

High version count for INSERTS due to HASH_MATCH_FAILED with cursor_sharing = force

Vsevolod AfanassievMay 14 2016 — edited May 15 2016

12.1.0.2 on Linux, standalone instance

We are testing new -3-rd party application, it isn't using bind variables. During the first test cursor_sharing was set to default value EXACT, the rate of hard parses reaches 800 per second, and we saw events indicating issues with shared pool (library cache: mutex X, latch: shared pool, cursor: pin S wait on X). However the percentage of these waits was small, less than 3% of total waits.

During the second test we set cursor_sharing to FORCE. Waits on shared pool-related events were high:

37% of total waits for 'cursor: pin S wait on X'

21% for 'library cache lock'

V$SQL shows several statements with hundreds of child cursors, mostly single-row INSERTS:

INSERT INTO schema.tableName (column1,columns2,..)

VALUES (:"SYS_B_0",:"SYS_B_1",:"SYS_B_2",:"SYS_B_3",:"SYS_B_4",:"SYS_B_5",:"SYS_B_6",timestamp:"SYS_B_7",SYSTIMESTAMP,:"SYS_B_8")

These cursors aren't being shared, every execution created new child cursor.


V$SQL_SHARED_CURSOR has 'Y' for HASH_MATCH_FAILED.This is the definition: No existing child cursors have the unsafe literal bind hash values required by the current cursor.

What does it mean?

There is nothing special about these tables, they have VARCHAR2, NUMBER, and DATE columns, no triggers.


This post has been answered by Jonathan Lewis on May 14 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 12 2016
Added on May 14 2016
9 comments
1,955 views