"ORA-00060: deadlock" error when trying to set up parallel EIM jobs (Doc ID 493239.1)

Last updated on SEPTEMBER 18, 2024

Applies to:

Symptoms

I'm trying to improve EIM performance by running EIM jobs to process different batches in parallel. For example, I have one job where initially the IFB file had BATCH = 1-20, so I split it into 4 different EIM jobs, and each one of them has the following settings for BATCH:

BATCH = 1,5,9,13,17
BATCH = 2,6,10,14,18
BATCH = 3,7,11,15,19
BATCH = 4,8,12,16,20

I've tried this a few times successfully in the past, but this time I'm getting the following error:

ORA-00060: deadlock detected while waiting for resource

Running jobs in parallel for different batches has worked fine in the past. Also, the jobs in question are for inserts only and contain no duplicate records.

Parallel inserts were actually recommended by Siebel Solution Support Engineering during an EIM Performance Tuning Review. Below is an excerpt from the document delivered by the Solution Support Engineering engineer:

"Multiple EIM processes can be executed against an EIM or interface table, provided they all use different batches or batch ranges. The concern is that contention for locks on common objects may be experienced. To run multiple jobs in parallel against the same EIM or interface table, check that the 'FREELIST' parameter is set appropriately..."

I've reviewed the details on FREELISTS from Siebel Enterprise Manager Administration Guide > EIM Performance Tuning > Database Optimization Tips for EIM > Oracle Databases > FREELIST Parameter, and forwarded the information to our DBA. However, our DBA thinks that the FREELIST parameter is an obsolete parameter that should not be used.

Please suggest a solution.

Cause

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