ORA-12838 is reported by recursive query TBL$OR$IDX$PART$NUM for DML with subquery pruning (Doc ID 3043668.1)

Last updated on FEBRUARY 25, 2025

Applies to:

Symptoms

1. ORA-12838 error when running MERGE statement.

    ORA-00604: error occurred at recursive SQL level 1
    ORA-12838: cannot read/modify an object after modifying it in parallel

   - As per 10046 and errorstack, ORA-00604/ORA-12838 error occurred when running merge statement due to recursive query.

=====================
PARSING IN CURSOR #11529215043903337280 len=1556 dep=0 uid=0 oct=189 lid=0 tim=32449323455140 hv=1291536510 ad='c0000003f6b1e798' sqlid='69d6k2d6gqk3y'
MERGE /*+ INDEX(T_XP32, TAB1_UX01) enable_parallel_dml */
INTO TAB1 T_XP32
USING
(
SELECT /*+ ORDERED USE_HASH(XP32, M40A) FULL(XP32) FULL(M40A) PARALLEL(2) */
XP32.WFG_CD
, XP32.BAS_DT

........ abbreviation

EXEC #11529215043903337280:c=2340000,e=98076015,p=1,cr=277,cu=6,mis=0,r=0,dep=0,og=1,plh=1073788349,tim=32449421531283
ERROR #11529215043903337280:err=604 tim=32449421531335 ------------------------------------------------------------------->> ora-00604
STAT #11529215043903337280 id=1 cnt=0 pid=0 pos=1 obj=0 op='PX COORDINATOR (cr=277 pr=1 pw=0 str=1 time=706073 us)'
STAT #11529215043903337280 id=2 cnt=0 pid=1 pos=1 obj=0 op='PX SEND QC (RANDOM) :TQ10004 (cr=0 pr=0 pw=0 str=0 time=0

........ abbreviation

=====================
PARSING IN CURSOR #11529215043900771656 len=257 dep=1 uid=0 oct=3 lid=0 tim=32449419227288 hv=299915104 ad='c0000003d25d1d60' sqlid='gv92w9s8y0pv0'
SELECT distinct TBL$OR$IDX$PART$NUM("RDMDU"."TAB1", 0, 2, 46, "WK_BSDT") FROM (SELECT "XP32"."WK_BSDT" "WK_BSDT" FROM "RDMDU"."TAB1" "XP32" WHERE "XP32"."WK_BSDT"='2024-01-01' AND "XP32"."BAS_DT"='2023-12-31' AND "XP32"."WFG_CD"='01') ORDER BY 1
END OF STMT
PARSE #11529215043900771656:c=0,e=4111,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=452566051,tim=32449419227285

*** 2024-03-21T13:41:38.508882+09:00
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
<error barrier> at 0x9ffffffffffe79f0 placed dbkda.c@298
ORA-12838: cannot read/modify an object after modifying it in parallel             ------>> !!
----- Current SQL Statement for this session (sql_id=gv92w9s8y0pv0) -----
SELECT distinct TBL$OR$IDX$PART$NUM("RDMDU"."TAB1", 0, 2, 46, "WK_BSDT") FROM (SELECT "XP32"."WK_BSDT" "WK_BSDT" FROM "RDMDU"."TAB1" "XP32" WHERE "XP32"."WK_BSDT"='2024-01-01' AND "XP32"."BAS_DT"='2023-12-31' AND "XP32"."WFG_CD"='01') ORDER BY 1

2. Applied 30978868 and enabled with "_fix_control" = '30978868:1'. But it didn't help.

    -> Bug 30978868 - Redundant Sub-Query Pruning Causing Performance Degradation (Doc ID 30978868.8)

Cause

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