> 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.

> production:
>  adapter: oracle_enhanced
>  database: ...
>  username: ...
>  password: ...
>  cursor_sharing: force

> 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?

> Raimonds