11.2 on Linux
The application is written in Java and uses Java programming framework where UPDATE statements are auto-generated. This means that every column gets updated and the values come from bind variables. So a table with 50 columns gets an UPDATE statement with 50 bind variables.
UPDATE TABLE_A SET COLUMN1 = :1, COLUNM2 = :2, COLUMN3 = :3 ... WHERE COLUMN0 = :50
This UPDATE gets high version count due to bind_mismatch as determined from v$sql_shared_cursor: there are
about 1000 child cursors for this UPDATE.
High version count leads to waits on 'cursor: mutex X' and 'cursor: mutex S'. The waits are significant, with dozens of sessions waiting on these events, all for this UPDATE. I've tried flushing this cursor using dbms_shared_pool.purge and it didn't work. Flushing the entire shared pool with ALTER SYSTEM FLUSH SHARED_POOL works but may have its own side effects.
For bind mismatch to occur a bind variable needs to be re-defined between executions:
http://www.toadworld.com/platforms/oracle/b/weblog/archive/2016/09/26/why-my-execution-plan-has-not-been-shared-part-iii
And this is exactly what I am seeing in V$SQL_BIND_CAPTURE: the same bind variable may be defined either as VARCHAR2(32) or VARCHAR2(128) or VARCHAR2(2000). As there are so many bind variables there are many combinations and we get high version count.
What could be done about it other than changing the application?