SQL with high version count

I was looking at different things when investigating "library cache : mutex x" wait issue. One of the problems were SQL with high version counts. In simple terms it means - for repeated execution of the SQL, child cursor could not be reused and thus a large number of new child cursors have been generated along with the executions.


I found that, one of our SQLs had over 1600 child cursors - which is too much!

SQL> select * from (
select count(sc.sql_id) cursor_count, a.sql_id, a.sql_text
from v$sqlarea a, v$sql_shared_cursor sc
where  a.sql_id = sc.sql_id
group by a.sql_id, a.sql_text
order by cursor_count desc)
where rownum <=10; 

CURSOR_COUNT SQL_ID        SQL_TEXT
------------ ------------- ------------------------------------------------------------
        1693 2vzfs9ww6u3xw INSERT INTO admin_record (id, clnt, clnt_display
                           _name, pr_id, mar_id, mar_medication_id, medication_name,
                           prescriber_id, prescriber_display_name, dosage, total_dosage
                           , total_unit, measurement_unit, form_of_dosage, other_form_o
                           f_dosage, route, other_route, frequency, medication_type, ad
                           minister_time, scheduled_time, record_type, comments, admini
                           stered_by, administered_by_display_name, user_initial, recor
                           ded_by, recorded_by_display_name, updated_by, administer_dat
                           e, record_date, updated_date, administer_date_int, record_da
                           te_int, tz, status, test_form, version, mar_med_row_num, mar
                           _med_column_num, scheduled_entry, schede_date, scheduled_b
                           y_id) VALUES (admin_rec_seq.nextval, :1, :2, :3, :4,
                            :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17,
                            :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29,
                            :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41,
                            :42)


The insert statement consumes the highest amount of shared pool memory by producing so many child cursors. This is because, we are not able to reuse cursors even though we use bind variables. During yesterday problem, the number of child cursors were 1693. Producing so many child cursors (and searching within the list) may causes shared pool mutex waits. This is an issue with 11g where database does not automatically obsolete a parent cursor "anymore" when it reaches 1024 child cursors.

The main problem with this SQL is - there are bind mismatch in most cases when the SQL comes into database for an execution with all its 42 bind variables.


SQL> select bind_mismatch, bind_length_upgradeable
from  v$sql_shared_cursor
where sql_id = '2vzfs9ww6u3xw'
and rownum <=20; 
B B
- -
Y Y
Y N
Y Y
Y Y
Y N
Y N
Y Y
Y Y
Y Y
Y N
Y Y
Y N
Y N
Y N
Y N
Y N
......
......


This should not be happening as we use bind variables. Then I looked at our application code. I found that, the developer picks column values from objects and those values could be null. I found that we need to change the jdbc prepared statement coding style and need to use "java.sql.Types" to set null values when object values are null. This way cursor will understand the bind type and won't create  child cursors for type mismatch caused by setting 'null' explicitly.


For example, for a column we use -

preparedStatement.setObject(

index++, record.getAdministeredBy() != null ? record.getAdministeredBy().getId() : null);


We need to program like -

preparedStatement.setNull(index++, java.sql.Types.INTEGER); 

when the object is null so that the cursor understand the datatype for the bind 

variable.

We are going to fix this soon in production!

Here is a nice article related with high number of child cursor problems.

http://www.usn-it.de/index.php/2010/08/04/oracle112-mutex-s-too-many-child-cursors/

***