X

Information, tips, tricks and sample code for Big Data Warehousing in an autonomous, cloud-driven world

  • Wednesday, August 5, 2015

Configuring and Controlling Auto DOP

By: Yasin Baskan | Senior Principal Product Manager

This is the second post in a series talking about Auto DOP. In the first post we talked about what Auto DOP is. Now, let's look at how you can configure and control Auto DOP.

 

How to enable/disable Auto DOP 

There are two ways to enable/disable Auto DOP, you can enable/disable it for the whole system or for a session using the initialization parameter parallel_degree_policy, or you can enable/disable it for specific SQL statements using hints.

PARALLEL_DEGREE_POLICY

This is the parameter used to enable/disable Auto DOP system-wise or session-wise. 

The default value of this parameter is MANUAL which disables Auto DOP. With this setting the DOP of a statement will be determined by table/index decorations and hints. This does not mean that you cannot use Auto DOP as you can still use hints to request Auto DOP for specific statements as we will see below.

When you set this parameter to LIMITED, Auto DOP is applied to statements accessing tables/indexes decorated with the default DOP unless those statements set a specific DOP using hints. For all other statements manual DOP will be used.

When you set this parameter to AUTO, Auto DOP is applied to all statements regardless of the table/index DOP decorations unless those statements set a specific DOP using hints.

Hints

Regardless of what you set for parallel_degree_policy you can request Auto DOP for SQL statements using the hint PARALLEL. The statement level hints PARALLEL and PARALLEL (AUTO) instruct the optimizer to use Auto DOP. The difference is that the first one uses at least a DOP of 2 whereas the second one can use serial execution depending on the optimizer's DOP computation.

Even if you enable Auto DOP session-wise or system-wise statements using hints that request a specific DOP, like PARALLEL(integer), will run with that DOP and Auto DOP will not be used for those statements.

Enabling Auto DOP by setting parallel_degree_policy to AUTO brings additional benefits like Parallel Statement Queuing and In-memory Parallel Execution, we will discuss these in future posts.

 

How to limit the DOP computed by Auto DOP

The DOP computed by the optimizer with Auto DOP can be quite high depending on the resource requirements of the statement. You can limit the DOP using the initialization parameter parallel_degree_limit or Database Resource Manager (DBRM).

PARALLEL_DEGREE_LIMIT

This parameter limits the DOP that can be computed by the optimizer. After computing the DOP the optimizer looks at this parameter and adjusts the DOP accordingly and generates a plan based on the adjusted DOP.

The default value of this parameter is CPU which means the maximum DOP you can get is the default DOP which is parallel_threads_per_cpu * SUM(cpu_count). Even if you increase this parameter's value to be higher than the default DOP, the optimizer will always limit the DOP at default DOP.

When this parameter is set to IO the maximum DOP will be determined based on the IO calibration values. The maximum DOP will be the total system IO bandwidth divided by the per process IO throughput which is DBA_RSRC_IO_CALIBRATE.MAX_MBPS / DBA_RSRC_IO_CALIBRATE.MAX_PMBPS. If these values are not set or gathered the maximum DOP will be set as the default DOP like mentioned above.

This is a system-wide limit, so it limits the DOP for all users in the system to the same value. You can set this parameter to an integer value if you want a global limit but for more fine-grained control on the DOP for different kinds of users we recommend leaving this parameter as default and using Database Resource Manager (DBRM) to limit the DOP.

This parameter has no effect on statements not using Auto DOP. 

DBRM

With DBRM, you can map different users/applications to different consumer groups depending on business requirements and set separate DOP limits for those consumer groups. The DBRM directive that specifies the DOP limit is parallel_degree_limit_p1. This directive in integrated with the optimizer which means the optimizer will look at this value, adjust the computed DOP and generate a plan accordingly.

 

How to set the threshold for Auto DOP 

As explained in the first post, for every SQL statement the optimizer first generates a serial plan and estimates the execution time. It compares the estimated time with a threshold and decides to use parallelism or not. This threshold is specified by the initialization parameter parallel_min_time_threshold

PARALLEL_MIN_TIME_THRESHOLD

This parameter specifies the estimated execution time of a SQL statement to be considered for parallel execution. If the estimated time is less than this value the statement runs serially. If it is greater the optimizer goes on to generate a parallel plan and compute the DOP for the statement. 

The default value for this parameter is AUTO which means 10 seconds. You can decrease this value if you want more statements to run in parallel or increase it if you want fewer statements to run in parallel.

This parameter has no effect on statements not using Auto DOP. 

 

So, for any SQL statement that the optimizer decides to run in parallel with Auto DOP, it takes into account the initialization parameters, DBRM settings, system and object statistics, and HW characteristics to compute the DOP. 

We have looked at what initialization parameters you can use to control Auto DOP, in the next post we will look at how system and object statistics are used and what we mean by "HW characteristics". 

UPDATE: The next post in this series is here

 

Join the discussion

Comments ( 3 )
  • Al Davis Thursday, March 9, 2017

    Hello,

    I'm trying to get a series of jobs to queue up if there are not enough resources to run all the jobs at once in parallel. I've set

    PARALLEL_DEGREE_POLICY to AUTO. The other parallel setting are:

    NAME TYPE VALUE

    ------------------------------------ ----------- ------------------------------

    fast_start_parallel_rollback string LOW

    parallel_adaptive_multi_user boolean FALSE

    parallel_automatic_tuning boolean FALSE

    parallel_degree_level integer 100

    parallel_degree_limit string CPU

    parallel_degree_policy string AUTO

    parallel_execution_message_size integer 16384

    parallel_force_local boolean FALSE

    parallel_instance_group string

    parallel_io_cap_enabled boolean FALSE

    parallel_max_servers integer 80

    parallel_min_percent integer 0

    parallel_min_servers integer 0

    parallel_min_time_threshold string AUTO

    parallel_server boolean TRUE

    parallel_server_instances integer 2

    parallel_servers_target integer 32

    parallel_threads_per_cpu integer 2

    recovery_parallelism integer 0

    The DB Version is:

    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Prod

    PL/SQL Release 12.1.0.2.0 - Prod

    CORE 12.1.0.2.0 Prod

    TNS for Linux: Version 12.1.0.2.0 - Prod

    NLSRTL Version 12.1.0.2.0 - Prod

    Is there another parameter that is required or have I misunderstood that setting Parallel_Degree_Policy to Auto can allow queuing of SQL when there are insufficient resources to run SQL in parallel?

    Assistance / advice would be appreciated,

    Al


  • Yasin Baskan Monday, March 13, 2017

    Hi Al,

    What do you mean by "jobs"? Parallel statement queuing looks at a single parallel statement, it runs it if there are enough parallel processes available, it queues the statement if there are not.

    Serial statements are not subject to parallel statement queuing.


  • PX_Reader Friday, February 16, 2018
    Hi Yasin,

    Could you clarify Which one takes precedence? DBRM or parallel_degree_limit?

    For example:
    ~~~~~~~
    cpu_count=64
    parallel_degree_limit=16
    DBRM-parallel_degree_limit_p1=4

    So, if a SQL with Auto-DOP enabled is run, whether Auto-DOP/Optimizer will consider to take DOP 4 (DBRM) or 16 (Parallel_Degree_Limit), assuming the user belongs to that particular resource consumer group and the parallel_min_time_threshold is passed (i.e. sql runs more than 10 seconds).
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services