Huge memory usage starting with version 2.2.7

[edlebert]

A feature was added in 2.2.7 to help with network round trips for LOBs by utilizing array fetching, if the table has a LOB. This is creating BindTypes for all the columns in the query with a max_array_size == 100. According to memory_profiler, those BindType objects are taking up a lot of space (hundreds of megabytes in my Ruby on Rails app). The memory appears to not be freed either, so it's some sort of cached memory.

Is there a way to disable this feature?

[kubo]

Is there a way to disable this feature?

How about adding the following monkey patch after require "oci8"?

class OCI8
  class Cursor
    private
    alias_method :define_one_column_orig, :define_one_column
    def define_one_column(pos, param)
      @fetch_array_size = nil # disable array fetching anytime
      define_one_column_orig(pos, param) # call original
    end
  end
end

[einjohn]

At our company we were having the same problem – using ruby-oci8 at 2.2.9.

Without disabling this feature, like suggested above, we saw an increase in memory consumption by 1.5 gigabytes after sequentially requesting just 100 objects from the database (using ransack); and another 1.5 gig increase with the next 100 requests … and so on and so forth.

With this feature disabled, we could easily request several thousand objects (using the otherwise exact same code; also sequentially and also using ransack) from the database and end up with an increase of a mere 300 megabytes – compared to the freshly launched app.

[Szemethym]

We have also noticed a huge decrease in memory usage after putting in the suggested monkey patch. @kubo Would you consider adding it as a configurable option, so there's no need for said patch?

[matthewtusker]

Is this still an issue? We're still relying on the Monkey Patch described above, and I'm not keen to try removing it if it's going to eat up RAM and kill our applications.

[akostadinov]

I made an attempt at fixing this with #271

I think this is a high priority issue badly affecting anybody caching cursors for possible reuse like for example the oracle-enhanced_adapter. Now there are some issues with cursors not being cleaned up in all code paths inside oracle-enhanced_adapter. It leads to temporary waste of memory as this preallocation remains effective through the whole life of the cursor even if it is never reused. That I will tackle next.

I will appreciate any feedback about #271 and the performance verification I've done as part of it.

Also I'm thinking that maybe these 2 statements in addition to the monkey patch from @kubo will enable the LONG piecewise fetching in affected versions. I'll try to verify this tomorrow.

OCI8::BindType::Mapping[:clob] = OCI8::BindType::Long
OCI8::BindType::Mapping[:blob] = OCI8::BindType::LongRaw

UPDATE: oracle enhanced adapter uses SELECT ... FOR UPDATE so this patch will break it unless it is patched itself. I'll look into it and update this comment further.

UPDATE2: to apply all runtime optimization I could discover, see this PR for a complete monkey patch list: rsim/oracle-enhanced#2483