Performance issue when updating Work Order status. Missing indexes for the field "Parent Request ID" (1000002706) on the form AST:CMDB Associations.

Version 5
    Share:|

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    Remedy IT Service Management Suite


    COMPONENT:

    BMC Service Request Management


    APPLIES TO:

    ARS / ITSM / SRM: 9.x ,18.x and 19.02



    PROBLEM:

    ARS / ITSM / SRM: 9.x ,18.x and 19.02

    The issue can happen to an environment where Work Orders are more than 2 millions.

    STEPS TO REPRODUCE:
    1. Login into Remedy with work order user.
    2. Create a new Work Order.
    3. Change Work Order status.

    ACTUAL RESULTS:
    Work Order status update takes more than 22 sec.

    EXPECTED RESULTS:
    Work Order status update should not take more than 22 sec.
     


    CAUSE:

    Missing indexes for the field "Parent Request ID" (1000002706) on the form AST:CMDB Associations.


    SOLUTION:

    The reported problem is caused by filter INT:ASIFND:ACT:UPDATEASSOCIATIONSTATUS_100_PAAA which is performing a push field on the AST:CMDB Associations form which is causing a full table scan.

    Log trace for long running query:

    <FLTR>  "INT:ASIFND:ACT:UPDATEASSOCIATIONSTATUS_100_PAAA" (0)
    <FLTR>  > 0 : Push Fields -> "AST:CMDB Associations"
    <FLTR>  > Filter INT:ASIFND:ACT:UPDATEASSOCIATIONSTATUS_100_PAAA
    <SQL >  >  */ SELECT * FROM (SELECT AR_SQL_Alias$1.*, rownum as AR_RowNumber_Alias$1 FROM (SELECT T1396.C301569500, T1396.C1 FROM T1396 WHERE ((T1396.C1000000204 = 'WO0000000718266') OR (T1396.C1000002706 = 'WO0000000718266')) ORDER BY T1396.C1 ASC NULLS FIRST ) AR_SQL_Alias$1) WHERE ((AR_RowNumber_Alias$1 > 0) AND (AR_RowNumber_Alias$1 < 1000000101) AND (rownum < 1000000101))


    When investigating the execution plan of the query on database directly notice the the where condition of the query:

    (T1396.C1000000204 = 'WO0000000718266') OR (T1396.C1000002706 = 'WO0000000718266')

    OOTB there are indexes on 1000000204 and 1000000205 field but no index on field 1000002706. So, due to the OR condition the query is doing a full table scan.
    When adding indexing on the field 1000002706 helped improved the performance.

    Engineering confirmed that this will be an enhancement on performance tuning.
    BMC IDEA: https://communities.bmc.com/ideas/20370


    Article Number:

    000169678


    Article Type:

    Solutions to a Product Problem



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles