oracle_enhanced adapter cursor_sharing option - similar vs force

206 views
Skip to first unread message

Raimonds Simanovskis

unread,
Sep 16, 2009, 3:11:40 AM9/16/09
to Oracle enhanced adapter for ActiveRecord
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

Lori Olson

unread,
Sep 16, 2009, 1:16:06 PM9/16/09
to oracle-...@googlegroups.com
I think I concur that the default should be updated.  However, it needs to be documented in a prominent place.  Probably multiple places.

Regards, Lori

toddwf

unread,
Sep 21, 2009, 11:09:58 AM9/21/09
to Oracle enhanced adapter for ActiveRecord


I support changing this to the default option. I was unaware I could
change it and we've been discussing this issue for quite awhile.

-Todd
Reply all
Reply to author
Forward
0 new messages