DML And DDL Referencing MDSYS.SDO_GEOMETRY Are Using Parallel Plans In 12.2, But Not In 19c (Doc ID 2908912.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

We're running a "create table as select" where the select part references MDSYS.SDO_GEOMETRY:

create table <SCHEMA_NAME>.<TABLE_NAME> PARALLEL 2 as
SELECT 
      ................
      , mdsys.sdo_geometry(...) AS geom
      ...............
FROM <SCHEMA_NAME>.<SOURCE_TABLE_NAME>;

In 19c the DML/DDL part of the query is executed serially, while in 12c it's executed in parallel.

If the reference to MDSYS.SDO_GEOMETRY is removed, 19c also generates a parallel plan. This difference in execution is affecting the refresh time of some tables.

Good results:

PLAN_TABLE_OUTPUT

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

| 0 | CREATE TABLE STATEMENT | | 1 | 3896 | 3 (0)| 00:00:01 | | | |

| 1 | PX COORDINATOR | | | | | | | | |

| 2 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 3896 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |

| 3 | LOAD AS SELECT (HYBRID TSM/HWMB)| <TABLE_NAME> | | | | | Q1,00 | PCWP | | <-----------------------

| 4 | OPTIMIZER STATISTICS GATHERING | | 1 | 3896 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |

| 5 | PX BLOCK ITERATOR | | 1 | 3896 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |

| 6 | TABLE ACCESS FULL | <SOURCE_TABLE_NAME> | 1 | 3896 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |

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

Bad Results:

PLAN_TABLE_OUTPUT

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

| 0 | CREATE TABLE STATEMENT | | 1 | 3896 | 3 (0)| 00:00:01 | | | |

| 1 | LOAD AS SELECT | <TABLE_NAME> | | | | | | | | <-----------------------

| 2 | PX COORDINATOR | | | | | | | | |

| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 3896 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |

| 4 | OPTIMIZER STATISTICS GATHERING | | 1 | 3896 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |

| 5 | PX BLOCK ITERATOR | | 1 | 3896 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |

| 6 | TABLE ACCESS FULL | <SOURCE_TABLE_NAME> | 1 | 3896 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |

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

Changes

 Upgrade from 12c to 19c

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.