Oracle is ignoring my DOP

Some time ago I came over interesting problem with parallel execution in Oracle Database 11g Release 2 (11.2.0.4 PSU 5) which I think is worth sharing.

One of the programmers came to me claiming, that Oracle is totally ignoring his parallel degree which he set with parallel hint. Query looked in principle like this (please keep in mind that query is specifically tailored for problem to manifest):

SELECT
            sel.item_id,
            COUNT(*)
FROM
            (SELECT
                       /*+ full(line) parallel(line, 2) */
                       DISTINCT item.item_id,
                       item.order_date
            FROM order_line line,
                      order_item item
            WHERE line.line_id = item.line_id
                       AND line.order_date = item.order_date
                       AND line.order_date BETWEEN '01012014' AND '31122014'
            ) sel,
            order_item_detail detail
WHERE detail.order_date BETWEEN '01012014' AND '31122014'
           AND detail.order_date = sel.order_date
           AND detail.item_id = sel.item_id
GROUP BY sel.item_id;

All tables and ranged partitioned by quarter of year on DATE column order_date. All indexes are local with no compression.

So I ran it and checked parallel query overview:
Username     QC/Slave Slave Set  SID  QC SID  Requested DOP Actual DOP ------------ -------- ---------- ---- ------- ------------- ----------  - p000      (Slave)  1          364  435                 4          4  - p001      (Slave)  1          433  435                 4          4  - p002      (Slave)  1          189  435                 4          4  - p003      (Slave)  1          249  435                 4          4  - p008      (Slave)  2          129  435                 4          4  - p009      (Slave)  2          188  435                 4          4  - p010      (Slave)  2          248  435                 4          4  - p011      (Slave)  2          304  435                 4          4 MYDBA        QC                  435  435  

As you can see, he was requesting DOP 2, but our parallel query overview claims he requested DOP 4. Even if that was true, how is it that we see 8 parallel slaves?

You can see something called Slave Set in our query witch has value 1 and 2. This means that Oracle has created two slave sets for processing of our query. This is because Oracle identified two operations which can be done at same time. Basically slave set 1 is producing data for slaves in set 2 which are performing our aggregation operation. Each set is respecting requested DOP, but together, they go double the DOP originally requested.

So this is why DOP 8 and not 4. Now let's try to find out why DOP 4 when we wanted 2. Let's check execution plan first:

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                                       | Name              | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |  OMem |  1Mem |  O/1/M   | Max-Tmp | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                                |                   |        |       |       | 87984 (100)|          |       |       |        |      |            |       |       |          |         | |   1 |  PX COORDINATOR                                 |                   |        |       |       |            |          |       |       |        |      |            |       |       |          |         | |   2 |   PX SEND QC (RANDOM)                           | :TQ10006          |  57770 |  1692K|       | 87984   (1)| 00:20:32 |       |       |  Q1,06 | P->S | QC (RAND)  |       |       |          |         | |   3 |    HASH GROUP BY                                |                   |  57770 |  1692K|  2288K| 87984   (1)| 00:20:32 |       |       |  Q1,06 | PCWP |            |  5747K|  2487K|          |         | |   4 |     PX RECEIVE                                  |                   |  57770 |  1692K|       | 87982   (1)| 00:20:32 |       |       |  Q1,06 | PCWP |            |       |       |          |         | |   5 |      PX SEND HASH                               | :TQ10005          |  57770 |  1692K|       | 87982   (1)| 00:20:32 |       |       |  Q1,05 | P->P | HASH       |       |       |          |         | |*  6 |       HASH JOIN BUFFERED                        |                   |  57770 |  1692K|       | 87982   (1)| 00:20:32 |       |       |  Q1,05 | PCWP |            |    32M|  7375K|    12/0/0|    6144 | |   7 |        PX RECEIVE                               |                   |    132K|  1939K|       | 45982   (1)| 00:10:44 |       |       |  Q1,05 | PCWP |            |       |       |          |         | |   8 |         PX SEND HASH                            | :TQ10003          |    132K|  1939K|       | 45982   (1)| 00:10:44 |       |       |  Q1,03 | P->P | HASH       |       |       |          |         | |   9 |          VIEW                                   |                   |    132K|  1939K|       | 45982   (1)| 00:10:44 |       |       |  Q1,03 | PCWP |            |       |       |          |         | |  10 |           HASH UNIQUE                           |                   |    132K|  4525K|  9104K| 45982   (1)| 00:10:44 |       |       |  Q1,03 | PCWP |            |    32M|  6786K|     8/0/0|         | |  11 |            PX RECEIVE                           |                   |    192K|  6590K|       | 45976   (1)| 00:10:44 |       |       |  Q1,03 | PCWP |            |       |       |          |         | |  12 |             PX SEND HASH                        | :TQ10001          |    192K|  6590K|       | 45976   (1)| 00:10:44 |       |       |  Q1,01 | P->P | HASH       |       |       |          |         | |* 13 |              FILTER                             |                   |        |       |       |            |          |       |       |  Q1,01 | PCWC |            |       |       |          |         | |  14 |               NESTED LOOPS                      |                   |    192K|  6590K|       | 45976   (1)| 00:10:44 |       |       |  Q1,01 | PCWP |            |       |       |          |         | |  15 |                NESTED LOOPS                     |                   |    192K|  6590K|       | 45976   (1)| 00:10:44 |       |       |  Q1,01 | PCWP |            |       |       |          |         | |  16 |                 PX BLOCK ITERATOR               |                   |  29124 |   398K|       |   930   (1)| 00:00:14 |   KEY |   KEY |  Q1,01 | PCWC |            |       |       |          |         | |* 17 |                  TABLE ACCESS FULL              | ITEM_LINE         |  29124 |   398K|       |   930   (1)| 00:00:14 |   KEY |   KEY |  Q1,01 | PCWP |            |       |       |          |         | |  18 |                 PARTITION RANGE AND             |                   |      1 |       |       |     2   (0)| 00:00:01 |KEY(AP)|KEY(AP)|  Q1,01 | PCWP |            |       |       |          |         | |* 19 |                  INDEX RANGE SCAN               | ITEM_ORDER_IX2    |      1 |       |       |     2   (0)| 00:00:01 |KEY(AP)|KEY(AP)|  Q1,01 | PCWP |            |       |       |          |         | |  20 |                TABLE ACCESS BY LOCAL INDEX ROWID| ITEM_ORDER        |      7 |   147 |       |     3   (0)| 00:00:01 |     1 |     1 |  Q1,01 | PCWP |            |       |       |          |         | |  21 |        PX RECEIVE                               |                   |    778K|    11M|       | 41998   (2)| 00:09:48 |       |       |  Q1,05 | PCWP |            |       |       |          |         | |  22 |         PX SEND HASH                            | :TQ10004          |    778K|    11M|       | 41998   (2)| 00:09:48 |       |       |  Q1,04 | P->P | HASH       |       |       |          |         | |  23 |          VIEW                                   | index$_join$_005  |    778K|    11M|       | 41998   (2)| 00:09:48 |       |       |  Q1,04 | PCWP |            |       |       |          |         | |* 24 |           HASH JOIN                             |                   |        |       |       |            |          |       |       |  Q1,04 | PCWP |            |    14M|  3248K|    12/0/0|         | |  25 |            PX RECEIVE                           |                   |    778K|    11M|       |  6406   (2)| 00:01:30 |       |       |  Q1,04 | PCWP |            |       |       |          |         | |  26 |             PX SEND HASH                        | :TQ10002          |    778K|    11M|       |  6406   (2)| 00:01:30 |       |       |  Q1,02 | P->P | HASH       |       |       |          |         | |  27 |              PX BLOCK ITERATOR                  |                   |    778K|    11M|       |  6406   (2)| 00:01:30 |   KEY |   KEY |  Q1,02 | PCWC |            |       |       |          |         | |* 28 |               INDEX FAST FULL SCAN              | ITEM_ORDR_DET_IX2 |    778K|    11M|       |  6406   (2)| 00:01:30 |   KEY |   KEY |  Q1,02 | PCWP |            |       |       |          |         | |  29 |            BUFFER SORT                          |                   |        |       |       |            |          |       |       |  Q1,04 | PCWC |            |    12M|  1246K|     8/0/0|         | |  30 |             PX RECEIVE                          |                   |    778K|    11M|       | 29363   (1)| 00:06:52 |       |       |  Q1,04 | PCWP |            |       |       |          |         | |  31 |              PX SEND HASH                       | :TQ10000          |    778K|    11M|       | 29363   (1)| 00:06:52 |       |       |        | S->P | HASH       |       |       |          |         | |  32 |               PARTITION RANGE ITERATOR          |                   |    778K|    11M|       | 29363   (1)| 00:06:52 |   KEY |   KEY |        |      |            |       |       |          |         | |* 33 |                INDEX FAST FULL SCAN             | ITEM_ORDR_DET_IX1 |    778K|    11M|       | 29363   (1)| 00:06:52 |   KEY |   KEY |        |      |            |       |       |          |         | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Well, nothing special there for a first sight, but DOP has to run up from somewhere. Let's try to limit it in our session by:

ALTER SESSION FORCE PARALLEL QUERY PARALLEL 3;

And run our query:
Username   QC/Slave Slave Set  SID   QC SID  Requested DOP Actual DOP ---------- -------- ---------- ----- ------- ------------- ----------  - p000    (Slave)  1          360   435                 3          3  - p001    (Slave)  1          436   435                 3          3  - p002    (Slave)  1          189   435                 3          3  - p003    (Slave)  2          249   435                 3          3  - p004    (Slave)  2          307   435                 3          3  - p005    (Slave)  2          365   435                 3          3 MYDBA      QC                  435   435                            

As we can see our DOP is 3 now. This must be an application of rule where if there is no DOP set, Oracle will use default DOP. We are limiting DOP of tables by our hint and this should be inherited to all tables, which are part of parallel query. From execution plan, we can see that Oracle is also performing parallel execution on some indexes with index fast full scan. Normally if Oracle sets to run parallel on indexes also, he'll use DOP which is set for the query. But it seems that he ignored our DOP from tables and overridden it with default value from parallel scan of indexes, since we did not specifically set it. Let's test our query with following hints then:

SELECT /*+ parallel_index(detail, item_ordr_det_ix1, 2) parallel_index(detail, item_ordr_det_ix2, 2) */
            sel.item_id,
            COUNT(*)
FROM
            (SELECT
                       /*+ full(line) parallel(line, 2) */
                       DISTINCT item.item_id,
                       item.order_date
            FROM order_line line,
                      order_item item
            WHERE line.line_id = item.line_id
                       AND line.order_date = item.order_date
                       AND line.order_date BETWEEN '01012014' AND '31122014'
            ) sel,
            order_item_detail detail
WHERE detail.order_date BETWEEN '01012014' AND '31122014'
           AND detail.order_date = sel.order_date
           AND detail.item_id = sel.item_id
GROUP BY sel.item_id;

Username  QC/Slave Slave Set  SID  QC SID  Requested DOP Actual DOP --------- -------- ---------- ---- ------- ------------- ----------  - p000   (Slave)  1          360  435                 2          2  - p001   (Slave)  1          436  435                 2          2  - p002   (Slave)  2          189  435                 2          2  - p003   (Slave)  2          249  435                 2          2 MYDBA     QC                  435  435                            

Now that's a nice BUG. Oracle has really overridden our DOP with default DOP from index parallel scans in index join.

This behavior is very hard to reproduce (I was not able to do so with my own data) and I have seen it only twice in very particular setup, so you might never run into it. But it's good to be prepared.