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…