High version count statements causes library cache

Due to high SQL version counts issue in Production database, the library cache issue has occurred.The PROD database has not able to accumulate the shared memory for the specific execution of the query.So the execution of the specific query can’t ran with that memory,the new child cursor has been created with a new plan of hash value.  
The PROD database has high version counts,so that the library cache issue has been occurred.
The reason for not sharing the cursor is bind mismatch.BIND_EQUIV_FAILURE: the bind value’s selectivity does not match that used to optimize the existing child cursor.


We need a bounce of database to change the cursor_obsolete_threshold parameter.
Change cursor_sharing parameter set  from EXACT to FORCE.The cursor_sharing parameter=FORCE has doesn’t affect the bind variable values.
SQL>  show parameter cursor;

NAME                                 TYPE                    VALUE
_cursor_obsolete_threshold           integer                  100
cursor_sharing                       string                  EXACT

alter system set cursor_sharing=FORCE;

If issue is still present after change the cursor_sharing parameter to FORCE, consider setting cursor_obsolete_threshold = 100,

alter system set cursor_obsolete_threshold=100;
1) Consider using cursor_sharing parameter set to FORCE, this will improve share ability on cursors. Current setting is EXACT.
2) Allows the creation of a new cursor if sharing an existing cursor, or if the cursor plan is not optimal.
3) Bind mismatch means that something about the bind variables was different.
  • April 25, 2017 | 13 views
  • Comments