Ruby oic8 fetch row make multiple networks calls

[stanneeru]

@kubo, Can we create a method like 'fetch_rows' in the c-language to return all the raw results of the executed query instead of making multiple networks call for every call to cursor from rails to each column of a row? instead of multiple calls can return the raw result then which can be sent to Oracle Enhanced to typecast cast result and then send it to ActiveRecord to improve the performance of fetch method

[kubo]

Oracle call interface cannot fetch all rows in a query. Instead it supports prefetching. When a row is fetched, more than one row is retrieved in a network call and stored into a prefetch buffer. The subsequent rows are got from the buffer until it becomes empty. When the buffer is empty, more rows are retrieved by a network call.

Ruby-oci8 prefetches 100 rows by default. See OCI8#prefetch_rows=.

[skudryav]

@kubo I also have a few questions related to what Srikanth asked:

(1) is there a way to set @fetch_array_size ? I am seeing @fetch_array_size = nil in fetch_row_internal() when running simple queries

if I set it to value > 1 in fetch_row_internal() I am getting this error message: /Users/skudryav/RAILS/TOGO/togo/config/initializers/oracle.rb:79:in `__fetch': fetch size (100) != define-handle size 1 (RuntimeError)

If I pass value > 1 as second parameter in @rowbuf_size = __fetch(@con, @fetch_array_size || 1) I am getting the same error message
** by default we have setting prefetch_rows: 500

(2) also seeing that @rowbuf_size is always 1 although we have setting prefetch_rows: 500

Is there some mode in which oci8_stmt_fetch returns/handles more than 1 row?

(3) in fetch_one_row_as_array I am seeing ruby code which it seems iterates via @define_handles
and call C method OCI8::BindType::Base#get_data
Is there a way to do all this in C code in order to improve performance?
** similar to what Postgres adapter does

[doug-seifert-oracle]

@skudryav In our case, we applied the work around described in #230 to set @fetch_array_size to nil always because without it, our apps would consume multiple gigabytes of memory over time, leading them to crash randomly, run very slowly and be unstable in general. There is some kind of memory leak associated with setting @fetch_array_size to a large value. Perhaps Cursor or Statement objects are not being freed correctly in the oci8 code?

[skudryav]

even if I remove this workaround I am still seeing @fetch_array_size = nil in fetch_row_internal()

[skudryav]

@kubo Currently OCI8 fetch_one_row_as_array method does a lot of get_data calls when iterating over list of select columns (~ @define_handles), that's why we are seeing degradation with large number of columns.
Ideally it will be good if some new version of get_data C method will return array of values - so there will be just one call of C method in fetch_one_row_as_array() instead of many.