Oracle 21c / 23c New Features - move a partitioned table in a single command

Just a quick note about a nice little enhancement that might have gone unnoticed so far - since 21c it's now possible to move all partitions and subpartitions of a partitioned heap table using a single ALTER TABLE MOVE command.

So before 21c - if you had a (sub)partitioned heap table and tried to do:

ALTER TABLE <PART_TABLE> MOVE ...

then you got the error message "ORA-14511: cannot perform operation on a partitioned object" and you had to move each physical segment separately - so for a composite partitioned table this was only allowed on subpartition level, for partitioned tables on partition level.

Now from 21c on this is possible and also officially documented here and here for example.

Note that it looks like that it is still not supported to move a partition in case the table is subpartitioned - this still throws the error message "ORA-14257: cannot move a partition which is a composite partition" - so the feature at present is limited to moving the entire table on global level.

However, what is still not possible - even in 23c (FREE) - rebuilding a (sub)partitioned index using a single command.

So running:

ALTER INDEX <PART_INDEX> REBUILD ...

on a (sub)partitioned index still throws the error message: "ORA-14086: a partitioned index may not be rebuilt as a whole", which is a pity - and leaves you again only with the option to perform the rebuilds on the lowest level.

Which might also explain the limitation of above new feature to heap tables only - index organized tables are not supported (and I haven't checked clustered tables).

And the very handy package DBMS_INDEX_UTL which allows very easy handling of (sub)partitioned index rebuilds is still not documented yet, which is even more a pity.

Oracle 23c FREE - Unrestricted Direct Path Loads glitches

Playing around with some new features added in 23c - here the removal of the restriction so far that Direct Path loads or Parallel DML prevented access to the same object within the same transaction - which meant that before you could access the same object again within the same session after manipulating it using Direct Path loads or Parallel DML the transaction had to be committed - please note that this applies only to the session performing the manipulation - other sessions outside the transaction won't notice any difference. 

So sometimes if you wanted to take advantage of these features you had to split a single transaction (from a business point of view) into multiple transactions due to this technical limitation. I assume this difference in behaviour was one of the main reasons why Oracle requires you to enable Parallel DML explicitly using ALTER SESSION ENABLE PARALLEL DML on session level or from 12c on using the ENABLE_PARALLEL_DML hint on statement level.

So generally speaking it's good to see that Oracle in 23c finally lifts this long lasting restriction - now users can make use of Direct Path and / or Parallel DML operations without having to worry about behaviour changes and splitting up transactions.

Tim Hall at Oracle-Base as usual has this new feature already covered - so you can check his notes here before continuing to get an idea what this is about.

Here I want to focus on what you get when you try this new feature with 23c FREE - Parallel Execution isn't supported in 23c FREE as far as I can see, and doesn't make a lot of sense given the limitations regarding maximum number of CPU threads allowed - but Direct Path loads are something you can certainly make use of in 23c FREE.

So to get an idea here is a very simple script (similar to what Tim used to demonstrate) using Session Statistics to confirm that Direct Path writes happened:

alter session set nls_language = american;

set echo on verify off

column value_start new_value value_start

column value_end new_value value_end

drop table /*if exists*/ t1 purge;

create table t1

-- Looks like the restriction on the tablespace isn't implemented properly in 23c FREE!

--tablespace TEST_8K

tablespace TEST_8K_ASSM_AUTO

as

select *

from   all_objects;

select count(*) from t1;

set termout off

select value as value_start from v$sesstat ss, v$statname sn

where sn.statistic# = ss.statistic#

and sn.name = 'physical writes direct'

and ss.sid = userenv('sid');

set termout on

insert /*+ append */ into t1

select * from t1;

set termout off

select value as value_end from v$sesstat ss, v$statname sn

where sn.statistic# = ss.statistic#

and sn.name = 'physical writes direct'

and ss.sid = userenv('sid');

set termout on

set echo off

select &value_end - &value_start as "Diff physical writes direct" from dual;

set echo on

select count(*) from t1;

set termout off

select value as value_start from v$sesstat ss, v$statname sn

where sn.statistic# = ss.statistic#

and sn.name = 'physical writes direct'

and ss.sid = userenv('sid');

set termout on

insert /*+ append */ into t1

select * from t1;

set termout off

select value as value_end from v$sesstat ss, v$statname sn

where sn.statistic# = ss.statistic#

and sn.name = 'physical writes direct'

and ss.sid = userenv('sid');

set termout on

set echo off

select &value_end - &value_start as "Diff physical writes direct" from dual;

set echo on

commit;

select count(*) from t1;

set termout off

select value as value_start from v$sesstat ss, v$statname sn

where sn.statistic# = ss.statistic#

and sn.name = 'physical writes direct'

and ss.sid = userenv('sid');

set termout on

insert /*+ append NO_MULTI_APPEND */ into t1

select * from t1;

set termout off

select value as value_end from v$sesstat ss, v$statname sn

where sn.statistic# = ss.statistic#

and sn.name = 'physical writes direct'

and ss.sid = userenv('sid');

set termout on

set echo off

select &value_end - &value_start as "Diff physical writes direct" from dual;

set echo on

select /*+ NO_MULTI_APPEND */ count(*) from t1;

select count(*) from t1;

set termout off

select value as value_start from v$sesstat ss, v$statname sn

where sn.statistic# = ss.statistic#

and sn.name = 'physical writes direct'

and ss.sid = userenv('sid');

set termout on

insert /*+ append */ into t1

select * from t1;

set termout off

select value as value_end from v$sesstat ss, v$statname sn

where sn.statistic# = ss.statistic#

and sn.name = 'physical writes direct'

and ss.sid = userenv('sid');

set termout on

set echo off

select &value_end - &value_start as "Diff physical writes direct" from dual;

set echo on

commit;

select count(*) from t1;

And here is the 19c output:

SQL*Plus: Release 21.0.0.0.0 - Production on Wed Aug 16 17:14:06 2023

Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Letzte erfolgreiche Anmeldezeit: Mi Aug 16 2023 17:08:15 +02:00

Verbunden mit:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.19.0.0.0

Session altered.

SQL> column value_start new_value value_start

SQL> column value_end new_value value_end

SQL> drop table /*if exists*/ t1 purge;

Table dropped.

SQL> create table t1

  2  -- Looks like the restriction on the tablespace isn't implemented properly in 23c FREE!

  3  --tablespace TEST_8K

  4  tablespace TEST_8K_ASSM_AUTO

  5  as

  6  select *

  7  from   all_objects;

Table created.

SQL> select count(*) from t1;

  COUNT(*)

----------

     67771

SQL> set termout off

SQL> insert /*+ append */ into t1

  2  select * from t1;

67771 rows created.

SQL> set termout off

SQL> set echo off

Diff physical writes direct

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

                       1329

SQL> select count(*) from t1;

select count(*) from t1

                     *

ERROR at line 1:

ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> set termout off

SQL> insert /*+ append */ into t1

  2  select * from t1;

insert /*+ append */ into t1

                          *

ERROR at line 1:

ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> set termout off

SQL> set echo off

Diff physical writes direct

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

                          0

SQL> commit;

Commit complete.

SQL> select count(*) from t1;

  COUNT(*)

----------

    135542

SQL> set termout off

SQL> insert /*+ append NO_MULTI_APPEND */ into t1

  2  select * from t1;

135542 rows created.

SQL> set termout off

SQL> set echo off

Diff physical writes direct

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

                       2658

SQL> select /*+ NO_MULTI_APPEND */ count(*) from t1;

select /*+ NO_MULTI_APPEND */ count(*) from t1

                                            *

ERROR at line 1:

ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> select count(*) from t1;

select count(*) from t1

*

ERROR at line 1:

ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> set termout off

SQL> insert /*+ append */ into t1

  2  select * from t1;

insert /*+ append */ into t1

*

ERROR at line 1:

ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> set termout off

SQL> set echo off

Diff physical writes direct

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

                          0

SQL> commit;

Commit complete.

SQL> select count(*) from t1;

  COUNT(*)

----------

    271084

As expected after performing an insert in APPEND mode the same object can't be accessed within the same transaction - the error "ORA-12838: cannot read/modify an object after modifying it in parallel" is raised (which isn't very concise anyway - this is not a parallel but a direct path modification) for both query and DML attempts. Only after ending the transaction using COMMIT access is possible again.

Now when reading the 23c documentation available so far - follow this link - the lifted restriction regarding Direct Path loads / Parallel DML comes with some interesting limitations (quoting from above link):

"However, the restrictions on the above operations still apply when:

• The tables are IOT or clustered tables. (The table must be a heap table.)
• The tablespace is not under Automatic Segment Space Management (ASSM). Temporary tables are not under ASSM, so this includes them as well.
• The tablespace is of uniform extent.

The restrictions on multiple queries and DML/PDML operations as well as the restriction on multiple direct-path inserts in the same session can be reinstated when needed by including the NO_MULTI_STATEMENT hint in SQL statements."

There is no NO_MULTI_STATEMENT hint by the way - as you will see later. So when the tablespace used for the table (what about indexes on the table?) is not using ASSM and system managed extents the new feature should not be supported.

Here is what I get when I run the same script as above using 23c FREE and an ASSM AUTOALLOCATE tablespace - which should be a supported configuration:

SQL*Plus: Release 21.0.0.0.0 - Production on Wed Aug 16 17:00:29 2023

Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Wed Aug 16 2023 16:59:53 +02:00

Connected to:

Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

Version 23.2.0.0.0

SQL> column value_start new_value value_start

SQL> column value_end new_value value_end

SQL> drop table if exists t1 purge;

Table dropped.

SQL> create table t1

  2  -- Looks like the restriction on the tablespace isn't implemented properly in 23c FREE!

  3  --tablespace TEST_8K

  4  tablespace TEST_8K_ASSM_AUTO

  5  as

  6  select *

  7  from   all_objects;

Table created.

SQL> select count(*) from t1;

  COUNT(*)

----------

     75677

SQL> set termout off

SQL> insert /*+ append */ into t1

  2  select * from t1;

75677 rows created.

SQL> set termout off

SQL> set echo off

Diff physical writes direct

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

                       1582

SQL> select count(*) from t1;

  COUNT(*)

----------

    151354

SQL> set termout off

SQL> insert /*+ append */ into t1

  2  select * from t1;

151354 rows created.

SQL> set termout off

SQL> set echo off

Diff physical writes direct

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

                       3163

SQL> commit;

Commit complete.

SQL> select count(*) from t1;

  COUNT(*)

----------

    302708

SQL> set termout off

SQL> insert /*+ append NO_MULTI_APPEND */ into t1

  2  select * from t1;

302708 rows created.

SQL> set termout off

SQL> set echo off

Diff physical writes direct

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

                       6325

SQL> select /*+ NO_MULTI_APPEND */ count(*) from t1;

  COUNT(*)

----------

    302708

SQL> select count(*) from t1;

  COUNT(*)

----------

    302708

SQL> set termout off

SQL> insert /*+ append */ into t1

  2  select * from t1;

insert /*+ append */ into t1

*

ERROR at line 1:

ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> set termout off

SQL> set echo off

Diff physical writes direct

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

                          0

SQL> commit;

Commit complete.

SQL> select count(*) from t1;

  COUNT(*)

----------

    605416

Some observations: First it's really nice that now in the first part of the script several Direct Path loads are possible within the same transaction, and also accessing the manipulated object using SELECT within the transaction works as expected.

But check closely the second part of the script where I make use of the NO_MULTI_APPEND hint (which is apparently meant by the NO_MULTI_STATEMENT hint mentioned in the official documentation) that should revert to the previous behaviour.

While it is expected now that the second insert in APPEND mode fails again with the known "ORA-12838" error, the SELECT within the same transaction does not raise an error but what is even worse it returns a wrong result - it doesn't count the newly added rows but obviously only sees the rows that existed before the insert was executed.

It becomes even worse if a configuration gets used that isn't supported for the new feature - a tablespace using MSSM and / or uniform extent size - running the same script again only using a different tablespace:

SQL*Plus: Release 21.0.0.0.0 - Production on Wed Aug 16 16:59:53 2023

Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Wed Aug 16 2023 16:57:27 +02:00

Connected to:

Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

Version 23.2.0.0.0

SQL> column value_start new_value value_start

SQL> column value_end new_value value_end

SQL>

SQL> drop table if exists t1 purge;

Table dropped.

SQL> create table t1

  2  -- Looks like the restriction on the tablespace isn't implemented properly in 23c FREE!

  3  tablespace TEST_8K

  4  --tablespace TEST_8K_ASSM_AUTO

  5  as

  6  select *

  7  from   all_objects;

Table created.

SQL> select count(*) from t1;

  COUNT(*)

----------

     75677

SQL> set termout off

SQL> insert /*+ append */ into t1

  2  select * from t1;

75677 rows created.

SQL> set termout off

SQL> set echo off

Diff physical writes direct

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

                       1580

SQL> select count(*) from t1;

  COUNT(*)

----------

     75677

SQL> set termout off

SQL> insert /*+ append */ into t1

  2  select * from t1;

insert /*+ append */ into t1

*

ERROR at line 1:

ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> set termout off

SQL> set echo off

Diff physical writes direct

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

                          0

SQL> commit;

Commit complete.

SQL> select count(*) from t1;

  COUNT(*)

----------

    151354

SQL> set termout off

SQL> insert /*+ append NO_MULTI_APPEND */ into t1

  2  select * from t1;

151354 rows created.

SQL> set termout off

SQL> set echo off

Diff physical writes direct

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

                       3159

SQL> select /*+ NO_MULTI_APPEND */ count(*) from t1;

  COUNT(*)

----------

    151354

SQL> select count(*) from t1;

  COUNT(*)

----------

    151354

SQL> set termout off

SQL> insert /*+ append */ into t1

  2  select * from t1;

insert /*+ append */ into t1

*

ERROR at line 1:

ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> set termout off

SQL> set echo off

Diff physical writes direct

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

                          0

SQL> commit;

Commit complete.

SQL> select count(*) from t1;

  COUNT(*)

----------

    302708

Now the same wrong results problem also occurs in the first part of the script - the SELECT within the same transaction doesn't raise an error but shows a wrong result.

Conclusion so far: It's good to see that this very long lasting restriction finally gets lifted in 23c, but at least in 23c FREE you need to be careful - the implementation doesn't seem to be complete yet. Hopefully this will all be sorted out when 23c final gets released.

12c Parallel Execution New Features: Parallel FILTER Subquery Evaluation - Part 3: The Optimizer And Distribution Methods

As mentioned in the first and second part of this instalment the different available distribution methods of the new parallel FILTER are selected automatically by the optimizer - in this last post of this series I want to focus on that optimizer behaviour.

It looks like there are two new optimizer related parameters that control the behaviour of the new feature: "_px_filter_parallelized" is the overall switch to enable/disable the new parallel filter capability - and defaults to "true" in 12c, and "_px_filter_skew_handling" influences how the optimizer determines the distribution methods - the parameter naming suggests that it somehow has to do with some kind of "skew" - note that the internal parameter that handles the new automatic join skew handling is called "_px_join_skew_handling" - rather similar in name.

But even after playing around with the feature for quite a while I couldn't come up with a good test case where the optimizer chose a different distribution method based on the typical data distribution skew patterns - so that the expression used for the FILTER lookup had some more popular values than others. So I got in touch with Yasin Baskan - product manager for Parallel Execution at Oracle, asking what kind of skew is meant to see a difference in behaviour.

As it turns out "skew" means something different in this context here. When the mentioned parameter "_px_filter_skew_handling" is set to "true" (default value in 12c) the optimizer will choose a different distribution method based on the size of object driving the filter. According to my tests this effectively means: If the object is such small that only one granule (usually 13 blocks) per PX slave can be assigned the optimizer will use automatically a HASH distribution, otherwise - if the object is larger than this threshold - no re-distribution will be selected. I wasn't able to come up with an example where the optimizer automatically comes up with the other available distribution method, which is RANDOM / ROUND-ROBIN (see previous post). To demonstrate the point, here is a small example:

create table t2 as select * from dba_objects where rownum <= 90000;

exec dbms_stats.gather_table_stats(null, 't2')

create table t3 as select * from dba_objects where rownum <= 90000;

exec dbms_stats.gather_table_stats(null, 't3')

explain plan for
select /*+ monitor
           parallel(4)
           --opt_param('_px_filter_skew_handling' 'false')
        */ count(*) from
        t3 t
        --(select /*+ no_merge */ a.* from t3 a) t
        --(select a.* from t3 a, t3 b where a.object_id = b.object_id) t
        where exists (select /*+ no_unnest */ 1 from t2 where t.object_id=t2.object_id);

-- Default plan, no redistribution before parallel FILTER
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |     1 |     5 |    32M  (1)| 00:21:13 |        |      |            |
|   1 |  SORT AGGREGATE         |          |     1 |     5 |            |          |        |      |            |
|   2 |   PX COORDINATOR        |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)  | :TQ10000 |     1 |     5 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE      |          |     1 |     5 |            |          |  Q1,00 | PCWP |            |
|*  5 |      FILTER             |          |       |       |            |          |  Q1,00 | PCWC |            |
|   6 |       PX BLOCK ITERATOR |          | 90000 |   439K|   114   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   7 |        TABLE ACCESS FULL| T3       | 90000 |   439K|   114   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|*  8 |       TABLE ACCESS FULL | T2       |     1 |     5 |   412   (1)| 00:00:01 |        |      |            |
-----------------------------------------------------------------------------------------------------------------

exec dbms_stats.set_table_stats(null, 't3', numblks => 52)

-- Setting stats of T3 to 52 (13 * DOP) blocks or smaller - HASH distribution will be used, 53 blocks or greater => no redistribution
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |          |     1 |     5 |    32M  (1)| 00:21:13 |        |      |            |
|   1 |  SORT AGGREGATE           |          |     1 |     5 |            |          |        |      |            |
|   2 |   PX COORDINATOR          |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)    | :TQ10001 |     1 |     5 |            |          |  Q1,01 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE        |          |     1 |     5 |            |          |  Q1,01 | PCWP |            |
|*  5 |      FILTER               |          |       |       |            |          |  Q1,01 | PCWP |            |
|   6 |       PX RECEIVE          |          | 90000 |   439K|     5  (20)| 00:00:01 |  Q1,01 | PCWP |            |
|   7 |        PX SEND HASH       | :TQ10000 | 90000 |   439K|     5  (20)| 00:00:01 |  Q1,00 | P->P | HASH       |
|   8 |         PX BLOCK ITERATOR |          | 90000 |   439K|     5  (20)| 00:00:01 |  Q1,00 | PCWC |            |
|   9 |          TABLE ACCESS FULL| T3       | 90000 |   439K|     5  (20)| 00:00:01 |  Q1,00 | PCWP |            |
|* 10 |       TABLE ACCESS FULL   | T2       |     1 |     5 |   412   (1)| 00:00:01 |        |      |            |
-------------------------------------------------------------------------------------------------------------------

So this example shows that the HASH distribution will be used by the optimizer if the object T3 driving the FILTER operation is 52 blocks or smaller, which corresponds to 13 blocks per PX slave at a degree of 4.

Now I find this behaviour pretty odd to explain - since usually you wouldn't want to use Parallel Execution on such a small object anyway. But things become even worse: Not only to me the "skew" handling based on the object size is questionable, but the behaviour can become a potential threat if the row source driving the FILTER operator no longer is a plain table but the result of a more complex operation, which can be simply a join or non-mergeable view:

-- Resetting stats to true size of table - this would mean no redistribution at a DOP of 4, see above
exec dbms_stats.gather_table_stats(null, 't3')

explain plan for
select /*+ monitor
           parallel(4)
           --opt_param('_px_filter_skew_handling' 'false')
        */ count(*) from
        --t3 t
        (select /*+ no_merge */ a.* from t3 a) t
        --(select a.* from t3 a, t3 b where a.object_id = b.object_id) t
        where exists (select /*+ no_unnest */ 1 from t2 where t.object_id=t2.object_id);

-- But simply using a NO_MERGE hint on the select from the simple T3 row source results in an unnecessary HASH re-distribution
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |          |     1 |    13 |  9755K  (1)| 00:06:22 |        |      |            |
|   1 |  SORT AGGREGATE            |          |     1 |    13 |            |          |        |      |            |
|   2 |   PX COORDINATOR           |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)     | :TQ10001 |     1 |    13 |            |          |  Q1,01 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE         |          |     1 |    13 |            |          |  Q1,01 | PCWP |            |
|*  5 |      FILTER                |          |       |       |            |          |  Q1,01 | PCWP |            |
|   6 |       PX RECEIVE           |          | 90000 |  1142K|   114   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   7 |        PX SEND HASH        | :TQ10000 | 90000 |  1142K|   114   (0)| 00:00:01 |  Q1,00 | P->P | HASH       |
|   8 |         VIEW               |          | 90000 |  1142K|   114   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   9 |          PX BLOCK ITERATOR |          | 90000 |   439K|   114   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|  10 |           TABLE ACCESS FULL| T3       | 90000 |   439K|   114   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|* 11 |       TABLE ACCESS FULL    | T2       |     1 |     5 |   114   (0)| 00:00:01 |        |      |            |
--------------------------------------------------------------------------------------------------------------------

explain plan for
select /*+ monitor
           parallel(4)
           --opt_param('_px_filter_skew_handling' 'false')
        */ count(*) from
        --t3 t
        --(select /*+ no_merge */ a.* from t3 a) t
        (select a.* from t3 a, t3 b where a.object_id = b.object_id) t
        where exists (select /*+ no_unnest */ 1 from t2 where t.object_id=t2.object_id);

-- If we use a simple join as driving row source again a HASH re-distribution before the FILTER gets added
-- As a result the dreaded HASH JOIN BUFFERED will be used instead of the plain HASH JOIN
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |          |     1 |    10 |    32M  (1)| 00:21:13 |        |      |            |
|   1 |  SORT AGGREGATE                 |          |     1 |    10 |            |          |        |      |            |
|   2 |   PX COORDINATOR                |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)          | :TQ10003 |     1 |    10 |            |          |  Q1,03 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE              |          |     1 |    10 |            |          |  Q1,03 | PCWP |            |
|*  5 |      FILTER                     |          |       |       |            |          |  Q1,03 | PCWP |            |
|   6 |       PX RECEIVE                |          | 90000 |   878K|   229   (1)| 00:00:01 |  Q1,03 | PCWP |            |
|   7 |        PX SEND HASH             | :TQ10002 | 90000 |   878K|   229   (1)| 00:00:01 |  Q1,02 | P->P | HASH       |
|*  8 |         HASH JOIN BUFFERED      |          | 90000 |   878K|   229   (1)| 00:00:01 |  Q1,02 | PCWP |            |
|   9 |          PX RECEIVE             |          | 90000 |   439K|   114   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|  10 |           PX SEND HYBRID HASH   | :TQ10000 | 90000 |   439K|   114   (0)| 00:00:01 |  Q1,00 | P->P | HYBRID HASH|
|  11 |            STATISTICS COLLECTOR |          |       |       |            |          |  Q1,00 | PCWC |            |
|  12 |             PX BLOCK ITERATOR   |          | 90000 |   439K|   114   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|  13 |              TABLE ACCESS FULL  | T3       | 90000 |   439K|   114   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  14 |          PX RECEIVE             |          | 90000 |   439K|   114   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|  15 |           PX SEND HYBRID HASH   | :TQ10001 | 90000 |   439K|   114   (0)| 00:00:01 |  Q1,01 | P->P | HYBRID HASH|
|  16 |            PX BLOCK ITERATOR    |          | 90000 |   439K|   114   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|  17 |             TABLE ACCESS FULL   | T3       | 90000 |   439K|   114   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|* 18 |       TABLE ACCESS FULL         | T2       |     1 |     5 |   412   (1)| 00:00:01 |        |      |            |
-------------------------------------------------------------------------------------------------------------------------

explain plan for
select /*+ monitor
           parallel(4)
           opt_param('_px_filter_skew_handling' 'false')
        */ count(*) from
        --t3 t
        --(select /*+ no_merge */ a.* from t3 a) t
        (select a.* from t3 a, t3 b where a.object_id = b.object_id) t
        where exists (select /*+ no_unnest */ 1 from t2 where t.object_id=t2.object_id);

-- Disabling the FILTER skew handling behaviour means no re-distribution before the FILTER, and hence no HASH JOIN BUFFERED
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |     1 |    10 |    32M  (1)| 00:21:13 |        |      |            |
|   1 |  SORT AGGREGATE               |          |     1 |    10 |            |          |        |      |            |
|   2 |   PX COORDINATOR              |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)        | :TQ10002 |     1 |    10 |            |          |  Q1,02 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE            |          |     1 |    10 |            |          |  Q1,02 | PCWP |            |
|*  5 |      FILTER                   |          |       |       |            |          |  Q1,02 | PCWC |            |
|*  6 |       HASH JOIN               |          | 90000 |   878K|   229   (1)| 00:00:01 |  Q1,02 | PCWP |            |
|   7 |        PX RECEIVE             |          | 90000 |   439K|   114   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|   8 |         PX SEND HYBRID HASH   | :TQ10000 | 90000 |   439K|   114   (0)| 00:00:01 |  Q1,00 | P->P | HYBRID HASH|
|   9 |          STATISTICS COLLECTOR |          |       |       |            |          |  Q1,00 | PCWC |            |
|  10 |           PX BLOCK ITERATOR   |          | 90000 |   439K|   114   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|  11 |            TABLE ACCESS FULL  | T3       | 90000 |   439K|   114   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  12 |        PX RECEIVE             |          | 90000 |   439K|   114   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|  13 |         PX SEND HYBRID HASH   | :TQ10001 | 90000 |   439K|   114   (0)| 00:00:01 |  Q1,01 | P->P | HYBRID HASH|
|  14 |          PX BLOCK ITERATOR    |          | 90000 |   439K|   114   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|  15 |           TABLE ACCESS FULL   | T3       | 90000 |   439K|   114   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|* 16 |       TABLE ACCESS FULL       | T2       |     1 |     5 |   412   (1)| 00:00:01 |        |      |            |
-----------------------------------------------------------------------------------------------------------------------

So it looks like if the row source driving the parallel FILTER operator is complex (in this case by complex I mean not a simple table) the optimizer will always add a HASH distribution unconditionally before the FILTER. It it obvious that such a re-distribution adds overhead - it requires resources to perform. What is even worse is that in general the rules is: The more redistributions the more likely the dreaded buffering will be added to the execution plans, as can be seen from the example above, where the HASH JOIN turns into a HASH JOIN BUFFERED due to the HASH distribution by default added by the optimizer after the join and before the FILTER. By disabling the filter "skew" handling this in my opinion unnecessary redistribution doesn't show up and hence the HASH JOIN without buffering can be used in this example.

Summary

The new parallel FILTER operator comes with different distribution methods available to the optimizer. However, at present the way the optimizer determines automatically if and how to re-distribute the data seems to be questionable to me.

The skew handling is based on the size of the driving object - for very small objects a re-distribution gets added before the FILTER. For row sources driving the filter that are no simple tables the skew handling seems to add a re-distribution unconditionally.

For the reasons outlined at present I would recommend considering to disable the filter skew handling by setting the parameter "_px_filter_skew_handling" to "false", of course not without getting the blessing from Oracle Support before doing so - this should allow minimising the number of re-distributions added to an execution plan. Losing the capability of handling the "skew" caused by very small objects in my opinion is negligible in most cases.

12c Parallel Execution New Features: Parallel FILTER Subquery Evaluation - Part 2: Distribution Methods

Picking up from the first part of this instalment I'll focus in this post on the available distribution methods for the new parallel FILTER subquery feature.

In this post I won't go into the details how the optimizer selects the distribution method automatically - this will be covered in the last part.

Here I merely describe the different available methods and how to control them using the new PQ_FILTER hint, which is also mentioned in the official documentation, although I find a bit hard to follow the description there.

There are four different options available to the PQ_FILTER hint, and only two of them actually describe a distribution method. One of them tells to not distribute the data at all, and the last one reverts to the former pre-12c plan shape:

SERIAL: This tells the optimizer to use the pre-12c plan shape where the FILTER operator is executed by the Query Coordinator. Depending on the SQL features used and combined, 12c sometimes still reverts to this plan shape and it looks like in those cases you can't force the parallel filter evaluation via the PQ_FILTER hint - the outline then contains a PQ_FILTER hint that is supposed to result in a parallel filter evaluation, but the serial plan shape gets used anyway despite the hint.

NONE: This tells the optimizer to not redistribute the data before executing the FILTER operator driving the subqueries, which means in whatever way the data is distributed the Parallel Execution Servers will execute the filter and the corresponding subqueries for the data currently processed

HASH: Redistribute the data by HASH before running the FILTER operator and the corresponding subqueries.

RANDOM: Redistribute the data by ROUND-ROBIN before running the FILTER operator and the corresponding subqueries. Despite being called RANDOM the distribution method shown in the plan is ROUND-ROBIN rather than RANDOM.

In this post I want to show an example for each of those variants and address the following questions in addition:

1. Where and how has the PQ_FILTER hint to be specified? How has the hint to be used in case of multiple FILTERs with subqueries in the execution plan?

2. In case of a HASH distribution and multiple subqueries what keys get used as input for the HASH function used for distribution?

Let's start with an example for each of the hints used to demonstrate the different plan shapes. For that purpose I'll re-use the same setup and query from the initial part of this instalment, with the index set to invisible:

create table t_1
compress
as
select  /*+ use_nl(a b) */
        rownum as id
      , rpad('x', 100) as filler
from
        (select /*+ cardinality(1e5) */ * from dual
connect by
        level <= 1e5) a, (select /*+ cardinality(20) */ * from dual connect by level <= 20) b
;

exec dbms_stats.gather_table_stats(null, 't_1', method_opt=>'for all columns size 1')

alter table t_1 parallel 4;

create index t_1_idx on t_1 (id) invisible;

explain plan for
select /*+ 
           pq_filter(<DIST_METHOD>)
        */ count(*) from
        t_1 t
        where exists (select /*+ no_unnest */ null from t_1 where t.id = t_1.id);

-- 12.1.0.2 plan shape with index invisible and PQ_FILTER(SERIAL)
------------------------------------------------------------------------
| Id  | Operation              | Name     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |        |      |            |
|   1 |  SORT AGGREGATE        |          |        |      |            |
|*  2 |   FILTER               |          |        |      |            |
|   3 |    PX COORDINATOR      |          |        |      |            |
|   4 |     PX SEND QC (RANDOM)| :TQ20000 |  Q2,00 | P->S | QC (RAND)  |
|   5 |      PX BLOCK ITERATOR |          |  Q2,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| T_1      |  Q2,00 | PCWP |            |
|   7 |    PX COORDINATOR      |          |        |      |            |
|   8 |     PX SEND QC (RANDOM)| :TQ10000 |  Q1,00 | P->S | QC (RAND)  |
|   9 |      PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|* 10 |       TABLE ACCESS FULL| T_1      |  Q1,00 | PCWP |            |
------------------------------------------------------------------------

-- 12.1.0.2 plan shape with index invisible and PQ_FILTER(NONE)
-------------------------------------------------------------------------
| Id  | Operation               | Name     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |        |      |            |
|   1 |  SORT AGGREGATE         |          |        |      |            |
|   2 |   PX COORDINATOR        |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)  | :TQ10000 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE      |          |  Q1,00 | PCWP |            |
|*  5 |      FILTER             |          |  Q1,00 | PCWC |            |
|   6 |       PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   7 |        TABLE ACCESS FULL| T_1      |  Q1,00 | PCWP |            |
|*  8 |       TABLE ACCESS FULL | T_1      |        |      |            |
-------------------------------------------------------------------------

-- 12.1.0.2 plan shape with index invisible and PQ_FILTER(RANDOM)
---------------------------------------------------------------------------
| Id  | Operation                 | Name     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |          |        |      |            |
|   1 |  SORT AGGREGATE           |          |        |      |            |
|   2 |   PX COORDINATOR          |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)    | :TQ10001 |  Q1,01 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE        |          |  Q1,01 | PCWP |            |
|*  5 |      FILTER               |          |  Q1,01 | PCWP |            |
|   6 |       PX RECEIVE          |          |  Q1,01 | PCWP |            |
|   7 |        PX SEND ROUND-ROBIN| :TQ10000 |  Q1,00 | P->P | RND-ROBIN  |
|   8 |         PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   9 |          TABLE ACCESS FULL| T_1      |  Q1,00 | PCWP |            |
|* 10 |       TABLE ACCESS FULL   | T_1      |        |      |            |
---------------------------------------------------------------------------

-- 12.1.0.2 plan shape with index invisible and PQ_FILTER(HASH)
---------------------------------------------------------------------------
| Id  | Operation                 | Name     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |          |        |      |            |
|   1 |  SORT AGGREGATE           |          |        |      |            |
|   2 |   PX COORDINATOR          |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)    | :TQ10001 |  Q1,01 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE        |          |  Q1,01 | PCWP |            |
|*  5 |      FILTER               |          |  Q1,01 | PCWP |            |
|   6 |       PX RECEIVE          |          |  Q1,01 | PCWP |            |
|   7 |        PX SEND HASH       | :TQ10000 |  Q1,00 | P->P | HASH       |
|   8 |         PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   9 |          TABLE ACCESS FULL| T_1      |  Q1,00 | PCWP |            |
|* 10 |       TABLE ACCESS FULL   | T_1      |        |      |            |
---------------------------------------------------------------------------

So we can see with PQ_FILTER(SERIAL) we get the former, pre-12c plan shape, which in this case results in a parallel full table scan in the filter subquery and a decomposition into multiple DFO trees, both things that should be avoided in general.

I've omitted the cost estimates but there seems to be an odd inconsistency: The new parallel FILTER evaluation results in a higher cost estimate than the old serial one, simply due to the fact that the full table scan cost is lowered by the chunked parallel table scan (PX BLOCK ITERATOR) in the old plan shape, whereas the complete full table scans performed in the Parallel Execution Servers is treated as serial full table scan cost-wise. Nevertheless by default the plan with the higher costs is selected by the optimizer in 12c.

Let's address the first question how to specify the hint: As we can see from the simple example here when using the format of the hint mentioned in the official documentation it should be put into the main (driving) query, not into the filter subqueries, which makes kind of sense, since there could be multiple subqueries but only one FILTER operation in the main query driving them.

Let's have a look at a slightly more complicated query:

explain plan for
select /*+
           qb_name(main)
           -- When merging the two query blocks there will be a single FILTER in the main query block
           --pq_filter(@main hash)
           -- Global form of the PQ_FILTER hint
           no_merge(@qb1)
           no_merge(@qb2)
           pq_filter(@qb1 random)
           pq_filter(@qb2 hash)
       */
       *
       from
(
select /*+
           qb_name(qb1)
           -- Alternatively use local form of the hints
           --pq_filter(random)
           --no_merge
        */ *
        from
        t_1 t
        where exists (select /*+ no_unnest */ null from t_1 where t.id = t_1.id)
) a,
(
select /*+
           qb_name(qb2)
           -- Alternatively use local form of the hints
           --pq_filter(hash)
           --no_merge
        */ *
        from t_1 t
        where exists (select /*+ no_unnest */ null from t_1 where t.id = t_1.id)
) b
where b.id = a.id
;

-- Plan shape with the hints as specified
-------------------------------------------------------------------------------
| Id  | Operation                     | Name     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |        |      |            |
|   1 |  PX COORDINATOR               |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)         | :TQ10004 |  Q1,04 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN BUFFERED         |          |  Q1,04 | PCWP |            |
|   4 |     PX RECEIVE                |          |  Q1,04 | PCWP |            |
|   5 |      PX SEND HYBRID HASH      | :TQ10002 |  Q1,02 | P->P | HYBRID HASH|
|   6 |       STATISTICS COLLECTOR    |          |  Q1,02 | PCWC |            |
|   7 |        BUFFER SORT            |          |  Q1,02 | PCWP |            |
|   8 |         VIEW                  |          |  Q1,02 | PCWP |            |
|*  9 |          FILTER               |          |  Q1,02 | PCWP |            |
|  10 |           PX RECEIVE          |          |  Q1,02 | PCWP |            |
|  11 |            PX SEND ROUND-ROBIN| :TQ10000 |  Q1,00 | P->P | RND-ROBIN  |
|  12 |             PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|  13 |              TABLE ACCESS FULL| T_1      |  Q1,00 | PCWP |            |
|* 14 |           TABLE ACCESS FULL   | T_1      |        |      |            |
|  15 |     PX RECEIVE                |          |  Q1,04 | PCWP |            |
|  16 |      PX SEND HYBRID HASH      | :TQ10003 |  Q1,03 | P->P | HYBRID HASH|
|  17 |       BUFFER SORT             |          |  Q1,03 | PCWP |            |
|  18 |        VIEW                   |          |  Q1,03 | PCWP |            |
|* 19 |         FILTER                |          |  Q1,03 | PCWP |            |
|  20 |          PX RECEIVE           |          |  Q1,03 | PCWP |            |
|  21 |           PX SEND HASH        | :TQ10001 |  Q1,01 | P->P | HASH       |
|  22 |            PX BLOCK ITERATOR  |          |  Q1,01 | PCWC |            |
|  23 |             TABLE ACCESS FULL | T_1      |  Q1,01 | PCWP |            |
|* 24 |          TABLE ACCESS FULL    | T_1      |        |      |            |
-------------------------------------------------------------------------------

So in this example here we can see that multiple FILTER operations with subqueries are possible - in principle one per query block. Here I've used the global form of the hint, which includes a query block name to assign the hint to the corresponding FILTER operation. The same plan shape could be achieved by using the local hints within the query blocks. If no NO_MERGE hint gets used, both query blocks will be merged and there will be only a single FILTER operation - which then can be influenced by a corresponding PQ_FILTER hint in the main/outer query block.

Regarding the second question, what keys get used as input for the HASH distribution, we can check the corresponding "Projection information" section of the DBMS_XPLAN output, and we can see there that it is pretty straightforward and as expected: All columns / expressions used in the filter subqueries for correlation will be used as input. Using the variation of above query with merged query blocks and HASH distribution of the single FILTER with two subqueries results in the following:

explain plan for
select /*+
           qb_name(main)
           pq_filter(@main hash)
           --no_merge(@qb1)
           --no_merge(@qb2)
           --pq_filter(@qb1 random)
           --pq_filter(@qb2 hash)
       */
       *
       from
(
select /*+
           qb_name(qb1)
           --pq_filter(random)
           --no_merge
        */ *
        from
        t_1 t
        where exists (select /*+ no_unnest */ null from t_1 where t.id = t_1.id)
) a,
(
select /*+
           qb_name(qb2)
           --pq_filter(hash)
           --no_merge
        */ *
        from t_1 t
        where exists (select /*+ no_unnest */ null from t_1 where t.id = t_1.id)
) b
where b.id = a.id
;

--------------------------------------------------------------------------------
| Id  | Operation                      | Name     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |        |      |            |
|   1 |  PX COORDINATOR                |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)          | :TQ10003 |  Q1,03 | P->S | QC (RAND)  |
|   3 |    BUFFER SORT                 |          |  Q1,03 | PCWP |            |
|*  4 |     FILTER                     |          |  Q1,03 | PCWP |            |
|   5 |      PX RECEIVE                |          |  Q1,03 | PCWP |            |
|   6 |       PX SEND HASH             | :TQ10002 |  Q1,02 | P->P | HASH       |
|*  7 |        HASH JOIN BUFFERED      |          |  Q1,02 | PCWP |            |
|   8 |         PX RECEIVE             |          |  Q1,02 | PCWP |            |
|   9 |          PX SEND HYBRID HASH   | :TQ10000 |  Q1,00 | P->P | HYBRID HASH|
|  10 |           STATISTICS COLLECTOR |          |  Q1,00 | PCWC |            |
|  11 |            PX BLOCK ITERATOR   |          |  Q1,00 | PCWC |            |
|  12 |             TABLE ACCESS FULL  | T_1      |  Q1,00 | PCWP |            |
|  13 |         PX RECEIVE             |          |  Q1,02 | PCWP |            |
|  14 |          PX SEND HYBRID HASH   | :TQ10001 |  Q1,01 | P->P | HYBRID HASH|
|  15 |           PX BLOCK ITERATOR    |          |  Q1,01 | PCWC |            |
|  16 |            TABLE ACCESS FULL   | T_1      |  Q1,01 | PCWP |            |
|* 17 |      TABLE ACCESS FULL         | T_1      |        |      |            |
|* 18 |      TABLE ACCESS FULL         | T_1      |        |      |            |
--------------------------------------------------------------------------------

Column Projection Information (identified by operation id):
-----------------------------------------------------------
...
   6 - (#keys=2) "T"."ID"[NUMBER,22], "T"."ID"[NUMBER,22], "T"."FILLER"[VARCHAR2,100], "T"."FILLER"[VARCHAR2,100]
...

So both T.IDs from each subquery are used as keys "(#keys=2)" for the hash function.

In the last part of this instalment I'll focus on how the optimizer chooses the distribution method for the parallel filter operation.

12c Parallel Execution New Features: Parallel FILTER Subquery Evaluation - Part 1: Introduction

12c introduces another interesting new Parallel Execution feature - the parallel evaluation of FILTER subqueries. In pre-12c FILTER subqueries always had to be evaluated in the Query Coordinator. This had several consequences, in particular the data driving the FILTER subquery always had to flow through the Query Coordinator, and hence represented a forced serial execution part of a parallel execution plan. This limitation also meant that depending on the overall plan shape the parallel plan was possibly decomposed into multiple DFO trees, leading to other side effects I've outlined in some of my other publications already.

In 12c now the FILTER subquery can be evaluated in the Parallel Slaves, and the driving data no longer needs to be processed in the Query Coordinator. However, the resulting plan shape can be a little bit confusing. Let's have a look at a simple example:

create table t_1
compress
as
select  /*+ use_nl(a b) */
        rownum as id
      , rpad('x', 100) as filler
from
        (select /*+ cardinality(1e5) */ * from dual
connect by
        level <= 1e5) a, (select /*+ cardinality(20) */ * from dual connect by level <= 20) b
;

exec dbms_stats.gather_table_stats(null, 't_1', method_opt=>'for all columns size 1')

alter table t_1 parallel 4;

create index t_1_idx on t_1 (id) invisible;

explain plan for
select /*+ 
           --optimizer_features_enable('11.2.0.4')
        */ count(*) from
        t_1 t
        where exists (select /*+ no_unnest */ null from t_1 where t.id = t_1.id);

-- 11.2.0.4 plan shape with index invisible
----------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |     6 |   440M  (2)| 04:47:04 |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |     6 |            |          |        |      |            |
|*  2 |   FILTER               |          |       |       |            |          |        |      |            |
|   3 |    PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   4 |     PX SEND QC (RANDOM)| :TQ20000 |  2000K|    11M|   221   (1)| 00:00:01 |  Q2,00 | P->S | QC (RAND)  |
|   5 |      PX BLOCK ITERATOR |          |  2000K|    11M|   221   (1)| 00:00:01 |  Q2,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| T_1      |  2000K|    11M|   221   (1)| 00:00:01 |  Q2,00 | PCWP |            |
|   7 |    PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   8 |     PX SEND QC (RANDOM)| :TQ10000 |     1 |     6 |   222   (2)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   9 |      PX BLOCK ITERATOR |          |     1 |     6 |   222   (2)| 00:00:01 |  Q1,00 | PCWC |            |
|* 10 |       TABLE ACCESS FULL| T_1      |     1 |     6 |   222   (2)| 00:00:01 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T_1" "T_1" WHERE "T_1"."ID"=:B1))
  10 - filter("T_1"."ID"=:B1)

-- 12.1.0.2 plan shape with index invisible
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |     1 |     6 |  1588M  (2)| 17:14:09 |        |      |            |
|   1 |  SORT AGGREGATE         |          |     1 |     6 |            |          |        |      |            |
|   2 |   PX COORDINATOR        |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)  | :TQ10000 |     1 |     6 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE      |          |     1 |     6 |            |          |  Q1,00 | PCWP |            |
|*  5 |      FILTER             |          |       |       |            |          |  Q1,00 | PCWC |            |
|   6 |       PX BLOCK ITERATOR |          |  2000K|    11M|   221   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|   7 |        TABLE ACCESS FULL| T_1      |  2000K|    11M|   221   (1)| 00:00:01 |  Q1,00 | PCWP |            |
|*  8 |       TABLE ACCESS FULL | T_1      |     1 |     6 |   798   (2)| 00:00:01 |        |      |            |
-----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T_1" "T_1" WHERE "T_1"."ID"=:B1))
   8 - filter("T_1"."ID"=:B1)

-- 11.2.0.4 plan shape with index visible
----------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |     6 |  5973K  (1)| 00:03:54 |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |     6 |            |          |        |      |            |
|*  2 |   FILTER               |          |       |       |            |          |        |      |            |
|   3 |    PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   4 |     PX SEND QC (RANDOM)| :TQ10000 |  2000K|    11M|   221   (1)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   5 |      PX BLOCK ITERATOR |          |  2000K|    11M|   221   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| T_1      |  2000K|    11M|   221   (1)| 00:00:01 |  Q1,00 | PCWP |            |
|*  7 |    INDEX RANGE SCAN    | T_1_IDX  |     1 |     6 |     3   (0)| 00:00:01 |        |      |            |
----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T_1" "T_1" WHERE "T_1"."ID"=:B1))
   7 - access("T_1"."ID"=:B1)

-- 12.1.0.2 plan shape with index visible
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |     1 |     6 |  5973K  (1)| 00:03:54 |        |      |            |
|   1 |  SORT AGGREGATE         |          |     1 |     6 |            |          |        |      |            |
|   2 |   PX COORDINATOR        |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)  | :TQ10000 |     1 |     6 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE      |          |     1 |     6 |            |          |  Q1,00 | PCWP |            |
|*  5 |      FILTER             |          |       |       |            |          |  Q1,00 | PCWC |            |
|   6 |       PX BLOCK ITERATOR |          |  2000K|    11M|   221   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|   7 |        TABLE ACCESS FULL| T_1      |  2000K|    11M|   221   (1)| 00:00:01 |  Q1,00 | PCWP |            |
|*  8 |       INDEX RANGE SCAN  | T_1_IDX  |     1 |     6 |     3   (0)| 00:00:01 |        |      |            |
-----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T_1" "T_1" WHERE "T_1"."ID"=:B1))
   8 - access("T_1"."ID"=:B1)

I've included two variations of the setup, one without available index for evaluating the FILTER subquery and one with index.

The pre-12c plan shape without index makes the former limitation particularly obvious: The FILTER operator is above the PX COORDINATOR and marked serial, and the table scan in the FILTER subquery gets parallelized as separate DFO tree (indicated among others by the two PX COORDINATOR operators), which means that each time this separate DFO tree starts, a separate set of Parallel Slave will be allocated/deallocated, adding possibly a lot of overhead to a probably already inefficient execution plan anyway - assuming the FILTER subquery needs to be evaluated many times.

In 12c the FILTER operator is marked parallel and the need for a separate DFO tree is gone. What might be confusing with this plan shape is that the operations of the FILTER subquery are not marked parallel. In my opinion this is misleading and should actually be marked parallel, because at runtime the operations will be performed by the Parallel Slaves, and in case of a Full Table Scan each slave will run the entire full table scan (so no PX ITERATOR for dividing the scan into chunks / granules), which is comparable to what happens when a parallel Nested Loop join runs or the new PQ_REPLICATE feature gets used - and in those cases the operations are marked parallel:

-- 11.2.0.4 / 12.1.0.2 plan shape with index invisible
-- and subquery unnested using NL SEMI join
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |     1 |    12 |   442M  (2)| 04:48:03 |        |      |            |
|   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 |      NESTED LOOPS SEMI  |          |  2000K|    22M|   442M  (2)| 04:48:03 |  Q1,00 | PCWP |            |
|   6 |       PX BLOCK ITERATOR |          |       |       |            |          |  Q1,00 | PCWC |            |
|   7 |        TABLE ACCESS FULL| T_1      |  2000K|    11M|   221   (1)| 00:00:01 |  Q1,00 | PCWP |            |
|*  8 |       TABLE ACCESS FULL | T_1      |  2000K|    11M|   796   (2)| 00:00:01 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 
   8 - filter("T"."ID"="T_1"."ID")

Summary

So the operators of the FILTER subquery can now be run in the slaves, which is the main point of this feature, although being represented in a confusing way in the execution plan. Avoiding the potential decomposition into multiple DFO trees is another possible side effect. Decreased query duration should be possible if the evaluation of the FILTER subquery requires significant time and can now be run in the Parallel Slaves instead of serial execution through the Query Coordinator.

Note that depending on the plan shape and SQL features used, it's still possible that 12c reverts to the old serial FILTER subquery evaluation plan shape, so the new feature doesn't get used always.

There is more to say about this feature. In the next part of this instalment I'll focus on the different distribution methods possible with the new parallel FILTER operator - there is a new PQ_FILTER hint that allows controlling the distribution, but there are also some interesting points to make about how the optimizer seems to make its choice which distribution method to use automatically. In the examples shown here there's no separate distribution for the FILTER, by the way, but this can look differently, as I'll show in the next part.

12c Parallel Execution New Features: 1 SLAVE distribution

When certain SQL features get used in pre-12c versions that force non-parallel evaluation, like using ROWNUM or certain Analytic Functions like LAG/LEAD, then - depending on the overall plan shape - Oracle can start to decompose the parallel execution plan into several so called DFO trees (If you want learn more about DFO trees and DFOs I recommend watching my online tutorial on my Youtube channel).

Now having multiple DFO trees in a single parallel execution plan comes with several side effects that are confusing and complicate matters unnecessarily, like each DFO tree allocates its own PX slave set(s), and so each one can potenially end up with a different DOP, which means you can have more than one DOP in a single parallel execution plan.

Depending on the overall plan shape this might also mean that a DFO tree can get started multiple times, and again this means that each time it is started / completed PX slaves need to be allocated and de-allocated, potentially causing a significant overhead coordinating all that activity that is not directly related to the actual execution.

This also means that having multiple DFO trees can lead to a situation where (a lot) more PX slaves are allocated than expected, in case multiple DFO trees are active at the same time - which again means that if you believe you can limit the number of PX slaves allocated by a single parallel execution using Resource Manager directives you might be wrong.

Since all these are undesirable side effects, starting with release 12c Oracle has put effort into new features that minimize the need for such a decomposition into multiple DFO trees. One of these new features is the so called "1 SLAVE" distribution method that can get used if such a non-parallel evaluation is required.

Quite similar to the recently described "PX SELECTOR" operator the "1 SLAVE" distribution uses a single PX slave out of a slave set to execute the non-parallel operations instead of the Query Coordinator. The main difference this makes is that the parallel and serial operations now are still part of the same DFO tree instead of having parts of the execution plan executed by the Query Coordinator and different DFO trees before and after such serial operations.

Let's have a look at a simple example to demonstrate the new feature. I use here three identical tables, just for the sake of being able to differentiate the tables in the plan output - in principle re-using a single table three times would be sufficient.

create table t2
compress
as
select
        (rownum * 2) + 1 as id
      , mod(rownum, 2000) + 1 as id2
      , rpad('x', 100) as filler
from
        (select /*+ cardinality(100000) */ * from dual
connect by
        level <= 100000) a, (select /*+ cardinality(20) */ * from dual connect by level <= 20) b
;

exec dbms_stats.gather_table_stats(null, 't2')

create table t4
compress
as
select
        (rownum * 2) + 1 as id
      , mod(rownum, 2000) + 1 as id2
      , rpad('x', 100) as filler
from
        (select /*+ cardinality(100000) */ * from dual
connect by
        level <= 100000) a, (select /*+ cardinality(20) */ * from dual connect by level <= 20) b
;

exec dbms_stats.gather_table_stats(null, 't4')

create table t6
compress
as
select
        (rownum * 2) + 1 as id
      , mod(rownum, 2000) + 1 as id2
      , rpad('x', 100) as filler
from
        (select /*+ cardinality(100000) */ * from dual
connect by
        level <= 100000) a, (select /*+ cardinality(20) */ * from dual connect by level <= 20) b
;

exec dbms_stats.gather_table_stats(null, 't6')

explain plan for
select /*+ no_merge(x) */
       *
from
     (
       select /*+ parallel(t6 4)
                  --optimizer_features_enable('11.2.0.4')
              */
               *
       from
            (
              select /*+ parallel(t2 4) */
                     --lag(id) over (order by id)    as v1_rn
                     rownum    as v1_rn
                   , t2.id     as v1_id
                   , t2.filler as v1_filler
              from
                     t2
            ) v1
          , (
              select /*+ parallel(t4 2) */
                     --lag(id) over (order by id)    as v2_rn
                     rownum    as v2_rn
                   , t4.id     as v2_id
                   , t4.filler as v2_filler
              from
                     t4
            ) v2
          , t6
       where
              v1_id = v2_id
       and    v1_id = t6.id
     ) x
where
      rownum > 1
;

-- 11.2.0.4 plan shape
----------------------------------------------------------------------------------
| Id  | Operation                        | Name     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |          |        |      |            |
|   1 |  COUNT                           |          |        |      |            |
|*  2 |   FILTER                         |          |        |      |            |
|   3 |    PX COORDINATOR                |          |        |      |            |
|   4 |     PX SEND QC (RANDOM)          | :TQ30002 |  Q3,02 | P->S | QC (RAND)  |
|   5 |      VIEW                        |          |  Q3,02 | PCWP |            |
|*  6 |       HASH JOIN                  |          |  Q3,02 | PCWP |            |
|   7 |        PX RECEIVE                |          |  Q3,02 | PCWP |            |
|   8 |         PX SEND HASH             | :TQ30001 |  Q3,01 | P->P | HASH       |
|   9 |          PX BLOCK ITERATOR       |          |  Q3,01 | PCWC |            |
|  10 |           TABLE ACCESS FULL      | T6       |  Q3,01 | PCWP |            |
|  11 |        BUFFER SORT               |          |  Q3,02 | PCWC |            |
|  12 |         PX RECEIVE               |          |  Q3,02 | PCWP |            |
|  13 |          PX SEND HASH            | :TQ30000 |        | S->P | HASH       |
|* 14 |           HASH JOIN              |          |        |      |            |
|  15 |            VIEW                  |          |        |      |            |
|  16 |             COUNT                |          |        |      |            |
|  17 |              PX COORDINATOR      |          |        |      |            |
|  18 |               PX SEND QC (RANDOM)| :TQ10000 |  Q1,00 | P->S | QC (RAND)  |
|  19 |                PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|  20 |                 TABLE ACCESS FULL| T2       |  Q1,00 | PCWP |            |
|  21 |            VIEW                  |          |        |      |            |
|  22 |             COUNT                |          |        |      |            |
|  23 |              PX COORDINATOR      |          |        |      |            |
|  24 |               PX SEND QC (RANDOM)| :TQ20000 |  Q2,00 | P->S | QC (RAND)  |
|  25 |                PX BLOCK ITERATOR |          |  Q2,00 | PCWC |            |
|  26 |                 TABLE ACCESS FULL| T4       |  Q2,00 | PCWP |            |
----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(ROWNUM>1)
   6 - access("V1_ID"="T6"."ID")
  14 - access("V1_ID"="V2_ID")

-- 12.1.0.2 plan shape
---------------------------------------------------------------------------------
| Id  | Operation                       | Name     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |          |        |      |            |
|   1 |  COUNT                          |          |        |      |            |
|*  2 |   FILTER                        |          |        |      |            |
|   3 |    PX COORDINATOR               |          |        |      |            |
|   4 |     PX SEND QC (RANDOM)         | :TQ10004 |  Q1,04 | P->S | QC (RAND)  |
|   5 |      VIEW                       |          |  Q1,04 | PCWP |            |
|*  6 |       HASH JOIN BUFFERED        |          |  Q1,04 | PCWP |            |
|   7 |        PX RECEIVE               |          |  Q1,04 | PCWP |            |
|   8 |         PX SEND HASH            | :TQ10002 |  Q1,02 | P->P | HASH       |
|   9 |          PX BLOCK ITERATOR      |          |  Q1,02 | PCWC |            |
|  10 |           TABLE ACCESS FULL     | T6       |  Q1,02 | PCWP |            |
|  11 |        PX RECEIVE               |          |  Q1,04 | PCWP |            |
|  12 |         PX SEND HASH            | :TQ10003 |  Q1,03 | S->P | HASH       |
|* 13 |          HASH JOIN BUFFERED     |          |  Q1,03 | SCWC |            |
|  14 |           VIEW                  |          |  Q1,03 | SCWC |            |
|  15 |            COUNT                |          |  Q1,03 | SCWP |            |
|  16 |             PX RECEIVE          |          |  Q1,03 | SCWP |            |
|  17 |              PX SEND 1 SLAVE    | :TQ10000 |  Q1,00 | P->S | 1 SLAVE    |
|  18 |               PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|  19 |                TABLE ACCESS FULL| T2       |  Q1,00 | PCWP |            |
|  20 |           VIEW                  |          |  Q1,03 | SCWC |            |
|  21 |            COUNT                |          |  Q1,03 | SCWP |            |
|  22 |             PX RECEIVE          |          |  Q1,03 | SCWP |            |
|  23 |              PX SEND 1 SLAVE    | :TQ10001 |  Q1,01 | P->S | 1 SLAVE    |
|  24 |               PX BLOCK ITERATOR |          |  Q1,01 | PCWC |            |
|  25 |                TABLE ACCESS FULL| T4       |  Q1,01 | PCWP |            |
---------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(ROWNUM>1)
   6 - access("V1_ID"="T6"."ID")
  13 - access("V1_ID"="V2_ID")

Let's start with the 11.2.0.4 plan shape: We can see from multiple occurrences of the PX COORDINATOR operator, the TQ column and the corresponding TQ entries in the NAME column that this parallel execution plan consists of three DFO trees. When analyzing the actual run time activity it would also become obvious that the different DFO trees actually run at a different DOP and use different PX slave sets. In this particular case here it would even become obvious that two DFO trees are active at the same time - so it's pretty much demonstrating all the undesirable side effects of having multiple DFO trees that I mentioned above.

The HASH JOIN between V1 and V2 runs serially, whereas the HASH JOIN between T6 and the join result of V1 and V2 runs parallel. Since we have a Serial->Parallel distribution between these two HASH JOINs, an additional BUFFER SORT operation gets added - as outlined in the PX SELECTOR note.

If we now look at the 12.1.0.2 plan shape we notice that the execution plan consists of a single DFO tree, and hence all the side effects I've outlined are gone. The serial evaluation is done by a single PX slave of one set, again indicated by the SCWC/SCWP decorator, similar to the PX SELECTOR operator.

However, the plan shape also demonstrates one possible disadvantage of the new feature: Since now everything is part of a single DFO tree we end up with more re-distributions per DFO tree, and hence might have an increased demand for the dreaded BUFFERing to prevent multiple re-distributions being active at the same time - here this can be seen in operation ID 13 - the HASH JOIN executed serially by one PX slave is actually turned into a HASH JOIN BUFFERED (so the worst case for a (not really) "parallel" hash join - executed only by a single PX slave and needs to buffer the right hand row source in addition), and also the second HASH JOIN is turned into its BUFFERED variant. This means compared to the 11.2.0.4 plan shape that contains no HASH JOIN BUFFERED but an additional BUFFER SORT operation, the 12.1.0.2 plan shape with this data pattern actually requires more PGA / TEMP space than the 11.2.0.4 plan shape due to the double buffering now necessary.

Footnote

The new 1 SLAVE distribution doesn't get used always in 12c. If you run just the join between V1 and V2 for example, then the old plan shape will be used, and there are again multiple DFO trees. Furthermore, in this particular case, when you start changing the DOP used in the PARALLEL hints you also might end up with a plan shape where one view uses the 1 SLAVE distribution whereas the other one uses the old plan shape with Query Coordinator activity - I haven't investigated further why this happens.

If Analytic Functions get used, you might also see a "1 SLAVE (ORDER)" variation of the distribution that enforces a certain order when re-distributing the data, similar to the "PX SEND QC (ORDER)" operator.