Why Did My Oracle SQL Run in Parallel? Uncovering Hidden Parallelism and Fixing It

Analyzing Unexpected Parallel Execution

A colleague reported a high‑load session where a SQL statement was running in parallel without any obvious parallel settings. Common causes are objects (tables or indexes) marked PARALLEL or explicit PARALLEL hints.

Initial checks using show parameter parallel revealed no suspicious values. A trace file (event 10053) showed the parameter parallel_query_default_dop = 16. The default degree of parallelism (DOP) is calculated as:

DEFAULT DOP = cpu_count * parallel_threads_per_cpu * cluster_database_instances

Since the objects had PARALLEL set to DEFAULT, the session used 16 parallel processes. The fix was to disable parallelism on the objects:

alter table table_name noparallel;</code>
<code>alter index index_name noparallel;

After setting the degree to 1, the SQL ran serially and the database returned to normal performance.

DBLINK‑Induced Execution‑Plan Anomalies After a DB Upgrade

After upgrading a middle‑tier Oracle database from 10.2.0.4 to 11.2.0.4, a data‑synchronization job using a DBLINK became extremely slow or failed. An initial attempt to revert the optimizer features with:

alter system set optimizer_features_enable='10.2.0.4' scope=both;

did not resolve the issue.

The problematic statement was an INSERT INTO remote_table@dblink SELECT … FROM local_table. The remote execution plan showed an internal function SYS_OP_C2C performing character‑set conversion. The old B database used UTF‑8, the new B database used ZHS16GBK, while the A database remained UTF‑8.

The mismatch caused the conversion overhead and plan degradation. Two remedies were suggested:

Align the character sets of source and target databases.

Create a function‑based index to avoid the conversion.

Domain Index Errors Triggering ORA‑00604 on COMMIT

A COMMIT raised ORA‑00604 with several Oracle Text errors, indicating a missing domain index. The error stack included:

ORA‑20000: Oracle Text error: DRG‑50610, DRG‑50857, ORA‑00942, ORA‑06512 at "CTXSYS.SYNCRN"

Investigation showed that the domain index objects (basic storage tables) were absent. Querying SELECT * FROM ctxsys.ctx_indexes confirmed the index no longer existed.

Recreating the domain index with explicit storage parameters and a Chinese lexer resolved the issue. Example commands:

ctx_ddl.create_preference('t1_stor','BASIC_STORAGE');</code>
<code>ctx_ddl.set_attribute('t1_stor','I_TABLE_CLAUSE','tablespace TEST');</code>
<code>ctx_ddl.set_attribute('t1_stor','I_INDEX_CLAUSE','tablespace TEST');</code>
<code>ctx_ddl.set_attribute('t1_stor','K_TABLE_CLAUSE','tablespace TEST');</code>
<code>ctx_ddl.set_attribute('t1_stor','R_TABLE_CLAUSE','tablespace TEST');</code>
<code>ctx_ddl.set_attribute('t1_stor','N_TABLE_CLAUSE','tablespace TEST');</code>
<code>create index idx1_t1 on t1(object_name) indextype is ctxsys.context parameters ('lexer chinese_lexer storage t1_stor');</code>
<code>exec ctx_ddl.sync_index('IDX1_T1');</code>
<code>exec ctx_ddl.optimize_index('IDX1_T1','full');

After rebuilding and synchronizing the index, COMMIT succeeded without errors.

Key Takeaways

Parallel execution can be triggered by default DOP settings; verify object parallel attributes and session parameters.

DBLINK performance issues after upgrades often stem from character‑set mismatches; align character sets or use function‑based indexes.

ORA‑00604 errors involving Oracle Text usually indicate missing or corrupted domain indexes; recreate them with proper storage and lexer settings.