[cx-oracle-users] Loop over Cursor stalls for several minutes

[Grzegorz D. <gol...@wp...>]

Hello,

you're likely the last place I can find some help. I wrapped a Cursor inside
a generator function which first performs some operations against the DB
using the cursor, then performs a select query, creates a custom Row object
from every fetched row and yields it. The cursor is a subclassed
cx_Oracle.Cursor with an increased arraysize and an outputtypehandler that
converts strings to Unicode (as shown in demos coming with cx_Oracle).
Removing any of these doeasn't help anyway.

I can't understand why in some executions of my script (actually in most
cases, though not all) the execution stalls for several minutes performing
the line 'for row in cursor:'. Only memory usage increases during this time.
>From time to time the issue doesn't occur, the line is processed in few
hundreds of a second and my entire test completes in about 55 seconds. In
most cases though the total execution time is around 11-12 minutes with all
the additional time spent in that single line. I also noticed that often
when the entire test is done in a loop (retaining the same Connection and
Cursor objects), the issue manifests itself only in the first iteration.

:: 1597 records in 11:33
:: 1597 records in 0:56
:: 1597 records in 0:55
:: 1597 records in 0:55
:: 1597 records in 0:55
:: 1597 records in 0:55

I will welcome any hint as to what actually happens when the cursor is used
as an iterator here and what can I check to get more understanding of my
problem.

Many thanks in advance.

- Grzegorz Dabkowski