Parallel PL/SQL launcher update

A while ago I wrote an experimental function to submit up to four PL/SQL commands simultaneously using Oracle's Parallel Query capabilities and a pipelined function. The results were promising but somewhat frustrating, as I could only get two parallel threads in my 11g test setup, and I couldn't figure out why.

I still don't have the full picture (perhaps one or more of the trace events will light some shed - 10975 trace execution of parallel propagation, 10383 auto parallelization testing event and 10384 parallel dataflow scheduler tracing look promising, amongst others) but I can now get all four parallel threads on my single-CPU 11g test system (11.1.0.6.0 Enterprise Edition on Windows XP Pro, in a Parallels VM on my 2GHz iMac, to be precise).

Finding myself with some spare time, I had another look at my initialisation parameters, and I noticed CPU_COUNT was set to 1 (which was true, there is only 1 cpu) and PARALLEL_THREADS_PER_CPU was 2 (the default setting). I found that setting PARALLEL_THREADS_PER_CPU = 4 (or CPU_COUNT = 2) enabled the full 4 parallel threads requested in parallel_launch.test(). Also, since gathering statistics on the PQ_DRIVER table had the effect of serialising queries against it, and since by default the background stats job will do this, I added a call to dbms_stats.lock_table_stats(user,'pq_driver'). I still don't know what statistics cause this to happen and why, and it's a little puzzling because I would have expected dynamic sampling (enabled by default since 10g) to give the same results.

Also I noticed a potential bug in the pq_submit function. It queries v$px_session as a self-diagnostic check, but if for any reason no parallel slaves were used there will be no rows in it, leading to a no_data_found exception because I used a SELECT INTO construction. Since no_data_found exceptions are automatically handled by the SQL engine, my pq_submit might appear to return successfully after mysteriously doing nothing. That's now fixed.