Search

OakieTags

Who's online

There are currently 0 users and 39 guests online.

Recent comments

Affiliations

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.