performance for CLOB column type is very slow

[sealor]

We noticed a performance problem when using CLOB columns versus LONG columns. This problem does not occur with JDBC drivers.

Test scenario:

• table with one CLOB column
• 8192 rows with one column containing 'abc'
• querying with cx_Oracle: 31186 ms
• querying with OJDBC: 359 ms

I already wrote a StackOverFlow question where you can find the exact code I used:

https://stackoverflow.com/questions/45571421/how-can-i-improve-query-performance-for-clob-and-long-values-in-oracle-db-cx-or

1. What is your version of Python? Is it 32-bit or 64-bit?
   Python 3.6.2 (default, Jul 31 2017, 10:36:59) on Linux

2. What is your version of cx_Oracle?
   cx_Oracle-5.3

3. What is your version of the Oracle client (e.g. Instant Client)? How was it installed? Where is it installed?
   Oracle client Release 11.2.0.4.0

4. What is your version of the Oracle Database?
   Oracle Database 12c Enterprise Edition 12.1.0.2.0 64bit

5. What is your OS and version?
   Linux - SLES11

6. What compiler version did you use? For example, with GCC, run gcc --version.
   gcc 4.3.4

7. What environment variables did you set? How exactly did you set them?

8. What exact command caused the problem (e.g. what command did you try to
   install with)? Who were you logged in as?
   normal user (not root)

9. What error(s) you are seeing?
   very slow query performance with big datasets containing column "CLOB"

[anthony-tuininga]

I'll take a look at this next week. Just to confirm: the 8192 rows all contain the same value "abc"?

[sealor]

Yes, all rows contain the exact same text 'abc'.
Thank you for your support!

[anthony-tuininga]

To get the same performance as LONG, you need to tell cx_Oracle to fetch the CLOBs in that fashion. You can look at this sample.

In your code, I added this method:

def output_type_handler(self, cursor, name, defaultType, size, precision, scale):
        if defaultType == cx_Oracle.CLOB:
            return cursor.var(cx_Oracle.LONG_STRING, arraysize = cursor.arraysize)

Then, after the connection to the database has been created, I added this code:

db.outputtypehandler = self.output_type_handler

With those changes, the performance is virtually identical.

Note that behind the scenes, cx_Oracle is using dynamic fetching and allocation. This method works very well for small CLOBs (where small generally means a few megabytes or less). In that case, the database can send the data directly, whereas when LOBs are used, just the locator is returned to the client and then another round trip to the database is required to fetch the data. As you can imagine, that significantly slows down the operation, particularly if the database and client are separated on the network!

[sealor]

Thank you!

Does this also work for very big CLOB data?
Is there any limit?

[anthony-tuininga]

You're welcome.

There is no technical limit. But the code is optimised for smaller CLOBs so may not perform as well with larger CLOBs. Note as well that you require a contiguous block of memory so this could become a problem with larger CLOBs.

[elfunits]

Anthony, there actually seems to be a limit. cx_Oracle, by default sets buffer size to 128MB for LONG_BINARY, which, with a large enough arraysize will blow out of memory very quickly, because, it seems, the space is pre-allocated. So, you actually have to know what the largest blob size is in the database to properly set the size.

On a related note, I'm running into the same performance issue on insert/update. I'm using sqlalchemy ORM over cx_Oracle, and sqlalchemy sets input sizes appropriately. I thought I could use an inputtypehandler in a similar fashion, but its arguments don't provide enough information for me to determine if the input column is a BLOB (unlike the output handler which passes in the type). Is there a way around it?

It's also not clear to me why LOBs need to be fetched separately. Oracle doesn't seem to mention this in their documentation. In fact the official documentation says that the first 4KB of LOB-type values are stored in-line with other row data. The performance implication of doing a round-trip per row are quite significant.

LONG_BINARY itself is a deprecated type. So, is there no way to tell cx_Oracle to load LOB data in-line without resorting to output handlers and deprecated types?

[anthony-tuininga]

Anthony, there actually seems to be a limit. cx_Oracle, by default sets buffer size to 128MB for LONG_BINARY, which, with a large enough arraysize will blow out of memory very quickly, because, it seems, the space is pre-allocated. So, you actually have to know what the largest blob size is in the database to properly set the size.

The default for LONG and LONG RAW for cx_Oracle 5.x is 128 KB, and you do have to know in advance what your largest value is going to be. With cx_Oracle 6.x, however, this is not true. There is no default size and there is no technical limit, beyond the fact that it has to fit in contiguous memory.

On a related note, I'm running into the same performance issue on insert/update. I'm using sqlalchemy ORM over cx_Oracle, and sqlalchemy sets input sizes appropriately. I thought I could use an inputtypehandler in a similar fashion, but its arguments don't provide enough information for me to determine if the input column is a BLOB (unlike the output handler which passes in the type). Is there a way around it?

You can, in fact, simply pass in very long strings or binary data without having to create a CLOB or BLOB. cx_Oracle handles that just fine without the need for input type handlers or the like.

It's also not clear to me why LOBs need to be fetched separately. Oracle doesn't seem to mention this in their documentation. In fact the official documentation says that the first 4KB of LOB-type values are stored in-line with other row data. The performance implication of doing a round-trip per row are quite significant.

The assumption is that you are using CLOB/BLOB for larger values that need to be handled in smaller pieces. That has become less relevant over time, and there are many cases where 4000 (now 32,767) bytes is nowhere near big enough, but where a few megabytes is generally sufficient. In those cases, retrieving the LOB locator and then fetching from it is significantly slower. For these you want to use an output type handler to tell Oracle to fetch inline, rather than as a LOB locator. You can set an output type handler directly at the connection level so that it encompasses all cursors created by that connection. It might be worth adding a flag to the connection creation function that turns this feature on without the need for an output type handler, then later on (cx_Oracle 7) make this the default behaviour.

LONG_BINARY itself is a deprecated type. So, is there no way to tell cx_Oracle to load LOB data in-line without resorting to output handlers and deprecated types?

In cx_Oracle 6.x you can specify the type as "str" or "bytes" and as long as the size you specify exceeds 32KB dynamic fetching will take place. The types cx_Oracle.LONG_STRING and cx_Oracle.LONG_BINARY simply do this for you automatically. The actual deprecated types are no longer used.

[mikecheong]

Thanks for the idea. I actually have some large data in CLOB and when I tried the above method I got the following error:
DatabaseError: column at array pos 49 fetched with error: 1406.
Can anyone please help?

[cjbj]

@mikecheong Can you open a new issue and include a python script that runs, and also the SQL to create the tables and data?

Also review https://github.com/oracle/python-cx_Oracle/blob/master/samples/ReturnLobsAsStrings.py

[mikecheong]

Thanks @cjbj. I tried the script you posted on my Oracle server and I got the same error as before. I will start a new issue when I get the chance. Many thanks~