2 Replies Latest reply on Feb 9, 2018 7:53 AM by Aman Mishra

    slowness adding public workinfo in ARS 9.1.04 - underly sql not efficient !  - you only see it if you have a lot of entries in HPD:Help Desk

    Jesus Diaz-Ropero Simon


      We've a undesiderable behaviour when we add public workinfo to an incident in ARS 9.1.04.

      It takes too long time to do it!  (more a 1 min.)

      In version ARS 8.1.02 it works ok.


      We've done the sql & filter analysis, and the guilty query is this:


      SELECT T2269.C1000005784, T2269.C3, T2269.C4, T2269.C5, T2269.C1000005791, T2269.C6, T2269.C1000000156, T2269.C7, T2269.C1000000150, T2269.C1000005782, T2269.C1000000151, T2269.C1000005783, T2269.C798001027, T2269.C798001028, T2269.C798001029, T2269.C798001030, T2269.C1000000396, T2269.C1000000652, T2269.C1000005261, T2269.C798001033, T2269.C1000002488, T2269.C303523900, T2269.C1000000169, T2269.C1000000162, T2269.C1000000163, T2269.C1000000161, T2269.C1000000164, T2269.C1000000218, T2269.C303497300, T2269.C303522900, T2269.C1000000217, T2269.C1000000215, T2269.C303519300, T2269.C301572000, T2269.C1000000251, T2269.C112, T2269.C303790700, T2269.C260000001, T2269.C1000000026, T2269.C1000000027, T2269.C1000005661, T2269.C1000000018, T2269.C1000000019, T2269.C301368700, T2269.C1000000022, T2269.C1000000020, T2269.C1000000010, T2269.C301572200, T2269.C1000000014, T2269.C303356300, T2269.C1000000002, T2269.C1000000000, T2269.C1000000001, T2269.C1000000004, T2269.C798014000, T2269.C1000000056, T2269.C1000003896, T2269.C240001005, T2269.C240001002, T2269.C240001003, T2269.C1000000063, T2269.C1000003890, T2269.C1000003891, T2269.C1000000048, T2269.C1000000560, T2269.C1000003888, T2269.C1000000561, T2269.C1000003889, T2269.C1000003894, T2269.C1000003895, T2269.C1000000564, T2269.C1000003892, T2269.C303497400, T2269.C1000003893, T2269.C1000000298, T2269.C179, T2269.C1000000300, T2269.C1000000035, T2269.C1000000036, T2269.C301571900, T2269.C303758300, T2269.C1000000082, T2269.C1000000080, T2269.C1000003664, B2269.C300615200, B2269.CO300615200, B2269.CC300615200, T2269.C1000000074, T2269.C1000003662, T2269.C1000000079, T2269.C1000003663, T2269.C1000000064, T2269.C1000000065, T2269.C60900, T2269.C200000012, T2269.C200000003, T2269.C200000007, T2269.C200000006, T2269.C200000005, T2269.C200000004, T2269.C301394438, T2269.C1000000099, T2269.C301572100, T2269.C1 FROM T2269 LEFT JOIN B2269 ON (((T2269.E0 = B2269.E0) OR (COALESCE(T2269.E0, B2269.E0) IS NULL)) AND ((T2269.E1 = B2269.E1) OR (COALESCE(T2269.E1, B2269.E1) IS NULL))) WHERE ((T2269.E0 = 'INC000003204305') AND (T2269.E1 = 'INC000003204305'))


      It's related to the form HPD:IncidentInterface, and is triggered by the filter:



      FYI, in ARS v8 the query that ARS submit to oracle, is different, and much more efficient, it does not involve the "left join", and it's like this:


      SELECT C1,C3,C4,C5,C6,C7,C112,C179,C60900,C200000003,C200000004,C200000005,C200000006,C200000007,C200000012,C240001002,C240001003,C240001005,C260000001,0,C301368700,C301394438,C301571900,C301572000,C301572100,C301572200,C303356300,C303497300,C303497400,C303519300,C303522900,C303523900,C303758300,C303790700,C1000000000,C1000000001,C1000000002,C1000000004,C1000000010,C1000000014,C1000000018,C1000000019,C1000000020,C1000000022,C1000000026,C1000000027,C1000000035,C1000000036,C1000000048,C1000000056,C1000000063,C1000000064,C1000000065,C1000000074,C1000000079,C1000000080,C1000000082,C1000000099,C1000000150,C1000000151,C1000000156,C1000000161,C1000000162,C1000000163,C1000000164,C1000000169,C1000000215,C1000000217,C1000000218,C1000000251,C1000000298,C1000000300,C1000000396,C1000000560,C1000000561,C1000000564,C1000000652,C1000002488,C1000003662,C1000003663,C1000003664,C1000003888,C1000003889,C1000003890,C1000003891,C1000003892,C1000003893,C1000003894,C1000003895,C1000003896,C1000005261,C1000005661,C1000005782,C1000005783,C1000005784,C1000005791,C798001027,C798001028,C798001029,C798001030,C798001033,C798014000,C798014001,C798014010,C798014012,C798014013 FROM T2269 WHERE E0 = 'INC000004095863' and E1 = 'INC000004095863'





      ** This behaviour is only visible if you have a lot of entries in HPD:Help Desk, for ex. 3M ;  take this in account in order to test it, otherwise you won't see the bug **


      I attach the execution plan of the query. You'll see the full scan over T2262 & B2262, that are pointing HPD:Help Desk (with more than 3M rows)


      #    operation    optios    obj_name    mode    cost    bytes    cardinality

      0    SELECT STATEMENT            ALL_ROWS    420799    399720024    159378

      1    NESTED LOOPS    OUTER            420799    399720024    159378

      2    NESTED LOOPS                5    2353    1

      3    TABLE ACCESS    BY INDEX ROWID    T2262    ANALYZED    3    50    1

      4    INDEX    UNIQUE SCAN    IT2262    ANALYZED    2        1

      5    TABLE ACCESS    BY INDEX ROWID    T2262    ANALYZED    2    2303    1

      6    INDEX    UNIQUE SCAN    IT2262    ANALYZED    1        1

      7    VIEW                420794    24703590    159378

      8    HASH JOIN                420794    18328470    159378

      9    TABLE ACCESS    FULL    T2262    ANALYZED    205465    7968950    159379

      10    HASH JOIN                214277    10359635    159379

      11    TABLE ACCESS    FULL    T2262    ANALYZED    205465    5100128    159379

      12    TABLE ACCESS    FULL    B2262    ANALYZED    1659    105220401    3188497



      We think that the way ARS9.1.04 builds this query, is not efficient in large environments, and potencially could affect the forms that are managed with workfow using the "interfaces" (the self-join forms), in the way our example shows for HPD:IncidentInterface.


      Is someone experiencing sth. similar?


      We think that the use of COALESCE in the outer joint is not good idea, because implies that the indexes are not used. If you use this:

      ... FROM T2269 LEFT JOIN B2269 ON (((T2269.E0 = B2269.E0)) AND ((T2269.E1 = B2269.E1)))   ....

      instead of

      ... FROM T2269 LEFT JOIN B2269 ON (((T2269.E0 = B2269.E0) OR (COALESCE(T2269.E0, B2269.E0) IS NULL)) AND ((T2269.E1 = B2269.E1) OR (COALESCE(T2269.E1, B2269.E1) IS NULL)))  ....

      it works fine.

      But it's built in the calls ARS9.1.04 do to Oracle, so we can't  avoid this yet.



      Relevant Environment desc.:

      - ARS 9.1.04

      - Oracle 11.2.04

      - ITSM 8.0