SELECT involving CLOB column is 15X slow than without CLOB column

[gyut]

Steps to reproduce

User.role is a CLOB. Takes 80ms.
User.select(User.column_names).all.to_a
D, [2018-08-13T12:03:31.270377 #56195] DEBUG -- : User Load (78.8ms)

Without User.role it takes 5ms.
User.select(User.column_names - %w(roles)).all.to_a
D, [2018-08-13T12:03:23.412199 #56195] DEBUG -- : User Load (4.5ms)

Expected behavior

Running a SELECT query that includes a CLOB column should be roughly as fast as running a SELECT query without the CLOB column.

Actual behavior

Running a SELECT query that includes a CLOB column is about 15x slower than without the CLOB column.

System configuration

Rails version: 5.2.1

Oracle enhanced adapter version: 5.2.3

Ruby version: 2.4.2p198

Oracle Database version: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

[kubo]

I guess that it is due to the number of network round trips.
When a CLOB column is not included in a query, rows are fetched up to the number of prefetch rows (default 100) in a network round trip. However when a CLOB column is included, CLOB column values are fetched as LOB locators. Then contents in a CLOB are retrieved from LOB locators. It requires more than one network round trip for each CLOB value.

When OCI_ATTR_DEFAULT_LOBPREFETCH_SIZE (the default value seems zero) is changed, the performance will be improved.

class OCI8
  def default_lobprefetch_size
    # OCI_ATTR_DEFAULT_LOBPREFETCH_SIZE = 438 in oci.h
    @session_handle.send(:attr_get_ub4, 438)
  end
  def default_lobprefetch_size=(newsize)
    @session_handle.send(:attr_set_ub4, 438, newsize)
  end
end

...
conn = ... get ruby-oci8 connection from oracle-enhanced connection ...
conn.default_lobprefetch_size = 8192 # determine proper size by yourself.
...
User.select(User.column_names).all.to_a

[gyut]

Thanks for the quick reply, @kubo. That looks like it might help. Can you expand on the details of how to get the connection and set the default_lobprefetch_size in a Rails app?

[kubo]

ActiveRecord::Base.connection.raw_connection?
I'm not familiar with Rails specific features.

I think it is better to set the lobprefetch size just after ruby-oci8 connection is established as prefetch_rows.

[kubo]

@gyut
As far as I checked packets captured by wireshark, lobprefetch size may not help. When lobprefech size is set, LOB contents are sent with other column values. However same contents are sent again when OCI8::CLOB#read is called. Moreover it may decrease performance because same contents are sent twice.

I'll add OCI8#lobprefetch_size (renamed from default_lobprefetch_size) in the next ruby-oci8 release. But I'll hide it in documents because I'm not sure whether it is usable for users.

[gyut]

Thanks, @kubo. That's exactly what we saw last week. We were able to increase and vary the lobprefetch, but no setting improved performance, and some settings degraded performance.

We'd love to hear other ideas for improving the performance of tables with CLOB columns.

[kubo]

There are likely two issues.

1. Lob prefetching doesn't work as explained at my previous comment.

   This will be solved by changing ruby-oci8 code to fetch LOBs as strings, not as LOB locators.

   If you know the maximum size of CLOB data, change ruby-oci8 code as follows.

   • Add the following code here to make OCI8::BindType::String handle CLOB data

        when :clob
          param = {:length => 1024 * 1024}
        when :nclob
          param = {:length => 1024 * 1024, :nchar => true}

     It allocates 1024 * 1024 bytes for each CLOB even when the actual size is small.
     When the actual size is larger than the allocated size, fetching fails with ORA-01406: fetched column value was truncated.
   • Add the following code somewhere to make ruby-oci8 use OCI8::BindType::String for CLOB columns

     OCI8::BindType::Mapping[:clob] = OCI8::BindType::String
     OCI8::BindType::Mapping[:nclob] = OCI8::BindType::String

   If you don't know the maximum size of CLOB data, it will be fixed by using piecewise defines for LOBs using the data interface. It will need some time to implement it in ruby-oci8. It will be at the weekend of the next at earliest.

2. Rows prefetching doesn't work when a CLOB column is included in a query.

   When a CLOB column is not included in a query, rows are fetched up to the number of prefetch rows (default 100) in a network round trip as noted before. However when a CLOB column is included in a query, rows are fetched one by one no matter whether the column is fetched as LOB locater or as string (using OCI8::BindType::String). (I checked it on the latest Oracle version - 18.3.0.0.0.)

   This issue is fixed by using array fetching - allocating buffer for rows in advance and fetching more than one row at once in ruby-oci8 layer. (Prefetching caches rows in Oracle library layer.) Ruby-oci8 doesn't support array fetching now. However it may be implemented relatively easily. I cannot say when. I may overlook some pitfalls to implement it.

[gyut]

Thanks for the super-details analysis, @kubo! If you have time in the next few weeks to make improvements, we'd be happy to test them.

[kubo]

@gyut
I commited kubo/ruby-oci8@669e29f. Use the latest code as follows.

$ git clone https://github.com/kubo/ruby-oci8.git
$ cd ruby-oci8
$ gem build ruby-oci8.gemspec
$ gem install ./ruby-oci8-2.2.6.gem

LOB contents are retrieved as strings, not as LOB locators, by the following code.

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

Note that when LOB columns are fetched as LOB locators, empty lobs and NULLs are distinguishable. However when LOB columns are fetched as string, both empty lobs and NULLs are fetched as nil.

OCI8::BindType::Long and OCI8::BindType::LongRaw allocates buffers internally as follows.

They initially allocate a 32-kilobyte buffer for each column. When column data are longer than allocated buffers, they allocate an additional buffer whose size is twice. The size doesn't increase infinitely. The maximum size of one buffer is 8M.

For example

• Initial buffer size is 32K.
• When column data are longer than 32K, 64K buffer is added and total size become 96K.
• When column data are longer than 96K, 128K buffer is added and total size become 224K.
• When column data are longer than 224K, 256K ...
• ..., 512K ...
• ..., 1M ...
• ..., 2M ...
• ..., 4M ...
• ..., 8M ...
• ..., 8M ...
• ..., 8M ...

When a LOB size is 100K, the initial, second and third buffers contain 32K, 64K, 4K respectively.

The initial and maximum sizes are customizable by the following code.

OCI8::BindType::Base.initial_chunk_size = 4 * 1024  # default: 32 * 1024
OCI8::BindType::Base.max_chunk_size = 128 * 1024 * 1024 # default: 8 * 1024 * 1024

I may change the default sizes later.

Array fetching is working locally and has not been committed yet. I'm now writing tests about it.

[kubo]

@gyut
I tested the commit in the previous comment by using ruby-oci8 test cases.
I'm not sure whether it passes oracle-enhanced test cases...

[yahonda]

@kubo Thanks for the commit. I have not taken a look at the commit in detail yet, let me inform that Oracle enhanced adapter CI uses the master branch of ruby-oci8. The latest CI https://travis-ci.org/rsim/oracle-enhanced/jobs/423702697#L672 works fine using kubo/ruby-oci8@669e29f

[kubo]

@yahonda Thanks for letting me know the CI.

I didn't explain my concern enough. I guess that the oracle-enhanced test works well when OCI8::BindType::Mapping[:clob] = OCI8::BindType::Long. However I haven't checked it.

[yahonda]

Understood your concern.

[kubo]

Unfortunately, oracle-enhanced tests failed when LOBs are fetched as strings.
See https://travis-ci.org/kubo/oracle-enhanced/jobs/424338945

Failures are categorized into two types.

1. Empty CLOB data are fetched as nil.
   This is similar to varchar2 data type, which treats empty strings as NULL.
   I have no idea to fix it.
2. write_lob expects a LOB locator but a string are fetched.
   This disappeared by commenting out this line.
   See https://travis-ci.org/kubo/oracle-enhanced/jobs/424355106

[kubo]

@gyut
I did all what I could. Could you use the latest code as follows.

$ git clone https://github.com/kubo/ruby-oci8.git
$ cd ruby-oci8
$ gem build ruby-oci8.gemspec
$ gem install ./ruby-oci8-2.2.6.gem

It reduces number of network round trips even when LOB data are fetched as LOB locators.

Suppose the following cases.

• A: No CLOB in a query
• B: CLOBs in a query & ruby-oci8 2.2.6 or before
• C: CLOBs in a query & latest ruby-oci8 in the github.
• D: CLOBs in a query & latest ruby-oci8 in the github & fetching LOB as string

C is the case just using the latest code in your application.
D is the case that (1) OCI8::BindType::Mapping[:clob] = OCI8::BindType::Long is set, (2) one line of oracle-enhanced is commented out as the previous comment and (3) your application doesn't use empty CLOB.

Case	number of network round trips
A	((total_rows + 1).to_f / prefetch_rows).ceil
B	1 + (total_rows + 1) + total_rows * number_of_clob_columns * 2
C	1 + ((total_rows + 1).to_f / prefetch_rows).ceil + total_rows * number_of_clob_columns
D	1 + ((total_rows + 1).to_f / prefetch_rows).ceil

The following table is the number of network round trips for each query step when one CLOB column is included in a query which returns 5 rows and the setting of prefetch rows is 3.
Emoji symbols 1️⃣, 2️⃣ and 3️⃣ indicate number of rows in the response of a network round trip. 🔚 is no-data-found in the response. a in a+b is the number of network round trips by fetch and b is by lob.read.

Case	A	B	C	D
exec	1 3️⃣	1	1	1
1st fetch + lob.read	0	1+2 1️⃣	1+1 3️⃣	1 3️⃣
2nd fetch + lob.read	0	1+2 1️⃣	0+1	0
3rd fetch + lob.read	0	1+2 1️⃣	0+1	0
4th fetch + lob.read	1 2️⃣ 🔚	1+2 1️⃣	1+1 2️⃣ 🔚	1 2️⃣ 🔚
5th fetch + lob.read	0	1+2 1️⃣	0+1	0
6th fetch returning nil	0	1 🔚	0	0
total	2	17	8	3

[cjbj]

Now that @kubo has done this really nifty rewrite (and informative tables!), you could also look at tuning the Oracle Net SDU, depending on how your LOBs are stored.

[gyut]

Very nice work, @kubo. I've not yet been able to successfully run the 2.2.6 version. I'll try again tomorrow.

/Users/yut/projects/ruby-oci8/lib/oci8.rb:107:in require': cannot load such file -- oci8lib_240 (LoadError)`

  /Users/yut/.rvm/gems/ruby-2.4.2/gems/ruby-oci8-2.2.6/lib:
  drwxr-xr-x   3 yut        96 Sep 10 19:31 dbd
  drwxr-xr-x  18 yut       576 Sep 10 19:31 oci8
  -rw-r--r--   1 yut      5563 Sep 10 19:31 oci8.rb
  -rwxr-xr-x   1 yut    239080 Sep 10 19:31 oci8lib_240.bundle
  -rw-r--r--   1 yut        15 Sep 10 19:31 ruby-oci8.rb

[gyut]

Thanks for all of your time and effort, @kubo!

I was able to test your latest version using Case C, and it does appear to be twice as fast (35ms vs 70ms) as your analysis predicts.

I'll next look more carefully at Case D, fetching LOBs as strings to see if that's viable.

Note: roles column is a CLOB.

User.select(User.column_names).all.to_a
User Load (33.3ms)
User Load (34.5ms)
User Load (34.9ms)
User Load (31.1ms)
User Load (31.1ms)
User Load (31.7ms)
User Load (33.6ms)
User Load (33.9ms)
User Load (33.8ms)
User Load (34.7ms)

User.select(User.column_names - %w(roles)).all.to_a
User Load (4.9ms)
User Load (5.1ms)
User Load (5.5ms)
User Load (5.2ms)
User Load (5.4ms)
User Load (5.2ms)

[kubo]

I got a new idea.

• E: CLOBs in a query & fetching LOB as string by another way.
  It internally fetches LOB as locators and converts them to string by using OCILobArrayRead.
  It gets an empty LOB as an empty string. (Case D gets it as nil.)
  Oracle-enhanced must be modified as case D.

Though I have not implemented yet, I estimate number of the network round trips as follows:

Case	number of network round trips
E	1 + ((total_rows + 1).to_f / prefetch_rows).ceil + (total_rows.to_f / prefetch_rows).ceil * number_of_lob_types

When a query includes three CLOB clolumns, number_of_lob_types is one. When a query includes two CLOB columns and one NCLOB column, number_of_lob_types is two.

	E
exec	1
1st fetch	1(:three:) + 1 (read data for three LOB locators internally)
2nd fetch	0
3rd fetch	0
4th fetch	1(:two: :end:) + 1 (read data for two LOB locators internally)
5th fetch	0
6th fetch returning nil	0
total	5

[anthony-tuininga]

@kubo, fetching LOBs as strings/bytes is going to perform better than using OCILobArrayRead() since no additional round trips are needed. The only advantage to OCILobArrayRead() (assuming that you have told Oracle to prefetch the LOB length) is that you can allocate the full size up front. If you don't tell Oracle to prefetch the LOB length then you'll either have to use one round-trip to get the length and another one to read, or you'll have to use multiple round-trips to perform the reads.

Regarding case D getting the result as nil, presumably you can check for that situation and return an empty string instead?

[kubo]

@anthony-tuininga

@kubo, fetching LOBs as strings/bytes is going to perform better than using OCILobArrayRead() since no additional round trips are needed.

Agree about the number of round trips. However case E is better than case D in the point of view about empty LOBs.

If you don't tell Oracle to prefetch the LOB length then you'll either have to use one round-trip to get the length and another one to read, or you'll have to use multiple round-trips to perform the reads.

I won't use any way you wrote in case E and I didn't in case C. lob.read in case B needs two round trips; the first is to get the length, the second is to read data up to the length. Buffers to store LOB data are allocated before the second round trip. That is a way in what you wrote. On the other hand, lob.read in case C needs only one without lob prefetching when the length is less than 4G. When it is equal to or more than 4G, it needs additional round trips. Buffers to store LOB data are allocated dynamically while data are read. (The 4G limit is for temporary LOBs. I formerly set the maximum value of unsigned 64-bit integer to char_amt(CLOB) or byte_amt(BLOB) of OCILobRead2() and it worked for LOB columns in tables. But I got ORA-22925 for temporary LOBs. So I set 4G instead.)

I guess that OCILobArrayRead() works similarly to OCILobRead2(), which is used inside of lob.read. If so, all data for more than one LOB locator are retrieved in one round trip without lob prefetching when there are no LOBs bigger than 4G similarly to lob.read in case C.

Regarding case D getting the result as nil, presumably you can check for that situation and return an empty string instead?

I can't. I got no difference between empty_clob() and NULL when they are fetched using SQLT_CHR. indp and rlenp (arguments passed to OCIDefineByPos()) are -1 and 0 respectively in both cases.

[anthony-tuininga]

Regarding case D getting the result as nil, presumably you can check for that situation and return an empty string instead?

I can't. I got no difference between empty_clob() and NULL when they are fetched using SQLT_CHR. indp and rlenp (arguments passed to OCIDefineByPos()) are -1 and 0 respectively in both cases.

Well, if you have to differentiate between NULL and empty_clob() then you're out of luck, I guess, since Oracle decided a very long time ago that an empty string is NULL. :-) I'm not sure there's much point differentiating between the two, though! Especially for the additional cost in code and the performance drain that is required to implement your suggested approach.

[kubo]

Well, if you have to differentiate between NULL and empty_clob() then you're out of luck, I guess, since Oracle decided a very long time ago that an empty string is NULL. :-)

Yes. I think that it is impossible as long as they are fetched using SQLT_CHR or so. I guess that an empty CLOB is converted to NULL on the server-side while converting CLOB datatype to string datatype.

[kubo]

I tried case E. It worked fine when using local connections.(1, 2) However it stalled when using TNS connections. It may not be available for production. I'll make minimum code in C to check the usage of OCILobArrayRead().

@gyut If you try it even though it may not work, do the followings.

1. Install ruby-oci8 in read_lob_array branch.

   $ git clone https://github.com/kubo/ruby-oci8.git
   $ cd ruby-oci8
   $ git checkout read_lob_array
   $ gem build ruby-oci8.gemspec
   $ gem install ./ruby-oci8-2.2.6.gem

2. Add the following code:

   OCI8::BindType::Mapping[:clob] = OCI8::BindType::StringFromCLOB
   OCI8::BindType::Mapping[:blob] = OCI8::BindType::StringFromBLOB
   

3. Commen out this line.

[kubo]

As far as I checked, OCILobArrayRead() stalls when (1) empty_clob() is read, (2) TNS connection is used and (3) Oracle server version is 18.3.0.0.0.

I tested it for following Oracle client and server combinations by using this test program.

Oracle client \ Oracle server	18.3.0.0.0	11.2.0.1.0
18.3.0.0.0	stall	work
11.2.0.1.0	stall	work

[cjbj]

@kubo ouch. Send some form of a testcase and I'll pass it onto the LOB developers.

/cc @anthony-tuininga

[kubo]

$ sqlplus username/password
SQL> create table clob_test_1755 (clob_col clob);
SQL> insert into clob_test_1755 values (empty_clob());
SQL> commit;
SQL> exit
$ wget https://gist.githubusercontent.com/kubo/d5aa0ad913a57a9322748b198a4dd5ce/raw/1399c7495b9cbec43f69b48f317a6ee108d220a4/lob-read-test.c
$ vi lob-read-test.c # Change username, password and database variables. database must be `//host/service` or TNS name.
$ gcc -g -Wall -o lob-read-test lob-read-test.c -I$ORACLE_HOME/rdbms/public -L$ORACLE_HOME/lib -lclntsh -Wl,-rpath,$ORACLE_HOME/lib
$ ./lob-read-test

lob-read-test should print two len=0, data='' lines. The first line is data read by OCILobRead2(). The second is by OCILobArrayRead(). However it prints only the first and stalls forever.

Environment:

• OS: Linux x86_64
• Oracle version: 18.3.0.0.0
• Non-cdb
• SQLNET.ALLOWED_LOGON_VERSION_SERVER=11 is in $ORACLE_HOME/network/admin/sqlnet.ora

[cjbj]

@kubo I logged Oracle bug 28675041 to track this. Thank you.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.