1 2 Previous Next 15 Replies Latest reply on Jul 17, 2019 12:57 PM by Stefan Hall

    Poor Smart Reporting performance probably due to AR System SQL generation

    Stefan Hall
      Share This:

      Hi Experts,

      to focus more on AR SQL generation, I have created this spin off of We want to start, but our Smart Reporting is very slow ((goes more in the direction of missing caching).

      ---

      We want to use Smart Reporting in production environment, but actually the performance is too bad and it means "show stopper" for us. We urgently need your support as AR experts.

       

      System (briefly)

      • dedicated SmartReporting server with 4 cpu and 12GB
      • dedicated AR Reporting Server with 4CPU and 16GB
      • Tomcat currently can use 6GB
      • 76k incident tickets
      • Test case: OOB Incident Dashboard

       

      Problem

      Opening the Smart Reporting OOB "Incident Dashboard" takes a very long time for non "AR Administrator" users, or is not possible at all.
      Every diagram takes up to 60+ sec, every time you open the dashboard. This is not fun for any user and cannot be used in this way.

      Sample user and his authorization groups

      User has some modul permissions, one support group, 2 companies and 2 functional groups (I think a normal use case )

      Result

      We can't stress our live environment, so here "only" the values from our QS environment with a few fewer tickets. But the problem still becomes very clear, I think.

      With each ticket and each additional module permission, support group, functional role it gets worse and 70k tickets aren't really much now, are they?

      • New RLS enabled
        SQL Log shows runtimes for each diagram of the dashboard of more than 30 seconds with parameter "Disable-New-RLS-Implementation: F"
      • New RLS disabled

      Our Analyse

      • Checking the user permission in field 112 takes a very long time, if you remove this field from the permissions, you get SQL runtimes of about 1 second.
      • A descending sort order of the "OR (J0.C112 LIKE '%;-20032;%') OR (J0.C112 LIKE '%;-20003;%')" WHERE Clause generates SQL runtimes of 2.2 seconds (Five times faster!!!) because the support groups/companies are in the permission fields and not the module or role permissions of the users.
        OOB sort order is module or role permissions (first), support groups/companies (last)

       

      Conclusion

      • Unfortunately the sort order in the AR, but should!? And now?
      • How did you get the performance under control or do the users have no module permissions, support groups etc.?
        • 1. Re: Poor Smart Reporting performance probably due to AR System SQL generation
          Carl Wilson

          Hi Stefan,

          what DB are you using?

           

          I have recently moved a customer to a Replicated DB for Smart Reporting due to the performance impact it was having on their production system (Oracle, Case Insensitive, Infrastructure issue, etc) and this made a big difference to performance.

          There were also a couple of other "tweaks" specific to Oracle, but I am sure that some are also applicable to MSSQL.

           

          Cheers

          Carl

          • 2. Re: Poor Smart Reporting performance probably due to AR System SQL generation
            Marek Ceizel

            Hi Stefan,

             

            Does the Server Statistic record these Long Running SQLs ? (or it doesn't as it comes from SmartReporting).

            If yes, then just take the select statement and run it in SQL Client software.

             

            If you get long responses, then some DB Experts should take a look what is wrong....

             

             

            regards

            Marek

            • 3. Re: Poor Smart Reporting performance probably due to AR System SQL generation
              Stefan Hall

              Hi Carl,

              In fact, we use Oracle 11.2 with functional indexes for case insensitive. The DB doesn't have a load problem at the time of testing, only very few users. An additional read only DB is planned, but will take some time. During the test period, there was only one user (ITSM & SMartReporting) and no integration policy, so the productive DB should be able to do this.

               

              I'd like to hear more about your tweaks.

               

              Hi Marek,

              I don't think I understand your question. The SQL is always finally sent via the AR server, which completes the necessary RLS part.

              It was exactly this test that we did and together with our DBA we determined that a descending sorting would be much better than the OOB ascending sorting.

               

              Exciting, isn't it? Could be beneficial to any call across the ITSM application, especially the consoles. Unfortunately, you can't follow that up yourself and BMC support hasn't been helpful yet.

              • 4. Re: Poor Smart Reporting performance probably due to AR System SQL generation
                Stefan Hall

                Anyone got any specific leads? I have reserved a few extra test hours for Monday, it would be a shame if they remained unused, right

                 

                Carl Wilson

                Marek Ceizel

                Asif Bhat

                @All other

                 

                We have to go live, but we can't and have no further ideas. Very unpleasant situation.

                Where are the Smart Reporting / AR System experts?

                • 5. Re: Poor Smart Reporting performance probably due to AR System SQL generation
                  Carl Wilson

                  Hi Stefan,

                  for this particular customer, we moved them on to a dedicated Load Balanced Smart Reporting setup that talks to a replicated DB.

                  The Production DB could not take the strain of reporting and user load.

                  The setup uses 2 Smart Reporting servers, one AR Server and the replicated DB.  It was noted during testing that the CPU load for Smart Reporting was quite high unless both servers were being utilised, so might be prevalent to increase the CPU allocation for the Smart Reporting server (application) if using only one server.

                  The DB had tuning done in regards to things that were observed in the AWR reports (memory allocations, etc) and configuration surrounding control files and storage.

                  There is also a "read" based parameter that is recommended in the Wiki to be set for the DB for use with Smart Reporting that was implemented (but cannot seem to find the reference for it).

                   

                  Cheers

                  Carl

                  2 of 2 people found this helpful
                  • 6. Re: Poor Smart Reporting performance probably due to AR System SQL generation
                    Andreas Mitterdorfer

                    I checked on my testbox where i loaded 100k incidents prior testing, but I do not see such a poor load time.

                    The incident dashboard loads in somewhat below 5-7seconds, each of the sqls lasts ~0.8 seconds on ARS side, total ARS time is ~3.5 secs for the testuser whom I added to 6 groups. I've attached the plan for one of the statements and the arloganalyzer results, maybe its of some help.

                    • 7. Re: Poor Smart Reporting performance probably due to AR System SQL generation
                      Uwe Ryczek

                      Did you tested without "Administrator" and "unrestricted access" permissions? (the sql is not the same, the whole permission check is skipped)

                      Do you have some values in the 112 and 60900 field?

                      3 of 3 people found this helpful
                      • 8. Re: Poor Smart Reporting performance probably due to AR System SQL generation
                        Stefan Hall

                        Hi Andreas Mitterdorfer

                        Uwe is absolutely right, with these "super-right" it is also fast with us. What did your test look like?

                         

                        BTW, It's not easy to link you, you have three accounts here?

                        • 9. Re: Poor Smart Reporting performance probably due to AR System SQL generation
                          Andreas Mitterdorfer

                          II didn't use an admin account for testing and the user also had access restrictions to one company.

                          If you review the sql in xxx_explain_plan.txt you'll see the group ids & the NLS parameter for explain plan aswell.

                           

                          Thanks for the hint, I wasn't aware that I had 3 accounts.

                          • 10. Re: Poor Smart Reporting performance probably due to AR System SQL generation
                            Uwe Ryczek

                            Hi Andreas,

                             

                            our SQL' look like this one and it takes nearly 10 seconds

                            As you can see the fields 112,60900,60901 and 60989 (we use ITSM 9.104) are checked, and with a user how belongs to some support groups...this thing grows up.

                             

                            If the sql is generated differently (with decending group id's) it only takes 2 seconds. We suggested BMC to add a configuration parameter to control the SQL generation...but they are playing the "send me log files game" with us.

                             

                            /* Di Dez 04 2018 14:29:49.3510 */

                            SELECT * FROM (SELECT AR_SQL_Alias$1.*, rownum as AR_RowNumber_Alias$1 FROM (SELECT DISTINCT J0.C1000000251 select$1, J0.C303497300 select$2, J0.C7 select$3, COUNT(J0.C1000000161) FROM T2355 J0 WHERE ((J0.C3 >= 1298983752) AND (J0.C3 <= 1543930189) AND (((J0.C2 = 'a0088665') OR (J0.C4 = 'a0088665')) OR exists ( select /*+ NO_EXPAND  */ 1 from dual where ((J0.C112 LIKE '%;''a0088665'';%') OR (J0.C112 LIKE '%;0;%') OR (J0.C112 LIKE '%;-20032;%') OR (J0.C112 LIKE '%;-20003;%') OR (J0.C112 LIKE '%;-20000;%') OR (J0.C112 LIKE '%;-1098;%') OR (J0.C112 LIKE '%;803;%') OR (J0.C112 LIKE '%;804;%') OR (J0.C112 LIKE '%;1058;%') OR (J0.C112 LIKE '%;13006;%') OR (J0.C112 LIKE '%;13007;%') OR (J0.C112 LIKE '%;13010;%') OR (J0.C112 LIKE '%;20000;%') OR (J0.C112 LIKE '%;20003;%') OR (J0.C112 LIKE '%;20007;%') OR (J0.C112 LIKE '%;20012;%') OR (J0.C112 LIKE '%;20031;%') OR (J0.C112 LIKE '%;20032;%') OR (J0.C112 LIKE '%;20055;%') OR (J0.C112 LIKE '%;20077;%') OR (J0.C112 LIKE '%;20078;%') OR (J0.C112 LIKE '%;20216;%') OR (J0.C112 LIKE '%;20225;%') OR (J0.C112 LIKE '%;20302;%') OR (J0.C112 LIKE '%;20313;%') OR (J0.C112 LIKE '%;20315;%') OR (J0.C112 LIKE '%;20316;%') OR (J0.C112 LIKE '%;20403;%') OR (J0.C112 LIKE '%;71002;%') OR (J0.C112 LIKE '%;71052;%') OR (J0.C112 LIKE '%;71053;%') OR (J0.C112 LIKE '%;71062;%') OR (J0.C112 LIKE '%;1000000;%') OR (J0.C112 LIKE '%;1000001;%') OR (J0.C112 LIKE '%;1000002;%') OR (J0.C112 LIKE '%;1000004;%') OR (J0.C112 LIKE '%;1000000022;%') OR (J0.C112 LIKE '%;1000000157;%') OR (J0.C112 LIKE '%;1000000198;%') OR (J0.C112 LIKE '%;2000000001;%') OR (J0.C60900 LIKE '%;''a0088665'';%') OR (J0.C60900 LIKE '%;0;%') OR (J0.C60900 LIKE '%;-20032;%') OR (J0.C60900 LIKE '%;-20003;%') OR (J0.C60900 LIKE '%;-20000;%') OR (J0.C60900 LIKE '%;-1098;%') OR (J0.C60900 LIKE '%;803;%') OR (J0.C60900 LIKE '%;804;%') OR (J0.C60900 LIKE '%;1058;%') OR (J0.C60900 LIKE '%;13006;%') OR (J0.C60900 LIKE '%;13007;%') OR (J0.C60900 LIKE '%;13010;%') OR (J0.C60900 LIKE '%;20000;%') OR (J0.C60900 LIKE '%;20003;%') OR (J0.C60900 LIKE '%;20007;%') OR (J0.C60900 LIKE '%;20012;%') OR (J0.C60900 LIKE '%;20031;%') OR (J0.C60900 LIKE '%;20032;%') OR (J0.C60900 LIKE '%;20055;%') OR (J0.C60900 LIKE '%;20077;%') OR (J0.C60900 LIKE '%;20078;%') OR (J0.C60900 LIKE '%;20216;%') OR (J0.C60900 LIKE '%;20225;%') OR (J0.C60900 LIKE '%;20302;%') OR (J0.C60900 LIKE '%;20313;%') OR (J0.C60900 LIKE '%;20315;%') OR (J0.C60900 LIKE '%;20316;%') OR (J0.C60900 LIKE '%;20403;%') OR (J0.C60900 LIKE '%;71002;%') OR (J0.C60900 LIKE '%;71052;%') OR (J0.C60900 LIKE '%;71053;%') OR (J0.C60900 LIKE '%;71062;%') OR (J0.C60900 LIKE '%;1000000;%') OR (J0.C60900 LIKE '%;1000001;%') OR (J0.C60900 LIKE '%;1000002;%') OR (J0.C60900 LIKE '%;1000004;%') OR (J0.C60900 LIKE '%;1000000022;%') OR (J0.C60900 LIKE '%;1000000157;%') OR (J0.C60900 LIKE '%;1000000198;%') OR (J0.C60900 LIKE '%;2000000001;%') OR (J0.C60901 LIKE '%;''a0088665'';%') OR (J0.C60901 LIKE '%;0;%') OR (J0.C60901 LIKE '%;-20032;%') OR (J0.C60901 LIKE '%;-20003;%') OR (J0.C60901 LIKE '%;-20000;%') OR (J0.C60901 LIKE '%;-1098;%') OR (J0.C60901 LIKE '%;803;%') OR (J0.C60901 LIKE '%;804;%') OR (J0.C60901 LIKE '%;1058;%') OR (J0.C60901 LIKE '%;13006;%') OR (J0.C60901 LIKE '%;13007;%') OR (J0.C60901 LIKE '%;13010;%') OR (J0.C60901 LIKE '%;20000;%') OR (J0.C60901 LIKE '%;20003;%') OR (J0.C60901 LIKE '%;20007;%') OR (J0.C60901 LIKE '%;20012;%') OR (J0.C60901 LIKE '%;20031;%') OR (J0.C60901 LIKE '%;20032;%') OR (J0.C60901 LIKE '%;20055;%') OR (J0.C60901 LIKE '%;20077;%') OR (J0.C60901 LIKE '%;20078;%') OR (J0.C60901 LIKE '%;20216;%') OR (J0.C60901 LIKE '%;20225;%') OR (J0.C60901 LIKE '%;20302;%') OR (J0.C60901 LIKE '%;20313;%') OR (J0.C60901 LIKE '%;20315;%') OR (J0.C60901 LIKE '%;20316;%') OR (J0.C60901 LIKE '%;20403;%') OR (J0.C60901 LIKE '%;71002;%') OR (J0.C60901 LIKE '%;71052;%') OR (J0.C60901 LIKE '%;71053;%') OR (J0.C60901 LIKE '%;71062;%') OR (J0.C60901 LIKE '%;1000000;%') OR (J0.C60901 LIKE '%;1000001;%') OR (J0.C60901 LIKE '%;1000002;%') OR (J0.C60901 LIKE '%;1000004;%') OR (J0.C60901 LIKE '%;1000000022;%') OR (J0.C60901 LIKE '%;1000000157;%') OR (J0.C60901 LIKE '%;1000000198;%') OR (J0.C60901 LIKE '%;2000000001;%') OR (J0.C60989 LIKE '%;''a0088665'';%') OR (J0.C60989 LIKE '%;0;%') OR (J0.C60989 LIKE '%;-20032;%') OR (J0.C60989 LIKE '%;-20003;%') OR (J0.C60989 LIKE '%;-20000;%') OR (J0.C60989 LIKE '%;-1098;%') OR (J0.C60989 LIKE '%;803;%') OR (J0.C60989 LIKE '%;804;%') OR (J0.C60989 LIKE '%;1058;%') OR (J0.C60989 LIKE '%;13006;%') OR (J0.C60989 LIKE '%;13007;%') OR (J0.C60989 LIKE '%;13010;%') OR (J0.C60989 LIKE '%;20000;%') OR (J0.C60989 LIKE '%;20003;%') OR (J0.C60989 LIKE '%;20007;%') OR (J0.C60989 LIKE '%;20012;%') OR (J0.C60989 LIKE '%;20031;%') OR (J0.C60989 LIKE '%;20032;%') OR (J0.C60989 LIKE '%;20055;%') OR (J0.C60989 LIKE '%;20077;%') OR (J0.C60989 LIKE '%;20078;%') OR (J0.C60989 LIKE '%;20216;%') OR (J0.C60989 LIKE '%;20225;%') OR (J0.C60989 LIKE '%;20302;%') OR (J0.C60989 LIKE '%;20313;%') OR (J0.C60989 LIKE '%;20315;%') OR (J0.C60989 LIKE '%;20316;%') OR (J0.C60989 LIKE '%;20403;%') OR (J0.C60989 LIKE '%;71002;%') OR (J0.C60989 LIKE '%;71052;%') OR (J0.C60989 LIKE '%;71053;%') OR (J0.C60989 LIKE '%;71062;%') OR (J0.C60989 LIKE '%;1000000;%') OR (J0.C60989 LIKE '%;1000001;%') OR (J0.C60989 LIKE '%;1000002;%') OR (J0.C60989 LIKE '%;1000004;%') OR (J0.C60989 LIKE '%;1000000022;%') OR (J0.C60989 LIKE '%;1000000157;%') OR (J0.C60989 LIKE '%;1000000198;%') OR (J0.C60989 LIKE '%;2000000001;%'))))) GROUP BY J0.C303497300, J0.C7, J0.C1000000251) AR_SQL_Alias$1) WHERE ((AR_RowNumber_Alias$1 > 0) AND (AR_RowNumber_Alias$1 < 2102) AND (rownum < 2102))

                            /* Di Dez 04 2018 14:29:59.3320 */ OK

                            1 of 1 people found this helpful
                            • 11. Re: Poor Smart Reporting performance probably due to AR System SQL generation
                              Andreas Mitterdorfer

                              It's not descending on my system either, however i didn't have all the permissions, only more groups.

                               

                              Out of curiosity: Have you checked, wheter the order in the sql is the same order as in grouplist field on user form and if so, if reordering the grouplist in user form, changes the sql?

                              Edit: Forget the idea, the select is sorted already (except for the 0), but ascending not descending.

                              • 12. Re: Poor Smart Reporting performance probably due to AR System SQL generation
                                Uwe Ryczek

                                Yes we checked, we also changed the sort order of the "Group" form...no better results....so we decided to open a support case

                                • 13. Re: Poor Smart Reporting performance probably due to AR System SQL generation
                                  Carl Wilson

                                  Hi,

                                  not sure if this is relevant, but worth a look:

                                   

                                  Remedy 9.x, Oracle Case Insensitivity and SQL with LIKE conditions

                                   

                                  Cheers

                                  Carl

                                  2 of 2 people found this helpful
                                  1 2 Previous Next