Possible reasons for slow BLOB updates in Oracle

blobdatabase-designNetworkoracle

We have an application that transfers .bmp images as BLOBs to an Oracle table. One of the users of the app has complained that it is taking 2-3 seconds to transfer each image. Each image is ~1.2Mb. I've run some tests on our database and there is some lag, but more like 0.8 seconds. The user has an Oracle 9i R2 database. I've run tests on 11g, 10g and 9i and there doesn't seem to be any performance difference between them for me, so I'm guessing it's not because they're running an older database.

Here is the query that's being used:

UPDATE RESULTS_DATA 
     SET IMAGE_BLOB=:myBlob 
WHERE MEASUREMENT_INDEX=:mIndex 
  AND BATCH_ID=:batchId 
  AND SYSTEM_ID=:sysId

There are indexes on SYSTEM_ID and BATCH_ID but not MEASUREMENT_INDEX. The problem doesn't appear to get worse when you have large amounts of data in the table.

Can anyone think of a reason that BLOB transfers would be so slow? Is there any way of optimising the table structure to make writing to BLOBs more efficient? Could it just be a case of a slow network? If so, how could I diagnose this?

Thanks!

Here's the table structure:

CREATE TABLE RESULTS_DATA (
SYSTEM_ID       VARCHAR2(15),
BATCH_ID        VARCHAR2(15),
USER_ID         VARCHAR2(40),
MEASUREMENT_INDEX   NUMBER,
IMAGE_BLOB      BLOB,
CONSTRAINT DATA_PKEY PRIMARY KEY (MEASUREMENT_INDEX,SYSTEM_ID,BATCH_ID))
TABLESPACE MEASUREMENT_DATA
LOB (IMAGE_BLOB) STORE AS (TABLESPACE IMAGE_DATA);

CREATE INDEX RESULTS_INDEX ON RESULTS_DATA(SYSTEM_ID,BATCH_ID) TABLESPACE MEASUREMENT_DATA_INDEX;

Here's the output of EXPLAIN PLAN from Oracle SQL Developer:
enter image description here

Best Answer

There are various tricks for some performance improvements in the OCI library(for example chunk size settings). But since you do not have direct access to OCI you should contact you DOA vendor.

If you want to trace user's session from begging you have to create a "LOGON TRIGGER ON SCHEMA" and then call something like dbms_support.start_trace(true) in this trigger. Or you can set tracing in other session using dbms_monitor.

You should first "prove" that the root cause is BLOB handling. Server side log should give you some numbers. (Just beware - it's not easy to read).

PS: there is also a way how you can register your "interceptor" library in OCI.dll. This will give detailed tracing for the client's side and will tell you how your DAO is working. But this really requires some Oracle experience.

Related Question