Re: Parallel queries slow

The information you provided so far doesn't tell much. The recursive SQL you are observing has to do with data dictionary calls, they have exaclty NOTHING to do with Parallel Query. TKPROF doesn't display information about the Parallel Query execution path, please use DBMS_XPLAN and -IMPORTANT- drop the PLAN_TABLE and recreate using the Oracle provided script, as the structure has changed since 8i, to allow for more information, Parallel Query info being one of the items added.

That said, I have observed several things in 8i, and I think they are still valid for newer releases
- You should configure your parallel_servers etc parameters in
init.ora/spfile appropiately, otherwise for every query Oracle will need to start parallel query slaves.
- For each parallel query, you will need 2 * #processors parallel query
slavesx
- For each parallel query, the original session acts as parallel query
cooordinator, and the slaves message back and forth to the coordinator
- and finally, and MOST IMPORTANTLY: If you don't software stripe the
affected tables over several spindles, parallel query is completely useless, as all the slaves will be hitting one disk. In fact that seems to be the situation you are in right now, because in that situation I observed *exactly* the same behavior, and at last I could convince customer to turn it of completely.

Hth and regards,

-- 
Sybrand Bakker
Senior Oracle DBA