9 Replies Latest reply on Sep 28, 2018 9:19 AM by Scott Hartwick

    While submitting Incidents takes almost 2 minutes to save

    Ramakrishnan Govindaraj

      Greetings Everyone!

       

      We are facing performance issues while submitting Incidents. It takes around 2 mins to save a incident.  We have around 200 active service targets against Incident module. Does this number of SLA's  cause performance issues to the system? Could you please share your suggestion.  Currently we are on the Remedy 8.1 sp1

       

      Thanks in advance!

        • 1. Re: While submitting Incidents takes almost 2 minutes to save
          Sidhdesh Punaskar

          do you have api sql filter logs while submitting incident?

          • 2. Re: While submitting Incidents takes almost 2 minutes to save
            Carl Wilson

            Hi,

            yes, it is well known that SLA's cause issues with tickets.

            You will need to do some performance tuning on the queries run when the Targets are attached/checked.

             

            Cheers

            Carl

            • 4. Re: While submitting Incidents takes almost 2 minutes to save
              Ramakrishnan Govindaraj

              Hi Scott,

               

               

              Yes, we are using Oracle 11. Please share if you have any suggestion.

               

              Thanks,

              • 5. Re: While submitting Incidents takes almost 2 minutes to save
                Ramakrishnan Govindaraj

                Hi Sidhdesh,

                 

                Thanks for your showing interest on this !

                 

                I have attached the user side log for now. Could you please let me know if server side log needed.

                 

                Thanks,

                • 6. Re: While submitting Incidents takes almost 2 minutes to save
                  Mohammad Rehman

                  Are you creating the Service Request on Incident creation? Would you please share the screen shot of the Incident Rules? Also turn on the Filter logs and SQL logs to capture the delay where it is taking more time. It could be custom workflow as well as on incident creation create service request and auto assignment routing. Logs will better explain where the issue lies.

                  • 7. Re: While submitting Incidents takes almost 2 minutes to save
                    Sidhdesh Punaskar

                    The sql queries for each qualification in filter is taking almost .16 sec for executing the query. And there are numerous workflows in remedy to save incident. It does not look performance issue for running sql for specific entry in remedy db but overall performance issue with DB.

                     

                    I find some KBs with oracle db performance with 9.x

                    KnowledgeArticle - BMC

                    • 8. Re: While submitting Incidents takes almost 2 minutes to save
                      Scott Hartwick

                      Do you have an ardb.cfg file in the Conf directory? Are  you on Linux or windows?

                       

                      I found conflicts will will cause the time delay issue you describe when using NLS_SORT='BINARY_CI' and NLS_COMP='BINARY versus LINGUISTIC sort.  Which can be set at the instance level with the ardb.cfg.

                      • 9. Re: While submitting Incidents takes almost 2 minutes to save
                        Scott Hartwick

                        Hi Ramakrishnan,

                         

                        Have you solved the 2 minute delay performance issue on saving an Incident?

                         

                        Here is how I was able to isolate the issue and solved it for our Oracle 11g DB/Linux AR Server.  I'll give you a shortcut method on how to test it so see if it's an NLS_COMP issue.

                         

                        After exhausting all the SQL, API, Filter, etc. logs, we asked the Oracle DBA to run an AWR report. Both AWR and Remedy logs showed a specific point where the Query sent just hung there for a minute, while nothing appeared to be happening. If you can get the same pattern, this this may be helpful.

                         

                        We started looking at the NLS_SORT and NLS_COMP Instance Settings. Definitions below are from Oracle Docs online.

                         

                        You can change the Instance settings for the Oracle Database under the Database Tab in the

                        AR System Administration Console -> System -> General ->Server Information -> Database -> Database Configuration File:

                         

                        DB-Options: (file in conf or cfg directory on AR Server)

                        Ours looked like this:

                         

                        {

                        name:NLS_SORT

                        value:BINARY_CI

                        name:NLS_COMP

                        value:LINGUISTIC

                        }

                         

                        I changed it to this and the 1 minute pause disappeared completely after a restart:

                         

                        {

                        name:NLS_SORT

                        value:BINARY_CI

                        name:NLS_COMP

                        value:BINARY_CI

                        }

                         

                        Keep in mind, you are changing ONLY the INSTANCE setting for this startup. The database GLOBAL Settings could be anything the DBA chooses to set them to. So your GLOABAL settings may be the opposite of  these and you will have to reverse what I provided as an example.

                         

                        I suggest you add these settings in a text file if they aren't already there. Restart, test, Change the settings as displayed above, Restart. Test. let us know what you learned.

                         

                        NLS_SORT='BINARY_CI' and NLS_COMP='BINARY' versus LINGUISTIC sort.  Which can be set at the instance level with the ardb.cfg.

                         

                        NLS_COMP

                        PropertyDescription
                        Parameter typeString
                        SyntaxNLS_COMP = { BINARY | LINGUISTIC | ANSI }
                        Default valueBINARY
                        ModifiableALTER SESSION
                        BasicNo

                         

                        NLS_COMP specifies the collation behavior of the database session.

                        Values:

                        • BINARY
                            • Normally, comparisons in the WHERE clause and in PL/SQL blocks is binary unless you specify the NLSSORT function.
                        • LINGUISTIC
                            • Comparisons for all SQL operations in the WHERE clause and in PL/SQL blocks should use the linguistic sort specified in the NLS_SORT parameter. To improve the performance, you can also define a linguistic index on the column for which you want linguistic comparisons.
                        • ANSI
                            • A setting of ANSI is for backwards compatibility; in general, you should set NLS_COMP to LINGUISTIC

                         

                        See Also:

                        Oracle Database Globalization Support Guide for more information on setting this parameter

                         

                        NLS_SORT

                         

                        PropertyDescription
                        Parameter typeString
                        SyntaxNLS_SORT = { BINARY | linguistic_definition }
                        Default valueDerived from NLS_LANGUAGE
                        ModifiableALTER SESSION
                        Range of valuesBINARY or any valid linguistic definition name

                         

                        NLS_SORT specifies the collating sequence for ORDER BY queries:

                         

                        • If the value is BINARY, then the collating sequence for ORDER BY queries is based on the numeric value of characters (a binary sort that requires less system overhead).
                        • If the value is a named linguistic sort, sorting is based on the order of the defined linguistic sort. Most (but not all) languages supported by the NLS_LANGUAGE parameter also support a linguistic sort with the same name.

                         

                          • Note:Setting NLS_SORT to anything other than BINARY causes a sort to use a full table scan, regardless of the path chosen by the optimizer. BINARY is the exception because indexes are built according to a binary order of keys. Thus the optimizer can use an index to satisfy the ORDER BY clause when NLS_SORT is set to BINARY. If NLS_SORT is set to any linguistic sort, the optimizer must include a full table scan and a full sort in the execution plan.

                         

                        You must use the NLS_SORT operator with comparison operations if you want the linguistic sort behavior.See Also:

                         

                         

                        GOOD LUCK!

                        Scott.

                        2 of 2 people found this helpful