Jonathan Lewis has already written about this behavior from the angle of PARALLEL hints.
I’m writing a similar article just because the word FORCE in the ALTER SESSION FORCE PARALLEL QUERY syntax. Force should mean that some behavior would always happen (when possible), right? Let’s test:
SQL> CREATE TABLE t AS SELECT * FROM dba_objects; Table created. SQL> CREATE INDEX i ON t(owner); Index created. SQL> @gts t Gather Table Statistics for table t... PL/SQL procedure successfully completed.
Now let’s “force” the parallel query in my session, run the query and check the execution plan:
SQL> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 2;
Session altered.
SQL> SELECT SUM(object_id) FROM t WHERE owner LIKE 'S%';
SUM(OBJECT_ID)
--------------
979900956
SQL> @x
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)|
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 186 (100)|
| 1 | SORT AGGREGATE | | 1 | 12 | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 6741 | 80892 | 186 (0)|
|* 3 | INDEX RANGE SCAN | I | 6741 | | 18 (0)|
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OWNER" LIKE 'S%')
filter("OWNER" LIKE 'S%')
The output shows a regular, serial execution plan!
Hmm, let’s increase the “forced” parallelism from 2 to 3 and run exactly the same query again:
SQL> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 3;
Session altered.
SQL> SELECT SUM(object_id) FROM t WHERE owner LIKE 'S%';
SUM(OBJECT_ID)
--------------
979900956
SQL> @x
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 128 (100)| | | |
| 1 | SORT AGGREGATE | | 1 | 12 | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 12 | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 12 | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 6741 | 80892 | 128 (0)| Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL| T | 6741 | 80892 | 128 (0)| Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(:Z>=:Z AND :Z<=:Z)
filter("OWNER" LIKE 'S%')
Now the query will get a parallel plan!
The reason for this behavior is that the FORCE parallel query syntax doesn’t really force Oracle to use a parallel plan, but rather just reduces optimizer cost estimates _for full table scans _(the higher the parallelism, the lower the FTS costs – Jonathan already has details about this in his blog entry, so I won’t replicate this). But the optimizer is still free to choose some other, non-parallel execution plan if that has a lower cost than the best parallel one!
So what happened above is that with “forced” parallel degree 2, the parallel full table scan plan must have had a higher cost than the serial index range scan (186), but once I increased the parallelism “factor” to 3, then the final cost of the parallel full table scan plan ended up being lower (128) than the best serial plan found.
This is a good example showing that both the PARALLEL hints and the FORCE PARALLEL session settings really just adjust a narrow set of optimizer cost computation inputs and don’t really fix the resulting execution plan. If you really want to fix an execution plan, you need to tie optimizer “hands” in every aspect with a full set of hints just like the stored profiles do. That way, even if there is a lower cost plan available, the optimizer doesn’t know about it as you’ve prohibited it from doing any calculations other than your hints direct it to.
Note that when testing this, your mileage may vary, depending on how much data you have in your test table (or rather in the optimizer stats for that table) plus system stats.
