“alter session force parallel query”, and indexes
“force parallel query” and hinting: test case
Let’s show that altering the session is equivalent to hinting. I will illustrate the simplest case only – a single-table statement that can be resolved either by a full table scan or an index fast full scan (check script force_parallel_main.sql in the test case), but in the test case zip two other scenarios (a join and a subquery) are tested as well. Note: I have only checked 9.2.0.8 and 11.2.0.3 (but I would be surprised if the test case could not reproduce in 10g as well).
Table “t” has an index t_idx on column x, and hence the statement
select sum(x) from t;
can be calculated by either scanning the table or the index. In serial, the CBO chooses to scan the smaller index (costs are from 11.2.0.3):
select /* serial */ sum(x) from t;
--------------------------------------
|Id|Operation |Name |Cost|
--------------------------------------
| 0|SELECT STATEMENT | | 502|
| 1| SORT AGGREGATE | | |
| 2| INDEX FAST FULL SCAN|T_IDX| 502|
--------------------------------------
If we now activate parallelism for the table, but not for the index, the CBO chooses to scan the table:
select /*+ parallel(t,20) */ sum(x) from t
------------------------------------------
|Id|Operation |Name |Cost|
------------------------------------------
| 0|SELECT STATEMENT | | 229|
| 1| SORT AGGREGATE | | |
| 2| PX COORDINATOR | | |
| 3| PX SEND QC (RANDOM) |:TQ10000| |
| 4| SORT AGGREGATE | | |
| 5| PX BLOCK ITERATOR | | 229|
| 6| TABLE ACCESS FULL|T | 229|
------------------------------------------
since the cost for the parallel table access is now down from the serial cost of 4135 (check the test case logs) to the parallel cost 4135 / (0.9 * 20) = 229, thus less than the cost (502) of the serial index access.
Hinting the index as well makes the CBO apply the same scaling factor (0.9*20) to the index as well, and hence we are back to index access:
select /*+ parallel_index(t, t_idx, 20) parallel(t,20) */ sum(x) from t
---------------------------------------------
|Id|Operation |Name |Cost|
---------------------------------------------
| 0|SELECT STATEMENT | | 28|
| 1| SORT AGGREGATE | | |
| 2| PX COORDINATOR | | |
| 3| PX SEND QC (RANDOM) |:TQ10000| |
| 4| SORT AGGREGATE | | |
| 5| PX BLOCK ITERATOR | | 28|
| 6| INDEX FAST FULL SCAN|T_IDX | 28|
---------------------------------------------
Note that the cost computation is 28 = 502 / (0.9 * 20), less than the previous one (229).
“Forcing” parallel query:
alter session force parallel query parallel 20;
select /* force parallel query */ sum(x) as from t
---------------------------------------------
|Id|Operation |Name |Cost|
---------------------------------------------
| 0|SELECT STATEMENT | | 28|
| 1| SORT AGGREGATE | | |
| 2| PX COORDINATOR | | |
| 3| PX SEND QC (RANDOM) |:TQ10000| |
| 4| SORT AGGREGATE | | |
| 5| PX BLOCK ITERATOR | | 28|
| 6| INDEX FAST FULL SCAN|T_IDX | 28|
---------------------------------------------
Note that the plan is the same (including costs), as predicted.
Side note: let’s verify, just for fun, that the statement can run serially even if the session is “forced” as parallel (note that I have changed the statement since the original always benefits from parallelism):
alter session force parallel query parallel 20;
select /* force parallel query (with no parallel execution) */ sum(x) from t
WHERE X < 0
----------------------------------
|Id|Operation |Name |Cost|
----------------------------------
| 0|SELECT STATEMENT | | 3|
| 1| SORT AGGREGATE | | |
| 2| INDEX RANGE SCAN|T_IDX| 3|
----------------------------------
Side note 2: activation of parallelism for all referenced objects can be obtained, in 11.2.0.3, using the new statement-level parallel hint (check this note by Randolf Geist for details):
select /*+ parallel(20) */ sum(x) from t
---------------------------------------------------
|Id|Operation |Name |Table|Cost|
---------------------------------------------------
| 0|SELECT STATEMENT | | | 28|
| 1| SORT AGGREGATE | | | |
| 2| PX COORDINATOR | | | |
| 3| PX SEND QC (RANDOM) |:TQ10000| | |
| 4| SORT AGGREGATE | | | |
| 5| PX BLOCK ITERATOR | | | 28|
| 6| INDEX FAST FULL SCAN|T_IDX |T | 28|
---------------------------------------------------
Page 2 of 3 | Previous page | Next page