Troubleshooting cursor: mutex X Due to High Version Count on the Insert Statements with Binds using JDBC (Doc ID 1469726.1)

APPLIES TO:
Oracle Database – Enterprise Edition – Version 11.1.0.7 and later
Information in this document applies to any platform.

SYMPTOMS
The following symptoms were encountered:
Database slowness.
The performance of the database was severely impacted with the ‘cursor: mutex X’ wait event.
AWR report for the problematic period shows a couple of inserts statements with several binds, having very high version count.
The version count report for those insert statements shows the cursor sharing failure reason as “bind mismatch”.
Note: If the Oracle Database software is below Patchset Update 11.2.0.2.2, then waits on ‘cursor: mutex X’ will inadvertently appear as waits on ‘cursor:
mutex S’, due to Bug 9591812.

CHANGES
There were no changes made to the database, nor any changes made to the insert statements.

CAUSE
The insert statements were coming into the database from the applications, which were using JDBC thin driver connection to connect to the database. Details
for the JDBC driver are below:
Oracle JDBC Driver version – “11.1.0.6.0-Production+”
JDK Version- JDK6
The couple of insert statements in question were getting fired from the JDBC connection, and all the bind variables were passed through JDBC driver. Since
the inserts statements have huge numbers of binds (~40) , some of the binds were passed as a nulls for different inserts statements.
In JDBC, the default datatype for nulls variables is defined as varchar2(32). This means if a value for any bind variable is passed as null , the datatype for
that bind variable explictly gets set to varchar2 irrespective of the datatype of the column datatype for which it holds the data for.On the other hand, when the
bind variable holds not null values, then it uses the datatype of the column.
One particular column which was defined as char(1) during insert statement on the table was using bind variables. Some insert statements were passing null
value for this column and some were passing as not nulls. So the datatype of the particular binds keep switching between varchar and char. Hence, the
existing cursor couldn’t be shared due to datatype mismatch of the binds.
Similarly, there were several such columns in that insert statements which can get nulls or not nulls values. This caused cursor sharing failure due to datatype
mismatch. This resulted in thousands of versions for this particular insert statement.
Because of the datatype change of the bind variables in the subsequent insert statements, the existing cursors were not getting shared due to BIND
MISMATCH. Truncated output from the library cache dump and the cursordump is shown below to illustrate the issue:

Bind variable at position -10
ChildNumber=82 ID=40 reason=Bind mismatch(22) size=4×4 bind_position=10 original_oacflg=1 original_oacmxl=32
upgradeable_new_oacmxl=128

ChildNumber=524 ID=40 reason=Bind mismatch(8) size=4×4 bind_position=10 original_oacflg=1 original_oacdty=96
new_oacdty=1
ChildNumber=523 ID=40 reason=Bind mismatch(8) size=4×4 bind_position=10 original_oacflg=1 original_oacdty=1
new_oacdty=96
Bind variable at position -11
ChildNumber=49 ID=40 reason=Bind mismatch(8) size=4×4 bind_position=11 original_oacflg=1 original_oacdty=96
new_oacdty=1
ChildNumber=232 ID=40 reason=Bind mismatch(8) size=4×4 bind_position=11 original_oacflg=1 original_oacdty=1
new_oacdty=96

SOLUTION
Note the permanent solution for this issue is to fix the behavior in the JDBC to handle the null values for bind variables without changing the
datatypes of the same bind variable. Since the application doesn’t have any control on how the JDBC driver handles the bind variables and its datatype,
workaround was to restrict the number of child cursors a parent can have before it becomes absolute.
Please note: To be able to set the threshold on the number of child cursors a parent can have, the fix for bug Document 10187168.8 must be applied.
This workaround sets the threshold on the number of child cursors to 200, which reduces the mutex problem:
_cursor_features_enabled=1026

vent=”106001 trace name context forever, level 200″
By default in Oracle 11g, there is no limit on maximum number of child cursors which a parent cursor can have. Due to very high version count, the child
cursor count was very high. So we limited the number of max child cursor a parent can have to 200 as shown above.