ORA-12838 no se puede leer/modificar un objeto después de modificarlo en paralelo

Cuando estamos utilizando Direct Path Insert, o Parallel Insert, para acelerar una carga masiva de datos, es probable que surja el ORA-12838.

Trabajando recientemente en la optimización de carga de datos en Oracle me apareció este error ORA-12838. Como es lógico, a la hora de hacer una carga masiva es necesario buscar la mayor eficiencia posible, y me resultó curioso encontrarme con el error ORA-12838. Lo que me resultaba curioso es que no estaba haciendo ninguna otra operación sobre la tabla implicad que la de mi proceso.

¿Por qué puede aparecer este error ORA-12838?

La descripción de este error es bastante descriptiva del problema, aunque, necesitamos algo de contexto:

ORA-12838: no se puede leer/modificar un objeto después de modificarlo en paralelo

Queda bastante claro que el problema surge al intentar leer o modificar un objeto con el que hemos hecho algo en paralelo.

El modo de ejecución en paralelo en Oracle permite dividir una operación en varios procesos, de forma que, evidentemente, podemos reducir el tiempo de ejecución. Esto sobre todo aplicará si tenemos mas de un CPU, y sobre algunas operaciones concretas. Donde mejor aprovechamiento del modo de ejecución en paralelo podemos obtener es en operaciones de estos tipos:

• Carga de datos con SQL*Loader teniendo el parámetro PARALLEL=TRUE
• Sentencias DML (Insert as select, update, merge) y DLL (Rebuild index partition, sort merge o nested loop)
• Algunas operaciones SQL, como las algebráicas (union, minus, union all) de agrupación (group by) y comparación con complejidad mayor de n (not in, cube, rollup) y aquellas que usan acceso index fullscan (not in)

Pero, veamos con un ejemplo qué significa la descripción del error, para saber de qué estamos hablando:
Primero activaremos el modo paralelo, creamos una tabla de prueba e insertamos en ella con el hint Parallel. Una vez hecho esto, sin terminar la transacción, con una operación DML sobre la tabla, mostrará el error:

SQL> alter session enable parallel dml;

Session altered.

SQL> create table test(nombre varchar2(30), numero number);

Table created.

SQL> insert /*+ parallel (test) */ into test select table_name,num_rows from user_tables;

833 rows created.

SQL> select count(*) from test;
select count(*) from test
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

¿Cómo resolverlo?

Veamos qué nos dice la ayuda de Oracle y su recomendación para este error:

Esta solución es algo confusa ¿reescribir la transacción? ¿dividirla en dos? Para evitarlo, basta con hacer Commit/Rollback antes de realizar consultas o modificaciones sobre la tabla implicada.

Pero ¿Y si no estamos usando el hint parallel, ni hemos activado la sesión en parallel mode, por qué?

Indirectamente podemos estar usándolo a través de otra operación, por ejemplo, con Direct Path insert. Si usamos el hint Append, podemos obtener el mismo error, al realizar la misma operación, sin haber mencionado la sugerencia parallel.

Un ejemplo en SQL Oracle que ilustra lo que comento

SQL*Plus: Release 11.2.0.2.0 Production on Sßb Feb 08 00:10:51 2020

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


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> create table test(nombre varchar2(30), numero number);

Table created.

SQL> insert /*+ append */ into test select table_name,num_rows from user_tables;

833 rows created.

SQL> select count(*) from test;
select count(*) from test
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


SQL> commit;

Commit complete.

SQL> select count(*) from test;

  COUNT(*)
----------
        833

SQL>

La solución, como podemos ver en el código, es la misma: Commit/Rollback de la transacción antes de la siguiente operación. O bien, como dice la ayuda de Oracle, si no podemos hacer esto, dividirlo en dos transacciones.