12c – parallel_degree_level (control for auto DOP)
I heard JP Dijcks speak at RMOUG in 2012 about a new parameter that would show up in 12c called parallel_degree_level. It’s basically a knob that you can turn to dial up (or down) the calculated DOP when setting parallel_degree_policy=auto. Early on (11.2.0.1) auto DOP seemed to vastly overestimate what the DOP should be. In a later version (11.2.0.3) it seems to often underestimate what the DOP should be. I’ve said in the past that I thought auto DOP was too hard to control and thus too scary for production systems. I’ve also said that I thought auto DOP was the wave of the future. And I think this parameter alone may make it possible to use this feature in production because it gives us the ability to dial in the level of parallelism that works for our system. So here’s a quick demo:
SYS@db12c1> @parms
Enter value for parameter: parallel_degree
Enter value for isset:
Enter value for show_hidden:
NAME VALUE ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
parallel_degree_level 100 TRUE FALSE FALSE
parallel_degree_limit 16 FALSE FALSE TRUE
parallel_degree_policy AUTO FALSE TRUE TRUE
3 rows selected.
Elapsed: 00:00:00.05
SYS@db12c1> alter session set parallel_degree_policy=auto;
Session altered.
Elapsed: 00:00:00.00
SYS@db12c1> select count(*) from kso.TT_CLUSTER_ONAME;
COUNT(*)
------------
79429632
1 row selected.
Elapsed: 00:00:01.96
SYS@db12c1> @x
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID apvrg0vpxxw8k, child number 2
-------------------------------------
select count(*) from kso.TT_CLUSTER_ONAME
Plan hash value: 2036413816
--------------------------------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | PX COORDINATOR | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 |
| 4 | SORT AGGREGATE | | 1 |
| 5 | PX BLOCK ITERATOR | | 79M|
|* 6 | TABLE ACCESS STORAGE FULL| TT_CLUSTER_ONAME | 79M|
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - storage(:Z>=:Z AND :Z<=:Z)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 14
- parallel scans affinitized
31 rows selected.
Elapsed: 00:00:00.02
SYS@db12c1> alter session set parallel_degree_level=10;
Session altered.
Elapsed: 00:00:00.01
SYS@db12c1> select count(*) from kso.TT_CLUSTER_ONAME;
COUNT(*)
------------
79429632
1 row selected.
Elapsed: 00:00:19.95
SYS@db12c1> @x
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID apvrg0vpxxw8k, child number 4
-------------------------------------
select count(*) from kso.TT_CLUSTER_ONAME
Plan hash value: 2036413816
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 174K(100)| | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 79M| 174K (1)| 00:00:07 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS STORAGE FULL| TT_CLUSTER_ONAME | 79M| 174K (1)| 00:00:07 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - storage(:Z>=:Z AND :Z<=:Z)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
- parallel scans affinitized
31 rows selected.
Elapsed: 00:00:00.09
SYS@db12c1> alter session set parallel_degree_level=100;
Session altered.
Elapsed: 00:00:00.00
SYS@db12c1> select count(*) from kso.TT_CLUSTER_ONAME;
COUNT(*)
------------
79429632
1 row selected.
Elapsed: 00:00:04.07
SYS@db12c1> @x
Enter value for sql_id: apvrg0vpxxw8k
Enter value for child_no:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID apvrg0vpxxw8k, child number 2
-------------------------------------
select count(*) from kso.TT_CLUSTER_ONAME
Plan hash value: 2036413816
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 24875 (100)| | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 79M| 24875 (1)| 00:00:01 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS STORAGE FULL| TT_CLUSTER_ONAME | 79M| 24875 (1)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - storage(:Z>=:Z AND :Z<=:Z)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 14
- parallel scans affinitized
31 rows selected.
Elapsed: 00:00:00.09
SYS@db12c1> alter session set parallel_degree_level=200;
Session altered.
SYS@db12c1> select count(*) from kso.TT_CLUSTER_ONAME;
COUNT(*)
----------
79429632
Elapsed: 00:00:00.59
SYS@db12c1> @x
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID apvrg0vpxxw8k, child number 5
-------------------------------------
select count(*) from kso.TT_CLUSTER_ONAME
Plan hash value: 2036413816
--------------------------------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | PX COORDINATOR | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 |
| 4 | SORT AGGREGATE | | 1 |
| 5 | PX BLOCK ITERATOR | | 79M|
|* 6 | TABLE ACCESS STORAGE FULL| TT_CLUSTER_ONAME | 79M|
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - storage(:Z>=:Z AND :Z<=:Z)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 16 because of degree limit
- parallel scans affinitized
31 rows selected.
Elapsed: 00:00:00.12
SYS@db12c1> alter session set parallel_degree_limit=32;
Session altered.
Elapsed: 00:00:00.00
SYS@db12c1> select count(*) from kso.TT_CLUSTER_ONAME;
COUNT(*)
----------
79429632
Elapsed: 00:00:07.53
SYS@db12c1> @x
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID apvrg0vpxxw8k, child number 6
-------------------------------------
select count(*) from kso.TT_CLUSTER_ONAME
Plan hash value: 2036413816
--------------------------------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | PX COORDINATOR | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 |
| 4 | SORT AGGREGATE | | 1 |
| 5 | PX BLOCK ITERATOR | | 79M|
|* 6 | TABLE ACCESS STORAGE FULL| TT_CLUSTER_ONAME | 79M|
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - storage(:Z>=:Z AND :Z<=:Z)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 28
- parallel scans affinitized
31 rows selected.
Elapsed: 00:00:00.06
So as you can see, parallel_degree_level is basically a percentage. The default is 100 and setting it to a value of 10 decreases the calculated value to roughly 10% while increasing it to 200 doubles the calculated DOP.
So just to reiterate, the auto DOP calculations have gotten progressively better over the last couple of years, but I think the simple addition of this new parameter makes it a much more palatable option.
Hi
What is the contents @x.sql is it same as the dplan.sql from expert oracle exadata ?
Hi Jhon,
x.sql is one of Tanel’s scripts. It basically does this:
select * from table(dbms_xplan.display_cursor(null,null,’LAST’));
which just shows the plan for the last statement executed in your session. If you have serveroutput turned on, it will messup this approach since it causes a little pl/sql bit to be executed after every SQL statement in SQL*Plus. So “set serveroutput off”.
Kerry
Hi Kerry,
Thanks for sharing the information – as you know i have earlier been “scared” from using Auto DOP and i was wondering with the introduction of parallel_degree_level parameter, how well does it work when the system is under stress or are already utilizing a larger number of parallel slaves. To me it is all good that you have a percentage, to adjust the numbers; however it is my understanding that Auto DOP was supposed to resolve the hassle of working out what is the right number of DOP under different workloads.
All the best,
– Peter
Hi Peter,
Yes – that’s the idea. You don’t have to work it all out, just let Oracle do that. 12c appears to be better as the calculations have had some extra work and include consideration of the current workload on the box (I’ve heard but not tested). I have not seen a 12c DB in production using this setting yet by the way, so as with everything – your mileage may vary. My point in the post was that there is now an easy, documented way to scale all the calculated DOP values up or down. 11.2.0.3 was actually pretty good but tended to pick relatively low values for DOP (which was better than early versions which seemed to go the other direction) in my observations. We have actually come close to implementing it a couple of times with 11.2.0.3. This knob would probably have let us give it a try in those cases. Hope that helps.
Kerry
[…] […]