Re: PARALLEL Hint in 11.2.0.3

the Auto DOP computation in 11.2 is mainly based on the cost of scanning the segments, scaled by the I/O calibration results. I've shown a simple example some time ago on my blog:

http://oracle-randolf.blogspot.com/2011/07/cost-is-time-next-generation.html

For DML the computation also includes as factor the data volume to process, so in your case also the number of rows to be inserted determined by the optimizer can influence the DOP computation.

The DOP defined on object level isn't considered for the Auto DOP calculation, so it doesn't matter if your tables are defined with DOP = 1 or 32.

Having said that this means that with stable statistics and no side effects from Dynamic Sampling you should get deterministic results from this algorithm.

So I assume that you actually have different data volumes in the source table, either reflected in different gathered stats or Dynamic Sampling results (the Notes section doesn't tell anything about Dynamic Sampling though, so stats seem to be in place).

_at_Jonathan: The PARALLEL_SERVERS_TARGET parameter is only relevant for determining when to queue PX execution when using PARALLEL_DEGREE_POLICY = AUTO, it shouldn't influence the DOP calculation itself as far as I know. PARALLEL_DEGREE_LIMIT is relevant for that, but then the message in the "Notes" section would reflect that the limit was used to cap the DOP.

Randolf

> Anyone know how Oracle auto-computes the DoP for the PARALLEL Hint in 11.2.0.3
>
> For a statement like
>
> INSERT /*+ PARALLEL */ INTO TABLE_A SELECT /*+ PARALLEL */ * FROM TABLE_B
>
> where both TABLE_A and TABLE_B have a defined DEGREE of 32, at various times I
> see the INSERT ... SELECT running with different Requested DoPs (2, 7, 22, 24
> !) even as no other session is attempting to use PQ slaves.

--
http://www.freelists.org/webpage/oracle-l