Re: Monitoring parallelism

Quoting Oracle doc:
"OPTIMIZER_PERCENT_PARALLEL specifies the amount of parallelism that the optimizer uses in its cost functions. The default of 0 means that the optimizer chooses the best serial plan. A value of 100 means that the optimizer uses each object's degree of parallelism in computing the cost of a full table scan operation. Low values favor indexes, and high values favor table scans. Cost-based optimization is always used for queries that reference an object with a nonzero degree of parallelism. For such queries, a RULE hint or optimizer mode or goal will be ignored. Note: Use of a FIRST_ROWS hint or of optimizer mode override a non-zero setting of OPTIMIZER_PERCENT_PARALLEL" "When PARALLEL_AUTOMATIC_TUNING is set to TRUE, Oracle determines the default values for parameters that control parallel execution. In addition to setting this parameter, you must specify the PARALLEL clause for the target tables in the system. Oracle then tunes all subsequent parallel operations automatically. If you used parallel execution in a previous release and are now enabling PARALLEL_AUTOMATIC_TUNING, you should reduce the amount of memory allocated from the shared pool to account for the decreased demand on that pool. This memory will be now be allocated from the large pool, and will be computed automatically if LARGE_POOL_SIZE  is left unset.
As part of the automatic tuning, Oracle will enable the PARALLEL_ADAPTIVE_MULTI_USER parameter. You can override any of the system-provided defaults if desired."

Also, note that optimizer_percent_parallel is obsolete in Oracle 9i.

Now since you are beginning to try understanding the "parallel query", I guess its time to read the Concepts manual .. the part which talks about this. Try things out after that. It will help you help others after that :)

http://download-east.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c22paral.htm#365

The URL above might get wrapped. Also, you might need a *free* subscription to technet.

Anurag

"Rick Denoire" <100.17706_at_germanynet.de> wrote in message news:peu2muk9g5eu5jg95ghordebsc67bk1k6f_at_4ax.com...
> afilonov_at_yahoo.com (Alex Filonov) wrote:
>
>
> >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).
>
> OK, I will try that. Does this parameter have an influence on Oracle's
> tendence to do full table scans? It seems that when parallelizing
> things, Oracle will more likely do full table scans - bad.
>
> >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).
>
> As I wrote in other parts of this thread, I found out that it is
> enough to parallelize tables to get things done in parallel, without
> changing any init.ora parameter.
>
> If I use PARALLEL_AUTOMATIC_TUNING, should then the degree of
> parallelism for the particular table be set to default? (Since it
> should work *automatically*). I could not really understand the
> meaning of the default degree of parallelism for a table.
>
Received on Mon Aug 19 2002 - 21:14:49 CDT