X

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

  • Thursday, July 9, 2015

Parallel_Degree_Limit, Parallel_Max_Degree, Maximum DOP? Confused?

By: Yasin Baskan | Senior Principal Product Manager

Here are two questions for you. What is the maximum DOP you can get with Auto DOP in a 12c database? Where can you see what the number is without generating execution plans? The answers to these question may get tricky if you start to play with init.ora parameters. 

What is the maximum DOP you can get with Auto DOP in a 12c database? 

The answer to this question may seem obvious if you look at the documentation, the init.ora parameter PARALLEL_DEGREE_LIMIT determines the maximum DOP. So, you can set it to any integer value you want and that will be your maximum, that is it, or is it? What happens if you set it to a really high value? What is missing from the documentation is that the maximum DOP you can get will always be the default DOP, which is CPU_COUNT * PARALLEL_THREADS_PER_CPU. No matter what number you set for PARALLEL_DEGREE_LIMIT, the internal enforced limit will stay the same, any value higher than this will be ignored. This also applies to the Database Resource Manager (DBRM). The parameter and resource manager directives will be obeyed as long as they are lower than the internal limit.

Here is how the maximum DOP is derived for a session. 

Max DOP = MIN (CPU_COUNT * PARALLEL_THREADS_PER_CPU, PARALLEL_DEGREE_LIMIT, DBRM DIRECTIVE PARALLEL_DEGREE_LIMIT_P1

Here is a sample test case to show the behavior in 12.1.0.2. 

SQL> create table t as select * from all_objects;
Table created.
SQL> exec dbms_stats.set_table_stats(user,'T',numrows=>1000000000000,numblks=>1000000000);
PL/SQL procedure successfully completed.

I created an empty table and set the statistics so that the optimizer thinks it is a very large table, this is to make sure that Auto DOP will calculate a super high DOP. Here are the related parameters:

SQL> select name, value from v$parameter
  2  where name in ('parallel_degree_limit','cpu_count','parallel_threads_per_cpu');
NAME                           VALUE
------------------------------ --------------------------------------------------------------------------------
cpu_count                      2
parallel_threads_per_cpu       2
parallel_degree_limit          128 

Let's look at a statement accessing the large table.

The DOP is capped at 4 even though PARALLEL_DEGREE_LIMIT is 128. This is because CPU_COUNT * PARALLEL_THREADS_PER_CPU is 4

Where can you see the maximum DOP allowed without generating execution plans?

As you can see above an execution plan shows the computed DOP in the notes section under the plan. But, that is the computed DOP and if it does not hit any limits you will not be able to find out what the maximum DOP can be. Where can we see what our database allows as maximum DOP?

The view V$SES_OPTIMIZER_ENV can give a clue about this. This view shows the optimizer settings for all sessions in an instance. It also includes settings related to parallel execution. Of particular interest to this discussion is two of them, PARALLEL_DEGREE_LIMIT and PARALLEL_MAX_DEGREE. Let's see what they show in my test environment. Here are the init.ora parameters.

SQL> select name, value from v$parameter
  2  where name in ('parallel_degree_limit','cpu_count','parallel_threads_per_cpu');
NAME                           VALUE
------------------------------ --------------------------------------------------------------------------------
cpu_count                      2
parallel_threads_per_cpu       2
parallel_degree_limit          CPU 

Here is what the view shows with these parameters.

SQL> select name, value from v$ses_optimizer_env
  2  where name in ('parallel_degree_limit','parallel_max_degree')
  3  and sid=(select sid from v$mystat where rownum=1);
NAME                           VALUE
------------------------------ --------------------------------------------------------------------------------
parallel_degree_limit          65535
parallel_max_degree            4 

The first thing to notice is that parallel_degree_limit in this view is not the same thing as the init.ora parameter PARALLEL_DEGREE_LIMIT. Since I did not explicitly set the init.ora parameter to an integer value the view shows a large number (64K-1), the actual limit is shown as parallel_max_degree. This shows me that in this system I cannot get a DOP higher than 4 with Auto DOP.

If you set the init.ora parameter to an integer value this is what the view shows. 

SQL> alter system set parallel_degree_limit=128;
System altered. 
SQL> select name, value from v$ses_optimizer_env
  2  where name in ('parallel_degree_limit','parallel_max_degree')
  3  and sid=(select sid from v$mystat where rownum=1);
NAME                           VALUE
------------------------------ --------------------------------------------------------------------------------
parallel_degree_limit          128
parallel_max_degree            4 

Now parallel_degree_limit shows the actual init.ora parameter. The rule still applies, the maximum DOP I can get is parallel_max_degree which is 4 as shown in the execution plan above. If you set the init.ora parameter to a lower value than CPU_COUNT * PARALLEL_THREADS_PER_CPU you will see that parallel_max_degree reflects that. This also true for DBRM.

So, parallel_max_degree is calculated as;  MIN (CPU_COUNT * PARALLEL_THREADS_PER_CPU, PARALLEL_DEGREE_LIMIT, DBRM DIRECTIVE PARALLEL_DEGREE_LIMIT_P1) .

The only caveat with parallel_max_degree is it can only show values up to 4095 (4K-1). Above this number is starts over from 0. If you bump up one of the variable in the formula, like CPU_COUNT, you will see cases like below.

SQL> select name, value from v$parameter
  2  where name in ('parallel_degree_limit','cpu_count','parallel_threads_per_cpu');
NAME                           VALUE
------------------------------ ----------
cpu_count                      2048
parallel_threads_per_cpu       2
parallel_degree_limit          CPU 
SQL> select name, value from v$ses_optimizer_env
2 where name in ('parallel_degree_limit','parallel_max_degree')
3 and sid=(select sid from v$mystat where rownum=1);
NAME                           VALUE
------------------------------ --------------------------------------------------------------------------------
parallel_degree_limit          65535
parallel_max_degree            0 

In these cases the maximum DOP you can get will be different than parallel_max_degree.

So, you can rely on this value if your CPU_COUNT is not in the two thousands range! If you have such a huge system use the formula given above to calculate max DOP allowed. And please let me know how you are using parallel execution in such an environment :)

Remember that these are only maximum values that can be calculated by the optimizer, at runtime you are still bounded by PARALLEL_MAX_SERVERS. Obviously your statement will get downgraded if PARALLEL_MAX_SERVERS is reached.

Join the discussion

Comments ( 9 )
  • Hemant K Chitale Friday, July 10, 2015

    Do people actually run queries with DoP CPU_COUNT x PARALLEL_THREADS_PER_CPU when the number of CPUs is, say 32 or 64 ?


  • Yasin Baskan Friday, July 10, 2015

    What I mostly see is that the DOP is capped using resource manager. There are customers using the parameter PARALLEL_DEGREE_LIMIT set to a lower value too.


  • guest Monday, October 10, 2016

    Hi there, I am a new to Oracle,

    If there are 10 sql select statements each set to parallel default dop, runs at same time, how init.ora or resource manager will allocate PX resources to all of the 10 sql statements (Will it share equal resources or execute one after another).

    Actual Issue: I have a situation where there are more than 50 reports running at same time, using hints parallel(10)/(15) etc in the select statements, which is causing server to run reports longer time; my assumption is, will resource manager/or init.ora component handle resources without causing deadlock situation if I mention parallel(default) without blocking all the resources.


  • guest Monday, October 10, 2016

    If you do not enable parallel statement queuing, the queries are allocated PX servers on a first come first served basis. When the number of parallel processes in-use reaches the value of the parallel_max_servers parameter, new queries get downgraded. To manage concurrent parallel queries please see my Open World 2015 presentation here, https://published-rs.lanyonevents.com/published/oracleus2015/sessionsFiles/3067/CON8739_Baskan-OW2015_Yasin_Baskan_CON8739_with_notes.pdf.


  • guest Tuesday, October 11, 2016

    HI There, Thanks for the pdf, it answered lots of my questions. I see the below statement in one of the Oracle websites:

    Automatic degree of parallelism will be enabled regardless of the value of PARALLEL_DEGREE_POLICY (MANUAL/LIMITED/AUTO/ADAPTIVE) if a PARALLEL hint is used at the SQL statement level.

    My question is, will parallel statement queuing also be enabled when a parallel hint is used (anyone of these parallel/parallel(10)/parallel(auto)/parallel(default))


  • Yasin Baskan Tuesday, October 11, 2016

    Hints do not enable parallel statement queuing. It is enabled by the parameter parallel_degree_policy. What is mentioned in that text is only the DOP calculation. If you specify the PARALLEL hint like "select /*+ parallel */", auto DOP will be used for the statement meaning the optimizer will calculate the DOP. The statement will not be queued if parallel statement queuing is not enabled by the parameter. Please see the documentation (http://docs.oracle.com/database/121/SQLRF/sql_elements006.htm#SQLRF51120) for hints to understand how different parallel hints behave.


  • guest Monday, October 17, 2016

    Hi Yasin,

    In Oracle 12C, By default how a sql query is executed, is that either parallel or serial regardless of parallel hint, if parallel_degree_policy is set to Auto.

    i.e., If parallel_degree_policy is set to Auto and I do not specify parallel hint in my sql query, will these three concepts (Auto DOP, parallel statement queuing and In Memory PX) be invoked?

    I want to know if it is always helpful (in fact not harmful) to enable or not.


  • Yasin Baskan Monday, October 17, 2016

    If parallel_degree_policy is set to AUTO and you do not have hints, the optimizer will calculate the DOP for you meaning that query will use auto DOP. Parallel statement queuing and in-memory PX will also be enabled.

    You need to test your application to see the effects of enabling auto DOP.

    Please have a look at our whitepaper that talks about these here, http://www.oracle.com/technetwork/articles/datawarehouse/twp-parallel-execution-fundamentals-133639.pdf


  • Baskar Wednesday, February 21, 2018
    Hi,

    Having a small doubt in the given calculation:

    parallel_max_degree is calculated as:
    MIN (CPU_COUNT * PARALLEL_THREADS_PER_CPU, PARALLEL_DEGREE_LIMIT, DBRM DIRECTIVE PARALLEL_DEGREE_LIMIT_P1) .

    SQL> select name, value from v$parameter
    2 where name in ('parallel_degree_limit','cpu_count','parallel_threads_per_cpu');
    NAME VALUE
    ------------------------------ ----------
    cpu_count 2048
    parallel_threads_per_cpu 2
    parallel_degree_limit CPU

    SQL> select name, value from v$ses_optimizer_env
    2 where name in ('parallel_degree_limit','parallel_max_degree')
    3 and sid=(select sid from v$mystat where rownum=1);
    NAME VALUE
    ------------------------------ ------------
    parallel_degree_limit 65535
    parallel_max_degree 0

    parallel_degree_limit is set to CPU. So the calculation for the last query should come like:

    min (2048*2, CPU which is nothing but 2048, _p1 directive).

    But, the explain plan notes section showed:
    computed degree of parallelism is 4096 because of degree limit.

    But, it should show/calculate to 2048 as the formula was min of (cpu_count*pthreadspercpu, CPU, _p1 directive).
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