Sybase TEXT vs Oracle CLOB performance

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? 开发者_Go百科

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.

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.

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.