Sybase TEXT vs Oracle CLOB performance

Question

We're in the process of converting our database from Sybase to Oracle and we've hit a performance problem. In Sybase, we had a TEXT field and replaced it with a CLOB in Oracle.

This is how we accessed the data in our java code:

while(rs.next()) {
  String clobValue = rs.getString(1);  // This takes 176ms in Oracle!
  .
  .
}

The database is across the country, but still, we didn't have any performance problems with Sybase and its retrieval of TEXT data.

Is there something we can do to increase this performance?

score 4 · Answer 1 · answered Jul 19 '11 at 17:05

4

By default, LOBs are not fetched along with the table data and it takes an extra round-trip to the database to fetch them in getString.

If you are using Oracle's .NET provider, you may set InitialLOBFetchSize in the data reader settings to a value large enough to accommodate your large objects in memory so they could be fetched in all their entirety along with the other data.

answered Jul 19 '11 at 17:05

Quassnoi

413,100
91
616
614

Unfortunately we aren't using this framework. We are using JDBC on Solaris. – wsaxton Jul 21 '11 at 20:01

score 3 · Accepted Answer · answered Jul 19 '11 at 18:30

Some other options:

Are the LOB columns being stored in-line (in the data row) or out-of-line (in a separate place)? If the LOB columns tend to be small (under 4k in size), you can use the ENABLE STORAGE IN ROW clause to tell Oracle to store the data in-line where possible.

If your LOBs are larger and frequently used, are they being stored in the buffer cache? The default in 10g is that LOBs are NOCACHE, meaning each i/o operation against them involve direct reads to the database, a synchronous disk event, which can be slow. A database trace would reveal significant waits on direct path read / direct path write events.

This chapter of the Oracle Application Developer's Guide - Large Objects would be valuable reading.

Flagging this as the answer because it came the closest to providing information that MAY have led to an answer :) — wsaxton, Jul 22 '11 at 02:08

score 0 · Answer 3 · answered Jul 22 '11 at 02:07

We decided to take a different approach which will allow us to ignore clob performance.

Our current code (I didn't write it!) queries a table in the database and retrieves all of the information in the table, including the clobs, even though it wasn't quite necessary to retrieve them all @ the time. Instead, we created another field with the first 4k characters in a varchar and query that instead. Then, when we need the full clob, we query it on an individual basis, rather than all clobs for all records.

Sybase TEXT vs Oracle CLOB performance

3 Answers3