Parallel_degree_limit hierarchy – CPU, IO, Auto or Integer

Part 3 (or is it 4?) in this ongoing series on limiting DOPs.

In this post (kind of a follow up on the one about who rules over whom) I want to dig into the hierarchy between the settings CPU, IO, Auto and Integer. On top of that we should quickly set the record straight on AUTO.

Parallel_degree_limit = AUTO

Right now this is setting the parallel_degree_limit to CPU. The future might hold something much cooler, but for now you can either use it and it is CPU, or ignore it and set it to CPU. Setting it to CPU assumes you use DBRM to set a specific limit per consumer group depending on your workload.

The Degree Limiting Hierarchy

Assume my parallel_degree_limit values are as follows (relatively arbitrarily chosen to make a point):

CPU = 8

IO = 5

I will see the following behavior with my init.ora setting being parallel_degree_limit = CPU (the default out of box):

explain plan for
select count(1) from
  (select t1.pk from tbl t1, tbl t2 where t1.pk = t2.pk);
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3965211852
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |          |     1 |    10 |  3781   (1)| 00:00:30 |        |      |            |
|   1 |  SORT AGGREGATE           |          |     1 |    10 |            |     |        |      |            |
|   2 |   PX COORDINATOR          |          |       |       |            |     |        |      |            |
|   3 |    PX SEND QC (RANDOM)    | :TQ10002 |     1 |    10 |            |     |  Q1,02 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE        |          |     1 |    10 |            |     |  Q1,02 | PCWP |            |
|*  5 |      HASH JOIN            |          |   300K|  2929K|  3781   (1)| 00:00:30 |  Q1,02 | PCWP |            |
|   6 |       PX RECEIVE          |          |   300K|  1464K|  1886   (1)| 00:00:15 |  Q1,02 | PCWP |            |
|   7 |        PX SEND HASH       | :TQ10000 |   300K|  1464K|  1886   (1)| 00:00:15 |  Q1,00 | P->P | HASH       |
|   8 |         PX BLOCK ITERATOR |          |   300K|  1464K|  1886   (1)| 00:00:15 |  Q1,00 | PCWC |            |
|   9 |          TABLE ACCESS FULL| TBL      |   300K|  1464K|  1886   (1)| 00:00:15 |  Q1,00 | PCWP |            |
|  10 |       PX RECEIVE          |          |   300K|  1464K|  1886   (1)| 00:00:15 |  Q1,02 | PCWP |            |
|  11 |        PX SEND HASH       | :TQ10001 |   300K|  1464K|  1886   (1)| 00:00:15 |  Q1,01 | P->P | HASH       |
|  12 |         PX BLOCK ITERATOR |          |   300K|  1464K|  1886   (1)| 00:00:15 |  Q1,01 | PCWC |            |
|  13 |          TABLE ACCESS FULL| TBL      |   300K|  1464K|  1886   (1)| 00:00:15 |  Q1,01 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("T1"."PK"="T2"."PK")
Note
-----
  - automatic DOP: Computed Degree of Parallelism is 8 because of degree limit

29 rows selected.

As we expected here, the degree limit (if capped) is capped by CPU, being the 8 mentioned here.

Now if I cap this at IO (using the session to do so in this case) or capping it at the integer 5 you will get the degree capped at 5. Again, as we expected.

Now I'll raise the parallel_degree_limit to 20 (which is > 8). The degree limit actually enforced will be:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3965211852

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |          |     1 |    10 |  3781   (1)| 00:00:30 |        |      |            |
|   1 |  SORT AGGREGATE           |          |     1 |    10 |            |          |        |      |            |
|   2 |   PX COORDINATOR          |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)    | :TQ10002 |     1 |    10 |            |          |  Q1,02 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE        |          |     1 |    10 |            |          |  Q1,02 | PCWP |            |
|*  5 |      HASH JOIN            |          |   300K|  2929K|  3781   (1)| 00:00:30 |  Q1,02 | PCWP |            |
|   6 |       PX RECEIVE          |          |   300K|  1464K|  1886   (1)| 00:00:15 |  Q1,02 | PCWP |            |
|   7 |        PX SEND HASH       | :TQ10000 |   300K|  1464K|  1886   (1)| 00:00:15 |  Q1,00 | P->P | HASH       |
|   8 |         PX BLOCK ITERATOR |          |   300K|  1464K|  1886   (1)| 00:00:15 |  Q1,00 | PCWC |            |
|   9 |          TABLE ACCESS FULL| TBL      |   300K|  1464K|  1886   (1)| 00:00:15 |  Q1,00 | PCWP |            |
|  10 |       PX RECEIVE          |          |   300K|  1464K|  1886   (1)| 00:00:15 |  Q1,02 | PCWP |            |
|  11 |        PX SEND HASH       | :TQ10001 |   300K|  1464K|  1886   (1)| 00:00:15 |  Q1,01 | P->P | HASH       |
|  12 |         PX BLOCK ITERATOR |          |   300K|  1464K|  1886   (1)| 00:00:15 |  Q1,01 | PCWC |            |
|  13 |          TABLE ACCESS FULL| TBL      |   300K|  1464K|  1886   (1)| 00:00:15 |  Q1,01 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("T1"."PK"="T2"."PK")

Note
-----
  - automatic DOP: Computed Degree of Parallelism is 8 because of degree limit

In other words, the hierarchy being enforced is:

CPU is the #1 degree_limit cap which is always in place. If IO or my integer value is lower than the CPU boundary then they are enforced. If they are higher, they will be ignored.

Comments:

Post a Comment:
Comments are closed for this entry.
About

The data warehouse insider is written by the Oracle product management team and sheds lights on all thing data warehousing and big data.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
4
5
6
7
8
9
10
11
12
13
14
16
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today