“alter session force parallel query”, and indexes

May 17th, 2013 by Alberto Dell'Era

“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;

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

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

| 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

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

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

| 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

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

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

| 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

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

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

| 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

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

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

| 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

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

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

| 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