As you probably know ActiveRecord generates SQL statements without
bind variables which would be quite bad for Oracle as it will generate
separate execution plans and cursors for each SQL statement even if
just WHERE condition values are changed.
Therefore oracle_enhanced adapter sets session option cursor_sharing
to 'similar' which extracts all literals from SQL statement and puts
them as bind variables. The issue is that sometimes 'similar'
cursor_sharing can still decide to create separate execution plan and
cursor if it thinks that execution plan might be different for
different bind values. Therefore there is other cursor_similar option
'force' which will always extract all literals as bind variables and
will always produce just one execution plan and cursor. There is quite
good explanation of this at http://www.oracle.com/technology/oramag/oracle/06-jan/o16asktom.html
oracle_enhanced adapter since very beginning (already since original
oracle adapter) was setting cursor_sharing to similar. But recently we
found in one application that uses delayed_job plugin (and which
queries delayed_jobs table very frequently) that it generates huge
amount of execution plans and cursors. Probably it is because time
parameters are inserted as TO_DATE(..., ...) functions in SQL query
and therefore Oracle optimizer is deciding that it needs to create
separate execution plan for each query. This application is using 10g
database, have heard that in 11g this might be different.
In such cases it is recommended to use cursor_sharing force mode. You
can do it quite simply by specifying this option in database.yml, e.g.
Actually I am thinking about making this force option to be default in
next version of oracle_enhanced adapter as probably quite many Rails
developers are not aware of potential issues with similar option.
Those who knows the details will be able to chose by changing this
option in database.yml.
What do you think about changing default value for this option?