Individual Attribute Denorm Consuming All Resources And Causing Timeouts
Last updated on JULY 11, 2016
Applies to:Oracle Agile Product Lifecycle Management for Process - Version 188.8.131.52 to 184.108.40.206 [Release 6.0.0 to 6.1.0]
Information in this document applies to any platform.
On Oracle Agile PLM for Process 220.127.116.11, 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 18.104.22.168) 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  records. - Ran for 27 minutes
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%
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