Serial plans: Threshold / Parallel_degree_limit = 1

As a very short follow up on the previous post. So here is some more on getting a serial plan and why that happens

Another reason - compared to the auto DOP is not on as we looked at in the earlier post - and often more prevalent to get a serial plan is if the plan simply does not take long enough to consider a parallel path. The resulting plan and note looks like this (note that this is a serial plan!):

explain plan for
select count(1) from sales;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 672559287

--------------------------------------------------------------------------------------

| Id  | Operation            | Name  | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |

--------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |     1 |     5   (0)| 00:00:01 |       |     |

|   1 |  SORT AGGREGATE      |       |     1 |            |          |       |     |

|   2 |   PARTITION RANGE ALL|       |   960 |     5   (0)| 00:00:01 |     1 |  16 |

|   3 |    TABLE ACCESS FULL | SALES |   960 |     5   (0)| 00:00:01 |     1 |  16 |

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

14 rows selected.

The parallel threshold is referring to parallel_min_time_threshold and since I did not change the default (10s) the plan is not being considered for a parallel degree computation and is therefore staying with the serial execution.

Now we go into the land of crazy:

Assume I do want this DOP=1 to happen, I could set the parameter in the init.ora, but to highlight it in this case I changed it on the session:

alter session set parallel_degree_limit = 1;

The result I get is:

ERROR:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00096: invalid value 1 for parameter parallel_degree_limit, must be from among CPU IO AUTO INTEGER>=2

Which of course makes perfect sense...

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
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today