Performance of retrieval of BLOB/CLOB values

[kannurih]

Hello,

We are migrating Informix database to Oracle database. The Informix database has some Images and large text files which are migrated to Oracle successfully.

But, when we are trying to retrieve the images from the Oracle database, it is taking more time (10%) when compared with Informix database.

Please let us know how can we improve this performance of retrieving BLOB data.

Appreciate any help on this issue.

Thanks and Regards,
Vamsi Mohan Harish

[kannurih]

Just to add this, we are testing the performance of the select statement through a Java application. And below are the details of the Table (with storage parameters).

Please note that the BLOB is moved to a seperate tablespace for better performance. The Block Size for BLOB is increased from 8K to 16K and also the Chunk size of Tablespace for images is set to 32K and CACHE is set. We had done these changes to improve BLOB insertion, and the results of BLOB insertion after doing these changes is amazing as it is taking less than 10 milli seconds for inserting images.

Average size of Image is around 40 KB.

CREATE TABLE S_IMAGE_IMAGE
(
Z_IM_IMAGE_NUM NUMBER(10),
Z_IM_IMAGE BLOB
)
TABLESPACE ICSCAPTURE_D
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
LOB (Z_IM_IMAGE) STORE AS
( TABLESPACE ICSCAPTURE_IMG
ENABLE STORAGE IN ROW
CHUNK 32K
PCTVERSION 10
CACHE
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
)
CACHE
NOPARALLEL;


CREATE UNIQUE INDEX IX1_S_IMAGE_IMAGE ON S_IMAGE_IMAGE
(Z_IM_IMAGE_NUM)
LOGGING
TABLESPACE ICSCAPTURE_X
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;

[davey23uk]

trace the query and see what you are waiting on

[tamilselvan]

Do you know the average size the BLOB data?

On what basis did you choose 16k block size for BLOB data? Why not 32K since you choose chunk size of 32K?

Tamil