For interoperability with existing data, we run Rails with Oracle.
However, Rails doesn't support bind variables with Oracle, which are
critical for achieving good performance at high scale.
Can anyone say ActiveRecord will support bind variables on Oracle?
In the meanwhile, my coworker Ray Fortna has developed fairly clean
workaround; there is a session setting in Oracle which will cause
Oracle to automatically turn all literals into bind variables. This is
not optimal since it can lead to overbinding, but in the majority of
apps it is a significant improvement, and applying this at the DB level
makes it very low risk in terms of correctness. (For us, it cut the
CPU usage to 1/3 of the previous value.)
The following code updates the Oracle connection class to set cursor
sharing to force in the session, which converts literals to bind
variables.
Can you sketch the approach being taken to support bind variables in
ActiveRecord?
At a high level it's to keep the sql represented as an array of sql text and bind values, and pass that array to the connection adapter for binding, rather than binding it in AR and passing a sql string. For those adapters that can handle that.
It was pretty straightforward to do for inserts and updates, much harder for selects since the sql is being constructed and bound all over the place.
Yes, I can get you the quantification of the more aggressive
prefetching in our application; it was significant. I will post that
tomorrow.
Can you sketch the approach being taken to support bind variables in
ActiveRecord?
At a high level it's to keep the sql represented as an array of sql text
and bind values, and pass that array to the connection adapter for
binding, rather than binding it in AR and passing a sql string. For
those adapters that can handle that.
It was pretty straightforward to do for inserts and updates, much harder
for selects since the sql is being constructed and bound all over the place.
I may have time to help (and I'd be interested to see better support
for Oracle bind variables), just let me know how i can help (may you post
your patches?)
Yes, I can get you the quantification of the more aggressive
prefetching in our application; it was significant. I will post that
tomorrow.
Played w/ this some, and for many-row extracts, I'm also seeing huge improvements.
I've got a cleaner implementation that leverages the fact that the OCI8 lib exposes prefetch_rows at the connection level. Also will allow folks to set prefetch_rows directly in their database.yml file.
I'll post it to the Trac server tonight, along with some benchmark results. I'm running some more data now, at the moment it appears that 100 is a good value -- higher seems to have only slight benefit for large extracts, and actually hurts small queries.
PhilBo wrote:
I'll post it to the Trac server tonight, along with some benchmark results. I'm running some more data now, at the moment it appears that 100 is a good value -- higher seems to have only slight benefit for large extracts, and actually hurts small queries.
On the bind variables fix, would it be possible to reduce the scope of
changes required by having sanitize_sql return a subclass of string
that preserves binding information for drivers that know how to
interpret that info?
BindVariablesString#unbound_string would return the original query with
bind variables intact, and BindVariablesString#bind_variables_array
would return the bind variables themselves.
The value of BindVariablesString would be the string after replacement
of bind variables with literals, just as it is now, so drivers that
don't know about the extended behavior wouldn't need any changes.
This is just a though, I haven't traced through the code enough to be
certain that the BindVariablesString would be passed through intact
through all the layers of code (perhaps ActiveRecord creates a new
string for instance, losing the bind variables).