IV37053: PERFORMANCE CREATE REQUISTION APPLICATION WORKORDER ATTRIBUTE LOOKUP IS FILTERING ON NULL CAUSING A FULL TABLE SCAN.

APAR status

• Closed as program error.

Error description

• Query on siteid not using indexes causing performance problem.
  
  Envir: BS7503, Oracle 10.2, Websphere 8003, AIX 6.1
  
  Steps to recreate:
  
  1. Go to Self Service -> Desktop Requisitions -> Create
  Requisition
  2. In Charge Information group click on >> next to Work Order
  box
  (Detail Menu).
  
  SQL Query:
  
  select *
  from workorder
  where
  
  (:"SYS_B_00" is null
  or (:"SYS_B_01" is not null
  and
  siteid="SYS_B_02"))
  and historyflag=:"SYS_B_03"
  and woacceptscharges=:"SYS_B_04"
  and status not in (:"SYS_B_05,:"SYS_B_06",:"SYS_B_06",
  :"SYS_B_07")
  
  Client has confirmed they have set the mxe.db.lookupMaxRow
  with out any improvement to performance.
  
  Site: PSD01
  
  [2/18/13 14:53:39:908 PST] 00000065 SystemOut O 18 Feb 2013
  14:53:39:908 [WARN] [MXServer] [] BMXAA6720W - USER =
  (MAXADMIN) SPID =
  (196) app (null) object (WORKORDER) :
  
  select * from workorder where (( 'PSD01' is null or ( 'PSD01'
  is not
  null and siteid= 'PSD01' )) and historyflag = 0 and
  woacceptscharges =
  1 and status not in ( 'BA' , 'BPA1' , 'EST' , 'GR' , 'HF' ,
  'PC' , 'RB'
  , 'SR' , 'WAPPR' , 'CANCELLED' , 'CLOSE' , 'CAN' )) and
  (rownum<=200)
  
  (execution took 226154 milliseconds)
  --------------------------------------------------------------
  The query is a result of the validation class on the
  MR.WORKORDER attribute.
  
  C:\maximo\applications\maximo\businessobjects\classes\psdi\app\m
  r\FldMRWonum.class
  
  Only workaround:
  To remove validation class but may cause issues.
  
  Escalated to Development:
  
  Yes, this is definitely a bug. Please create an APAR.
  
  1. Is this a bug can an APAR be created?
  
  Yes
  
  2. Why does this validation class contain a query with NULLS?
  
  It's a legacy code. The where clause in list criteria (field
  validation class) looks very strange and has to be fixed.
  

Local fix

• Only workaround:
  To remove validation class but may cause issues.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Maximo users                                                 *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Performance problem with work order lookup in Create         *
  * Requisition application                                      *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply fix pack containing this fix.                          *
  ****************************************************************
  The lookup on the Work Order field in the Charge Information
  section of the Self Service -> Desktop Requisitions -> Create
  Requisition application takes a long time to load due to the SQL
  that is unnecessarily leading to a full table scan.
  

Problem conclusion

• SQL has been modified to improve the performance of this dialog.
  There is no need to check for null siteid since this is a
  required field.
  
  The fix for this APAR is contained in the following maintenance
  package:
  	 | release\fix pack | Interim Fix for Release 7.5.0.5 - BS
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Modules/Macros

• MAXIMO
  

Fix information

• Fixed component name

  PURCHASING

• Fixed component ID

  5724R46PU

Applicable component levels

• R750 PSY

     UP