Patch to Optimize To Do Summary Query to Improve Performance (Doc ID 2108486.1)

Last updated on MARCH 08, 2016

Applies to:

Oracle Utilities Meter Data Management - Version 2.1.0.2 to 2.1.0.3 [Release 2.1]
Oracle Utilities Framework - Version 4.2.0.2.0 to 4.2.0.3.0 [Release 4.2]
Information in this document applies to any platform.

Symptoms

ToDo Summary query takes a long time and there is a <> operator observed in the query. 

SELECT
 DISTINCT MIN (tde.TD_PRIORITY_FLG) AS tdePrty, tl.DESCR, tl.TD_TYPE_CD
FROM CI_TD_ENTRY tde,
  CI_TD_TYPE_L tl,
  CI_ROLE_USER ru,
  CI_TD_VAL_ROLE r
WHERE ru.USER_ID = 'ZZZ'
  AND ru.ROLE_ID = r.ROLE_ID
  AND r.TD_TYPE_CD = tl.TD_TYPE_CD
  AND tl.LANGUAGE_CD = 'ENG'
  AND tde.ROLE_ID = r.ROLE_ID
  AND tde.TD_TYPE_CD = tl.TD_TYPE_CD
  AND tde.ENTRY_STATUS_FLG <> 'C'
GROUP BY tl.DESCR, tl.TD_TYPE_CD
ORDER BY tdePrty, tl.DESCR;

Performance improvement was shown with the following changes:

SELECT
 DISTINCT MIN (tde.TD_PRIORITY_FLG) AS tdePrty, tl.DESCR, tl.TD_TYPE_CD
FROM CI_TD_ENTRY tde,
  CI_TD_TYPE_L tl,
  CI_ROLE_USER ru,
  CI_TD_VAL_ROLE r
WHERE ru.USER_ID = 'ZZZ'
  AND ru.ROLE_ID = r.ROLE_ID
  AND r.TD_TYPE_CD = tl.TD_TYPE_CD
  AND tl.LANGUAGE_CD = 'ENG'
  AND tde.ROLE_ID = r.ROLE_ID
  AND tde.TD_TYPE_CD = tl.TD_TYPE_CD
  AND tde.ENTRY_STATUS_FLG IN ('W','O')
GROUP BY tl.DESCR, tl.TD_TYPE_CD
ORDER BY tdePrty, tl.DESCR;

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