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 7, "Modeling and Design" in this manual, has a short section called "Best Performance Practices". Also see "Selecting a Table Architecture" in Chapter 7.

There was a web site with some information about LOB Performance but it is out of date. Check back periodically as there is a plan to update it!

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.