NMS Messages thread takes 500+MB RAM and causes high CPU

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

BG is taking high RAM or/and high CPU, a single NMS Messages thread can take up 500+MB memory and high CPU utilization/ spikes and process execution gets slower

STEPS TO REPRODUCE:

1. Enable multiple high-volume update processes on a custom subobject to project
2. Run the processes, which have 150-500k entries in NMS_MESSAGES table

Expected Results: The load to be normal on the BG service as the select should be paginated/in batches

Actual Results: BG service is fetching the entire 150-500k in the VM, which causes high memory and CPU utilization

In the heap dump, you see 500+MB are taken by a thread doing NMS Messages (not a process but the OOTB action bpm.nms.getMessages)

SQL query:

SELECT /*+ index(NMS_MESSAGES NMS_MESSAGES_N1) */.            *.          FROM.            NMS_MESSAGES .          WHERE.            TOPIC = $1 AND.            ID NOT IN.            (SELECT /*+ index(NMS_MESSAGE_DELIVERY NMS_MESSAGE_DELIVERY_N1) */ MESSAGE_ID.             FROM   NMS_MESSAGE_DELIVERY.             WHERE  TARGET = $2)

Environment

Release : 15.9.3, 16.0.0

Resolution

Fixed in 16.0.1 and also patch 16.0.0.1 as defect DE63094