Frequently Asked Questions about LOBs, 11 of 12

Performance

What Can We Do To Improve the Poor LOB Loading Performance When Using Veritas File System on Disk Arrays, UNIX, and Oracle?

Question 1

We were experiencing a load time of 70+ seconds when attempting to populate a BLOB column in the database with 250MB of video content. Compared to the 15 seconds transfer time using the UNIX copy, this seemed unacceptable. What can we do to improve this situation?

The BLOB was being stored in partitioned tablespace and NOLOGGING, NOCACHE options were specified to maximize performance.

The INITIAL and NEXT extents for the partition tablespace and partition storage were defined as 300M, with MINEXTENTS set to 1 in order to incur minimal overhead when loading the data.

CHUNK size was set to 32768 bytes - maximum for Oracle.

INIT.ORA parameters for db_block_buffers were increased as well as decreased.

All the above did very little to affect the load time - this stayed consistently around the 70-75 seconds range suggesting that there was minimal effect with these settings.

Answer 1

First examine the I/O storage devices and paths.

Question 2

I/O Devices/Paths

4 SUN AS5200 disk arrays were being used for data storage, i.e., the devices where the BLOB was to be written to. Disks on this array were RAID (0+1) with 4 stripes of (9+9). Veritas VxFS 3.2.1 was the file system on all disks.

In order to measure the effect of using a different device, the tablespace for the BLOB was defined on /tmp. /tmp is the swap space.

Needless to say, loading the BLOB now only took 14 seconds, implying a data transfer rate of 1.07GIG per minute - a performance rating as close, if not higher than the UNIX copy!

This prompted a closer examination of what was happening when the BLOB was being loaded to a tablespace on the disk arrays. SAR output indicated significant waits for I/O, gobbling up of memory, high CPU cycles and yes, the ever-consistent load time of 70 seconds. Any suggestions on how to resolve this?

Answer 2

Install the Veritas QuickIO Option!

Obviously, there seems to be an issue with Veritas, UNIX, and Oracle operating together. I have come up with supporting documentation on this. For acceptable performance with Veritas file-system on your disk arrays with Oracle, we recommend that you install the Veritas QuickIO option.

A Final Note:

Typically when customers complain that writing LOBs is slow, the problem is usually not how Oracle writes LOBs. In the above case, you were using Veritas File System, which uses UNIX file caching, so performance was very poor.

After disabling UNIX caching, performance should improve over that with the native file copy.

Is There a Difference in Performance When Using DBMS_LOB.SUBSTR Versus DBMS_LOB.READ?

Question

Is there a difference in performance when using DBMS_LOB.SUBSTR vs. DBMS_LOB.READ?

Answer

DBMS_LOB.SUBSTR is there because it's a function and you can use it in a SQL statement. There is no performance difference.

Are There Any White Papers or Guidelines on Tuning LOB Performance?

Question

I was wondering if anyone had any white papers or guidelines on tuning LOB performance.

Answer

Chapter 9, "LOBS: Best Practices" in this manual, discusses LOB performance issues. Also see "Selecting a Table Architecture" in Chapter 7, "Modeling and Design".

When Should I Use Chunks Over Reading the Whole Thing?

Question

When should I use chunks over reading the whole thing?

Answer

If you intend to read more than one chunk of the LOB, then use OCILobRead with the streaming mechanism either via polling or a callback. If you only need to read a small part of the LOB that will fit in one chunk, then only read that chunk. Reading more will incur extra network overhead.

Is Inlining the LOB a Good Idea and If So When?

Question

Is inlining the LOB a good idea. If so, then when?

Answer

Inlining the LOB is the default and is recommended most of the time. Oracle8i stores the LOB inline if the value is less than approximately 4K thus providing better performance than storing the value out of line. Once the LOB grows larger than 4K, the LOB value is moved into a different storage segment but meta information that allows quick lookup of the LOB value is still stored inline. So, inlining provides the best performance most of the time.

However, you probably don't want to inline the LOB if you'll be doing a lot of base table processing such as full table scans, multi-row accesses (range scans) or many updates/selects of columns other than the LOB columns.

How Can I Store LOBs >4Gb in the Database?

Question

How can I store LOBs that are >4Gb in the database?

Answer

Your alternatives for storing >4Gb LOBs are:

Compressing the LOB so that it fits in 4Gb
Breaking up the LOB into 4Gb chunks as separate LOB columns or as separate rows.

Why is Performance Affected When Temporary LOBs are Created in a Called Routine?

Question

We have a nasty performance problem that I have isolated to the creation of temporary LOBs in a called routine. The two procedures below demonstrate the problem.

When RUNLOB() is called with createlob=FALSE (its temporary LOB is created, but the inner routine's is not), it executes in less than a second.

When run with createlob=TRUE (both outer and inner routine LOBs are created), it takes about 30 seconds and is linear with respect to the size of the loop. Here's a log:

SQL> set serveroutput on size 20000;
SQL> execute runlob(FALSE);
Start time (seconds): 52089
End time (seconds): 52089
PL/SQL procedure successfully completed.

SQL> execute runlob(TRUE);
Start time (seconds): 52102
End time (seconds): 52131
PL/SQL procedure successfully completed.

This is really killing performance of DDL creation in our API. Any ideas what's happening here?

CREATE OR REPLACE PROCEDURE lob(createlob BOOLEAN)
 IS
 doc     CLOB;
 BEGIN
   IF createlob THEN
     DBMS_LOB.CREATETEMPORARY(doc, TRUE);
     DBMS_LOB.FREETEMPORARY(doc);
   END IF;
 END;
 /
 CREATE OR REPLACE PROCEDURE RUNLOB(createlob BOOLEAN DEFAULT FALSE) AS
 doc    CLOB;
 BEGIN
   dbms_output.put_line('Start time (seconds):
 '||to_char(sysdate,'SSSSS'));
   FOR i IN 1..400 LOOP
     DBMS_LOB.CREATETEMPORARY(doc, TRUE);
     lob(createlob);
     DBMS_LOB.FREETEMPORARY(doc);
   END LOOP;
   dbms_output.put_line('End time (seconds):
 '||to_char(sysdate,'SSSSS'));
 END;
 /

Answer

In your test case, the difference between creating temporary LOBs in RUNLOB() and in LOB() is that:

In RUNLOB(), there's a duration for the function frame and all the new temporary LOBs are linked from a single duration state object (DSO) created with the first temporary LOB in the function.
In LOB(), however, every time the temporary LOB is created Oracle allocates a new DSO since the function frame duration is new.

kdlt_add_dso_link() is an expensive operation compared to the rest of the temporary LOB creation cycles in kdlt? The overhead is from (de)allocating a DSO for LOB(). kdlt_add_dso_link() needs to allocate a new DSO, for its associated memory allocation and control structures initialization. The extra code path accounts for the cost.

To avoid new DSO creation, can you use the workaround of a package variable tmplob locator in LOB() instead of a local one? Please try the following modified script. The performance hit is no longer there with this script.

create or replace package pk is
   tmplob clob;
 end pk;
/
 
 CREATE OR REPLACE PROCEDURE lob(createlob BOOLEAN)
 IS
 doc     CLOB;
 BEGIN
   IF createlob THEN
     DBMS_LOB.CREATETEMPORARY(pk.tmplob, TRUE);
     DBMS_LOB.FREETEMPORARY(pk.tmplob);
     null;
  END IF;
 END;
 /
 
 CREATE OR REPLACE PROCEDURE RUNLOB(createlob BOOLEAN DEFAULT FALSE) AS
 doc    CLOB;
 BEGIN
   dbms_output.put_line('Start time (seconds):
 '||to_char(sysdate,'SSSSS'));
   FOR i IN 1..400 LOOP
     DBMS_LOB.CREATETEMPORARY(doc, TRUE);
     lob(createlob);
     DBMS_LOB.FREETEMPORARY(doc);
   END LOOP;
   dbms_output.put_line('End time (seconds):
 '||to_char(sysdate,'SSSSS'));
 END;
 /

Response

Thank you. We should be able to use package-scoped temporary LOBs almost everywhere we currently have function-local LOBs.