My Oracle Support Banner

Why Two SQLs Run Frequently Against the Change_History Table (Doc ID 1390018.1)

Last updated on JANUARY 16, 2020

Applies to:

Oracle Agile PLM Framework - Version 9.3.3.0 and later
Information in this document applies to any platform.

Goal

 Why below SQL runs frequently each 30 seconds?
## SQL 1
SELECT S.PROCESS_ID, A.ID, A.USER_TYPE, A.USER_ID, 63, A.CHANGE_ID, A.CLASS_ID, A.COMMENTS, A.PROCESSED, A.DETAILS, A.NEXT_STATUS, A.PREV_STATUS, A.FIND_NUMBER, A.AFFECTED_ITEM, A.AFFECTED_OBJECT_CLASS, A.TIMEZONE, A.TIMESTAMP, A.LOCAL_DATE, W.STATE, S.VERSION FROM CHANGE_HISTORY A, SIGNOFF S, WORKFLOW_PROCESS W WHERE A.ID = S.HISTORY_ID AND W.STATE_CODE = 0 AND (W.STATUSTYPE = 2 OR W.STATUSTYPE = 3) AND S.SIGNOFF_STATUS = 0 AND subStr(S.FLAGS, 1,1) = '0' AND S.PROCESS_ID = W.ID AND A.TIMESTAMP > SYSDATE - 2 - 4


## SQL 2
SELECT S.PROCESS_ID, A.ID, A.USER_TYPE, A.USER_ID, 63, A.CHANGE_ID, A.CLASS_ID, A.COMMENTS, A.PROCESSED, A.DETAILS, A.NEXT_STATUS, A.PREV_STATUS, A.FIND_NUMBER, A.AFFECTED_ITEM, A.AFFECTED_OBJECT_CLASS, A.TIMEZONE, A.TIMESTAMP, A.LOCAL_DATE, 6357, S.VERSION FROM CHANGE_HISTORY A, SIGNOFF S WHERE A.ID = S.HISTORY_ID AND S.SIGNOFF_STATUS = 0 AND subStr(S.FLAGS, 1,1) = '0' AND A.CLASS_ID IN (2000008297,6159) AND A.TIMESTAMP > SYSDATE - 2 - 4

Solution

To view full details, sign in with your My Oracle Support account.

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


In this Document
Goal
Solution

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