This discussion is archived
5 Replies Latest reply: Nov 29, 2012 7:32 AM by 587955 RSS

reading blob hangs

587955 Newbie
Currently Being Moderated
i'm facing a performance problem.
in my dev machine (win xp sp3 and jdk 1.6.0_35) this line of code takes 2 seconds

byte[] img = rs.getBytes(1); (rs is a java.sql.ResultSet)
the column is a blob with aprox. 1Mg

in the server (virtual machine centos 5.6 and 1.7.0-b147) the same line takes aprox. 84 seconds

in both machine i'm using ojdbc6.jar (11.2.0.3.0)
my db is Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
i also tested againts a Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 and it takes too long also.
i'm using glassfish 3.1.1 build12 in both machines

Any ideas of what is wrong?

thanks
  • 1. Re: reading blob hangs
    rp0428 Guru
    Currently Being Moderated
    >
    i'm facing a performance problem.
    in my dev machine (win xp sp3 and jdk 1.6.0_35) this line of code takes 2 seconds

    byte[] img = rs.getBytes(1); (rs is a java.sql.ResultSet)
    the column is a blob with aprox. 1Mg

    in the server (virtual machine centos 5.6 and 1.7.0-b147) the same line takes aprox. 84 seconds

    in both machine i'm using ojdbc6.jar (11.2.0.3.0)
    my db is Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
    i also tested againts a Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 and it takes too long also.
    i'm using glassfish 3.1.1 build12 in both machines

    Any ideas of what is wrong?
    >
    What is wrong is that if that is really the code you are using then you are not reading the blob data correctly.

    You need to get the BLOB locator from the result set and then you can call 'getBytes' on the locator. You don't call that method on the resultset itself.

    See 'Accessing and Manipulating BLOB and CLOB Data' in the JDBC Developer's Guide
    http://docs.oracle.com/cd/B19306_01/java.102/b14355/oralob.htm#sthref846
    Once you have your BLOB or CLOB locator in a table, you can access and manipulate the data to which it points. To access and manipulate the data, you first must select their locators from a result set or from a callable statement.
    
    After you select the locators, you can retrieve the BLOB or CLOB data. You will usually want to cast the result set to OracleResultSet, so that you can retrieve the data in oracle.sql.* format. After retrieving the BLOB or CLOB data, you can manipulate it however you want.
    
    This example is a continuation of the example in the previous section. It uses the SQL SELECT statement to select the BLOB locator from the table my_blob_table into a result set. The result of the data manipulation is to print the length of the BLOB in bytes.
    
    // Select the blob - what we are really doing here
    // is getting the blob locator into a result set 
    BLOB blob;
    cmd = "SELECT *  FROM my_blob_table";
    ResultSet rset = stmt.executeQuery (cmd);
    
    // Get the blob data - cast to OracleResult set to 
    // retrieve the data in oracle.sql format 
    String index = ((OracleResultSet)rset).getString(1);
    blob = ((OracleResultSet)rset).getBLOB(2);
    
    // get the length of the blob
    int length = blob.length();
    
    // print the length of the blob
    System.out.println("blob length" + length);
    
    // read the blob into a byte array 
    // then print the blob from the array
    byte bytes[] = blob.getBytes(1, length);
    blob.printBytes(bytes, length);
    You should review ALL of the doc sections related to BLOBs since you would typically access the blob content using streams rather than use the 'getBytes' method.
  • 2. Re: reading blob hangs
    587955 Newbie
    Currently Being Moderated
    i garantee to you that im reading the correct bytes. to prove that i can generate a pdf with an image represente by that bytes.

    i also test your code but the result is the same. it runs ok in my dev machine but it is very slow in the server.

    i also try the code:
    oracle.sql.BLOB blob = ((oracle.jdbc.OracleResultSet)rs).getBLOB(1);
    byte[] img = new byte[(int)blob.length()];
    InputStream blobInputStream = blob.getBinaryStream();
    blobInputStream.read(img);
    blob.free();

    but the line "InputStream blobInputStream = blob.getBinaryStream();" thakes to slow to execute...
  • 3. Re: reading blob hangs
    rp0428 Guru
    Currently Being Moderated
    >
    i garantee to you that im reading the correct bytes. to prove that i can generate a pdf with an image represente by that bytes.
    >
    I didn't say it wasn't. But your code won't work correctly at all on any version of Oracle prior to 10.2 since for those earlier versions the 'getBytes' method only returns the LOB locator. At a minimum you should include a comment/warning in code whenever you use version-specific features.

    I would begin troubleshooting by performing some Java and timing tests on both client and server. For 11g BLOB data is stored in Oracle segments rather than the file system but all the data has to be read from the file system of course.

    First test I would do, if possible is to use the same version of Java for the tests. If testing on the dev machine is easier then test there with version .7.0-b147. You don't mention if all of the Java parameters are identical: client/server jvm, min/max heap, etc. but they should be for the tests.

    Other tests would include a query that returns at least the 1MB of data you say the image is and iterating through the entire result set to make sure all data is fetched from the DB. See how those times compare.

    I suspect the major difference is due to either the Java 7 or the virtual environment. My bet is on the virtual environment; it may be throttlying the bandwidth for large data streams.
  • 4. Re: reading blob hangs
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated
    mfx wrote:
    it runs ok in my dev machine but it is very slow in the server.
    Exactly how long does it take?
    Exactly how much data are you reading?
    Exactly what does the network topology of the 'server' look like?
    Exactly how long does it take to read a record from the blob table where you do NOT return the blob?
  • 5. Re: reading blob hangs
    587955 Newbie
    Currently Being Moderated
    I didn't say it wasn't. But your code won't work correctly at all on any version of Oracle prior to 10.2 since for those earlier versions the 'getBytes' method only returns the LOB locator. At a minimum you should include a comment/warning in code whenever you use version-specific features.
    thank you for the explanation
    I suspect the major difference is due to either the Java 7 or the virtual environment. My bet is on the virtual environment; it may be throttlying the bandwidth for large data streams.
    I concluded that my java machine is in another datacenter from the oracle database. i suspect that firewall is analising sqlnet packages requested by the java machine. i passed the "problem" to the network guys

Legend

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