Oracle PL/SQL: Performance of CLOB data type in PL/SQL

Question

If I use many CLOB variables in the PL/SQL stored procedure to store many long length string , are there any performance issues? Is the length of the CLOB is also variable? Are there any known limitations/disadvantages for CLOB instead using varchar2 and long?

Answer 1

CLOBs are variable in length, yes. The upper limit varies according to which version of Oracle you are on and your database block size. For 11G the limit is "4G * value of DB_BLOCK_SIZE parameter" (from 11G PL/SQL Language Reference). VARCHAR2 values are limited to 32767 bytes in PL/SQL.

I don't have any definitive information on the relative perforance of CLOB vs. VARCHAR2 in PL/SQL, and a brief Google didn't return anything useful either. However I would strongly suspect that VARCHAR2 performs better than CLOB in general (for data that can be stored in either), since it is simpler. You could easily set up a test to prove this right or wrong by writing the same simple program once with VARCHAR2 and once with CLOB, and running each 1000s of times and comparing the total elapsed time. Tom Kyte's Runstats utility is a great help here, and shows other differences between 2 approaches in terms of Oracle resources.

My advice would be: if your data will exceed 32K then you must use CLOB; if not then don't use CLOB, use VARCHAR2.

NB: if CLOB were better than VARCHAR2 for all sizes of character data, Oracle would probably deprecate VARCHAR2, as they did with the LONG datatype - but they haven't.

Answer 2

CLOBs are substantially more expensive (slower) and more difficult to work with than VARCHAR2. If you needlessly use CLOB instead of VARCHAR2 you are suffering a performance penalty that is measurable.

In the end you should as was noted earlier, use the right datatype for the right job.

1) if you are storing 4000 bytes or less in the database, use VARCHAR2 otherwise use CLOB.

2) if you are storing 32k bytes or less in PLSQL use VARCHAR2 otherwise use CLOB.

It is all about what you need. If your data can exceed a VARCHAR2 limit then use CLOB otherwise use VARCHAR2.

As for clear negatives, consider that when working with LOB datatypes of any kind, a LOB can be either TEMPORARY (never stored in a real row in a table) or PERMANENT (stored in a real row in a table). If you dynamically build a CLOB in PLSQL and pass that CLOB out to JAVA or someother external client then you have created a temporary CLOB and pushed it outside the control of the Oracle database. This means your code that accepts the temporary CLOB is now responsible for releasing the CLOB when it is done with it. Your code must have a method native to its environment that you can use for this purpose. If you fail to do so, you temporary storage tablespace will eventually fill and your database will stall (stop working). It won't crash, it just does not work. A reboot will likely be needed. The problem is that many development tools (many versions of java for example) do not have the needed library call.

Good luck.

Answer 3

CLOB variables are basically pointers, so they're not inherently slow. The problem is accessing the content of the CLOB. In my experience:

• Relying on Oracle's implicit CLOB <-> VARCHAR2 conversions tends to be slow.
• Working with the dbms_lob package is reasonably performant. Just do this in a loop:
  1. Get a chunk of data from your CLOB into a buffer
  2. Use the data from your buffer. Maybe transform it into another chunk of data.
  3. Posibly write the transformed chunk of data into a destination CLOB.