Degree of Parallelism in Software

Printer Denso QR Bar Code in Software Degree of Parallelism

You can configure the number of parallel servers to handle all the parallel execution requests for the entire instance, but how do you control how many parallel servers are used for a particular statement The number of parallel servers used for a statement is called the degree of parallelism for the statement, commonly abbreviated to DOP

You can set the default degree of parallelism at four different levels First of all, the default DOP for a database is calculated with the following formula:

The CPU_COUNT parameter is normally set by the Oracle database, which monitors the number of CPUs reported to the operating system, and the PARALLEL_THREADS_PER_CPU also has a default, based on the platform, although the normal default is 2 You can set either of these parameters yourself if this default allows for too many parallel servers, or if the system is I/O bound, which could represent the need to split I/O operations further with parallel execution Default DOP will be used when the parallel attribute has been set on an object explicitly or via a hint but no parallel degree was specified You can also set a specific DOP for an individual table, index, or materialized view, either when you create the object or subsequently modify it If a SQL statement includes objects with different DOPs, Oracle will take the highest DOP as the DOP for the statement The ALTER SESSION command was discussed earlier in this section with regard to enabling parallelism This command has another format that allows you to specify the degree of parallelism:

ALTER SESSION FORCE PARALLEL (DML | DDL | QUERY) PARALLEL n;

where n is the DOP for the duration of the session Finally, you can include a hint in a SQL statement that specifies the DOP for the statement A particular SQL statement may have multiple defaults that apply to the statement The order of precedence for the DOP is hint, session, object, database, so the DOP indicated in a hint will be used instead of any other DOP that is present

The last step in understanding how the degree of parallelism affects parallel operations is to understand exactly how this value is used in the runtime environment If your Oracle instance has parallelism enabled for a particular type of statement, the optimizer will calculate the degree of parallelism for that statement A statement can only have a single DOP, which will dictate the number of parallel servers used for all subtasks

2:

When the statement execution begins, the query coordinator goes to the parallel server pool and grabs the number of parallel servers that will be required for execution either the DOP or twice the DOP, if the execution will involve both producers and consumers If no parallel servers are available, the statement will be run serially But what if some parallel servers are available in the pool, but just not enough to satisfy the full request In this case, the query coordinator will take as many parallel servers as it can and adjust the DOP for the statement accordingly unless the initialization parameter PARALLEL_MIN_PERCENT is set, in which case the statement can fail to execute at all, as explained later in this section

That last statement may have caused you some concern Here you are, carefully calculating and setting the DOP for objects, sessions, and statements in order to produce the best performance from your Oracle instance But an instance can support many users, applications, and SQL statements, so you may have a situation where there are not enough parallel servers to go around at a particular point in time You may not want to populate the parallel server pool with enough servers to guarantee a full DOP during times of peak demand, since this may waste resources most of the time What will you do Oracle has provided several different ways to address this situation dynamically One method is adaptive parallelism, enabled by setting the PARALLEL_ ADAPTIVE_MULTI_USER parameter When this parameter is set to TRUE, the Oracle instance will adjust the DOP for statements while taking into account the workload at the time of execution The goal of adaptive parallelism is to ensure that there will be enough resources available for all parallel executions, so the Oracle instance will reduce the DOP when appropriate Adaptive parallelism takes a fairly aggressive approach to ensuring that there are sufficient parallel resources, and this approach may lead to different DOPs being set for the same statement at different times If you choose to use this approach to limiting parallelism, you should make sure to test its effect on your true runtime environment