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.