Rails core - Oct 2006 - Optimizations to Rails on Oracle: bind variable support and prefetching

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
}


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core
-~----------~----~----~----~------~----~------~--~---

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?




--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core
-~----------~----~----~----~------~----~------~--~---

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.


--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

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.



--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

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

--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

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.


--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

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


--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

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


--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---