Cursor Sharing

The init.ora parameter "CURSOR_SHARING" can have the values of FORCE, SIMILAR or EXACT. When set to FORCE, all string/numeric literals will be replaced by bind variables. This will improve the performance of code that should use bind variables but doesn't (by making it possible to use the same plan for all the versions of the statements). Disadvantages are the binding of real constants (things that never change), which will give the optimiser less data to work with. The explain plan will see different query to the actual optimiser, and hence the results will be unreliable. See: http://asktom.oracle.com/pls/asktom/f?p=100:11:1328478115348624::::P11_QUESTION_ID:5180609822543 When set to SIMILAR, it will share the cursor if any of the different bind variable values don't change the plan (e.g. due to a difference in "selectiveness" which means that a Index Range scan should be used instead of Full Table scan). If bind peeking is enabled, it used to peek once and then use the same plan then onwards. Since 11g it keeps checking if the new values have increased the physical reads etc and if so it discards the original plan and creates new ones for each new value in that bind variable (bind sensitive and bind aware columns in the child cursor records) EXACT will never share a cursor, and will always create a new one for each statement with different values.

Useful links

Oracle optimisation blog: http://optimizermagic.blogspot.com