This discussion is archived
11 Replies Latest reply: Dec 23, 2012 9:02 PM by BillyVerreynne RSS

BLOB column inserts are slow

SreekeshavaS Newbie
Currently Being Moderated
Hi All,

Have a table with 7 columns where 4 columns are of Varchar2 type, 2 columns are of NUMBER type and 1 column is of type BLOB.

Myself inserting the values to the table from JAVA program. Insertion to VARCHAR2 and NUMBER type columns are very much fast. But insertion to BLOB column is dead slow(data to BLOB column values are about 10KB).

Please help me in this regard to insert BLOB values fastly.


Regards/Sreekeshava S
  • 1. Re: BLOB column inserts are slow
    Bawer Journeyer
    Currently Being Moderated
    Sreekeshava S wrote:
    Myself inserting the values to the table from JAVA program. Insertion to VARCHAR2 and NUMBER type columns are very much fast. But insertion to BLOB column is dead slow(data to BLOB column values are about 10KB).
    provide more info pls.
    where do you run 'insert blob'? (in java or pl/sql)
    how do you get BLOB data? (do you create it in java or is it a parameter passed from plsql?
    post a small sample code.
  • 2. Re: BLOB column inserts are slow
    avish16 Explorer
    Currently Being Moderated
    Please check if the following might help you -
    1) You can speed it up by breaking the problem by running the process in parallel.
    2) Also you can try altering the table to nologing.
    3) Also check the query execution plans and track out areas of concern for sample rows and share the same in case insertion is too slow.
  • 3. Re: BLOB column inserts are slow
    SreekeshavaS Newbie
    Currently Being Moderated
    Insert blob is from JAVA program. Inserting a Java object in BLOB column.
  • 4. Re: BLOB column inserts are slow
    BluShadow Guru Moderator
    Currently Being Moderated
    Sreekeshava S wrote:
    Insert blob is from JAVA program. Inserting a Java object in BLOB column.
    How many blobs are you inserting? Just one record or many?
    Is the Java running on the server or on a client computer across the network?

    We need more information, otherwise we can only guess.
  • 5. Re: BLOB column inserts are slow
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    avish16 wrote:
    Please check if the following might help you -
    1) You can speed it up by breaking the problem by running the process in parallel.
    2) Also you can try altering the table to nologing.
    3) Also check the query execution plans and track out areas of concern for sample rows and share the same in case insertion is too slow.
    Not feasible or sensible given the problem description.
  • 6. Re: BLOB column inserts are slow
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Sreekeshava S wrote:

    Myself inserting the values to the table from JAVA program. Insertion to VARCHAR2 and NUMBER type columns are very much fast. But insertion to BLOB column is dead slow(data to BLOB column values are about 10KB).
    What exactly is slow? Is the database server slow? Is the network slow? You are sending a lot more data from the client to the server when inserting LOBs.

    More data means more work. More work for the client to ship data. More work for the network to transport the data. More work for the server to process that data.

    So which of these moving parts is the major contributor to slow performance?

    You have identified a symptom - slow performance.

    You now have to identify the actual problem. Where is it slow?

    Once you have identified that, you can look at the why it is slow, and the how to fix it.
  • 7. Re: BLOB column inserts are slow
    SreekeshavaS Newbie
    Currently Being Moderated
    Running JAVA program in the same server as that of DB. And inserting 250 records/ sec(during peak load and 50 records/sec during normal load). where each record is having size of 10K(Blob column size).
  • 8. Re: BLOB column inserts are slow
    SreekeshavaS Newbie
    Currently Being Moderated
    Running JAVA program in the same server as that of DB. And inserting 250 records/ sec(during peak load and 50 records/sec during normal load). where each record is having size of 10K(Blob column size).
  • 9. Re: BLOB column inserts are slow
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Sreekeshava S wrote:
    Running JAVA program in the same server as that of DB.
    Connecting how? IPC? TCP? Dedicated server? Shared server?

    Calling Oracle how? Doing a SQL statement prepare per insert? Reusing the SQL cursor handle? Binding variables?
    And inserting 250 records/ sec(during peak load and 50 records/sec during normal load). where each record is having size of 10K(Blob column size).
    And what is slow? You have NOT yet provided ANY evidence that points to the actual INSERT being slow.

    As I have already explained, there are a number of layers from client to server - and any, or all of these, could be contributing to the problem.

    Use your web browser and look up what instrumentation is. Apply it. Instrument your code. On the client. On the server. So you have evidence (call stats and metrics) to use to determine what and where the performance problem is. And not have to guess - and like most developers point your finger at the database in the false belief that your client code, client design, and client usage of the database, are all perfect.
  • 10. Re: BLOB column inserts are slow
    SreekeshavaS Newbie
    Currently Being Moderated
    Removed the Blob column. broken down BLOB contents and stored in to VARCHAR2
  • 11. Re: BLOB column inserts are slow
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    And how did that identify and address the problem?

    You've implemented a workaround. And you are still very much ignorant as to why using a LOB was slow.

    What have you learned? Nothing.

    Would you be able to address a similar performance problem in future, where you cannot implement a varchar2 hack/work-around. No.

    Problem. 1. You. 0.

    Not a good situation to be in as a programmer.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points