cx_Oracle

Email Archive: cx-oracle-users (read-only)

[cx-oracle-users] LOB Performance and proposal for a file-like interface to LOBs.

From: Henning von Bargen <H.vonBargen@t-...> - 2007-05-11 15:13

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[0]
        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=20
            # 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:=20
       # I'm not sure if the LOB should be closed=20
       # 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:
       =20
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.=20


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 beginning 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.=20

Note: The concept of openness is associated with a LOB and not its =
locator. The locator does not store any information about whether the =
LOB to which it refers is open. It is possible for more than one locator =
to point to the same open LOB.=20

If an application does not wrap LOB operations between a set of =
OCILobOpen() and OCILobClose() calls, then each modification to the LOB =
implicitly opens and closes the LOB, thereby firing any triggers =
associated with changes to the LOB.=20

Note: If LOB operations are not wrapped inside open and close calls, any =
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 =
modified between a set of OCILobOpen() and OCILobClose() calls, triggers =
are not fired for individual LOB modifications. Triggers are only fired =
after the OCILobClose() call, so indices are not updated until after the =
close call and thus are not valid in between the open and close calls. =
OCILobIsOpen() can be used with internal and external LOBs (BFILEs).=20







Mit freundlichen Gr=FC=DFen
=20
Henning von Bargen

Thread View

Thread	Author	Date
[cx-oracle-users] LOB Performance and proposal for a file-like interface to LOBs.	Henning von Bargen <H.vonBargen@t-...>

Re: [cx-oracle-users] LOB Performance and proposal for a file-like interface to LOBs.

From: Anthony Tuininga <anthony.tuininga@gm...> - 2007-05-11 17:05

Hi Henning, I've taken a look at your proposal. I need to do a bit of
research first to determine whether errors occur if you attempt to
open a LOB that is already open, whether or not a missing close()
causes a problem or not, etc. This would allow you to do the
following:

offset =3D 1
blob.open()
while True:
    chunk =3D source.read(CHUNK_SIZE)
    if not chunk:
        break
    blob.write(chunk, offset)
    offset +=3D len(chunk)
blob.close()

As you suggest it might also be convenient to have a method for
populating a LOB from a stream and vice versa, using the internal
chunk size to improve performance. The first step could be done
relatively easily. The additional methods for reading/writing to a
stream would be a little more work -- maybe. I'll look into it. Thanks
for bringing it up.

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 f=
or update of DOKUMENT",
>                  DODA_ID=3Dtemp_id)
>     for row in curs:
>         offset =3D 0
>         lob =3D row[0]
>         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 (O=
CILobClose()) a LOB, and also to test whether a particular LOB is already o=
pen (OCILobIsOpen()). These functions allow an application to mark the begi=
nning 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 locato=
r. The locator does not store any information about whether the LOB to whic=
h it refers is open. It is possible for more than one locator to point to t=
he same open LOB.
>
> If an application does not wrap LOB operations between a set of OCILobOpe=
n() and OCILobClose() calls, then each modification to the LOB implicitly o=
pens and closes the LOB, thereby firing any triggers associated with change=
s to the LOB.
>
> Note: If LOB operations are not wrapped inside open and close calls, any =
extensible indices on the LOB are updated as LOB modifications are made, an=
d thus are always valid and may be used at any time. If the LOB is modified=
 between a set of OCILobOpen() and OCILobClose() calls, triggers are not fi=
red for individual LOB modifications. Triggers are only fired after the OCI=
LobClose() call, so indices are not updated until after the close call and =
thus are not valid in between the open and close calls. OCILobIsOpen() can =
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
>

Re: [cx-oracle-users] LOB Performance and proposal for a file-like interface to LOBs.

From: Anthony Tuininga <anthony.tuininga@gm...> - 2007-05-30 17:41

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
chunks.

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 f=
or update of DOKUMENT",
>                  DODA_ID=3Dtemp_id)
>     for row in curs:
>         offset =3D 0
>         lob =3D row[0]
>         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 (O=
CILobClose()) a LOB, and also to test whether a particular LOB is already o=
pen (OCILobIsOpen()). These functions allow an application to mark the begi=
nning 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 locato=
r. The locator does not store any information about whether the LOB to whic=
h it refers is open. It is possible for more than one locator to point to t=
he same open LOB.
>
> If an application does not wrap LOB operations between a set of OCILobOpe=
n() and OCILobClose() calls, then each modification to the LOB implicitly o=
pens and closes the LOB, thereby firing any triggers associated with change=
s to the LOB.
>
> Note: If LOB operations are not wrapped inside open and close calls, any =
extensible indices on the LOB are updated as LOB modifications are made, an=
d thus are always valid and may be used at any time. If the LOB is modified=
 between a set of OCILobOpen() and OCILobClose() calls, triggers are not fi=
red for individual LOB modifications. Triggers are only fired after the OCI=
LobClose() call, so indices are not updated until after the close call and =
thus are not valid in between the open and close calls. OCILobIsOpen() can =
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
>

Re: [cx-oracle-users] LOB Performance and proposal for a file-like interface to LOBs.

From: Anthony Tuininga <anthony.tuininga@gm...> - 2007-08-09 18:59

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
> chunks.
>
> 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[0]
> >         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
> >
>