This discussion is archived
1 Reply Latest reply: Aug 17, 2012 5:56 AM by 929890 RSS

Performance problem with temporary LOBs

929890 Newbie
Currently Being Moderated
I have written a Java Stored Procedure to load a bunch of XML documents from an Oracle database 11g 2.0.3 which basically works well.
This Java Sored Procedure puts all documents into an temporary LOB and returns it to the caller. After working with the LOB I invoke BLOB.freeTemporary((BLOB)myBlob). (Is this necessary at all? At this point I am on the client side and not in the Java Stored Procedure.)
Finally, I have to assemble a new LOB out of the contents a bunch of other LOBs.

When calling this Java Stored Procedure two or three times after another the time to get the LOB from the Java Stored Procedure increases:
1. call: ~10 minutes
2. call: ~25 minutes
...
(There are a lot of XML documents.)

Here's the code of the Java Stored Procedure. Am I doing anything wrong???
     /**
      * Creates and returns a temporary Blob and fills it with ALL XML documents of a container.
      * 
      * @param containerName
      *           The name of the container to get ALL XML documents from. 
      * @return
      *           A Blob object filled with ALL XML dcouments of table <code>container</code>.
      * @throws Exception
      */
     public static Blob createTmpBlobForContainerDocs(String containerName) throws Exception {
//          BLOB blob = BLOB.createTemporary(conn, /*load into cache*/true, /*duration*/BLOB.DURATION_SESSION);
          BLOB blob = BLOB.createTemporary(conn, /*load into cache*/false, /*duration*/BLOB.DURATION_CALL);
          blob.open(BLOB.MODE_READWRITE);
          OutputStream outputStream = blob.setBinaryStream(1);
          
          // get all document names...
          List<String> xmlDocNameList = new ArrayList<String>();
          String query = "SELECT id FROM " + containerName;
          OracleStatement stmt = (OracleStatement) conn.createStatement();
          ResultSet rs = stmt.executeQuery(query);
          while( rs.next() ){
               xmlDocNameList.add(rs.getString(1));
          }
          
          for( String documentName : xmlDocNameList ) {
               XMLType xmlDoc = loadObjectInternal(documentName, containerName);           // SELECT xml FROM ? WHERE id=?
               InputStream is = xmlDoc.getBlobVal(CharacterSet.UTF8_CHARSET).getBinaryStream();
               byte[] buf = new byte[blob.getBufferSize()];
               int byteRead = 0;
               while( (byteRead = is.read(buf)) != -1 ){
                    outputStream.write(buf, 0, byteRead);
               }
               outputStream.flush();
               is.close();
          }
          outputStream.close();
          blob.close();
          
          return blob;
     }
  • 1. Re: Performance problem with temporary LOBs
    929890 Newbie
    Currently Being Moderated
    I found the problem by myself.
    Although the XMLType variable was creatred locally in the for loop, I have to close() it and set it to null so that it can be garbage collected.
    for( String documentName : xmlDocNameList ) {
                   XMLType xmlDoc = loadObjectInternal(documentName, containerName);
                   InputStream is = xmlDoc.getBlobVal(CharacterSet.UTF8_CHARSET).getBinaryStream();
                   byte[] buf = new byte[clob.getBufferSize()];
                   int byteRead = 0;
                   while( (byteRead = is.read(buf)) != -1 ){
                        outputStream.write(buf, 0, byteRead);
                   }
                   outputStream.flush();
                   is.close();
                   xmlDoc.close();               // !!!!!!!!!!!!!!!!!!!!!!!
                   xmlDoc = null;                // !!!!!!!!!!!!!!!!!!!!!!!
              }

Legend

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