About
Oracle's parallel execution framework enables you to either explicitly chose - or even enforce - a specific degree of parallelism (DOP) or to rely on Oracle to control it.
A degree of 1 is the default and means basically “not parallel”.
The original query which initially took 10 minutes to complete should complete within less than 3 minutes at DOP of 4, assuming sufficient resources are available.
Three modes are available to request a DOP :
The DOP is determined in the following priority order:
and limited by the Oracle Database Resource Manager (DBRM) settings. For example, if your resource plan has a policy of using a maximum DOP of 4 and you request a DOP of 16 via a hint, your SQL will run with a DOP of 4.
Then Database Resource Manager ultimately decides the final DOP for a parallel SQL operation before executing it. Database Resource Manager is an excellent tool to guarantee resources for operations that require a certain response time. Consider using the Database Resource Manager if you want to restrict users from using unlimited parallelism (and hence overload the system).
Once an operation starts at a certain DOP, it will not change its DOP throughout the execution.
Articles Related
Mode
DEFAULT
The DEFAULT algorithm was designed to use maximum resources assuming the operation will finish faster if you use more resources.
The default DOP is determined based on the system configuration:
- on a single database configuration:
- in a cluster configuration:
where:
- number of CPU cores is determined by the cpu_count parameter.
So, on a four node cluster with each node having 8 CPU cores, the default DOP would be 2 x 8 x 4 = 64.
DEFAULT parallelism and concurrency:
- the single-user workload is the target of DEFAULT parallelism
- In a multi-user environment DEFAULT parallelism will rapidly starve system resources leaving no available resources for other users to execute in parallel.
Fixed
Unlike the DEFAULT parallelism, a specific or Fixed DOP can be requested from the Oracle database.
For example, you can set a fixed DOP at a table or index level:
alter table customers parallel 8 ;
alter table sales parallel 16 ;
In this case:
- queries accessing just the customers table use a requested DOP of 8,
- and queries accessing the sales table request a DOP of 16.
- and a query accessing both the sales and the customers table will be processed with a DOP of 16 and potentially allocate 32 parallel servers (producer/consumer).
Whenever different DOPs are specified, Oracle is using the higher DOP.
Adaptive
When using Oracle's adaptive parallelism capabilities, the database will use an algorithm at SQL execution time to determine whether a parallel operation should receive the requested DOP or be throttled down to a lower DOP.
In a system that makes aggressive use of parallel execution by using a high DOP the adaptive algorithm will throttle down with only few operations running in parallel. While the algorithm will still ensure optimal resource utilization, users may experience inconsistent response times.
Using solely the adaptive parallelism capabilities in an environment that requires deterministic response times is not advised.
Adaptive parallelism is controlled through the database initialization parameter parallel_adaptive_multi_user
Guaranteeing a minimal DOP with Adaptive parallelism
Once a SQL statement starts execution at a certain DOP it will not change the DOP throughout its execution. However if you start at a low DOP – either as a result of adaptive parallel execution or because there were simply not enough parallel servers available - it may take a very long time to complete the execution of the SQL statement.
If the completion of a statement is time-critical then you may want to either guarantee a minimal DOP or not execute at all (and maybe warn the DBA or programmatically try again later when the system is less loaded).
To guarantee a minimal DOP, use the initialization parameter parallel_min_percent. This parameter controls the minimal percentage of parallel server processes that must be available to start the operation; it defaults to 0, meaning that Oracle will always execute the statement, irrespective of the number of available parallel server processes.
Auto DOP
Its goal
The idea behind calculating the Automatic Degree of Parallelism is to find the highest possible DOP (ideal DOP) that still scales. In other words, if we were to increase the DOP even more above a certain DOP we would see a tailing off of the performance curve and the resource cost / performance would become less optimal. Therefore the ideal DOP is the best resource/performance point for that statement.
And Query Concurrency
A basic assumption about running high DOP statements at high concurrency is that you at some point in time (and this is true on any parallel processing platform!) will run into a resource limitation.
The goal is to find a balance between the highest possible DOP for each statement and the number of statements running concurrently, but with an emphasis on running each statement at that highest efficiency DOP.
The PARALLEL_SERVER_TARGET parameter is the all important concurrency slider here. Let's say we have PARALLEL_SERVER_TARGET set to 128. With a default DOP set to 32, we are able to run 4 statements concurrently at the highest DOP possible on this system before we start queuing. If these 4 statements are running, any next statement will be queued.
By using both PARALLEL_SERVER_TARGET and PARALLEL_DEGREE_LIMIT you can control easily how many statements run concurrently at good DOPs without excessive queuing. Because each workload is a little different, it makes sense to plan ahead and look at these parameters and set these based on your requirements.