Performance of retrieval of BLOB/CLOB values
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Performance of retrieval of BLOB/CLOB values

  1. #1
    Join Date
    May 2005
    Posts
    2

    Performance of retrieval of BLOB/CLOB values

    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

  2. #2
    Join Date
    May 2005
    Posts
    2
    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;

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    trace the query and see what you are waiting on

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width