1 2 Previous Next 22 Replies Latest reply on Aug 2, 2017 1:07 PM by Brian Stone

    Overview Console Taking 6-7 Seconds In Loading

    Rahul Priyadarshy
      Share:|

      Hi Experts,

      I need some input on this. We are on 7.6.04 SP4 System . I am trying to improve the performance of Loading of OverView Cosnole.

      My Finding using Logs (API+SQL)

       

      Mon Aug 31 2015 07:51:25.0620  /+GLEWF  ARGetListEntryWithFields -- schema SHR:ARDBC_OverviewConsole from Mid-tier

       

      /* Mon Aug 31 2015 07:51:31.0780 */-GLEWF        OK

       

      In between these statement SQL query executes to fetch records from various ITSM Forms and i see non of  queries are not taking more than 1.8 Sec.

       

      Few More Input overview Console is loaded with 300+ records. Records in System are Never Archived. I am also attaching the Log Snippets for above 6 Seconds.

       

      As per BMC KB we have already added additional index for Helpdesk and ChG forms. Also Private queues are also added.

       

      Any Input will be very Appreciable.

       

      Regards

      RP

        • 1. Re: Overview Console Taking 6-7 Seconds In Loading

          Together all the SQLs takes 5.8 sec and are mostly due to following API calls.

             

          APIFormTime
          ARGetListEntryWithFieldsHPD:Help Desk1453
          ARGetListEntryWithFieldsCHG:Infrastructure Change1250
          ARGetListEntryWithFieldsHPD:Help Desk1235
          ARGetListEntryWithFieldsTMS:Task1156
          ARGetListEntryWithFieldsTMS:Task672
          ARGetListEntryWithFieldsWOI:WorkOrder31
          ARGetListEntryWithFieldsWOI:WorkOrder16

           

          I don't know why the Get List Entry was executed twice for all the forms except Change form. May be this is how the vendor form works. The way I would proceed is consider where condition of one of form, lets say, Help Desk, in which C1000000716,C230000009,C4,C7 fields are used EVERY time. so probability having composite index on these fields make sense. OR you may try executing this SQL manually from SQL console, keep removing one of these fields at a time from where clause and check which is that column causing trouble.

           

          Also, returning 300 records indicate the search was wide, try with narrow search result if it reduces the time? What was value of 'Show' field ?

           

          I had the same issue while back even though there was proper index. I was informed perform archiving.

           

          Hope this would help.

           

          Thanks

          Anandsagar

          ARSKit Studio - Powerful multi-feature Application for Remedy | Free Chrome Extension for Remedy Web Debug

          • 2. Re: Overview Console Taking 6-7 Seconds In Loading
            Rahul Priyadarshy

            @

            • 3. Re: Overview Console Taking 6-7 Seconds In Loading
              Ravindrakumar Rodge

              BMC has an overview console fix available for performance issue on 7.6.04.

              Also a plugin comes into the picture called ARSYS. ARDBC. APPQUERY.

              Try increasing the threads for plugin and also increase plugin loop back

              threads.

              But lookin at time taken I feel its not that slow provided there is not

              much data in database.

               

              Regards,

              Ravindrakumar

              • 4. Re: Overview Console Taking 6-7 Seconds In Loading
                Rahul Priyadarshy

                @ And @

                Plugin-ARDBC-Threads: 8 24

                Plugin-Loopback-RPC-Socket: 390626

                 

                These settings are already in Place , Just Checked.

                 

                More Inputs Please--

                 

                I will keep you posted- Help desk form seems to be area of more Concern( On Live Its not in Good Shape)

                 

                Regards

                RP

                • 5. Re: Overview Console Taking 6-7 Seconds In Loading
                  Ravindrakumar Rodge

                  Has this been recently observed or it's occurring since long?

                  Is it occurring for all users or specific users?

                  Have you checked for application preference?

                  What is the setting for server side table chunk size n max entries per

                  query setting?

                  Check with the fields that are in where clause if those are already indexed

                  at database?

                  Also check with storage space and input output operations are occurring

                  frequently which are causing delay.

                  Also has all database related settings already applied like snapshot

                  isolation, is read committed, max degree of parallelism.

                   

                  Check with bmc support for overview console fix.

                   

                  Regards,

                  Ravindrakumar

                  On 31-Aug-2015 11:09 PM, "Rahul Priyadarshy" <forums@developer.bmc.com>

                  • 6. Re: Overview Console Taking 6-7 Seconds In Loading
                    Evan Ruble

                    Sounds to me like at the very least, inefficient SQL queries are being performed.  At worst, you may be performing table scans.  I think it would be helpful to see the same log for the Production environment.  I don't think there's enough info in the Dev log to conclusively say which query is causing the performance issues in Prod.  From there, we can start investigating why individual queries are taking too long.  We'll likely need to add an index to resolve the issue.

                     

                    -ER

                    • 7. Re: Overview Console Taking 6-7 Seconds In Loading
                      Rahul Priyadarshy

                      @

                      Response is below

                       

                      Has this been recently observed or it's occurring since long?-- Since Long I joined the assignment recently.

                      Is it occurring for all users or specific users?-- Almost all

                      Have you checked for application preference?-- I Checked for my self and seems its fine there.

                      What is the setting for server side table chunk size n max entries per query setting?---> 100 and 2000

                      Check with the fields that are in where clause if those are already indexed at database?--> From Form Property i can see its indexed. I am waiting for DB access for More Play

                      Also check with storage space and input output operations are occurring frequently which are causing delay--> Storage Seems fine but I am not sure about I/O Going on at Storage Level.

                       

                      Also has all database related settings already applied like snapshot isolation, is read committed, max degree of parallelism.---> I hope but can not confirm at this point.


                      BMC Support Ticket we will raise -

                       

                      Regards

                      RP

                      • 8. Re: Overview Console Taking 6-7 Seconds In Loading
                        Ravindrakumar Rodge

                        Try setting chunk size to 1000 so there will be less read operations and

                        data will be readily available in cache.

                         

                        I recommend to check with storage level io operations as I have recently

                        observed that too many operation at storage level has lead to big

                        performance issue. Open few more ports at storage level.

                         

                        Regards,

                        Ravindrakumar

                        On 31-Aug-2015 11:42 PM, "Rahul Priyadarshy" <forums@developer.bmc.com>

                        • 9. Re: Overview Console Taking 6-7 Seconds In Loading
                          Evan Ruble

                          Rahul,

                           

                          What are T2070, T1920, and T1277?  In the Dev log, all of the time was spent on those three forms (except .0470 seconds).  Based on the queries, they appear to be joins/unions.

                           

                          -ER

                          • 10. Re: Overview Console Taking 6-7 Seconds In Loading
                            Rahul Priyadarshy

                            @

                             

                            T2070 --> HPD:Help Desk Form

                            T1920 --> CHG:Infrastructure Form

                            T1277--> TMS:Taks Form

                             

                             

                            Regards

                            RP

                            • 11. Re: Overview Console Taking 6-7 Seconds In Loading
                              Evan Ruble

                              That's curious.  I don't have a 7.6.04 environment anymore, so I don't have a perfect environment to compare to, but I thought the query should have been run against SHR:Union_OverviewConsole.  Based on what you've provided, it looks like the query is being run against each of the base forms, rather than the join/union.

                               

                              I tested the query against HPD:Help Desk in my environment, and was able to replicate the problem (on Oracle rather than SQL).  It took some 7-8 seconds to complete the query.  The execution plan is hideous, which makes me think even more that it's being run against the wrong form.  Is this environment customized?

                               

                              -ER

                              • 12. Re: Overview Console Taking 6-7 Seconds In Loading
                                Rahul Priyadarshy

                                @

                                Is this environment customized?--> I dont think its Customized to run against base form. but i will look as i am pretty new to this environment (2-3 days only).

                                 

                                I do not see the name which listed in 7.6.04 env here is the screenshot. I see few as Overlay, i will explore more.

                                 

                                SC.png

                                 

                                Regards

                                RP

                                • 13. Re: Overview Console Taking 6-7 Seconds In Loading
                                  Ganesh Gore
                                  My memory says it's something related to APPQUERY PLUGIN in 7.604 version and to resolve this we did some server configuration changes. Issue occurs mainly to the users who are part of many number of support groups. (You can test and confirm the behaviour with user who is part of single support group and user having more than 5 support group. Capture SQL+plugin Logs for this test and post here.
                                   Archive is always a best option to improve the performance but I know it will take some time to implement the archival process if it is already not there. Till that time you can suggest user to select 'show' from my selected group option on the console. I know it will help you! 
                                  #tips & #tricks
                                  • 14. Re: Overview Console Taking 6-7 Seconds In Loading

                                    The backend form of main table of overview console was replace from SHR:ARDBC_OverviewConsole to SHR:Union_OverviewConsole in 8.1.

                                     

                                    When my environment was upgraded from 7604 to 81, I noticed this change and because of which we face performance issue, therefore I think the vendor form (SHR:ARDBC_OverviewConsole) is faster than view form (SHR:Union_OverviewConsole), at least as per my environment.

                                     

                                    @All, I am not expert in plugin thread configuration, but if i observe from log is, over 90% of time is consumed by SQLs, and less then 10% by plugin or other area. By thing logic, I think it is more of tuning with Database, SQL where clause and indexes and less with plugin.

                                    Rahul, may be you can confirm once you are able to run the same query manually.

                                     

                                     

                                    Thanks

                                    Anandsagar

                                    ARSKit Studio - Powerful multi-feature Application for Remedy | Free Chrome Extension for Remedy Web Debug

                                    1 2 Previous Next