Alter session force parallel query doesn’t really force anything

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.

This entry was posted in Oracle. Bookmark the permalink.

4 Responses to Alter session force parallel query doesn’t really force anything

  1. Matt Morris says:

    Long Live Auto Degree of Parallelism…..

    Good post. We have also found that the darn statistics based Cost Based Optimizer actually uses statistics to choose poorly or choose wisely.
    The values associated with the following SQL will can also affect the how/when and how many threads CBO may choose to startup.
    Reducing/increasing the value “200” will indicate to CBO how much data a single thread will read. The lower the number the more threads CBO will start. The higher the number the fewer number of threads CBO will start. Potentially not even choosing a parallel query –

    delete from resource_io_calibrate$;
    insert into resource_io_calibrate$
    values(current_timestamp, current_timestamp, 0, 0, 200, 0, 0);
    commit;

  2. Alberto Dell'Era says:

    It is also interesting to note that “alter session force parallel query parallel N” sets the degree of parallelism to be used for all indexes too
    – and that is much more handy than using the parallel_index hint or altering their degree to N :)
    At least that is what I observed in 9.2.0.8 and 11.2.0.3

  3. Tony Hasler says:

    Thanks! Another basic misconception on my part corrected! Two clarifications:
    1) Hints override any session level settings but these too don’t force parallel operations
    2) Forcing parallel DML or parallel DDL does seem to do what it says on the tin (in the absence of hints). The query won’t necessarily be parallelized but the writes will be.

  4. Dax says:

    Good Post!

    I have one questions regarding stats. When should one gather stats on index ? does table stats is good enough for optimizer to select best execution plan ?

    Regards
    Dax

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>