Ruby on Rails with Oracle

Hello everyone,

I am trying to convince my colleagues to convert to Ruby on Rails,
however I am up against the following problem:

Ruby on rails seems to be primarily designed a single database doing a
small number of queries. While trying to process a webpage that does
thousands of sql queries on Oracle, I get the message that I have used
up too many cursors.

In Java, I can use stmt.close() to close all the cursors on a sql
statement. In Ruby on Rails the sql “statement” and the sql “result set”
are hidden inside ActiveRecord’s methods. Therefore I can’t close the
statement.

As a work around I can close the database connection, however it takes
too long to open another connection thousands of times.

How can I solve this problem?

Thanks

  • Jon

take a look to this link
http://vsbabu.org/mt/archives/2005/07/19/oracle_with_active_record.html

Jon Leung wrote:

Hello everyone,

I am trying to convince my colleagues to convert to Ruby on Rails,
however I am up against the following problem:

Ruby on rails seems to be primarily designed a single database doing a
small number of queries. While trying to process a webpage that does
thousands of sql queries on Oracle, I get the message that I have used
up too many cursors.

In Java, I can use stmt.close() to close all the cursors on a sql
statement. In Ruby on Rails the sql “statement” and the sql “result set”
are hidden inside ActiveRecord’s methods. Therefore I can’t close the
statement.

As a work around I can close the database connection, however it takes
too long to open another connection thousands of times.

How can I solve this problem?

Thanks

  • Jon

If I wer your Oracle DBA, I would be very concerned that you’re running
an
app that has a web page which runs thousands of SQL queries. I would
expect
you to run out of cursors. Are you able to take advantage of eager
loading?
I don’t know if this is so much a Rails issue as an issue with your app.
(Sounds like the n+1 problem)

A quick glance at the source for the Oracle connection adapter for
ActiveRecord shows that cursors are being created and closed. If you
are
sure that this isn’t happening on your database then you might want to
consider filing a ticket.

C:\ruby\lib\ruby\gems\1.8\gems\activerecord-
1.14.4\lib\active_record\connection_adapters\oracle_adapter.rb line 472
def select(sql, name = nil)
cursor = execute(sql, name)
cols = cursor.get_col_names.map { |x| oracle_downcase(x) }
rows = []

      while row = cursor.fetch
        hash = Hash.new

        cols.each_with_index do |col, i|
          hash[col] =
            case row[i]
            when OCI8::LOB
              name == 'Writable Large Object' ? row[i]: row[i].read
            when OraDate
              (row[i].hour == 0 and row[i].minute == 0 and 

row[i].second
== 0) ?
row[i].to_date : row[i].to_time
else row[i]
end unless col == ‘raw_rnum_’
end

        rows << hash
      end

      rows
    ensure
      cursor.close if cursor
    end

You could increase the number of open cursors at the server level too.
Check to make sure you are using eager-lading though… not doing that
can
kill your app.

Jon Leung wrote:

In Java, I can use stmt.close() to close all the cursors on a sql
statement. In Ruby on Rails the sql “statement” and the sql “result set”
are hidden inside ActiveRecord’s methods. Therefore I can’t close the
statement.

As a work around I can close the database connection, however it takes
too long to open another connection thousands of times.

How can I solve this problem?

Hi Jon,

I don’t know where you’re seeing this.

We’re using AR against Oracle and don’t have this issue at all.

In the active_record/connection_adapters/oracle_adapter.rb, there’s this
code,
which closes the cursor:

     def select(sql, name = nil)
       cursor = execute(sql, name)
       cols = cursor.get_col_names.map { |x| oracle_downcase(x) }
       rows = []

       while row = cursor.fetch




rows << hash
end

       rows
     ensure
       cursor.close if cursor
     end

So it does close the cursor.

Regards,
Blair

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs