Blog

Oracle parallel hint

Share this article

Parallel processing in Oracle is controlled via object properties, parameters or an Oracle parallel hint.
In today’s post, I want to show you how an oracle parallel hint influence the degree of parallelism (DOP) with the parallel_degree_policy parameter set to “MANUAL” and to “AUTO”.

Using the Oracle “PARALLEL” hint, you can indicate to the optimizer that you want to run this statement in parallel.
This hint can be put on statement level (not specifying the table to which it should be applied), or object level (referencing the table to which it should be applied).
When used at statement level, following settings can be added (from documentation):

  • PARALLEL: The statement is always run parallel, and the database computes the degree of parallelism, which can be 2 or greater
  • PARALLEL(DEFAULT): The optimizer calculates a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter
  • PARALLEL(AUTO): The database computes the degree of parallelism, which can be 1 or greater. If the computed degree of parallelism is 1, then the statement runs serially
  • PARALLEL(MANUAL): The optimizer is forced to use the parallel settings of the objects in the statement
  • PARALLEL(INTEGER): The optimizer uses the degree of parallelism specified by integer

When using the hint at object level, following options are allowed:

  • PARALLEL: The query coordinator should examine the settings of the initialization parameters to determine the default degree of parallelism.
  • PARALLEL(DEFAULT): The optimizer calculates a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter
  • PARALLEL(INTEGER): The optimizer uses the degree of parallelism specified by integer

It is interesting to note that the documentation states that the object level hint is there for backwards compatibility and is superseded by the statement level hint.
Also, the statement level hint will override the parallel_degree_policy parameter.

Leaving out the MANUAL option on the statement level and combining this with the parallel_degree_policy set to either MANUAL or AUTO, we have 14 different tests.
The test script and the full results can be downloaded here and I summarized the results in the table below:

Oracle parallel hint testing results table

Test 1

The explain plan reports that auto DOP is used and that the computed DOP is 4 because of the degree limit set.
This aligns with the documentation

Test 2

The explain plan does not report on the used DOP (but the plan shows the use of parallel query).
When the statement is executed the number of slaves equals “#cpu * parallel_threads_per_cpu”.
Again this is as documented.

Test 3

The explain plan reports that auto DOP is used and that the computed DOP is 4 because of the degree limit set.
This is the same as with the first test, but further tests show that when we lower the number of rows and blocks in the statistics, Oracle will switch to a serialize plan when the hint value is set to AUTO. Without specification in the hint, however, the plan will keep using parallel slaves.
Documentation is right again.

Test 4

The explain plan notes reports the DOP is set to 20 because of a hint and when executed also uses 20 slaves.
One point more for the documentation.

Test 5

Explain plan notes list a DOP of 4 because of table property, which aligns with the value of the parallel_degree_limit parameter.
But when the query is executed, the number of slaves equals “#cpu * parallel_threads_per_cpu”.
This is different from the statement level hint, where the number of slaves used was also 4.
The documentation (see above) about this is rather vague, so it is difficult to tell which of the 2 is correct.

Test 6

The notes in the explain plan reports a DOP of 4 because of table property.
But when the query is executed, the number of slaves equals “#cpu * parallel_threads_per_cpu”.
In this case, the actual number of slaves used, matches the behavior describes in the documentation, and it looks like the DOP in the explain plan is wrong.

Test 7

The notes in the explain plan report a DOP of 4 because of table property.
But when the query is executed, the number of slaves matches the requested number of slaves (20).
Again it seems that the DOP in the explain plan is wrong.

Test 8 -11

The behavior seen in these tests, is the same as with tests 1-4.
Changing the parallel_degree_policy from manual to auto has no effect.

Test 12

The explain plan states that auto DOP is used and limited to 10 because of the degree limit.
When executed the number of slaves is indeed 10 (matching #cpu * parallel_threads_per_cpu).
What stands out is that the explain plan refers to auto DOP instead of table property (as I would expect with an object level hint) and the limit is 10, not 4.
Here the change in the parallel_degree_policy value does have an effect on the test result.

Test 13

The notes in the explain plan report an auto DOP of 10 because of degree limit.
And when executed the number of slaves is indeed 10 (matching #cpu * parallel_threads_per_cpu).
10 slaves is indeed what I, based on the documentation, was expecting. But again the explain plan is talking about auto DOP rather than table property.

Test 14

The explain plan notes report the DOP is set to 20 because of a hint and when executed also uses 20 slaves.
This differs from test 7, where the DOP in the explain plan was 4 because of a table property.

It looks like there are some inconsistencies when the object level hint is used.
Either the reported DOP does not match the actual DOP or the reported DOP is misleading to the mechanism used to calculate the DOP.
The documentation states that the object level hint is deprecated, so best to no longer use it.

Which made me wonder about the parallel clause on the table itself.
So I added 4 more tests in which the table parallel clause is set: once without integer, once to 20 and this for both the degree policy MANUAL and AUTO.
The results were again gathered into table

Oracle parallel hint testing results parts 2

Results show that the parallel_degree_policy overrides the parallel clause on the table. (explain plan notes also state auto DOP was used)
When the parallel_degree_policy was set to manual, the same inconsistencies as with the object level hint appeared.

In conclusion I would try to avoid the use of the object level hint.
It is deprecated and the tests show that there are inconsistencies in its behavior.
But these inconsistencies also exist when using the parallel clause on the table itself, so the choice is yours.

It also shows that if you want to limit the number of slaves used by a single statement, you can’t depend on the parallel_degree_limit – not even when setting the parallel_degree_policy to ‘AUTO’ and you will have to use the DB resource manager instead.
But more about this in a future post.

Tags: Blog
Exitas Technical Lunch: Database security
DOP and explain plan notes

You May Also Like