Forum: Rails-core (closed, excessive spam) Optimizations to Rails on Oracle: bind variable support and

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.
-----------------------------------
module OracleConnectionFactoryPatches
    def self.included(base) #nodoc
        base.class_eval do
            alias_method :new_connection_without_cursor_sharing,
:new_connection
            alias_method :new_connection,
:new_connection_with_cursor_sharing
        end
    end

    def new_connection_with_cursor_sharing(username, password,
database)
        conn = new_connection_without_cursor_sharing(username,
password, database)
        conn.exec %q{alter session set cursor_sharing = 'FORCE'}
        conn
    end
end
--------------------------------

The following code in environment.rb applies the patch
--------------------------------
IN environment.rb

require dirname + '/../lib/oracle_connection_factory_patches.rb'
   OracleConnectionFactory.send(:include,
OracleConnectionFactoryPatches)
--------------------------------

A second significant optimization we've applied has been to set the
Oracle driver to more aggressively prefetch rows.

-------------
module OCI8CursorPatches

    def self.included(base) #nodoc
        class << base
            def options=(opt)
                @@options = opt
            end
        end

        base.class_eval do
            alias_method :initialize_without_options, :initialize
            alias_method :initialize, :initialize_with_options
        end
    end

    def initialize_with_options(env, svc, ctx, stmt = nil)
        initialize_without_options(env, svc, ctx, stmt)
        unless @@options.nil?
            @@options.each do |x, y|
                do_ocicall(@ctx) { @stmt.attrSet(x, y) }
            end
        end
    end
end

-------------
IN environment.rb

require dirname + '/../lib/oci8_cursor_patches.rb'
OCI8::Cursor.send(:include, OCI8CursorPatches) OCI8::Cursor.options = {
    OCI_ATTR_PREFETCH_ROWS => 500
}

PhilBo wrote:
> Can anyone say ActiveRecord will support bind variables on Oracle?

Yes. I've gotten it working for insert/update, still working on select.

> The following code updates the Oracle connection class to set cursor
> sharing to force in the session, which converts literals to bind
> variables.

I've had more success using the "SIMILAR" option, rather than "FORCE".

> A second significant optimization we've applied has been to set the
> Oracle driver to more aggressively prefetch rows.

Have you quantified the impact of this?

Can you sketch the approach being taken to support bind variables in
ActiveRecord?

Yes, I can get you the quantification of the more aggressive
prefetching in our application; it was significant.   I will post that
tomorrow.

PhilBo wrote:
> 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.

Thanks.

Michael A. Schoen wrote:
>
> 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?)

bye
Luca Mearelli
--

Web:   http://spazidigitali.com
Email: mailto://l.mearelli@spazidigitali.com
--

PhilBo wrote:
> 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.

Michael A. Schoen wrote:
> 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.

	http://dev.rubyonrails.org/ticket/6607

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