My Oracle Support Banner

SQL Running Frequently On CHANGE_HISTORY and SIGNOFF Table (Doc ID 2531546.1)

Last updated on MARCH 14, 2024

Applies to:

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

Goal

AWR report analysis reveals that below SQL has been running over 360 times per hour.
How can we lower the frequency of these SQL?

SQL 1)
SELECT S.PROCESS_ID, A.ID, A.USER_TYPE, A.USER_ID, decode(S.SIGNOFF_STATUS, 0, 63, 124), 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 or S.SIGNOFF_STATUS = 12) AND subStr(S.FLAGS, 1, 1) = '0' AND S.USER_ASSIGNED <> 0 AND A.CLASS_ID IN (2000008297, 6159) AND A.TIMESTAMP > SYSDATE - 2 - 4

SQL 2)
SELECT S.PROCESS_ID, A.ID, A.USER_TYPE, A.USER_ID, decode(S.SIGNOFF_STATUS, 0, 64, 125), 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, S.USER_ASSIGNED, S.ID, 6357, S.VERSION FROM CHANGE_HISTORY A, SIGNOFF S WHERE A.ID = S.HISTORY_ID AND (S.SIGNOFF_STATUS = 0 or S.SIGNOFF_STATUS = 12) AND subStr(S.FLAGS, 2, 1) = '0' AND A.CLASS_ID IN (2000008297, 6159) AND A.TIMESTAMP > SYSDATE - 2 - 4

SQL 3)
SELECT S.PROCESS_ID, A.ID, A.USER_TYPE, A.USER_ID, decode(S.SIGNOFF_STATUS, 0, 64, 125), 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, S.USER_ASSIGNED, S.ID, 6357, S.VERSION FROM CHANGE_HISTORY A, SIGNOFF S WHERE A.ID = S.HISTORY_ID AND (S.SIGNOFF_STATUS = 0 or S.SIGNOFF_STATUS = 12) AND subStr(S.FLAGS, 2, 1) = '0' AND A.CLASS_ID IN (2000008297, 6159) AND A.TIMESTAMP > SYSDATE - 2 - 4

SQL 4)
SELECT S.PROCESS_ID, A.ID, A.USER_TYPE, A.USER_ID, decode(S.SIGNOFF_STATUS, 0, 64, 125), 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, S.USER_ASSIGNED, S.ID, 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 or S.SIGNOFF_STATUS = 12) AND subStr(S.FLAGS, 2, 1) = '0' AND S.PROCESS_ID = W.ID 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
References


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