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

[Mark H. <mh...@pi...>]

On 4/18/13 12:14 PM, Grzegorz Dąbkowski wrote:
> 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

Could it be the case that the initial time might be in setting up and executing
the query?  Are there joins, order bys, etc?

That's fairly typical in many applications.