Performace Is Slow When The Query Is Being Run By Esm_proc.exe Process Multiple Times (Doc ID 1989892.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Argus Safety - Version 7.0.1 and later
Information in this document applies to any platform.

Symptoms


Performance issue noted when the following query is invoked by esm_proc.exe process multiple times.
Every time it runs it is doing a full table scan taking time to complete.

Query:
----------
SELECT b.*
FROM
(SELECT a.*
FROM
(SELECT a.ack_id record_id, a.enterprise_id
FROM acknowledgment a
WHERE not exists
(select 1
from ESMPROC_LOCKED_RECORDS b
where a.ack_id = b.record_id and a.enterprise_id = b.enterprise_id and b.Process_type_id = :Proc_Type) AND a.status in (2,4) ORDER BY a.date_transmitted) a
WHERE ROWNUM < 101) b, acknowledgment a
WHERE b.record_id = a.ack_id and b.enterprise_id = a.enterprise_id FOR UPDATE of a.ack_id, a.enterprise_id


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