prefetch LOB types (e.g. CLOB) using OCI_ATTR_LOBPREFETCH_*

[ciapecki]

there is an excellent prefetch_rows() method but it seems to not have any effect with LOB Types. def prefetch_rows=(rows) attr_set_ub4(11, rows) # OCI_ATTR_PREFETCH_ROWS(11) end

Oracle mentions in http://docs.oracle.com/cd/B28359_01/appdev.111/b28395/oci07lob.htm#CHDDHFAB about OCI_ATTR_LOBPREFETCH_SIZE, OCI_ATTR_DEFAULT_LOBPREFETCH_SIZE and OCI_ATTR_LOBPREFETCH_LENGTH

Is it possible to use it with ruby-oci8?

[kubo]

It is possible. You can use it as follows.

class OCI8
  def lob_prefetch_size=(size)
    @session_handle.send(:attr_set_ub4, 438, size) #  OCI_ATTR_DEFAULT_LOBPREFETCH_SIZE(438)
  end

  class Cursor
    def set_lob_prefetch_size(pos, size)
      @define_handles[pos - 1].send(:attr_set_ub4, 439, size) # OCI_ATTR_LOBPREFETCH_SIZE(439)
    end
    def set_lob_prefetch_length(pos, boolval)
      @define_handles[pos - 1].send(:attr_set_boolean, 440, boolval) # OCI_ATTR_LOBPREFETCH_LENGTH(440)
    end
  end
end

conn = OCI8.new(...)
conn.lob_prefetch_size = 2000
cursor = conn.parse('select clob_column from ...')
cursor.define(1, OCI8::CLOB)
cursor.set_lob_prefetch_size(1, 4000) # Use this method after cursor.define(pos, OCI8::CLOB).
cursor.set_lob_prefetch_length(1, true) # Use this method after cursor.define(pos, OCI8::CLOB).
cursor.exec
while row = cursor.fetch
  p row[0].read
end
cursor.close

I'll add OCI8#lob_prefetch_size= in the next release.

OCI8::Cursor#set_lob_prefetch_size may not be added. IMO, only a few people use this.

OCI8::Cursor#set_lob_prefetch_length will not be added. OCI_ATTR_LOBPREFETCH_LENGTH's description is "Specifies the prefetch length and chunk size ..." But it will be "Specifies whether the lob length and chunk size should be prefetched or not ..." as far as I checked network packets. The lob length and chunk size are prefeched when lob_prefetch_size is not zero. IMO, nobody use this.

[ciapecki]

hi Kubo,

Thanks for your quick response with solution.

I tried to run it and it runs successfully, just I do not notice any difference between using different values to lob_prefetch_size.

Do you have an idea how to dig the issue?

I am writing to file with: File.open('out140123.txt','w') do |f| while row = cursor.fetch f.write row[0].read end end

and the file gets written always in 4k bytes chunks, disregarding of the lob_prefetch_size values.

thanks for your help, Chris

On Thu, Jan 23, 2014 at 3:31 PM, Kubo Takehiro notifications@github.comwrote:

It is possible. You can use it as follows.

class OCI8 def lob_prefetch_size=(size) @session_handle.send(:attr_set_ub4, 438, size) # OCI_ATTR_DEFAULT_LOBPREFETCH_SIZE(438) end

class Cursor def set_lob_prefetch_size(pos, size) @define_handles[pos - 1].send(:attr_set_ub4, 439, size) # OCI_ATTR_LOBPREFETCH_SIZE(439) end def set_lob_prefetch_length(pos, boolval) @define_handles[pos - 1].send(:attr_set_boolean, 440, boolval) # OCI_ATTR_LOBPREFETCH_LENGTH(440) end endend conn = OCI8.new(...)conn.lob_prefetch_size = 2000cursor = conn.parse('select clob_column from ...')cursor.define(1, OCI8::CLOB)cursor.set_lob_prefetch_size(1, 4000) # Use this method after cursor.define(pos, OCI8::CLOB).cursor.set_lob_prefetch_length(1, true) # Use this method after cursor.define(pos, OCI8::CLOB).cursor.execwhile row = cursor.fetch p row[0].readendcursor.close

I'll add OCI8#lob_prefetch_size= in the next release.

OCI8::Cursor#set_lob_prefetch_size may not be added. IMO, only a few people use this.

OCI8::Cursor#set_lob_prefetch_length will not be added. OCI_ATTR_LOBPREFETCH_LENGTHhttp://docs.oracle.com/cd/B28359_01/appdev.111/b28395/ociaahan.htm#CHDECDDE's description is "Specifies the prefetch length and chunk size ..." But it will be "Specifies whether the lob length and chunk size should be prefetched or not ..." as far as I checked network packets. The lob length and chunk size are prefeched when lob_prefetch_size is not zero. IMO, nobody use this.

— Reply to this email directly or view it on GitHubhttps://github.com/kubo/ruby-oci8/issues/52#issuecomment-33128048 .

[kubo]

Sorry, it is impossible. I checked lob prefetch behaviour with a small C program and said 'possible' mistakenly. I noticed that the lob_prefetch_size affected negative impact to ruby-oci8. C code should be changed to improve lob read operations.

When OCI_ATTR_LOBPREFETCH_SIZE is set, lob data is prefetched at fetch time. It doesn't affect subsequent read of lob data. It is efficient when total lob length equals or smaller than the specified size. But it gets small effect when total lob length is far larger than the specified size. As for ruby-oci8, it uses 8192 byte buffer to read lob. The buffer size should be changed for performance. See: https://github.com/kubo/ruby-oci8/blob/fa1817a287b7e3482f1c099fbb3e702fe2bae894/ext/oci8/lob.c#L522

Moreover ruby-oci8 cannot use the prefetched lob data. Ruby-oci8 copies lob locators when a fetch is done. That is, OCI8::Cursor#fetch returns different instances of a lob locator for each fetch. But copied lob locators cannot use prefetched data. Unused lob data are fetched when the lob_prefetch_size is set.

[kubo]

I don't use OCI_ATTRLOBPREFETCH*. That's because they don't improve LOB operations in ruby-oci8. The reason was written in the previous comment.

I rechecked lob.c and network packets. I found that the 8192 byte buffer in ruby-oci8 didn't affect network packets. lob.read() requests the Oracle server to send whole LOB content. No additional requests are sent until whole LOB content arrives to the client.

The 4k bytes chunks you found will be determined by SDU_SIZE. See http://docs.oracle.com/cd/E11882_01/network.112/e10836/performance.htm#NETAG0141 The default size is 2k in Oracle 10g and 8k in Oracle 11g.

Increasing the SDU_SIZE may improve LOB operations. In http://www.oracle.com/technetwork/database/windows/ukoug-2007-db-windows-best-practice-130819.ppt

For bulk data transfer scenarios, increase SDU_SIZE in sqlnet.ora or tnsnames.ora