Skip navigation

Index on field Parent Request ID02 (1000002706) on  AST:CMDB Associations

score 135
You have not voted. Below Review Threshold

Use case: When you reassign a workorder with a service ci to another group then you might experience some delay on saving the changes.

 

OOTB filter filter INT:ASIFND:ACT:UPDATEASSOCIATIONSTATUS_100_PAAA is triggering following query on AST:CMDB Associations form:

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))

 

The where condition in this query is

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

 

OOTB on form AST:CMDB Associations field 1000000204 is indexed however 1000002706  is not, which causes above query to do a full table scan.

On systems with large amounts of records in the form this will cause a performance issue.

In customers case the query lasted ~7,2 seconds with  ~2.000.000 records in AST:CMDB Associations.

Execution plan:

--------------------------------------------------------------------------------

| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |       |     4 |   140 | 24397  (13)| 00:00:02 |

|*  1 |  COUNT STOPKEY         |       |       |       |            |          |

|*  2 |   VIEW                 |       |     4 |   140 | 24397  (13)| 00:00:02 |

|   3 |    COUNT               |       |       |       |            |          |

|   4 |     VIEW               |       |     4 |    88 | 24397  (13)| 00:00:02 |

|   5 |      SORT ORDER BY     |       |     4 |   156 | 24397  (13)| 00:00:02 |

|*  6 |       TABLE ACCESS FULL| T1396 |     4 |   156 | 24396  (13)| 00:00:02 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM<1000000101)

   2 - filter("AR_ROWNUMBER_ALIAS$1"<1000000101 AND

              "AR_ROWNUMBER_ALIAS$1">0)

   6 - filter("T1396"."C1000002706"='WO0000000718266' OR

              "T1396"."C1000000204"='WO0000000718266')

 

Execution plan with custom index T1396_TEST on Column C1000002706

---------------------------------------------------------------------------------------------------------------

| Id  | Operation                                | Name               | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                         |                    |     4 |   140 |     6  (17)| 00:00:01 |

|*  1 |  COUNT STOPKEY                           |                    |       |       |            |          |

|*  2 |   VIEW                                   |                    |     4 |   140 |     6  (17)| 00:00:01 |

|   3 |    COUNT                                 |                    |       |       |            |          |

|   4 |     VIEW                                 |                    |     4 |    88 |     6  (17)| 00:00:01 |

|   5 |      SORT ORDER BY                       |                    |     4 |   156 |     6  (17)| 00:00:01 |

|   6 |       TABLE ACCESS BY INDEX ROWID BATCHED| T1396              |     4 |   156 |     5   (0)| 00:00:01 |

|   7 |        BITMAP CONVERSION TO ROWIDS       |                    |       |       |            |          |

|   8 |         BITMAP OR                        |                    |       |       |            |          |

|   9 |          BITMAP CONVERSION FROM ROWIDS   |                    |       |       |            |          |

|* 10 |           INDEX RANGE SCAN               | T1396_TEST         |       |       |     1   (0)| 00:00:01 |

|  11 |          BITMAP CONVERSION FROM ROWIDS   |                    |       |       |            |          |

|* 12 |           INDEX RANGE SCAN               | I1396_1000000204_1 |       |       |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter(ROWNUM<1000000101)

   2 - filter("AR_ROWNUMBER_ALIAS$1"<1000000101 AND "AR_ROWNUMBER_ALIAS$1">0)

  10 - access("T1396"."C1000002706"='WO0000000718266')

  12 - access("T1396"."C1000000204"='WO0000000718266')

 

 

Problematic filter seems to be triggered by various other filters:

HPD:INC:UpdateAssociations_101_PACT

WOI:WOI:UpdateAssociations_101_PACT

PBM:PBI:UpdateAssociations_101_PACT

PBM:PKE:UpdateAssociations_101_PACT

PBM:KDB:UpdateAssociations_101_PACT

so the delay in the query will affect multiple ITSM modules.

Comments

Vote history