Individual Attribute Denorm Consuming All Resources And Causing Timeouts

(Doc ID 1920055.1)

Last updated on JULY 11, 2016

Applies to:

Oracle Agile Product Lifecycle Management for Process - Version 6.0.0.7 to 6.1.0.3 [Release 6.0.0 to 6.1.0]
Information in this document applies to any platform.

Symptoms

On Oracle Agile PLM for Process 6.0.0.7, Denormalization Runs
Find that the Individual Attribute Denorm stored procedure SP_DENORM_EA is consuming the majority (up to 100% at times) of the database server resources and causing timeouts and other degraded performance issues if there is a lot of data in the database.
The performance hotfix on Extended Attributes /Custom Sections denorm functionality for the extensibility pack (EP 2.4.1.18) is included already..
The Denorm_EA.sql looks like this:


The Entire Individual EA Denorm ran for 48 minutes. All processes ran quickly except for the following -
Denorm for Quantitative Range EAs processing for new denormalized records. Inserted [3467] records. - Ran for 27 minutes

Expected Behavior:
=============
The Individual Attribute Denorm run should not consume all resources (no matter if it takes a little longer as this is a background process).

The issue can be reproduced at will with the following steps:
==========================================
1. Use customer database with appropriate amount of data in it.
2. Start the Individual Attribute Denorm stored procedure SP_DENORM_EA and check CPU usage
=> while running the CPU usage rises up to 99%

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms