My Oracle Support Banner

CHANGE_HISTORY と SIGNOFF 表に対して頻繁に実行されるSQLについて (Doc ID 2531589.1)

Last updated on JUNE 05, 2019

適用範囲:

Oracle Agile PLM Framework - バージョン 9.3.3.0 以降
この文書の内容はすべてのプラットフォームに適用されます。

目的

AWRレポートの結果以下のSQLが一時間に360回以上実行されていることがわかりました。
この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
 

 

解決策

To view full details, 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 a vibrant support community of peers and Oracle experts.