I'm not sure if you've noticed but I have released a new version of
cx_Oracle that contains most of the features you requested. Let me
know if what I have provided is still not sufficient. Thanks.
On 5/30/07, Anthony Tuininga <anthony.tuininga@...> wrote:
> Hi all,
> I took a look at the OCI documentation for LOBs and discovered a few
> things. I have also added the following methods to LOB values in order
> to aid in improving performance of reading/writing LOB values in
> open() - Open the LOB for writing. Use this in order to "batch"
> writes. If you don't do this, the LOB is opened and closed for each
> write operation and any associated indexes are updated each time. Note
> that for "default" LOBs this does not have any appreciable difference
> in performance.
> close() - Close the LOB after writing to it in chunks. This will
> update any associated indexes and is necessary prior to a commit.
> isopen() - Return a boolean indicating if the LOB is opened.
> getchunksize() - Returns the chunk size for the LOB. If you are going
> to read/write in chunks, read/write in chunks that are a multiple of
> this size as the performance difference is significant.
> BTW, these can be accessed via the PL/SQL package dbms_lob() as well.
> I'm not sure when I will release a new version of cx_Oracle as I have
> other things I'd like to see done as well. If you need/want this
> sooner, let me know. Thank you.
> On 5/11/07, Henning von Bargen <H.vonBargen@...> wrote:
> > Hello cx_Oracle users,
> > recently our clients complained about poor performance
> > when they tried to store BLOBs in the database.
> > This is for LOBs with size >> 1MB, eg. a 20 MB reports output.
> > I wrote a little test program (which I don't post here
> > because it uses our applications document management packages)
> > and could track it down.
> > With cx_Oracle, to upload data to a BLOB, the code used in
> > our program looks like this (stream is a file object):
> > LOB_BLOCKGROESSE =3D 16384
> > curs.execute("select DOKUMENT from TDODA where DODA_ID =3D :DODA_ID=
for update of DOKUMENT",
> > DODA_ID=3Dtemp_id)
> > for row in curs:
> > offset =3D 0
> > lob =3D row
> > while True:
> > chunk =3D stream.read(LOB_BLOCKGROESSE)
> > if chunk =3D=3D "":
> > break
> > lob.write(chunk, offset+1)
> > offset +=3D len(chunk)
> > This code took about 20 minutes to upload a 40 MB file.
> > When we changed the constant LOB_BLOCKGROESSE to 1024*1024 (1MB),
> > the time needed could be reduced to less than 100 seconds.
> > I found in the OCI documentation that there are API to explicitly
> > open and close LOBs with OCILobOpen and OCILobClose.
> > If you don't do it, then Oracle does an implicit open and close
> > around every OCILobWrite operation and refreshed its internal
> > index structures and so on.
> > Obviously, it is this overhead that caused the poor performance.
> > Perhaps it can be mentioned in the documentation that one should
> > prefer few calls with large chunks when calling write() instead
> > of more calls with small chunks.
> > A few more OCI-API should be exposed to Python:
> > The implementation should go in ExternalLobVar.c ?
> > * OCILobGetChunkSize()
> > * OCILobWriteAppend() and/or (not sure about this)
> > * OCILobWrite using the "polling streaming" style.
> > * OCILobEnableBuffering etc
> > That would allow writing an additional method for LOBs
> > like this:
> > def appendFromFile (self, stream):
> > """
> > Appends contents from a file-like object to the LOB.
> > """
> > chunksize =3D self.getChunkSize()
> > first =3D True
> > isOpen =3D False
> > try:
> > while True:
> > buffer =3D stream.read(chunksize)
> > if not buffer:
> > break
> > # Note: The following code does not work
> > # if the file size is a multiple of chunksize!
> > if first:
> > self.open()
> > isOpen =3D True
> > self._write (buffer, OCI_FIRST_PIECE)
> > first =3D False
> > elif len(buffer) < chunksize:
> > self._write (buffer, OCI_LAST_PIECE)
> > else:
> > self._write (buffer, OCI_NEXT_PIECE)
> > finally:
> > # Note:
> > # I'm not sure if the LOB should be closed
> > # in case of errors.
> > if isOpen:
> > self.close()
> > This probably contains errors, but you get the idea.
> > Then this method could be used like this:
> > ifile =3D open("somedata.dat", "rb")
> > lob.appendFromFile (ifile)
> > ifile.close()
> > A similar method could be called "appendToFile",
> > which would read the LOB in chunks and append to the file,
> > automatically handling the nasty OCI streaming part.
> > Here is some documentation from Oracle I found regarding open/close:
> > The OCI provides functions to explicitly open (OCILobOpen()) and close =
(OCILobClose()) a LOB, and also to test whether a particular LOB is already=
open (OCILobIsOpen()). These functions allow an application to mark the be=
ginning and end of a series of LOB operations so that specific processing (=
e.g., updating indices, etc.) can be performed when a LOB is closed.
> > Note: The concept of openness is associated with a LOB and not its loca=
tor. The locator does not store any information about whether the LOB to wh=
ich it refers is open. It is possible for more than one locator to point to=
the same open LOB.
> > If an application does not wrap LOB operations between a set of OCILobO=
pen() and OCILobClose() calls, then each modification to the LOB implicitly=
opens and closes the LOB, thereby firing any triggers associated with chan=
ges to the LOB.
> > Note: If LOB operations are not wrapped inside open and close calls, an=
y extensible indices on the LOB are updated as LOB modifications are made, =
and thus are always valid and may be used at any time. If the LOB is modifi=
ed between a set of OCILobOpen() and OCILobClose() calls, triggers are not =
fired for individual LOB modifications. Triggers are only fired after the O=
CILobClose() call, so indices are not updated until after the close call an=
d thus are not valid in between the open and close calls. OCILobIsOpen() ca=
n be used with internal and external LOBs (BFILEs).
> > Mit freundlichen Gr=FC=DFen
> > Henning von Bargen
> > -----------------------------------------------------------------------=
> > This SF.net email is sponsored by DB2 Express
> > Download DB2 Express C - the FREE version of DB2 express and take
> > control of your XML. No limits. Just data. Click to get it now.
> > http://sourceforge.net/powerbar/db2/
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-oracle-users@...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users