Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
What is the app written in? Do you know how to trace sessions? –  Phil Mar 8 '13 at 12:38
    
Can you post the explain plan and autotrace stats please? –  Chris Saxon Mar 8 '13 at 12:40
    
@Phil The app's written in Pascal using Delphi. I believe it uses the Direct Oracle Access (DOA) component allroundautomations.com/doa.html for database access. I don't know how to trace a session! –  user1578653 Mar 8 '13 at 12:43

1 Answer 1

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.

share|improve this answer
    
Thanks for your answer - do you know where I can find the log you're talking about? –  user1578653 Mar 8 '13 at 16:12
    
In udump (user dump destination) on DB server's filesystem. This is a DBA's stuff. If you have DBA in your company consult it with them. –  ibre5041 Mar 8 '13 at 16:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.