Why INSERT…SELECT Slows Down on OceanBase and How to Fix It

Background

A customer performing a POC migration from Oracle to OceanBase (Oracle mode) discovered that certain large‑scale batch INSERT … SELECT statements ran extremely slowly, with OceanBase reporting timeouts or no results.

INSERT /*+ enable_parallel_dml parallel(186) */
INTO ACA6 (
    AAZ219,
    ...,
    AAA508
) SELECT /*+ USE_MERGE(A B) PARALLEL(186) monitor renzy888 */
    '313' || LPAD(SEQ_KEY_ID.NEXTVAL, 17, 0),
    A.AAZ661,
    ...,
    '0'
FROM ACA5 A,
     AC61 B
WHERE A.AAZ257 = B.AAZ257
  AND EXISTS (
        SELECT 1 FROM IMP_TREATMENT.AAA6 D
        WHERE D.AAA345 = B.AAA345
          AND D.AAE140 = A.AAE140
          AND D.BAA528 = '0'
          AND D.AAD128 = A.AAE013.ZY
          AND (A.AAE013.WH IS NULL OR (A.AAE013.WH IS NOT NULL AND A.AAE013.WH = SUBSTR(AAA345, 1, 4)))
    )
  AND NVL(B.AAE019, 0) <> 0
  AND B.AAE041 <= 202408
  AND (B.AAE042 IS NULL OR B.AAE042 >= 202408)
  AND A.AAZ654 = '31300000006706838590';

Analysis Process

Logical Execution Plan

The logical execution plan obtained from ODC shows the expected operators, including a MergeJoin between tables ACA5 and AC61 and an INDEX INSERT for the primary key.

Physical Execution Plan

By querying GV$OB_SQL_AUDIT for the plan_id, the physical plan was retrieved. The plan confirms that the logical and physical operators match, with the MergeJoin being the theoretically optimal path.

obclient [SYS]> select * from gv$ob_plan_cache_plan_explain where plan_id=2917135;
+-----------+--------------+----------+---------+------------+--------------+----------------------------------------+------+-----------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TENANT_ID | SVR_IP       | SVR_PORT | PLAN_ID | PLAN_DEPTH | PLAN_LINE_ID | OPERATOR                               | NAME | ROWS      | COST   | PROPERTY ...
+-----------+--------------+----------+---------+------------+--------------+----------------------------------------+------+-----------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1002      | 20.9.177.177 | 2882     | 2917135 | 0          | 0            | PHY_OPTIMIZER_STATS_GATHERING          | NULL | 423       | 696598 | NULL
| 1002      | 20.9.177.177 | 2882     | 2917135 | 1          | 1            | PHY_PX_FIFO_COORD                      | NULL | 423       | 696597 | NULL
... (remaining rows omitted for brevity) ...
+-----------+--------------+----------+---------+------------+--------------+----------------------------------------+------+-----------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

SQL Plan Monitor

Real‑time monitoring with sql_plan_monitor reveals that operators 4‑6 consume the most time, with operator 7 (PHY_PX_MULTI_PART_INSERT) writing roughly 200 million rows to the target table.

obclient [SYS]> SELECT op_id, op, output_rows, rescan, threads,
    close_time - open_time AS open_dt,
    last_row_eof_time - first_row_time AS row_dt,
    open_time, close_time, first_row_time, last_row_eof_time
FROM (
    SELECT plan_line_id AS op_id,
           concat(lpad(' ', max(plan_depth)), plan_operation) AS op,
           sum(output_rows) AS output_rows,
           sum(STARTS) AS rescan,
           min(first_refresh_time) AS open_time,
           max(last_refresh_time) AS close_time,
           min(first_change_time) AS first_row_time,
           max(last_change_time) AS last_row_eof_time,
           count(1) AS threads
    FROM gv$sql_plan_monitor
    WHERE trace_id = 'YB420A08E3AB-000625B15CFFA43F-0-0'
    GROUP BY plan_line_id, plan_operation, plan_depth
    ORDER BY plan_line_id
) a;

The monitor shows that operator 7 emitted its first row at 04:22:55.584554 and its last row at 04:31:52.650161, while operator 6 started receiving data at 04:31:51.670017, indicating a long wait for the upstream data.

Root Cause

Table Structure

CREATE TABLE "IMP_xxxxxxxx"."ACA6" (
    ...
    "AAA508" VARCHAR2(200) NOT NULL ENABLE,
    "AAA350" NUMBER(20),
    CONSTRAINT "UDX_ACA6_AAZ219" UNIQUE ("AAZ219"),
    ...
);

Why the Global Unique Index Is Slow

Operator 7 writes the primary key (the data) to a partitioned table, which means many partitions are written concurrently. Operator 6‑>3 must materialize the data from operator 7 and write a global unique non‑partitioned index, effectively writing to a single partition and incurring heavy RPC overhead due to the EXCHANGE IN/OUT distributed operator.

The logical plan contains an INDEX INSERT operator, confirming that a global unique non‑partitioned index is being maintained.

Conclusion

The global unique index originates from OMS. Because OceanBase requires the primary key to include the partition column, OMS rewrites the original primary key into a global unique non‑partitioned index consisting of the original primary key fields plus the partition key.

For INSERT ... SELECT ... statements, the presence of a global index causes the optimizer to generate an INDEX INSERT operator, which can dramatically increase execution time in large‑scale batch scenarios.

When encountering an INDEX INSERT on a global non‑partitioned index, performance may be unacceptable; the recommended optimization is to convert the index to a global partitioned index.

In this case, the index UDX_ACA6_AAZ219 on table ACA6 was rebuilt as a partitioned unique index, allowing the INSERT ... SELECT to finish in 14 minutes on OceanBase (versus a 32‑minute run on Oracle, where the original statement timed out).

CREATE UNIQUE INDEX "IMP_xxxxxxxx"."UDX_ACA6_AAZ219"
ON "IMP_xxxxxxxx"."ACA6" ("AAZ219")
PARTITION BY HASH (AAZ219) PARTITIONS 36;