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