Re: Monitoring parallelism

Rick Denoire <100.17706_at_germanynet.de> wrote in message news:<q5kvluo1hhhljkl7eomp2dgtomeb5cd6gj_at_4ax.com>...
> Hello
>
> I switched on parallel query by setting the init parameter
> PARALLEL_AUTOMATIC_TUNING=true and restarting the DB. Additionally, I
> set the max. number of parallel servers to 40 and the number of
> threads per CPU to 6.

If you are using 8i, you also need to set

optimizer_percent_parallel to something greater than 0. You can do it in init.ora or at a session level. It's also a good idea to increase hash_area_size to 8M at least (Oracle recommended value).

If omptimizer_percent_parallel is equal to 0, parallel query won't work, no way. It's trickier with hash_area_size, you need to experiment. Couple more issues. Table should be created or altered to use parallel query. Or you can hint your query to use parallel query. Hints work on base tables only, they don't work on views (at least with 7.3.3, 8.0, 8.1, don't know about 9 yet).

Don't know yet about 9i, you might need to set some other parameters.

>
> I haven't noticed any significant change in performance. How can I
> somehow see that parallelism is active at all? I understand that
> parallel query does not always result in a performance increase, but I
> am not sure that there is any parallelism at all. (Using Oracle
> 8.1.7/Solaris 2.7 on a Sun E3500 with 4 CPUs). If any long running job
> starts, it sticks to one CPU so the overall CPU usage never goes over
> 25%. I assume that if parallelism of any kind would be in effect, at
> least for some short periods of time the job would show a CPU usage
> above 25%. Most of the time, when only one job runs, the other 3 CPUs
> are sitting there doing almost nothing.
>
> Any sugestions?
>
> Thanks
>
> Rick
Received on Mon Aug 19 2002 - 11:20:49 CDT