Oracle Database - (Degree|Degree of Parallelism (DOP))

Card Puncher Data Processing

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.

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:
DOP = [number of CPU cores] x [parallels threads per cpu]
  • in a cluster configuration:
DOP = [number of CPU cores] x [number of nodes] x [parallels threads per cpu]

where:

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.

Documentation / Reference





Discover More
Data System Architecture
Data Warehousing - The Workload is always mixed

Also known as: active data warehousing, operational data warehousing etc. All this indicate something similar, namely a diverse workload running on a data warehouse system concurrently: Whether...
Card Puncher Data Processing
Oracle Database - (Degree|Degree of Parallelism (DOP))

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...
Card Puncher Data Processing
Oracle Database - (Parallel|PX) Server

Execution Thread in Oracle. Parallel server are the individual sessions that perform work in parallel in a parallel SQL execution. PX server processes can be easily identified on the OS level, for...
Card Puncher Data Processing
Oracle Database - CPU_COUNT parameter

CPU_COUNT specifies the number of CPUs core (processor) available for Oracle Database to use. CPU count is an automatically derived parameter by the Oracle system and is used to determine: the default...
Oracle Database Block Based Granule
Oracle Database - Granule (Parallel Data Access)

A granule is the smallest unit of work when accessing data. Because Oracle database uses a shared everything architecture (unlike the shared nothing architecture), Oracle can – and will - choose this...
Card Puncher Data Processing
Oracle Database - How to (enable|disable) parallel query and get (degree of parallelism|DOP) ?

How to enable a parallel execution: for a for a operation Small tables/indexes (up to thousands of records; up to 10s of data blocks) should never be enabled for parallel execution. Operations...
Card Puncher Data Processing
Oracle Database - PARALLEL_DEGREE_LIMIT

This parameter controls the degree of parallelism on the entire cluster and by default it is CPU bound (meaning it equals Default DOP).
Card Puncher Data Processing
Oracle Database - PARALLEL_MAX_SERVERS parameter

This parameter determines the maximum number of parallel servers that may be started for a database instance, should there be demand for them. The default value on Oracle Database 10g and higher is:...
Card Puncher Data Processing
Oracle Database - PARALLEL_MIN_PERCENT parameter

This initialization parameter is to guarantee a minimal DOP in a adaptive mode to a parallel query. This parameter controls the minimal percentage of parallel server processes that must be available...
Card Puncher Data Processing
Oracle Database - PARALLEL_THREADS_PER_CPU Parameter

This parameter describes the number of parallel execution processes or threads that a CPU can handle during parallel execution. It is used to: calculate the default degree of parallelism for the...



Share this page:
Follow us:
Task Runner