Re: Init. param. OPTIMIZER_PERCENT_PARALLEL > 0 ?

The Oracle 8.0.6 doc has a more detailed explanation of this parameter:

http://download-west.oracle.com/docs/cd/A64702_01/doc/server.805/a58246/pextune.htm#1620

Here's a summary:

Low value for OPTIMIZER_PERCENT_PARALLEL

- minimizes total resources used
- favors indexes
- optimizes throughput

High value for OPTIMIZER_PERCENT_PARALLEL

- does not minimize total resources used
- favors table scans
- optimizes response time

Recommended value is 100/number_of_concurrent_users, probably so that no user will hog all the resources.

HTH,
Dave

OPTIMIZER_PERCENT_PARALLEL Recommended Value: 100/number_of_concurrent_users This parameter determines how aggressively the optimizer will attempt to parallelize a given execution plan. OPTIMIZER_PERCENT_PARALLEL encourages the optimizer to use plans that have low response time because of parallel execution, even if total resource used is not minimized.

The default value of OPTIMIZER_PERCENT_PARALLEL is 0, which parallelizes the plan which uses the least resource, if possible. Here, the execution time of the operation may be long because only a small amount of resource is used. A value of 100 causes the optimizer to choose a parallel plan unless a serial plan would complete faster.

Note: Selecting a single record from a table, if there is an appropriate index, can be done very quickly and does not require parallelism. A full scan to find the single row can be executed in parallel. Normally, however, each parallel process examines many rows. In this case response time of a parallel plan will be higher and total system resource use would be much greater than if it were done by a serial plan using an index. With a parallel plan, the delay is shortened because more resource is used. The parallel plan could use up to D times more resource, where D is the degree of parallelism. A value between 0 and 100 sets an intermediate trade-off between throughput and response time. Low values favor indexes; high values favor table scans.

A FIRST_ROWS hint or optimizer mode will override a non-zero setting of OPTIMIZER_PERCENT_PARALLEL "Spendius" <spendius_at_muchomail.com> wrote in message news:aba30b75.0311060733.1b500410_at_posting.google.com...
> Hi,
> I'd just like to know whether you need to set the
> OPTIMIZER_PERCENT_PARALLEL parameter to a value different
> from its default (0) in order for the optimizer to take
> the tables' degree of parallelism in account.
>
> If yes, this OPTIMIZER_PERCENT_PARALLEL value can be set
> between 0 and 100: but what are the meanings of figures
> as different as 12, 44 or 87 ?!? Here again the Oracle
> doc. is pretty obscure about the details.
>
> Thanks a lot.
> Regards,
> Spendius
Received on Sat Nov 08 2003 - 17:35:02 CST