PhilBo
2006-Oct-31 00:15 UTC
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 -~----------~----~----~----~------~----~------~--~---
Michael A. Schoen
2006-Oct-31 06:20 UTC
Re: Optimizations to Rails on Oracle: bind variable support and prefetching
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 -~----------~----~----~----~------~----~------~--~---
PhilBo
2006-Nov-09 06:52 UTC
Re: Optimizations to Rails on Oracle: bind variable support and prefetching
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 -~----------~----~----~----~------~----~------~--~---
Michael A. Schoen
2006-Nov-09 20:06 UTC
Re: Optimizations to Rails on Oracle: bind variable support and prefetching
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 -~----------~----~----~----~------~----~------~--~---
Luca Mearelli
2006-Nov-10 15:20 UTC
Re: Optimizations to Rails on Oracle: bind variable support and prefetching
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 -~----------~----~----~----~------~----~------~--~---
Michael A. Schoen
2006-Nov-11 01:10 UTC
Re: Optimizations to Rails on Oracle: bind variable support and prefetching
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
2006-Nov-13 06:53 UTC
Re: Optimizations to Rails on Oracle: bind variable support and prefetching
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 -~----------~----~----~----~------~----~------~--~---
PhilBo
2006-Nov-13 21:58 UTC
Re: Optimizations to Rails on Oracle: bind variable support and prefetching
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 -~----------~----~----~----~------~----~------~--~---