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.