Allocations Failing With ORA-04021: Timeout Occurred While Waiting to Lock Object Errors (Doc ID 2526113.1)

Last updated on MAY 25, 2023

Applies to:

Symptoms

On PFT 8.0.5, allocations are failing with ORA-04021: timeout occurred while waiting to lock object errors.

Issue is occurring since yesterday, where allocation rule runs are failing after running for more than 100 minutes, giving the error stated.

There is understanding that this error can occur when two ongoing rules are trying to lock the same object or set of rows (there was only one case where that happened).
In given instances, the rule was executed when no other rules were running simultaneously. Even now, the same rule has been running for over 1 hour 50 mins.

An observation:
Given Management Ledger table is partitioned using Range-List composite partitioning. Range on AS_OF_DATE and List for FEs. However, the execution plan usually goes for FULL TABLE ACCESS as the filters in these rules are only on FISCAL MONTH and FISACAL YEAR.
Is there a way to enforce using AS_OF_DATE in the rule SQL?

ERROR
ORA-04021: timeout occurred while waiting to lock object

The issue can be reproduced at will with the following steps:
1. Run PFT Allocations in similar scenarios.
2. Check pft logs and FSI_MESSAGE_LOG information.

Cause

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