0

From the reference for PARALLEL_MIN_PERCENT:

PARALLEL_MIN_PERCENT lets you specify the minimum percentage of the requested number of parallel execution processes required for parallel execution. Setting this parameter ensures that parallel operations will not execute unless adequate resources are available. The default value of 0 means that no minimum percentage of processes has been set.

Consider the following settings:

PARALLEL_MIN_PERCENT = 50

PARALLEL_MIN_SERVERS = 5

PARALLEL_MAX_SERVERS = 10

If 8 of the 10 parallel execution processes are busy, only 2 processes are available. If you then request a query with a degree of parallelism of 8, the minimum 50% will not be met.

From Initializing and Tuning Parameters for Parallel Execution:

[PARALLEL_MIN_SERVERS] Specifies the minimum percentage of requested parallel execution processes required for parallel execution. With the default value of 0, a parallel statement executes serial if no parallel server processes are available.

What happens when the minimum 50% of servers is not available for the query when it runs? Is the query is queued, does it execute serially, or does it throw an error such as insufficient parallel query slaves available?

Improve this question

2 Answers 2

Reset to default
0

I believe for that configuration you will get an error like this:

ORA-12827: insufficient parallel query slaves available

See the references at PSORG and ORAFAQ.

If parallelism is configured differently on the server it's possible for the DOP to be downgraded. In some cases it will be downgraded to execute serially if there aren't enough PX servers available. There are a few examples here for parallel_degree_policy=MANUAL:

Since there are only 12 available PX servers this statement gets downgraded from DOP=8 to DOP=6 and gets all 12 available PX servers.

Consider another query submitted at this time with DOP=8. Even though this query requires 16 PX servers it will be given none because there are no available PX servers left. This query gets downgraded to DOP=1 and runs serially.

I have experienced that when running my own code. One way to see when DOP is downgraded is through SQL monitor. Here are a few examples of the messages you can see:

350 DOP downgrade due to adaptive DOP

351 DOP downgrade due to resource manager max DOP

352 DOP downgrade due to insufficient number of processes

353 DOP downgrade because slaves failed to join

Improve this answer
0

Adaptive parallelism is controlled through the database initialization parameter PARALLEL_ADAPTIVE_MULTI_USER

If there were insufficient parallel servers to satisfy the requested DOP, one of three things could occur:

  • The SQL would be run at a reduced DOP (be downgraded)
  • The SQL would run in serial mode (be serialized)
  • If PARALLEL_MIN_PERCENTwas specified and less than the nominated percentage of the DOP was achievable, the SQL statement might terminate with ORA-12827: insufficient parallel query slaves available
Improve this answer
2
  • What does insufficient parallel servers mean?
    – Toolkit
    Jul 31, 2019 at 7:32
  • we have downgrade because slaves failed to join. What can be the reason?
    – Toolkit
    Jul 31, 2019 at 7:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.