Re: Monitoring parallelism

Hi Rick,

OK, a couple of things.

Regarding the parameters in init.ora, you mentioned in your first post that you switched the parallel_automatic_tuning=true. By doing so, Oracle automatically set dependent parameters (such as parallel_adaptive_multi_user and parallel_max_servers). This in combination with the parallel clause/hints in your tables is what is causing parallel executions. You need appropriate parameters AND the parallel clause/hint to enable parallel operations.

Next, you are correct in suggesting setting parallel on encourages full table scans. This is what you want IF the parallel operations are correctly tuned. Assuming you have a parallel degree of 8, the CBO is effectively seeing FTS as being 8 times more efficient. It's now looking at 8 x the db_file_multiblock_read_count of blocks as being of a similar cost to one I/O of an index.

However, this improvement may not be accurate. Possible reasons are that the CPUs are maxing out and are not appropriately dedicated. Possibly there are multiple operations being performed in parallel and the parallel_adaptive_multi_user setting is sharing around the available parallel servers. Or and the more common problem, your data is not sufficiently distributed across enough devices to avoid I/O bottlenecks. The parallel servers are causing contention among themselves resulting in non optimum performance.

Therefore rather than 8 x performance improvements, you are getting only 2 X performance improvements with parallel operations and the CBO might hence be picking the wrong plan. Just bemuse you are getting FTS doesn't necessarily mean it's bad (it's actually what you want with parallel processing), it depends...

Lastly, by setting the parallel clause to default (i.e. without specifying an integer), you are effectively saying to Oracle I want you to perform parallel operations on this table as you see fit depending on the current usage of parallel servers in the database. Tuning all this is a right pain if you have multiple concurrent operations running in parallel (which in my opinion somewhat defeats the purpose). To tune all this I recommend trying to avoid multiple concurrent operations (if feasible or possible), use hints rather than the parallel clause (to better control when parallel operations are actually being performed) and manually setting the init.ora parameters until the particular "sweet spot" in your environment is found (by trial and error).

Good Luck

Richard

Rick Denoire wrote:
>
> Richard Foote <Richard.Foote_at_bigpond.com> wrote:
>
> >Hi Rick,
> >
> >Check out V$PQ_SESSTAT, V$PQ_SLAVE, V$PQ_TQSTAT, and V$PQ_SYSSTAT for
> >details regarding what/if/how parallel operations were executed.
>
> Thanks a lot, that was exactly the info I needed.
>
> But now I got some parallel processes spawned on another DB. One can
> easily recognize them in the process list.
>
> >And as previously mentioned, you must specify the parallel clause
> >(either at the table definition or via a hint) for Oracle to start
> >creating more shovels and passing then around appropriately.
>
> It seems that you are right here but then I just don't understand what
> the parameters in the init.ora are for. I just found out that changing
> the degree of parallelism of a table would be enough to parallelize
> some operations on this table, without changing any init.ora
> parameter.
>
> The problem I have now is that running in parallel, things runs
> *slower*, since Oracle is more in favor of full table scans. I need to
> tweak some parameter to discourage Oracle to do excessive scans.
>
> Bye
> Rick
Received on Mon Aug 19 2002 - 20:06:10 CDT