|Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 1 (9.0.1)
Part Number A88879-01
Frequently Asked Questions about LOBs, 11 of 12
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.
First examine the I/O storage devices and 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?
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.
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 vs. DBMS_LOB.READ?
DBMS_LOB.SUBSTR is there because it's a function and you can use it in a SQL statement. There is no performance difference.
I was wondering if anyone had any white papers or guidelines on tuning LOB performance.
When should I use chunks over reading the whole thing?
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. If so, then when?
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 that are >4Gb in the database?
Your alternatives for storing >4Gb LOBs are:
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.
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; /
In your test case, the difference between creating temporary LOBs in RUNLOB() and in LOB() is that:
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; /
Thank you. We should be able to use package-scoped temporary LOBs almost everywhere we currently have function-local LOBs.