SQL statement hangs when using parallel query hints

[aidystew]

Queries run in SQL*PLUS complete correctly. However, when placed in cursors and
run in two procedures (one after the other), the first appears to finish and
the second hangs (no output is produced for the second)
Problem cannot be reproduced in sql*plus they both always finish (in a single
sql script containing both queries - one after the other).
Both queries employ parallel query hints and the package uses UTL_FILE utility
- The procedures in the package both run and complete when the parallel query
hints are removed.

When querying the database the parallel processes appear to still be running but are not clocking up CPU time and have no lockwaits. The processes still exist in this form until the database is shut down.

Using PLSQL version 2.3.5.5

 

[aidystew]

Update on previous query.

This still seems to be causing a problem, however, the query only hangs when run as part of an 'end-of-day' suite. If we run the queries or the whole sql package on its own it seems fine.

there are two SQL selects that have had parallel query hints added. Both produce 16 processes and 1 control process (for the package) on the database. Both queries build cursor for-loops. sometimes the first cursor finishes, but the process that is within the loop fails after any number of iterations and sometimes the same thing happens in the 2nd loop. Although the cursors MUST have finished for it to start processing within the loop(s), the processes spawned by the parallel query do not seem to die. they all sit there clocking up no CPU and the control proceses just continually loops round the procedure, clocking up CPU and causing the procedure to never end.